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

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 zhangsan;//under the Administrator account, creating the users Zhangsan

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 zhangsan;//Grant Zhangsan user permission to create session, that is, login permission

Grant Unlimited tablespace to zhangsan;//grants Zhangsan users permission to use tablespaces

Grant CREATE table to zhangsan;//grants permissions for creating tables

Grante drop table to zhangsan;//grant permission to delete tables

Grant Insert table to zhangsan;//permissions for inserting tables

Grant Update table to zhangsan;//permissions to modify tables

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 zhangsan;//Grant Zhangsan user permission to view the specified table

Grant drop on TableName to zhangsan;//granting permission to delete table

Grant insert on TableName to zhangsan;//permission to be inserted

Grant update on TableName to zhangsan;//granting permission to modify tables

Grant Insert (ID) on tablename to Zhangsan;

Grant Update (ID) on TableName to zhangsan;//grants insert and Modify permissions to specific fields of the specified table, note that only the INSERT and update

Grant alert all table to zhangsan;//grants Zhangsan 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 Zhangsan.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 to Zhangsan with admin option;//keyword with admin option

Grant alert table on TableName to Zhangsan 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 zhangsan;//the role of Zhangsan user Myrole

Drop role myrole; remove roles

Transferred from: http://www.cnblogs.com/shlcn/archive/2011/07/21/2112879.html

Oracle user creation and permissions settings