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