[Reprinted] permission management for Oracle roles

---- Three default users

 
1 Sys change_on_install[As sysdba] //Highest System Administrator
2 System manage//Local administrator
3 Scott Tiger//Common User
 

---- Create a user

Create UserLeap identifiedBy 1234 //Set the leap user password to 1234.
 

---- System Authorization

 Grant     Create  Seesion  To  Leap;  //  The permission authorized by the system administrator to the leap session
 Grant     Create     Table     To  Leap ;;  //  The system administrator authorizes leap to create a table.
  Grant  Unlimited tablespace  To  Leap;  //  The default tablespace is users.

 Grant     Create  Session  To     Public  ;  //  Grant session permissions to all users
  Grant     Create     Any     Table    To     Public  ;  //  Grant the permission to create any table to all users.
  Grant     Alter     Any     Table     To     Public  ; //  Grant the permission to modify any table structure to all users. 

----------- The system revokes authorization

Revoke CreateSeesionFromLeap;//Revoke authorization
Revoke Create Table FromLeap;
RevokeUnlinited tablespaceFromLeap;
 

------ Data Dictionary

---- Query the system permissions of the current user

Select * FromUser_sys_privs
 

---- Query the permissions of the current user table

 
Select * FromUser_tab_privs
 

---- Permission to query the columns of the current user table

 
Select * FromUser_col_privs
 

---- Set the display width of the row

SetLinesize400
 

---- Object authorization{Note: Who owns the object (that is, who created the object) and who owns the authorization right}

 
Grant SelectMytableToLeap2
Grant AllMyTable ToLeap2
 

--- Authorize the column control permission of the table. The object permission can be controlled to the column range.

 Grant    Update  <  Columnname  >     On  Mytable  To  Leap2  //  User leap2 can only modify columnname columns to control permissions in the column of the table.
  Grant     Insert  <  Columnname >     On  Mytable  To  Leap2  //  User leap2 can only modify columnname columns to control permissions in the column of the table. 

{Note; control of authorized columns can only be performed on update and insert operations, and query and deletion cannot be performed on columns}

 
Select * FromUser_col_privs//Query the data dictionary of a column
 

--- Query leap2 users after successful authorization

Select * FromLeap. mytable
 

DDL -- Data Definition Language create drop... No need to submit commit
DDL-data manipulation language insert update Delete... Need to submit commit
DCL -- Data Control Language Grant... No need to submit commit

------- Object revocation authorization

Grant SelectMytableToLeap2
Grant All OnMytableToLeap2
 

----- Transfer Permissions

--- The permission cannot be transferred

Grant Alter Any Table ToLeap2//Authorize leap2 the permission to modify the table structure, and leap2 cannot grant this permission to other users leap3.
 

--- Permission Transfer

Grant Alter Any Table ToLeap2WithAdminOption//Authorize leap2 the permission to modify the table structure, and leap2 can grant this permission to other users leap3.
 

----- Granting the Table query permission to other users cannot be passed

Grant Select OnMytableToLeap2//Grant the permission to query a table to leap2, and leap2 cannot grant this permission to other users.
 

----- Grant the Table query permission to other users

Grant Select OnMytableToLeap2With Grant Option//Authorize leap2 the permission to query tables, and leap2 can grant this permission to other users leap3.
 

---- Role{A role is a set of permissions. You can grant a role to the user}

--- Create a role

 
CreateRole myrole
 

--- Delete a role

DropMyrole
 

-- A role is a set of permissions, so you must put the permissions in the role.

 
Grant CreateSessionToMyrole
Grant Create Table ToMyrole
 

--- Authorize a role to the user

 
GrantMyroleToLeap
 

{Note: Some permissions cannot be authorized to the role}
Example: unlimited tablespace
Indicates that
Roles are not shared by any user.

--- Restrict users

Alter UserUsername account lock//User lock
Alter UserUsername account unlock//User unlock
Alter UserUsername: password expire//The User Password becomes invalid.
 

--- Delete a user

Drop UserUser Name//Every object under the user
Drop UserUser NameCascade//Cascade must be used for tables or objects under the user,CascadeIndicates that all objects of the user are forcibly deleted.
 

---- Start Oracle listener under the command

  LSNRCTL start   [  listenername  ]   //   Start 
 LSNRCTL stop   [  listenername  ]  ///   close