Oracle Create user-given permissions

Oracle user creation and permission settings [reprint]

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

=====================================================>>

sys;//system Administrator with the highest privileges
system;//Local Administrator, sub-high privilege
scott;//Normal user, password default is tiger, default unlocked
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
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
Create user zhangsan;//under the Administrator account, creating the users Zhangsan
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 be granted permissions

/* Administrator Authorization */
Grant Create session to zhangsan;//Grant Zhangsan user permission to create session, that is, login permission
Grant Unlimited session to zhangsan;//grants Zhangsan user 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)
/* Administrator Authorization */
Grant Create session to zhangsan;//Grant Zhangsan user permission to create session, that is, login permission
Grant Unlimited session to zhangsan;//grants Zhangsan user 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

/*oralce more rigorous rights management, ordinary users are also the default can not access 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
/*oralce more rigorous rights management, ordinary users are also the default can not access 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
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
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

/* need to precede the table name with the user name, as follows */
SELECT * FROM Zhangsan.tablename
/* need to precede the table name with the user name, as follows */
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
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
/* But some permissions are not granted to the role, such as unlimited tablespace and the Any keyword */

Oracle Create user-given permissions