Common SQL statements for Oracle Database permission management

Common SQL statements for Oracle Database permission management 1. Three default users in Oracle9g: www.2cto.com username and password sys change_on_install [as sysdba] system manager scott tiger 2. User Logon 1) enable listening C: \ Users \ Administrator> lsnrctl start 2) Enable the database instance C: \ Users \ Administrator> oradim-starup-sid orcl 3) log on to the super Administrator C: \ Users \ Administrator> sqlplus/as sysdba 4) create a common user and log on to create user lisi identified by lisi; // create a username lisi, user with the password lisi default tablespace users // The default tablespace is users, which is used to store Put the data temporary tablespace temp // temporary tablespace temp, which is equal to the windows temporary Folder quota 50 M on users // limit that the user can only use the users tablespace as 50 m c: \ Users \ Administrator> sqlplus lisi/lisi // lisi User Logon 5) restrict User locks: alter User Username Account Lock User Unlock: alter User Username Account Unlock User Password Expiration: alter User Username Password Expire 6) delete user Drop User Username there is no object Drop User Username cascade where CasCade means to delete all objects of the user 7) disconnect the user SQL> disconn 3. grant the system permission to create session to lisi; // grant the permission to li Si session permission grant create table to lisi; // grant lisi table creation permission grant unlinited tablespace to lisi; // grant lisi unlimited permission to use tablespace revoke create session from lisi; // revoke the lisi session permission revoke create table from lisi; // revoke the lisi table creation permission revoke unlimited tablespace to lisi; // revoke lisi's unrestricted permission to use the tablespace 4. grant select on mytable to lisi object permission; // grant lisi the permission to view the mytable table grant insert on mytable to lisi; // grant lisi the permission to insert data into the mytable table grant all on mytable t O lisi; // grant lisi all permissions to operate on the mytable table revoke select on mytable from lisi; // revoke the permission of lisi to view the mytable revoke insert on mytable from lisi; // revoke the revoke all on mytable from lisi permission of lisi to insert data into the mytable table; // revoke all permissions for the lisi operation on the mytable table. 5. grant the create any table to public permission to all users; // grant the table creation permission to all users grant the create session to public; // grant the session permission to all users grant the unlinited tablespace to public; // grant the unlimited permission to use the tablespace to all users grant select on myt Able to public; // grant all users the revoke create any table from public permission to view the mytable table; // revoke the revoke create session from public permission to create a table from all users; // revoke the revoke unlinited tablespace from public; // revoke the unrestricted permission of the user to use the tablespace. 6. Use the digital dictionary select * from user_sys_privs; // view the system permissions that the current user has. select * from user_tab_privs; // view the objects that the current user has. select * from dba_users; // view all users in the database, accounts with the permissions of the former dba, such as sys, system select * from all_users; // view all users you can manage! Select * from user_users; // view the current user information! 7. The object permission can be controlled to the grant update (name) on mytable to lisi column; // grant the lisi user the permission to update the mytable name column grant insert (id) on mytable to lisi; // grant the lisi user the permission to insert data into the id column of the mytable Table select * from user_col_privs; // you can view the object permissions of which columns the current user has. Note: Query and deletion cannot control columns. // Ddl dml dcl 8. Pass the system permission grant alter any table to lisi with admin option; // The Super administrator grants lisi the administrative permission to modify any table. 9. grant select on A to lisi with grant option for object permission transfer; // grant the view permission of Table A to lisi 10, set the width of the command line screen to set linesize 400 // set the width of the row to 400 11, and create role myrole; // create a role grant create session to myrole; // grant the session permission to the role grant create table to myrole; // grant the table creation permission to the role drop role myrole; // Delete the role Create table Create any table Alter any t Able Drop any table Note: Some system permissions cannot be directly granted to the role Grant unlinited tablespace to myrole; Alter table Drop table belongs to a user, and the role does not belong to any user, is shared. 12. Obtain the table: www.2cto.com select table_name from user_tables; // select table_name from all_tables for the current user's table; // select table_name from dba_tables for all users; // including the system table select table_name from dba_tables where owner = 'username' user_tables: table_name, tablespace_name, role, and other dba_tables: ower, table_name, tablespace_name, role, and other all_tables: ower, tablespace_name, last_analyzed, and other all_objects: ower, obje Ct_name, subobject_name, object_id, created, last_ddl_time, timestamp, status, etc. 13. Obtain table fields: select * from user_tab_columns where Table_Name = 'user table '; select * from all_tab_columns where Table_Name = 'user table'; select * from region where Table_Name = 'user table'; user_tab_columns: table_name, column_name, data_type, data_length, data_precision, data_scale, nullable, column_id and other all_tab_columns: ower, table_name, column_name, Data_type, data_length, data_precision, data_scale, nullable, column_id, etc. values: ower, table_name, column_name, data_type, data_length, data_precision, data_scale, nullable, column_id, etc. 14: select * from user_tab_comments: table_name, table_type, and corresponding comments include dba_tab_comments and all_tab_comments. These two columns have more ower columns than user_tab_comments. 15. Obtain field comments: select * from user_col_comments: table_name, column_name, comments, and dba_col_comments and all_col_comments. These two columns have more ower columns than user_col_comments.