Oracle user, permission, role management

This article is transferred from: Http://dusong.blog.51cto.com/158065/139284 Oracle permission settings
I. Permission classification:
System permission: The system specifies the user's permission to use the database. (System permissions are for users ). Object permission: the access permission of a certain user to tables or views of other users. (For tables or views ). Ii. system permission management:
1. system permission classification:
DBA: it has all the privileges and is the highest system permission. only DBA can create a database structure. Resource: users with resource permissions can only create entities, but cannot create database structures. Connect: users with the connect permission can only log on to Oracle, create entities, and create database structures. For normal users: grant connect and resource permissions.
For DBA management users: grant the connect, resource, and DBA permissions. 2. system permission authorization command:
[System permissions can only be granted by DBA users: SYS and System (only these two users are allowed at the beginning)]
Authorization command: SQL> grant connect, resource, DBA to username 1 [, username 2]...; [Normal users can have the same user permissions as system through authorization, but they can never have the same permissions as sys users. system users can also be revoked.] Example:

SQL> connect system/Manager
SQL> create user user50 identified by user50;
SQL> grant connect, resource to user50;

Query the permissions of a user:

SQL> Select * From Dba_role_privs;
SQL>Select * From Dba_sys_privs;
SQL> Select * From Role_sys_privs;

Delete A User: SQL> drop User Username cascade; // Add cascade to delete all the users and their created items. 3. pass system permissions:
If the with admin option is added, the obtained permissions can be passed. SQL> grant connect, resorce to user50 with admin option; // You can pass the obtained permissions. 4. Revoke system permissions: only DBA users can revoke system permissions.
Command: SQL> revoke connect, resource from user50; The system permissions are continuously connected, that is, a grants B permissions, and B grants C permissions. If a revokes B permissions, C permissions are not affected. system permissions can be revoked across users, that is, a can directly revoke the permissions of user C. Iii. Object permission management
1. Object permission classification: Select, update, insert, alter, index, delete, all // All includes all Permissions

Execute // Stored Procedure execution permission
User01:
SQL> Grant Select , Update, insert on product to user02;
SQL> grant all on product to user02;
User02:
SQL> Select * From User01.product;

// At this time, user02 queries user_tables, excluding the table user01.product. However, if all_tables is checked, it can be found because it can be accessed.3. Grant table operation permissions to all users:
SQL> grant all on product to public; // public indicates all users. The All permission here does not include drop.[Object permission data dictionary]:

SQL> Select Owner, table_name From All_tables; // Tables that can be queried by users
SQL> Select Table_name From User_tables;// Table created by the user
SQL> Select Grantor, table_schema, table_name, privilege From All_tab_privs; // Authorized tables (authorized)
SQL> Select Grantee, owner, table_name, privilege From User_tab_privs; // Table granted permissions (granted permissions)

  4. DBA users can operate any base table of all users (no authorization required, including deletion ):
DBA User:

SQL> Create Table stud02.product (
ID number ( 10 ),
Name varchar2 ( 20 ));
SQL> drop table stud02.emp;
SQL> Create Table stud02.employee
As
Select * From Scott. EMP;

 
5. Transfer object permissions (with grant option ):User01:
SQL> GrantSelect, Update on product to user02 with grant option;//User02 is granted the permission and can be passed.

Recycling:

User01:
SQL> revoke Select , Update on Product From User02; // All permissions passed will be lost.

1. Create a user's profile fileSQL> Create profile student limit//Student is the resource file name
Failed_login_attempts3//Number of Logon failures of locked users
Password_lock_time5//Number of days the user is locked
Password_life_time30//Password availability days
 

 

 

2. Create a user

SQL> create user username identified by password
Default tablespace
Temporary tablespace
Profile
Quota integer/unlimited on tablespace;

Example:

SQL> create user acc01
Identified by acc01 // If the password is a number, enclose it in double quotation marks.
Default Tablespace account
Temporary tablespace temp
Profile Default
Quota 50 m on account;
SQL> grant connect, resource to acc01;

 [*] Query the user's default tablespace and temporary tablespace
SQL> select username, default_tablespace, temporary_tablespace from dba_users; [*] Query system resource file name:
SQL> select * From dba_profiles;

Resource files are similar to tables, which are saved in the database once created.

SQL>Select Username, profile, default_tablespace, temporary_tablespace From Dba_users;
SQL> Create profile common limit
Failed_login_attempts 5
Idle_time 5 ;
SQL> alter user acc01 profile common;

 

 

3. modify a user

SQL> alter user User Name
Identified Password
Default tablespace
Temporary tablespace
Profile
Quota integer/unlimited on tablespace;

 
1. Modify the password:

SQL> alter user acc01 identified " 12345 " ;

 2. Modify the user's default tablespace:

SQL> alter user acc01 Default Tablespace users;

 3. Modify the user's temporary tablespace

SQL> alter user acc01 temporary tablespace temp_data;

 4. Force the user to change the password:SQL> alter user acc01 password expire;

 

5. Lock the user SQL> alter user acc01 account Lock ; // Lock
SQL> alter user acc01 account unlock; // Unlock

 

 

4. delete a user

SQL> drop User username; // the user has not created any entity
SQL> drop User Username cascade; // delete all users and their created entities