SQL statement for obtaining users and permissions

During routine maintenance, users of a Database need to be migrated to another database.
You must create the same user and related permissions in the target database,
Relevant user and permission information can be easily obtained, but in case the relevant information is not recorded,
In this case, you need to obtain related statements from the database.
The following statements can be used to obtain the relevant creation statements. Www.2cto.com -- get the creation user Set pagesize 100; Set linesize 150; set serveroutput on size 1000000; undefine user_name; Declare v_ SQL Varchar2 (1000); v_Username Varchar2 (100 ): = '& user_name'; Begin v_Username: = Upper (v_Username); For x In (Select Username, Password, Default_Tablespace, Temporary_Tablespace, Profile, Account_Status From Sys. dba_Users Where Username = v_Username Order By Username) Loop v_Username: = X. username; www.2cto.com v_ SQL: = 'create user' | x. username | 'identified by values ''' | x. password | ''' | 'default tablespace' | x. default_Tablespace | 'temporary tablespace' | x. temporary_Tablespace | 'profile '| x. profile | '; Dbms_Output.Put_Line (v_ SQL); For x In (Select Granted_Role, Grantee From Dba_Role_Privs Where Grantee = Upper (v_Username) Loop v_ SQL: = 'Grant '| X. granted_Role | 'to' | x. grantee | '; Dbms_Output.Put_Line (v_ SQL); End Loop; For x In (Select Privilege, Grantee From Dba_Sys_Privs Where Grantee = Upper (v_Username) Loop v_ SQL: = 'Grant '| x. privilege | 'to' | x. grantee | '; Dbms_Output.Put_Line (v_ SQL); End Loop; For x In (Select Privilege, Owner, Table_Name, Grantee From Dba_Tab_Privs Where Grantee = Upper (v_Username) Ord Er By owner) Loop If x. owner! = 'Sys 'then www.2cto.com Dbms_Output.Put_Line ('-- use another user to log on and execute authorization'); End If; v_ SQL: = 'Grant '| x. privilege | 'on' | x. owner | '. '| x. table_Name | 'to' | x. grantee | '; Dbms_Output.Put_Line (v_ SQL); End Loop; For x In (Select User_Name, Ts_Name, Maxblocks, Blocksize From Ku $ _ Tsquota_View Where User_Name = Upper (v_Username )) loop v_ SQL: = 'alter user' | x. user_Name | 'quota '| x. maxblocks * x. blocksize | 'on' | x. ts_Name | '; Dbms_Output.Put_Line (v_ SQL); End Loop; End;/execution example: SQL> @ 1 Enter value for user_name: testold 3: v_Username Varchar2 (100): = '& user_name'; new 3: v_Username Varchar2 (100): = 'test'; create user test identified by values '7a0f2b1_c212d67 'default tablespace
USERS temporary tablespace TEMP profile DEFAULT; grant RESOURCE to TEST; www.2cto.com grant DBA to TEST; grant CONNECT to TEST; grant unlimited tablespace to TEST; grant WRITE on SYS. IMPDP_DIR to TEST; grant READ on SYS. IMPDP_DIR to TEST; -- use another user to log on and execute grant SELECT on TEST2.SMS _ VOTE_WEB to TEST;
By gtlions