Oracle Database Management-manage permissions and Roles

Introduction:

When a new user is created, the user does not have any permissions and cannot perform any operations. If you want to perform a database operation to be determined, you must grant the system permission to it. If you want to access objects in other schemes, you must grant the object permission to it. Roles can be used to simplify permission management.

(1) role:A role can be granted system or object permissions.
Oracle has defined several roles in advance to view all roles:
SQL> select * From dba_roles;
Where:
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.
You can query sys_dbs and sys_privs to understand the permissions of various roles.
Eg:
For normal users: grant connect and resource permissions.

For DBA management users: grant the connect, resource, and DBA permissions.

(2) permissions:Indicates the right to execute specific types of SQL commands or access other solution objects, including system and object permissions.
A. System permissions:Description: The user's permissions on the database. It refers to the right to execute a specific type of SQL command. It is used to control one or more database operations that the user 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.
Commonly used:
Create session
Create procedure creation process, function, package
For example, the create table permission allows users to create tables, and the grant any privilege permission allows users to grant any system permissions.
View user system permissions:
SQL> select * From dba_sys_privs;
SQL> select * From all_sys_privs;
SQL> select * From user_sys_privs;

Grant system permission transfer:
Generally, the DBA grants the system permission. If other users grant the system permission, the user must have the system permission grant any privilege, it can have the with admin option.
Grant system permissions to the user name [with admin option]. With the with clause, you can grant the user the system permissions to other users.
Eg: When system grants Xiaoming permission to users:
Grant connect to Xiaoming with admin option // indicates that the user Xiaoming can pass the connect permission.

Revoke system permissions:
Generally, DBA is used to revoke system permissions. If other users revoke system permissions, this user must have the corresponding system permission and the option to grant the system permission (with admin option). revoke the system permission and use Revoke to complete the process.
Eg:
SQL> revoke create Session from *** (User) cascade recovery

B. object permissions:The user's permission to access/operate Data Objects of other users. Data Objects refer to tables, views, stored procedures, and triggers created by the user.
Commonly used include alter, delete, select, insert, and update.
View object permissions: You can use the data field view to display the metadata of a user or role. The view is dba_tab_privs.
Eg:
SQL> conn system/Manager
SQL> select distinct privilege from dba_tab_privs
Eg: You want Xiaoming users to view Scott's EMP table (object permissions)
SQL> conn Scott/Sheng
SQL> grant select on EMP to Xiaoming

The Xiaoming user is expected to modify Scott's EMP table.
SQL> grant update on EMP to Xiaoming

The Xiaoming user is expected to go to the EMP table of Scott with all permissions.
SQL> grant all on EMP to Xiaoming

Grant object permissions:
For object permissions, add with grant option.
Grant select on EMP to Xiaoming with grant option // meaning Xiaoming can pass Permissions
Revoke permission revoke
Scott wants to revoke the query permission of Xiaoming on the EMP table
Revoke select on EMP from Xiaoming