Oracle user permission management method

Sys; // System Administrator with the highest Permissions
System; // local administrator with the next high permission
Scott; // common user. The default password is tiger and is not unlocked by default.
Sys; // System Administrator with the highest Permissions
System; // local administrator with the next high permission
Scott; // common user. The default password is tiger and is not unlocked by default.
2. Login

Sqlplus/as sysdba; // log on to the sys account
Sqlplus sys as sysdba; // same as above
Sqlplus scott/tiger; // log on to normal user scott
Sqlplus/as sysdba; // log on to the sys account
Sqlplus sys as sysdba; // same as above
Sqlplus scott/tiger; // log on to normal user scott
3. Manage Users

Create user zhangsan; // under the Administrator account, create the user zhangsan
Alert user scott identified by tiger; // modify the password
Create user zhangsan; // under the Administrator account, create the user zhangsan
Alert user scott identified by tiger; // modify the password
4. Grant Permissions
1. The default normal user scott is not unlocked by default and cannot be used. The new user does not have any permissions and must be granted permissions.

/* Administrator authorization */
Grant create session to zhangsan; // grant the zhangsan user the permission to create a session, that is, the login permission.
Grant unlimited session to zhangsan; // grant the zhangsan user the permission to use the tablespace.
Grant create table to zhangsan; // grant the table creation permission
Grante drop table to zhangsan; // grant the table deletion permission
Grant insert table to zhangsan; // insert table permission
Grant update table to zhangsan; // Modify table Permissions
Grant all to public; // This is important. grant all permissions to all users (public)
/* Administrator authorization */
Grant create session to zhangsan; // grant the zhangsan user the permission to create a session, that is, the login permission.
Grant unlimited session to zhangsan; // grant the zhangsan user the permission to use the tablespace.
Grant create table to zhangsan; // grant the table creation permission
Grante drop table to zhangsan; // grant the table deletion permission
Grant insert table to zhangsan; // insert table permission
Grant update table to zhangsan; // Modify table Permissions
Grant all to public; // This is important. grant all permissions to all users (public)
2. oralce is more rigorous in permission management. Common users cannot access each other by default and must be authorized to each other.

/* Oralce strictly manages permissions, and common users cannot access each other by default */
Grant select on tablename to zhangsan; // grant the zhangsan user the permission to view the specified table.
Grant drop on tablename to zhangsan; // grant the table deletion permission.
Grant insert on tablename to zhangsan; // grant the insert permission
Grant update on tablename to zhangsan; // grant the table modification permission.
Grant insert (id) on tablename to zhangsan;
Grant update (id) on tablename to zhangsan; // grant the insert and modify permissions for specific fields in the specified table. Note that only insert and update are allowed.
Grant alert all table to zhangsan; // grant permissions to any table of the zhangsan user alert
/* Oralce strictly manages permissions, and common users cannot access each other by default */
Grant select on tablename to zhangsan; // grant the zhangsan user the permission to view the specified table.
Grant drop on tablename to zhangsan; // grant the table deletion permission.
Grant insert on tablename to zhangsan; // grant the insert permission
Grant update on tablename to zhangsan; // grant the table modification permission.
Grant insert (id) on tablename to zhangsan;
Grant update (id) on tablename to zhangsan; // grant the insert and modify permissions for specific fields in the specified table. Note that only insert and update are allowed.
Grant alert all table to zhangsan; // grant permissions to any table of the zhangsan user alert
5. revoke permissions

The basic syntax is the same as grant. The keyword is revoke.
The basic syntax is the same as grant. The keyword is revoke.
6. View Permissions

Select * from user_sys_privs; // view all permissions of the current user
Select * from user_tab_privs; // view the table permissions of the users used
Select * from user_sys_privs; // view all permissions of the current user
Select * from user_tab_privs; // view the table permissions of the users used
VII. User table of the operation table

/* Add the user name before the table name, as shown below */
Select * from zhangsan. tablename
/* Add the user name before the table name, as shown below */
Select * from zhangsan. tablename

8. Permission Transfer
That is, user A grants permissions to user B, and user B can grant operation permissions to user C. The command is as follows:

Grant alert table on tablename to zhangsan with admin option; // keyword with admin option
Grant alert table on tablename to zhangsan with grant option; // The keyword with grant option is similar to admin.
Grant alert table on tablename to zhangsan with admin option; // keyword with admin option
Grant alert table on tablename to zhangsan with grant option; // The keyword with grant option is similar to admin.
9. Roles
A role is a set of permissions. You can assign a role to a user.

Create role myrole; // create a role
Grant create session to myrole; // grant the create session permission to myrole
Grant myrole to zhangsan; // grant the role of myrole to THE zhangsan user
Drop role myrole; delete a role
/* However, some permissions cannot be granted to the role, such as the unlimited tablespace and any keywords */