User Rights Management methods for Oracle SQL basic operations

User Rights Management methods for Oracle SQL basic operations

Recently put things about user operations and Rights management, although a lot of blogs have a similar collation, but they found their content is more or less a bit wrong. So, I personally on each statement to verify, and to do their own explanation and analysis, writing this blog. Or there are still errors, I hope you point out, thank you! Gossip less, start to get to the point!

first, the system users
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;//login sys account, method two
Sqlplus scott/tiger;//Landing Ordinary user Scott



Third, manage users
Create user starive;//under the Administrator account, creating the users Starive
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
# # # requires Administrator Authorization # # #
Grant Create session to starive;//Grant starive user permission to create session, that is, login permission

# # # tablespace # #
Grant Unlimited tablespace to starive;//grants starive users permission to use tablespaces
Grant create tablespace to starive;//Grant starive permission to create a tablespace
Grant drop tablespace to starive;//grants starive user permission to use tablespaces
Grant Alter TABLESPACE to starive;//Grant starive permission to modify the Tablespace
Grant manage tablespace to starive;//grants starive permissions to manage tablespaces


# # # table # # #
Grant create any table to starive;//grants permissions for creating tables
Grant drop any table to starive;//grants permission to delete tables
Grant insert any table to starive;//permissions for inserting tables
Grant update any table to starive;//permissions to modify tables
Grant Select any table to starive;//permissions for the query table
Grant all privileges to public; Grant all permissions (all) to all users (public)!!! Use it carefully, and use it as a system permission
Grant all privileges to starive; Grant all permissions (all) to the user starive!!! Use carefully



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 starive;//Grant starive user permission to view the specified table
Grant drop on TableName to starive;//granting permission to delete table
Grant insert on TableName to starive;//permission to be inserted
Grant update on TableName to starive;//granting permission to modify tables
Grant Insert (ID) on tablename to starive;
Grant Update (ID) on TableName to starive;//grants insert and Modify permissions to specific fields of the specified table, note that only the INSERT and update
Grant alert all table to starive;//grants starive user alert permission to any table

v. Revocation of Rights
Revoke select on tablename from starive;//Grant starive user permission to view the specified table
Revoke drop on TableName from starive;//grant permission to delete table
Revoke insert on TableName from starive;//permissions granted for insertion
Revoke update on TableName from starive;//granting permission to modify tables
Revoke insert (ID) on tablename from Starive;
Revoke update (ID) on TableName the from starive;//grants insert and Modify permissions to specific fields of the specified table, note that only the INSERT and update
Revoke alert all table from starive;//Grant starive user alert permission to any table

Revoke and grant syntax are basically the same, but the former is to< user, while the latter is from< user >


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
/* need to precede the table name with the user name, as in the following example */
SELECT * FROM Starive.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 starive with admin option;//keyword with admin option
Grant alert table on TableName to starive 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 starive;//the role of starive user Myrole
Drop role myrole; remove roles
/* But some permissions are not granted to the role, such as unlimited tablespace and the Any keyword */


10 Commands
Sql> SELECT distinct PRIVILEGE from Dba_sys_privs where PRIVILEGE like '%table% ';



Sql> SELECT distinct PRIVILEGE from Dba_sys_privs where PRIVILEGE like '%table% ';
Preface: Some permissions should be used with caution, because granting permissions is too large to be dangerous. Accidentally, the system will collapse ....

User Rights Management methods for Oracle SQL basic operations