oracle查询用户权限

1、查看用户的系统权限

[oracle@server248 myshell]$ cat user_DBA_SYS_PRIVS.sql
prompt ***********************************************************************************************
prompt * DBA_SYS_PRIVS describes system privileges granted to users and roles                        *
prompt ***********************************************************************************************
set pagesize 999;
set linesize 300;
col sqls for a120;
select *  from dba_sys_privs a
where a.grantee = upper('&tab_privs_grantee')
/

2、查看用户的对象权限

[oracle@server248 myshell]$ cat user_DBA_TAB_PRIVS.sql
prompt ***********************************************************************************************
prompt * DBA_TAB_PRIVS describes all object grants in the database                                   *
prompt ***********************************************************************************************
set pagesize 999;
set linesize 300;
col sqls for a120;
select 'grant '||privilege||' on '||owner||'.'||table_name||' to '||grantee||';' as sqls from dba_tab_privs a
where a.grantee = upper('&tab_privs_grantee')
order by a.owner,table_name
/

3、查看用户的角色

[oracle@server248 myshell]$ cat user_DBA_ROLE_PRIVS.sql
prompt ***********************************************************************************************
prompt * DBA_ROLE_PRIVS describes the roles granted to all users and roles in the database       *
prompt ***********************************************************************************************
set pagesize 999;
set linesize 300;
col sqls for a120;
select GRANTEE,GRANTED_ROLE,ADMIN_OPTION from DBA_ROLE_PRIVS  a
where a.grantee = upper('&role_privs_grantee')
order by a.GRANTEE
/

4、查看角色被授权的系统权限，对象权限，角色包含的角色

[oracle@server248 myshell]$ cat role_priv.sql
prompt ***********************************************************************************************
prompt * ROLE_ROLE_PRIVS describes the roles granted to other roles                                  *
prompt ***********************************************************************************************
SET LINESIZE 200;
SET PAGESIZE 200;
COL ROLE FORMAT A20;
COL GRANTED_ROLE FORMAT A30;
COL ADMIN_OPTION FORMAT A12;
SELECT ROLE,GRANTED_ROLE,ADMIN_OPTION FROM ROLE_ROLE_PRIVS WHERE ROLE = UPPER('&ROLE')
/

prompt ***********************************************************************************************
prompt * ROLE_SYS_PRIVS describes system privileges granted to roles                                 *
prompt ***********************************************************************************************
SET LINESIZE 200;
SET PAGESIZE 200;
COL ROLE FORMAT A20;
COL PRIVILEGE FORMAT A40;
COL ADMIN_OPTION FORMAT A12;
SELECT ROLE,PRIVILEGE,ADMIN_OPTION FROM ROLE_SYS_PRIVS WHERE ROLE = UPPER('&ROLE')
/

prompt ***********************************************************************************************
prompt * ROLE_TAB_PRIVS describes table privileges granted to roles                                  *
prompt ***********************************************************************************************
SET LINESIZE 200;
SET PAGESIZE 200;
COL ROLE FORMAT A20;
COL OWNER FORMAT A20;
COL TABLE_NAME FORMAT A30;
COL COLUMN_NAME FORMAT A30;
COL PRIVILEGE FORMAT A40;
COL GRANTABLE FORMAT A9;
SELECT ROLE,OWNER,TABLE_NAME,COLUMN_NAME,PRIVILEGE,GRANTABLE FROM ROLE_TAB_PRIVS WHERE ROLE = UPPER('&ROLE')
/