17. Oracle Permissions

First, Introduction
This section focuses on how to manage permissions and roles in Oracle, and what the difference between permissions and roles is.
When a user is just established, the user does not have any permissions and cannot perform any action. If you want to perform a particular database operation, you must grant it permissions to the system, and if the user wants to access an object of another scheme, you must grant it permission to the object. To simplify the management of permissions, you can use roles. Here we will introduce in detail.

Second, the Authority
Permissions are the rights to execute a specific type of SQL command or to access other schema objects, including both system and object permissions.

Third, System permissions
1), System permissions refer to the right to execute a specific type of SQL command. It is used to control one or a set of database operations that a user can perform. For example, when a user has CREATE TABLE permissions, a table can be built in its schema, and a table can be built in any scenario when the user has the Create any table permission. Oracle provides more than 100 system permissions.
Commonly used are:
Create Session Connection Database
CREATE TABLE Build Tables
Create View build views
Create public synonym synonyms
Create PROCEDURE build process, function, package
Create TRIGGER Build Trigger
Create cluster build Cluster

2), Display system permissions
Oracle provides more than 100 system permissions, and the higher the Oracle version, the more system permissions are available, and we can query the data dictionary view System_privilege_map to show all system permissions.
SELECT * from System_privilege_map order by name;

3), Grant system permissions
In general, the grant of system permissions is done by the DBA, and if you use other users to grant system permissions, you are required to have system permissions for the grant any privilege. When granting system permissions, you can have the WITH ADMIN option option so that the user or role that is granted permission can also grant that system permission to other users or roles. To give you a quick understanding, let's illustrate:
1. Create two user ken,tom. They do not have any permissions at the initial stage, and if they log in they will give the wrong information.
Create user Ken identified by Ken;
2 Grant to user Ken
1). Grant create session, create table to Ken with admin option;
2). Grant CREATE view to Ken;
3 giving the user Tom authorization
We can grant Tom permission through Ken, because with admin option is added. Of course, we can authorize Tom through the DBA, and we'll use Ken to give Tom authorization:
1. Grant create session, create table to Tom;
2. Grant CREATE view to Ken; --ok? Not OK

4), Recovery system permissions
In general, the recovery system permission is done by the DBA, and if other users are to reclaim system privileges, the user must have the appropriate system permissions and the option to delegate system permissions (with admin option). The recycle system permissions are done using revoke. When the system permissions are reclaimed, the user cannot perform the appropriate action, but notice that the SYSTEM permission cascade recovery problem? [NOT CASCADE Recycle!] ]
System--------->ken---------->tom
(Create session) (Create session) (Create session)
Perform the following actions with system:
Revoke create session from Ken; --Do you think Tom can still log in?
Answer: Yes, can login

Iv. Object Permissions
1), Object permissions Introduction
Refers to the right to access other schema objects, the user can directly access their own schema object, but if you want to access other scenarios of the object, you must have permission to the object.
For example, a Smith user wants to access the Scott.emp table (Scott: scheme, EMP: Table)
Commonly used are:
Insert Add
Delete Deletes
Alter Modify
Select query
Index indexes
References references
Execute execution

2), Display object permissions
The data field view allows you to display the object permissions that a user or role has. View as Dba_tab_privs
Sql> Conn System/manager;
Sql> SELECT distinct privilege from Dba_tab_privs;
Sql> Select Grantor, owner, TABLE_NAME, privilege from DBA_TAB_PRIVS where grantee = ' BLAKE ';

3), Grant object permissions
Before Oracle9i, the grant of object permissions is done by the owner of the object, and if manipulated by another user, requires the user to have the corresponding (with GRANT OPTION) permission, starting with Oracle9i, the DBA user (Sys,system) You can grant object permissions on any object to other users. The Grant object permission is done with the grant command. Object permissions can be granted to users, roles, and public. When you grant permissions, you can delegate this permission to other users if you have the WITH GRANTOPTION option. However, be aware that the WITH GRANT option cannot be granted a role.
1.monkey user to manipulate the Scott.emp table, you must grant the appropriate object permissions
1). Hope Monkey can query the data of Scott.emp table, how to operate?
Grant SELECT on the EMP to monkey;
2). Hope Monkey can modify the Scott.emp table data, how to do?
Grant update on EMP to monkey;
3). Hope Monkey can delete scott.emp table data, how to do it?
Grant Delete on the EMP to monkey;
4). Is there a simpler way to assign all permissions to monkey at once?
Grant all on the EMP to monkey;

2. Can you have finer control over monkey access rights? (Grant column permissions)
1). Hope Monkey can only modify the Sal field of the Scott.emp table, how to do it?
Grant Update on EMP (SAL) to Monkey
2). Hope monkey can only query the ename,sal data of scott.emp table, how to operate?
Grant SELECT on EMP (ename,sal) to Monkey

3. Grant ALTER permission
If the black user wants to modify the structure of the SCOTT.EMP table, the Alter object permission must be granted
Sql> Conn Scott/tiger
Sql> Grant alter on EMP to Blake;
Of course, you can also use System,sys to do the job.
4. Grant Execute permission
Execute permission is required if the user wants to execute packages/procedures/functions for other scenarios.
For example, to allow Ken to execute Package dbms_transaction, execute permissions can be granted.
Sql> Conn System/manager
Sql> Grant execute on dbms_transaction to Ken;
5. Grant the index permission
If you want to index on a table in another scenario, you must have the Index object permission.
If you want Black to index on the Scott.emp table, give it the object permission for index
Sql> Conn Scott/tiger
Sql> Grant Index on scott.emp to Blake;
6. Using the WITH GRANT option
This option is used to delegate object permissions. However, this option can only be granted to the user and cannot be granted to the role
Sql> Conn Scott/tiger;
Sql> Grant SELECT on EMP-Blake with GRANT option;
Sql> Conn Black/shunping
Sql> Grant Select on Scott.emp to Jones;

4), Reclaim object permissions
In Oracle9i, the permission to retract an object can be done by the owner of the object, or by the DBA User (Sys,system).
The point here is that the user will not be able to execute the corresponding SQL command after the object permission is retracted, but be aware that the object's permissions will be cascaded back up? "Cascade Recycle"
such as: Scott------------->blake-------------->jones
Select on EMP Select on EMP Select on EMP
Sql> Conn scott/[email protected]
sql> revoke select on EMP from Blake
Please think, Jones can query scott.emp table data.
Answer: I can't find it. (Cascade recovery, not the same as system permissions, just 1 opposite)

17. Oracle Permissions