Oracle _ Role _ permission details

1. Oracle built-in role connect and resource Permissions

Grant connect, resource to user;

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

You can find it in dba_sys_privs (note that you must Log On As a dba ):
Select grantee, privilege from dba_sys_privs
Where grantee = 'resource' order by privilege;
========================================================== ==========
1. What is a role?
The permissions and users are described in the previous section. 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 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 ';
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 user a does not take effect, the permissions contained in user b1 are not owned by user,
Only when the role takes effect can the permissions in the role be applied to the user. The maximum number of valid roles is set by the MAX_ENABLED_ROLES parameter;
After a user logs on, oracle assigns all permissions directly to the user and permissions in the user's default role to the user .)
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.

========================================================== ================================
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.]

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.

Iii. Object permission management
1. Object permission classification: select, update, insert, alter, index, delete, all // all includes all permissions, execute // execute stored procedure Permissions

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.

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 ):
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;

4. Object permission transfer (with grant option ):
User01:
SQL> grant select, update on product to user02 with grant option; // get the permission and pass it.

5. Revoke object permissions:
User01:
SQL> Revoke select, update on product from user02; // all permissions passed will be lost.

Description
1) if you cancel the object permissions of a user, the same permissions of the users who use the with grant option to GRANT permissions are also revoked, that is to say, it is Cascade when authorization is canceled.