Oracle collection User Permissions

[Application Scenario] preparations before the system goes online. Generally, the Dump File Import method is often used for data migration. Because the data is imported by one user, a permission error (which can be ignored) is reported during the import process. The system must be authorized in the production database for normal operation. Use the following script to obtain the authorization statement.

Execute the SELECT statement in The UAT environment, execute the grant statement in the production environment, and modify the statement according to the actual user.

Note: The query user must have the permission to view the data dictionary starting with DBA _; otherwise, the table or view does not exist.

Select 'Grant '| t1.granted _ Role | 'to' | t1.grantee |'; 'as text

From dba_role_privs T1

Where t1.grantee in ('abc ',

'Amlm ',

'Bop ',

'Credentials ',

'Datacore ',

'Mdr2 ',

'Metabase ',

'Metabase _ credentials ',

'Report ',

'Uprr ',

'Work ')

Union all

Select 'Grant '| t2.privilege | 'to' | t2.grantee |'; 'as text

From dba_sys_privs T2

Where t2.grantee in ('aboq ',

'Amlm ',

'Bop ',

'Credentials ',

'Datacore ',

'Mdr2 ',

'Metabase ',

'Metabase _ credentials ',

'Report ',

'Uprr ',

'Work ')

Union all

Select 'Grant '| t3.privilege | 'on' | t3.owner |'. '|

T3.table _ name | 'to' | t3.grantee | ';' as text

From dba_tab_privs T3

Where t3.grantee in ('aboq ',

'Amlm ',

'Bop ',

'Credentials ',

'Datacore ',

'Mdr2 ',

'Metabase ',

'Metabase _ credentials ',

'Report ',

'Uprr ',

'Work ');

In this way, writing is relatively rigid, and it is not easy to replace variables with '&'. You can create a stored procedure.

Create or replace procedure user_priv (username in varchar2)
Cursor v_cur is
Select 'Grant '| t1.granted _ Role | 'to' | t1.grantee |'; 'as text
From dba_role_privs T1
Where t1.grantee = upper (username)
Union all
Select 'Grant '| t2.privilege | 'to' | t2.grantee |'; 'as text
From dba_sys_privs T2
Where t2.grantee = upper (username)
Union all
Select 'Grant '| t3.privilege | 'on' | t3.owner |'. '|
T3.table _ name | 'to' | t3.grantee | ';' as text
From dba_tab_privs T3
Where t3.grantee = upper (username );
/* Select privilege from dba_sys_privs where grantee in (select granted_role from dba_role_privs where grantee = upper (username); if necessary-to collect permissions in the role */

Begin
For v_t in v_cur Loop
Begin
Dbms_output.put_line (v_t.text );
End;
End loop;
End;

Run procedure directly to execute user_priv (username => 'hr ');

For output text problems, see: http://blog.csdn.net/u010033674/article/details/8744629

Http://www.cnblogs.com/AlbertCQY/archive/2013/03/31/2992497.html