An analysis of the management method of the Oracle user permission table

We will start from creating an Oracle user permission table, and then explain the login and other general actions to give you an in-depth understanding of the Oracle user permission table.

I. Create

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 */

The Oracle user permission table is described here.

1. Oracle 11g R2 highlights the RAC technology as scheduled
2. Security Features of Oracle 11g transparent Data Encryption
3. Auto-tuning in Oracle 11g
4. Model Management for new features of Oracle 11g
5. Automatic database maintenance task management in Oracle 11g R1