Oracle permission management

Permission refers to the right to execute specific types of SQL commands or access other solution objects, including system and object permissions.

1. System Permissions
System permission refers to the right to execute specific types of SQL commands. It is used to control one or more database operations that users can perform. For example, if you have the create table permission, you can create a table in the solution. If you have the create any table permission, you can create a table in any solution. Oracle provides more than 100 system permissions.
Common examples include:
Create session -- connection data
Create Table -- create a table
Create View -- create View
Create public synonym -- Synonym
Create procedure-creation process, function, and Package
Create trigger -- create a trigger
Create Cluster -- create Cluster
 
(1). Display System Permissions
Oracle provides more than 100 system permissions. The higher the version of Oracle, the more system permissions it provides. You can query the data dictionary view system_privilege_map,

All system permissions are displayed.
Example: Select * From system_privilege_map order by name;

(2). Grant System Permissions
Generally, the DBA grants the system permission. If other users grant the system permission, the user must have the system permission to grant any privilege. The with admin option can be included when the system permission is granted. In this way, the authorized user or role can grant the system permission to other users or roles. For your quick understanding, let's give an example:
A. Create two user tokens, Tom. They do not have any permissions in the initial stage. If they log on, an error message is displayed.
Example: create user Ken identfied by Ken;
B. authorize user Ken
Example: grant create session, create table to Ken with admin option;
Grant create view to Ken;
C. Authorize the user Tom
We can use the Ken to authorize Tom, because with admin option is added. Of course, you can also authorize Tom through dBA, and we will use Ken to authorize Tom:
Grant create session, create table to Tom;
Grant create view to Ken; -- OK? Not OK

(3) Revoke system Permissions
Generally, DBA is used to revoke system permissions. If other users revoke system permissions, the option that requires the user to have the corresponding system permissions and to grant the system permissions (with admin option ). Revoke system permissions using revoke.
After the system permission is revoked, the user cannot perform the corresponding operations. However, please note that the system permission is cascaded? [Not cascade recovery!]
System ---------> Ken ----------> Tom
(Create session)
Use System to perform the following operations:
Revoke create Session from Ken; -- Can Tom log on?
Answer: Yes. You can log on.

2. Object Permissions
This permission is used to access objects in other schemes. Users can directly access objects in their own schemes. However, to access objects in other schemes, they must have the object permission. For example, the Smith user wants to access the Scott. EMP table (SCOTT: solution, EMP: Table ).
Common examples include:
Alter -- modify
Delete -- delete
Select -- Query
Insert -- add
Update -- modify
Index -- Index
References -- Reference
Execute -- execute

 

(1). Display object permissions
You can use the data field view to display the object permissions of users or roles. The view is dba_tab_privs.
Conn system/manager;
Select distinct privilege from dba_tab_privs;
Select grantor, owner, table_name, privilege from dba_tab_privs where grantee = 'bucke ';

 

(2). Grant object permissions
Before Oracle9i, the object permission is granted by the object owner. If other users are used to perform operations, the user must have the corresponding (with grant option) permission, starting from Oracle9i, DBA users (sys, system) can grant object permissions on any object to other users. Grant object permissions using the grant command.
Object permissions can be granted to users, roles, and public. When granting permissions, if the with grant option is included, you can grant the permission to other users. However, note that the with grant option cannot be granted a role.

A. To operate the Scott. EMP table as a monkey user, the corresponding object permission must be granted.
1) How can I query the data in the Scott. EMP table with monkey?
Grant select on EMP to monkey;
2) How can I modify the table data of Scott. EMP with monkey?
Grant update on EMP to monkey;
3) What should I do if I want monkey to delete table data of Scott. EMP?
Grant delete on EMP to monkey;
4) Is there a simpler way to grant all permissions to monkey at a time?
Grant all on EMP to monkey;

 

B. More precise control over monkey access permissions. (Grant column permissions)
1) I hope monkey can only modify the Sal field of the Scott. EMP table. How can I do this?
Grant update on EMP (SAL) to monkey;
2) I hope monkey can only query the ename and Sal data of the Scott. EMP table. How can I perform this operation?
Grant select on EMP (ename, Sal) to monkey;

 

C. Grant alter permission
If the black user wants to modify the structure of the Scott. EMP table, the alter object permission must be granted.
Conn Scott/tiger;
Grant alter on EMP to Blake;
Of course, you can also use system and sys to complete this task.

 

D. Grant execute permission
If you want to execute packages/procedures/functions of other schemes, you must have the execute permission.
For example, you can grant the execute permission to allow the Ken to execute the package dbms_transaction.
Conn system/manager;
Grant execute on dbms_transaction to Ken;

 

E. Grant the index permission
If you want to create an index on a table in another solution, you must have the index object permission.
If you want black to create an index on the Scott. EMP table, grant the object permission to its index.
Conn Scott/tiger;
Grant index on Scott. EMP to Blake;

 

F. Use the with grant option Option
This option is used to grant object permissions. However, this option can only be granted to users, but not roles.
Conn Scott/tiger;
Grant select on EMP to blke with grant option;
Conn black/shunping
Grant select on Scott. EMP to Jones;

G. Revoke object permissions
In Oracle9i, the permission to revoke an object can be completed by the object owner, or by DBA users (sys, system.
It should be noted that after the object permission is revoked, the user cannot execute the corresponding SQL command, but it should be noted that the object permission will be revoked in cascade mode? [Cascading back

Receiving]
For example: Scott -------------> blke ------------> Jones
Select on EMP select on EMP
Conn Scott/tiger @ ACCP
Revoke select on EMP from Blake
Think about whether Jones can query the Scott. EMP table data.
Answer: I cannot find it (it is the opposite of the system permission ).