Oracle Create user and permission settings

Oracle user creation and permissions settings

Permissions:

Create session

CREATE table

Unlimited tablespace

Connect

Resource

Dba

Cases:

#sqlplus/nolog

Sql> Conn/as sysdba;

Sql>create user username identified by password//create users and give passwords

Sql> Grant DBA to username;

Sql> Conn Username/password

Sql> select * from User_sys_privs;

We will start with the creation of Oracle User Rights table, then explain the general actions such as landing, so that you have a deep understanding of the Oracle User Rights table.

First, create

sys;//system Administrator with the highest privileges

system;//Local Administrator, sub-high privilege

scott;//Normal user, password default is tiger, default unlocked

Second, landing

Sqlplus/as sysdba;//Login SYS account

Sqlplus Sys as sysdba;//ibid.

Sqlplus scott/tiger;//Landing Ordinary user Scott

Third, manage users

Create user vashon;//under the Administrator account, creating the users Vashon

Alert user Scott identified by tiger;//change password

Four, grant permissions

1, the default ordinary user Scott is not unlocked by default, cannot do that use, the new user does not have any permissions, must be granted permissions

　　

Grant create session to Vashon;//Grant Vashon user permission to create session, that is, login permission

Grant Unlimited tablespace to Vashon;//Grant Vashon user permission to use tablespace

Grant CREATE table to Vashon;//grant permissions to create tables

Grant drop table to Vashon;//grant permission to delete table

Grant drop any table to Vashon ;//   Note : You need to specify "any" even if the above is logged in and authorized by the administrator but will also prompt for insufficient permissions

Grant Insert table to Vashon;//Permissions to insert Tables

Grant insert any table to vashon;// Note : You need to specify "any" even if the above is logged in and authorized by the administrator but will also prompt for insufficient permissions

Grant Update table to Vashon;//Modify Permissions for table

Grant Update any table to vashon;// Note : You need to specify "any" even if the above is logged in and authorized by the administrator but will also prompt for insufficient permissions

Grant all to public;//this is more important, grant all permissions (all) for all users (public)

2, Oralce on the rights management more rigorous, ordinary users are also the default can not access each other, need to authorize each other

　　

Grant SELECT on tablename to Vashon;//Grant Vashon user permission to view the specified table

Grant Select any table to Vashon;//grant the user permission to view all tables under this user

Grant drop on TableName to Vashon;//grant permission to delete table

Grant insert on TableName to Vashon;//Grant INSERT permission

Grant update on TableName to Vashon;//grant Modify table Permissions

Grant Insert (ID) on tablename toVashon;

Grant Update (ID) on tablename toVashon;//grant Insert and Modify permissions to specific fields of the specified table, note that only insert and update

Grant alert all table to Vashon;//Grant Vashon user alert permission to any table

V. Revocation of Rights

Basic syntax with GRANT, keyword revoke

Vi. Viewing permissions

SELECT * from user_sys_privs;//View all permissions for the current user

SELECT * from user_tab_privs;//View the user's permissions on the table

Vii. Table of users of the action table

　　

SELECT * from Vashon. tablename

Viii. Transfer of rights

That is, user a grants permission to B,b to grant the permission of the operation to C again, with the following command:

Grant alert table on TableName toVashon with admin option;//keyword with admin option

Grant alert table on TableName toVashon with Grant option;//keyword with GRANT option effect similar to admin

Nine, the role

A role is a collection of permissions that can grant a role to a user

Create role myrole;//creating roles

Grant create session to myrole;//grants permission to create session Myrole

Grant Myrole to Vashon;//Grant Vashon user Myrole role

Drop role myrole; remove roles

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Oracle Create user and permission settings