18. Oracle Role

First, Introduction
A role is a collection of commands related to permissions, and the primary purpose of using roles is to simplify the management of permissions.
Assume that there are user a,b,c in order for them to have the following permissions
1. Connect to the database
2. Select,insert,update on the Scott.emp table.
If you take a direct authorization operation, you need to authorize 12 times.
Because of the 12 authorization operation, so it is more troublesome! What to do?
If we adopt a role, we can simplify:
First, creat session,select on scott.emp, insert on scott.emp, update on SCOTT.EMP grant the role, and then grant the role to the A,b,c user, so that three authorizations can be done.

Second, the role is divided into pre-defined and custom roles two categories

III. Pre-defined roles
Predefined roles are the roles provided by Oracle, each of which is used to perform some specific administrative tasks, and we describe the commonly used predefined roles connect, resource, DBA.
1), Connect role
The Connect role has most of the permissions that a general application developer needs, and when a user is established, in most cases, as long as the user is granted the Connect and resource roles, what system permissions does the connect role have?
Create cluster
Create DATABASE link
Create session
Alter session
CREATE table
CREATE view
Create sequence

2), Resource role
The resource role has other permissions that the application developer needs, such as setting up stored procedures, triggers, and so on. It is important to note that the resource role implies unlimited tablespace system permissions.
The resource role contains the following system permissions:
Create cluster
Create Indextype
CREATE table
Create sequence
Create type
CREATE PROCEDURE
Create Trigger

3), DBA role
The DBA role has all system permissions, and with the admin option option, the default DBA user is SYS and system, and they can grant any system permissions to other users. Note, however, that the DBA role does not have the privileges of SYSDBA and sysoper (starting and shutting down the database).

Iv. Custom Roles
1, as the name implies is their own definition of the role, according to their own needs to define. Typically a DBA is established, and if it is established with another user, the system permissions that have the Create role are required. You can specify authentication methods (no validation, database validation, and so on) when you establish a role.
1), Build role (not verified)
If the role is a public role, the role can be established in a non-verifiable manner.
Create role name not identified;
2), establishing roles (database validation)
In this way, the role name and password are stored in the database. When the role is activated, the password must be supplied. When this role is established, it is required to provide a password.
Create role name identified by password;

2. Role authorization
1), Give role authorization
There is not much difference between granting permissions to a role and authorizing a user, but note that the unlimited Tablespace and object permissions of the system permissions cannot be granted to the role with the GRANT option option.
Sql> Conn System/oracle;
Sql> Grant create session to Role name with admin option
Sql> Conn scott/[email protected];
Sql> Grant Select on scott.emp to role name;
Sql> grant INSERT, UPDATE, delete on scott.emp to role name;
Through the above steps, the role is authorized.

2), assigning roles to a user
The General allocation role is done by the DBA, and if the role is to be assigned as a different user, the user must have system permissions for the grant any role.
Sql> Conn System/oracle;
Sql> Grant role name to Blake with admin option;
Because I gave the WITH admin option option, Blake can assign the role assigned to it by the system to another user.

3. Delete a role
Use drop role, which is typically performed by a DBA, if other users require that the user have drop any role system permissions.
Sql> Conn System/oracle;
sql> drop role name;
Question: If the role is deleted, does the user who is granted the role also have permissions in the previous role?
Answer: Do not have a

4. Show role Information
1), Show all characters
Sql> select * from Dba_roles;
2), display the system permissions that the role has
Sql> Select Privilege, admin_option from Role_sys_privs where role= ' role name ';
3), display the object permissions that the role has
Query the data dictionary view Dba_tab_privs to view the object permissions or the permissions of a column that the role has.
4), displays the role that the user has, and the default role
When connected to a database as a user, Oracle automatically activates the default role by querying the data dictionary view Dba_role_privs to display all the roles that a user has and the current default role.
Sql> Select Granted_role, default_role from dba_role_privs where grantee = ' user name ';

Five, fine access control
Granular access control refers to the ability of a user to use functions to implement more granular security access control. With granular access control, when the client issues an SQL statement (select,insert,update,delete), Oracle automatically appends the predicate (WHERE clause) to the SQL statement and executes the new SQL statement, with such control Allows different database users to return different data information when accessing the same table, such as:
User Scott Blake Jones
Strategy emp_access
database table emp
As shown in the policy emp_access, the user scott,black,jones can return different results when executing the same SQL statement.
For example: When performing a select ename from EMP, different results can be returned depending on the actual situation.