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 to Tiger, default unlocked

Second, landing

Sqlplus/as Sysdba; Login SYS Account

Sqlplus Sys as SYSDBA; Ditto

Sqlplus Scott/tiger; Login to General user Scott

Third, manage users

Create user Zhangsan; Under the Administrator account, create a user Zhangsan

Alert user Scott identified by Tiger; Change Password

Iv. granting of rights

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; Granting Zhangsan users permission to use tablespaces

Grant CREATE table to Zhangsan; Granting permissions to create tables

Grante drop table to Zhangsan; Granting permission to delete a table

Grant Insert table to Zhangsan; Permissions to insert Tables

Grant Update table to Zhangsan; Permissions to modify tables

Grant all to public; This is more important, grant all permissions (all) to 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 users permission to view the specified table

Grant drop on TableName to Zhangsan; Granting permission to delete a table

Grant insert on TableName to Zhangsan; Grant the Insert permission

Grant update on TableName to Zhangsan; Granting permissions to modify tables

Grant Insert (ID) on tablename to Zhangsan;

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

Grant alert all table to Zhangsan; Grant 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; To view the permissions used by the user 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; Create a role

Grant create session to Myrole; Grant Myrole permission to create session

Grant Myrole to Zhangsan; Roles granted to Zhangsan user Myrole

Drop role Myrole; Remove a role

This article is from a "a little" blog, make sure to keep this source http://pengai.blog.51cto.com/6326789/1976367

Oracle user creation and permissions settings