16th Oracle roles

Oracle role

1. What is a role?
In the previous sections, I described permissions and users. Slowly, you will find a problem: if a group of people have the same permissions they need, it will be 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.
The preceding is a typical use of a role. In fact, you only need to understand that a role is a set of permissions. The following sections describe the Oracle role in two departments.

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 ';

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.

2. 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;
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.