Detailed description of oracle user creation and permission settings, and detailed description of oracle

Detailed description of oracle user creation and permission settings, and detailed description of oracle

Permission:

Create session

Create table

Unlimited tablespace

Connect

Resource

Dba

Example:

# Sqlplus/nolog

SQL> conn/as sysdba;

SQL> create user username identified by password

SQL> grant dba to username;

SQL> conn username/password

SQL> select * from user_sys_privs;

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.

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

3. Manage Users

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.

GRANT

Create session, create any table, create any view, create any index, create any procedure,

Alter session, alter any table, alter any index, alter any procedure,

Drop any table, drop any view, drop any index, drop any procedure,

Select any table, select any sequence,

Insert any table, update any table, DELETE ANY TABLE

TO username;

Grant create session to zhangsan; // grant the zhangsan user the permission to create a session, that is, the login permission.

Grant unlimited tablespace 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.

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.

Example: revoke drop any view, drop any table from scott;

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

VII. User table of the operation table

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.

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