Create users and permission settings in Oracle, create permissions in oracle

Create users and permission settings in Oracle, create permissions in oracle
Oracle user creation and permission settings

Permission:

Create session

Create table

Unlimited tablespace

Connect

Resource

Dba

Example:

# Sqlplus/nolog

SQL> conn/as sysdba;

SQL> create user username identified by password // create a user and assign the 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 vashon; // create a user vashon under the Administrator account

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 to vashon; // grant the vashon user the permission to create a session, that is, the login permission.

Grant unlimited tablespace to vashon; // grant the table space permission to the vashon user.

Grant create table to vashon; // grant the table creation permission.

Grant drop table to vashon; // grant the table deletion permission.

Grant drop any table tovashon; // Note: even if the above table is logged on as an administrator and authorized, the system prompts that the permission is insufficient. You must specify "any"

Grant insert table to vashon; // insert table permission

Grant insert any table to vashon; // Note: even if the above table is logged on and authorized as an administrator, the system prompts that the permission is insufficient. You must specify "any"

Grant update table to vashon; // Modify table Permissions

Grant update any table to vashon; // Note: even if the above table is logged on as an administrator and authorized, the system prompts that the permission is insufficient. You must specify "any"

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 vashon; // grant the vashon user the permission to view the specified table.

Grant select any table to vashon; // grant the user the permission to view all tables of the current user.

Grant drop on tablename to vashon; // grant the table deletion permission.

Grant insert on tablename to vashon; // grant the insert permission

Grant update on tablename to vashon; // grant the table modification permission.

Grant insert (id) on tablename tovashon;

Grant update (id) on tablename tovashon; // 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 vashon; // grant permissions to any table of the vashon user alert

5. revoke permissions

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

VII. User table of the operation table

　　

Select * from vashon. 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 tovashon with admin option; // Keyword: with admin option

Grant alert table on tablename tovashon 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 vashon; // grant the role of myrole to THE vashon user

Drop role myrole; delete a role

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.