Oracle Create user and permission settings

                                                                Oracle Create user 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 the Oracle User Rights table and then explain general actions such as landing. Gives you 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;//Ordinary user, password feel 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. Create User Vashon

Alert user Scott identified by tiger;//change password

Four. granting 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 grant permissions

　　

Grant create session to Vashon;//Grant Vashon user permission to create session. That is, login permissions

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 : "Any" is required 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 : "Any" is required even if the above is logged in and authorized by the administrator but will also prompt for insufficient permissions

Grant Update table to Vashon;//ALTER TABLE permissions

Grant Update any table to vashon;// Note : Even though the above is logged in as an administrator and authorized, it will also prompt insufficient permissions. You need to specify "any"

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

2, Oralce on the rights management is more rigorous. Common users are also the default can not access to 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 ALTER TABLE permissions

Grant Insert (ID) on tablename toVashon;

Grant Update (ID) on tablename toVashon;//grants Insert and Change permissions to specific fields of the specified table. Note that it can only be insert and update

Grant alert all table to Vashon;//Grant Vashon user alert discretionary table permissions

V. Revocation of Rights

The basic syntax is the same as Grant,keyword for revoke

Vi. Viewing permissions

SELECT * from user_sys_privs;//view Current user full permissions

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. Commands such as the following:

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 collection of roles as 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

Oracle Create user and permission settings