Oracle roles and permissions

Oracle built-in role connect and resource Permissions

========================================================== ================================

1. What is a role?
If a group of people have the same permissions they need, it is inconvenient to manage their permissions. Because you need to manage the permissions of each user in this group.
There is a good solution: role. A role is a set of permissions. If a role is assigned to a user, the user has all permissions in the role. The above problem is well handled. As long as the role is assigned to this group of users for the first time, you only need to manage the role. In fact, a role is a set of permissions. The following describes the Oracle role in two parts.

========================================================== ================================
Ii. system pre-defined roles
Predefined roles are common roles automatically created by the system after the database is installed. The following describes the predefined roles. You can use the following statement to query the permissions contained in a role:
SQL> select * From role_sys_privs where role = 'Role name'; (Note: The role here is automatically converted to uppercase by the system and capitalized during query)

Or

Select grantee, privilege from dba_sys_privs
Where grantee = 'Role name' order by privilege;

Example: view the permissions of the resource role

Select grantee, privilege from dba_sys_privs where grantee = 'resource' order by privilege;

 

Grantee privilege
----------------------------------
Resource create Cluster
Resource create indextype
Resource create Operator
Resource create procedure
Resource create Sequence
Resource CREATE TABLE
Resource create trigger
Resource create type

 

Grant the user a connect role and a resource role:
Grant connect, resource to user;
Permissions granted to the user after successful running:

Connect role: -- is the most basic and typical right granted to end users.
Alter session -- modify a session
Create Cluster -- create a cluster
Create database link -- create a database link
Create sequence -- create a sequence
Create session -- create a session
Create synonym -- create a synonym
Create View -- create View

Resource role: -- grant permissions to developers
Create Cluster -- create a cluster
Create procedure -- creation process
Create sequence -- create a sequence
Create Table -- create a table
Create trigger -- create a trigger
Create type -- create type

1. Connect, resource, DBA
These predefined roles are primarily intended for backward compatibility. It is mainly used for database management. Oracle recommends that you design your own database management and security permission planning, instead of simply using these pre-roles. In future versions, these roles may not be pre-defined.
2. delete_catalog_role, execute_catalog_role, select_catalog_role
These roles are mainly used to access data dictionary views and packages.
3. exp_full_database, imp_full_database
These two roles are used for data import and export tools.
4. aq_user_role, aq_administrator_role
AQ: Advanced query. These two roles are used for advanced Oracle query.
5. snmpagent
For Oracle Enterprise Manager and intelligent agent
6. recovery_catalog_owner
Creates a user with a recovery database. For information on database restoration, see Oracle document Oracle9i user-managed backup and recovery guide.
7. hs_admin_role
A dba using Oracle's heterogeneous services feature needs this role to access appropriate tables in the data dictionary.

========================================================== ================================
3. Manage Roles
1. Create a role
SQL> Create role role1;
2. Authorize the role
SQL> grant create any table, create procedure to role1;
3. Grant a role to the user
SQL> grant role1 to user1;
4. view the permissions contained in the role
SQL> select * From role_sys_privs where role = 'role1 ';
5. Create a role with a password (a password must be provided when a role with a password takes effect)
SQL> Create role role1 identified by password1;
6. Modify role: Password required
SQL> alter role role1 not identified;
SQL> alter role role1 identified by password1;
7. Set the role to take effect for the current user
(Note: What is the concept of role effectiveness? Assume that user a has three roles: B1, B2, and B3. If B1 does not take effect, the permissions contained in B1 are not owned by user A, and only the role takes effect, the permissions in the role apply to the user. The maximum number of valid roles is set by the max_enabled_roles parameter. After the user logs on, oracle grants all permissions directly granted to the user and permissions granted to the user in the user's default role .)
SQL> set role role1; // enable role1
SQL> set role, role2; // make role1 and role2 take effect
SQL> set role role1 identified by password1; // use role1 with a password
SQL> set role all; // all roles used by the user take effect.
SQL> set role none; // you can specify that all roles are invalid.
SQL> set role all roles t role1; // all roles except role1 take effect.
SQL> select * From session_roles; // view the roles that take effect for the current user.
8. Modify the specified user and set its default role.
SQL> alter user user1 default role role1;
SQL> alter user user1 default role all role t role1;
For more information, see Oracle reference documentation.
9. delete a role
SQL> drop role role1;
After a role is deleted, the user who previously used this role no longer has this role, and the corresponding permissions are lost.

========================================================== ================================
Iv. 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 ).

 

(1) 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;

Note:
1) if you use the with admin option to grant system permissions to a user, for all users granted the same permissions by this user, canceling the user's system permissions does not cascade the same permissions of these users.
2) 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.

 

(2) entity permission management
1. Object permission classification: Select, update, insert, alter, I ndex, delete, all // All includes all permissions, execute // execute stored procedure Permissions

Example: grant the select, update, and insert permissions of table EMP to user user02.
SQL> grant select, update, insert on EMP to user02;

 

2. 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 the user
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 table (authorized)
SQL> select grantee, owner, table_name, privilege from user_tab_privs; // The table that grants permissions (granted permissions)

 

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

 

4. Object permission transfer (with grant option) (same as system permission transfer)

 

5. Revoke object permissions

Note: If you cancel the object permissions of a user, the same permissions of these users will also be revoked for users who use the with grant option to grant permissions, that is to say, it is Cascade when authorization is canceled.

========================================================== ================================

5. Oracle user management
(1) create a user's profile file
SQL> Create profile student limit // student is the resource file name
Failed_login_attempts 3 // specify the number of Logon failures for the user to be locked 3
Password_lock_time 5 // specify the number of days the user is locked for 5
Password_life_time 30 // the number of days the specified password can be used for 30 days

(2) create a user
SQL> Create User Username
Identified by password // if the password is a number, enclose it in double quotation marks.
Default tablespace // the user's default tablespace
Temporary tablespace // temporary tablespace
Profile profile // specifies the user resource file
Quota integer/unlimited on tablespace;

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

SQL> select username, profile, default_tablespace, temporary_tablespace from dba_users;
// A resource file is similar to a table. Once created, it is saved in the database.
SQL> Create profile common limit
Failed_login_attempts 5
Idle_time 5;
// Change the user resource file
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 by "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) Deleting Users
SQL> drop User Username; // The user has not created any entity
SQL> drop User Username cascade; // delete all users and their created entities
* 1. The user currently being connected cannot be deleted.

(5) Monitoring users:
1. query user session information:
SQL> select username, Sid, serial #, machine from V $ session;
2. delete user session information:
SQL> alter system kill session 'sid, serial #';
3. query user SQL statements:
SQL> select user_name, SQL _text from V $ open_cursor;