개요

계정 또는 ROLE에 부여된 권한을 ROLE 상속 관계에 따라 계층 구조로 조회하고, 각 권한을 재생성할 수 있는 DDL 형태로 출력하는 쿼리입니다.

계정 권한 점검, 계정 이관 또는 동일 권한의 계정 생성 시 활용할 수 있습니다.

방법

name 변수에 권한 DDL을 조회 할 계정 또는 ROLE을 입력 합니다.

exec :name := 'TIBERO'

set pages 1000
set lines 200
col div for a20
col priv for a100
var name varchar(20)

exec :name := 'TIBERO'

with
user_name as (
  select :name as uname from dual
),
all_roles as (
  select distinct granted_role as role_name
    from dba_role_privs
   start with grantee = (select uname from user_name)
 connect by prior granted_role = grantee
),
obj_privs as (
  select grantee,
         decode(owner, grantor, NULL, '/*GRANTOR:"'|| grantor ||'"*/ ')||
         'GRANT '||privilege||' ON '||
         decode(type,'DIRECTORY','DIRECTORY ','"'||owner||'".')||'"'||table_name||'"'||
         ' TO "'||grantee||'"'||
         decode(grantable,'YES',' WITH GRANT OPTION;',';') as priv
    from dba_tab_privs
   where table_name not in (select object_name from dba_recyclebin)
),
priv_tree as (
        select 'ROLE' as div, null as parent, rp.granted_role as child,
               'GRANT "'||rp.granted_role||'" TO "'||rp.grantee||'"'||
               decode(rp.admin_option,'YES',' WITH ADMIN OPTION;',';') as priv
          from dba_role_privs rp
         where rp.grantee = (select uname from user_name)
        union all
        select ' ROLE', rp.grantee, rp.granted_role,
               'GRANT "'||rp.granted_role||'" TO "'||rp.grantee||'"'||
               decode(rp.admin_option,'YES',' WITH ADMIN OPTION;',';')
          from dba_role_privs rp
         where rp.grantee in (select role_name from all_roles)
        union all
        select ' SYS_PRIV', spv.grantee, null,
               'GRANT '||spv.privilege||' TO "'||spv.grantee||'"'||
               decode(spv.admin_option,'YES',' WITH ADMIN OPTION;',';')
          from dba_sys_privs spv
         where spv.grantee in (select role_name from all_roles)
        union all
        select ' OBJ_PRIV', opv.grantee, null, opv.priv
          from obj_privs opv
         where opv.grantee in (select role_name from all_roles)
)
select lpad(' ',2*(level-1))||div||'('||level||')' as div,
       lpad(' ',2*(level-1))||
       replace(priv, chr(10), chr(10)||lpad(' ',2*(level-1))) as priv
  from priv_tree
 start with parent is null
connect by prior child = parent
union all
select 'SYS_PRIV(1)' as div,
       'GRANT '||privilege||' TO "'||grantee||'"'||
       decode(admin_option,'YES',' WITH ADMIN OPTION;',';')
  from dba_sys_privs
 where grantee = (select uname from user_name)
union all
select 'OBJ_PRIV(1)' as div, priv
  from obj_privs
 where grantee = (select uname from user_name)
  union all
 select 'DEFAULT_ROLE(1)','ALTER USER "'||grantee||'" DEFAULT ROLE '||
       nvl(listagg(decode(default_role,'YES','"'||granted_role||'"'), ',')
             within group (order by granted_role), 'NONE')||';' as ddl_text
  from dba_role_privs
 where grantee = (select uname from user_name)
 group by grantee
having count(decode(default_role,'NO',1)) > 0
;   

쿼리 실행 결과

DIV                  PRIV
-------------------- ----------------------------------------------------------------------------------------------------
ROLE(1)              GRANT "PLUSTRACE" TO "TIBERO";
   OBJ_PRIV(2)         GRANT SELECT ON "SYS"."V$SQL_PLAN_STATISTICS" TO "PLUSTRACE";
   OBJ_PRIV(2)         GRANT SELECT ON "SYS"."V$SQL_PLAN" TO "PLUSTRACE";
   OBJ_PRIV(2)         GRANT SELECT ON "SYS"."_VT_AUTOTRACESTAT" TO "PLUSTRACE";
ROLE(1)              GRANT "EXP_FULL_DATABASE" TO "TIBERO";
   SYS_PRIV(2)         GRANT EXEMPT REDACTION POLICY TO "EXP_FULL_DATABASE";
   SYS_PRIV(2)         GRANT FLASHBACK ANY TABLE TO "EXP_FULL_DATABASE";
   SYS_PRIV(2)         GRANT ANALYZE ANY TO "EXP_FULL_DATABASE";
   SYS_PRIV(2)         GRANT EXECUTE ANY PROCEDURE TO "EXP_FULL_DATABASE";
   SYS_PRIV(2)         GRANT SELECT ANY SEQUENCE TO "EXP_FULL_DATABASE";
   SYS_PRIV(2)         GRANT SELECT ANY TABLE TO "EXP_FULL_DATABASE";
   SYS_PRIV(2)         GRANT CREATE TABLE TO "EXP_FULL_DATABASE";
   SYS_PRIV(2)         GRANT CREATE SESSION TO "EXP_FULL_DATABASE";
   ROLE(2)             GRANT "SELECT_CATALOG_ROLE" TO "EXP_FULL_DATABASE";
     OBJ_PRIV(3)         GRANT SELECT ON "LBACSYS"."DBA_TLS_STATUS" TO "SELECT_CATALOG_ROLE";
     OBJ_PRIV(3)         GRANT SELECT ON "LBACSYS"."DBA_SA_AUDIT_OPTIONS" TO "SELECT_CATALOG_ROLE";
     OBJ_PRIV(3)         GRANT SELECT ON "LBACSYS"."DBA_SA_PROG_PRIVS" TO "SELECT_CATALOG_ROLE";
     OBJ_PRIV(3)         GRANT SELECT ON "LBACSYS"."DBA_SA_PROGRAMS" TO "SELECT_CATALOG_ROLE";
     OBJ_PRIV(3)         GRANT SELECT ON "LBACSYS"."DBA_SA_USER_PRIVS" TO "SELECT_CATALOG_ROLE";
     OBJ_PRIV(3)         GRANT SELECT ON "LBACSYS"."DBA_SA_USER_LABELS" TO "SELECT_CATALOG_ROLE";
     OBJ_PRIV(3)         GRANT SELECT ON "LBACSYS"."DBA_SA_USER_GROUPS" TO "SELECT_CATALOG_ROLE";
     OBJ_PRIV(3)         GRANT SELECT ON "LBACSYS"."DBA_SA_USER_COMPARTMENTS" TO "SELECT_CATALOG_ROLE";
     OBJ_PRIV(3)         GRANT SELECT ON "LBACSYS"."DBA_SA_USER_LEVELS" TO "SELECT_CATALOG_ROLE";
     OBJ_PRIV(3)         GRANT SELECT ON "LBACSYS"."DBA_SA_USERS" TO "SELECT_CATALOG_ROLE";
     OBJ_PRIV(3)         GRANT SELECT ON "LBACSYS"."DBA_SA_GROUP_HIERARCHY" TO "SELECT_CATALOG_ROLE";
     OBJ_PRIV(3)         GRANT SELECT ON "LBACSYS"."DBA_SA_GROUPS" TO "SELECT_CATALOG_ROLE";
     OBJ_PRIV(3)         GRANT SELECT ON "LBACSYS"."DBA_SA_COMPARTMENTS" TO "SELECT_CATALOG_ROLE";
     OBJ_PRIV(3)         GRANT SELECT ON "LBACSYS"."DBA_SA_LEVELS" TO "SELECT_CATALOG_ROLE";
     OBJ_PRIV(3)         GRANT SELECT ON "LBACSYS"."DBA_SA_DATA_LABELS" TO "SELECT_CATALOG_ROLE";
     OBJ_PRIV(3)         GRANT SELECT ON "LBACSYS"."DBA_SA_LABELS" TO "SELECT_CATALOG_ROLE";
     OBJ_PRIV(3)         GRANT SELECT ON "LBACSYS"."DBA_SA_SCHEMA_POLICIES" TO "SELECT_CATALOG_ROLE";
     OBJ_PRIV(3)         GRANT SELECT ON "LBACSYS"."DBA_SA_TABLE_POLICIES" TO "SELECT_CATALOG_ROLE";
     OBJ_PRIV(3)         GRANT SELECT ON "LBACSYS"."DBA_SA_POLICIES" TO "SELECT_CATALOG_ROLE";
     SYS_PRIV(3)         GRANT SELECT ANY DICTIONARY TO "SELECT_CATALOG_ROLE";
ROLE(1)              GRANT "RESOURCE" TO "TIBERO";
   SYS_PRIV(2)         GRANT CREATE TRIGGER TO "RESOURCE";
   SYS_PRIV(2)         GRANT CREATE PROCEDURE TO "RESOURCE";
   SYS_PRIV(2)         GRANT CREATE SEQUENCE TO "RESOURCE";
   SYS_PRIV(2)         GRANT CREATE TABLE TO "RESOURCE";
ROLE(1)              GRANT "CONNECT" TO "TIBERO" WITH ADMIN OPTION;
   SYS_PRIV(2)         GRANT CREATE SESSION TO "CONNECT";
SYS_PRIV(1)          GRANT SELECT ANY TABLE TO "TIBERO" WITH ADMIN OPTION;
OBJ_PRIV(1)          /*GRANTOR:"SYSCAT"*/ GRANT SELECT ON "SYS"."V$SESSION" TO "TIBERO" WITH GRANT OPTION;
DEFAULT_ROLE(1)      ALTER USER "TIBERO" DEFAULT ROLE "CONNECT","RESOURCE";

44 rows selected.