API: Get Users Privileges and characteristics.

It’s quite simple API that generates user’s belongings and privileges in Database. It’s useful to synchronize grants of users between more than one Databases.

I assume we are logged in with SYS User in order to grab full detailed information of all users.

I am using below database views to get the information.

select * from DBA_USERS where ACCOUNT_STATUS = 'OPEN' and USERNAME not in ('SYS','SCOTT','TEST') ;

select distinct DEFAULT_TABLESPACE from DBA_USERS where ACCOUNT_STATUS = 'OPEN' and USERNAME not in ('SYS','SCOTT','TEST');

select * from SYS.DBA_TABLESPACES ;

select * from SYS.DBA_DATA_FILES where 1 = 1 ;

select * from ALL_TAB_PRIVS where 1 = 1 ;

Select * From Dba_Sys_Privs Where 1 = 1 ;

select * from DBA_ROLE_PRIVS where 1 = 1;

Run this code to get all you want regarding database schema. 

Declare

 L_Create_Ts Varchar2(1000) ;     

Begin 

  For Tindx In (Select Distinct Default_Tablespace From Dba_Users Where Account_Status = 'OPEN' And Username Not In ('SYS','SCOTT','TEST')) Loop 

    Dbms_Output.Put_Line('v_Return = ' || Tindx.Default_Tablespace);

     Select Dbms_Metadata.Get_Ddl('TABLESPACE', Tindx.Default_Tablespace) 

     Into   L_Create_Ts

     From Dual;     

  End Loop ;  

  Dbms_Output.Put_Line(' ---------------------- User Grants ----------------------------');

 For Uindx In (Select * From Dba_Users Where Account_Status = 'OPEN' And Username Not In ('SYS','SCOTT','TEST')) Loop 

  Dbms_Output.Put_Line(' -------------Schema : ' || Uindx.Username || '''s Tablespace grants------------------');  

         Dbms_Output.Put_Line('Create user ' 

                             || Uindx.Username 

                             || ' Identified by {}  DEFAULT TABLESPACE "' 

                             ||  Uindx.Default_Tablespace 

                             || '" TEMPORARY TABLESPACE "'

                             || Uindx.Temporary_Tablespace 

                             || '" ; '

                             || Chr(10)

                             || ' Alter user ' 

                             || Uindx.Username 

                             || ' Quota Unlimited On Apps  ' ||  Uindx.Default_Tablespace  ) ;

  Dbms_Output.Put_Line(' -------------Schema : ' || Uindx.Username ||'''s Admin Level Grants------------------');  

      For Aidx In (Select * From Dba_Sys_Privs Where 1 = 1 And Upper(Grantee) = Upper(Uindx.Username)) Loop 

        Dbms_Output.Put_Line('Grant ' 

                             || Aidx.Privilege 

                             || ' To ' 

                             || Aidx.Grantee 

                             || Case When  Aidx.Admin_Option =  'YES' Then ' WITH ADMIN OPTION' Else '' End  

                             || ' ; ');

      End Loop ;     

  Dbms_Output.Put_Line(' --------------Schema : ' || Uindx.Username || '''s Role Level Grants------------------');     

      For Ridx In (Select * From Dba_Role_Privs Where 1 = 1 And Upper(Grantee) = Upper(uindx.Username)) Loop 

        Dbms_Output.Put_Line('Grant ' 

                             || Ridx.Granted_Role 

                             || ' To ' 

                             || Ridx.Grantee 

                             || Case When  Ridx.Admin_Option =  'YES' Then ' WITH ADMIN OPTION' Else '' End  

                             || ' ; ');

      End Loop ;     

  Dbms_Output.Put_Line(' ------------- Schema : ' || Uindx.Username || '''s Object Level Grants------------------');         

      For Tidx In (Select * From All_Tab_Privs Where 1 = 1 And Upper(Grantee) = Upper(Uindx.Username)) Loop 

        Dbms_Output.Put_Line('Grant ' 

                             || Tidx.Privilege 

                             || ' On ' 

                             || Tidx.Grantor 

                             || '.' 

                             || Tidx.Table_Name 

                             || ' To ' 

                             || Tidx.Grantee 

                             || ' ; ') ; 

      End Loop ;     

  End Loop ; 

End; 

Comment below if you feel I missed anything.