1.1 사용자에게 부여된 시스템 권한 확인[편집]

SELECT * FROM DBA_SYS_PRIVS
WHERE GRANTEE = '사용자명' ;

1.2 사용자에게 부여된 롤 확인(시스템 권한이 롤에 포함됨)[편집]

SELECT * FROM DBA_ROLE_PRIVS
WHERE GRANTEE = '사용자명' ;

1.3 사용자에게 부여된 롤에 부여된 시스템 권한 확인[편집]

SELECT * FROM DBA_SYS_PRIVS
WHERE GRANTEE = '롤명' ;

1.4 타 사용자에게 부여한 객체(테이블등) 권한 확인[편집]

SELECT * FROM DBA_TAB_PRIVS
WHERE OWNER = '테이블소유자명' ;
-- 또는,
SELECT * FROM DBA_TAB_PRIVS
WHERE GRANTEE = '권한부여자명' ;

1.5 TABLESPACE 사용권한[편집]

ALTER USER XXX QUOTA UNLIMITED ON TS_XXX_D01;

• DDL 권한

  기본적으로 CREATE TABLE 권한(1, 2번 SQL로 확인 가능)이 있으면,

  자기가 생성한 테이블에 대해서는ALTER, DROP, TRUNCATE 가능

  타 사용자에 대한 테이블 생성 권한은 DBA_SYS_PRIVS에서 확인 가능(CREATE ANY TABLE)

• DML 권한

  본인 소유의 테이블은 모든 작업 수행 가능

  타인 소유의 테이블에 대한 작업 수행은 4번 SQL로 확인 가능

• DCL 권한

  본인 소유의 테이블에 대한 타 사용자에게의 GRANT는 항상 가능

  타인 소유의 테이블에 대한 타 사용자에게의 GRANT 권한은,

  해당 테이블에 대한 권한을 DBA_TAB_PRIVS.GRANTABLE 컬럼에 YES 로 받은 경우 가능

1.6 TRUNCATE 권한[편집]

1. System 권한 중 ‘TRUNCATE TABLE’ 권한 이라는 것은 존재하지 않음.
2. Table을 Truncate하기 위해서는 ‘DROP TABLE’ 권한이 있어야 가능함.

grant execute on XXX_DBA.SP_TRUNC to scott;

CREATE OR REPLACE PROCEDURE XXX_DBA.SP_TRUNC (pTable IN VARCHAR2, reuse IN VARCHAR2 DEFAULT 'N')
IS
cmd	VARCHAR2 (4000) := 'truncate table XXX.' || pTable; BEGIN
IF UPPER (pTable) IN (TB_RC_XXX, 'TB_CM_XXX') -- Truncate이 허용되는 테이블 목록
THEN
CASE
WHEN reuse = 'N' THEN
-- DROP STORAGE
cmd := cmd || ' DROP STORAGE'; 
WHEN reuse = 'Y'
THEN
-- REUSE STORAGE
cmd := cmd || ' REUSE STORAGE'; 
END CASE;
-- DBMS_OUTPUT.put_line (cmd); EXECUTE IMMEDIATE cmd;
ELSE
RAISE_APPLICATION_ERROR (-20101, 'Requested Table is not allowed truncate operation.');
END IF;
END SP_TRUNC;
/

1.7 시스템 권한[편집]

1.8 오브젝트 권한[편집]

1.9 Privilege hierarchy[편집]

CREATE ANY CACHE GROUP
ALTER ANY CACHE GROUP
DROP ANY CACHE GROUP
FLUSH ANY CACHE GROUP
LOAD ANY CACHE GROUP
UNLOAD ANY CACHE GROUP
REFRESH ANY CACHE GROUP
FLUSH (object)
LOAD (object)
UNLOAD (object)
REFRESH (object)

1.10 PUBLIC ROLE[편집]

1. 데이터베이스의 모든 사용자는 PUBLIC 롤을 가지고 있음
2. 데이터베이스에서 기본적으로 PUBLIC은 다양한 시스템 테이블과 뷰, PL/SQL 함수, 프로시저 및 패키지에 대한 SELECT 및 EXECUTE 권한을 갖음.

SELECT table_name, privilege 
  FROM sys.dba_tab_privs 
 WHERE grantee='PUBLIC';

SELECT table_name, privilege 
  FROM sys.dba_tab_privs 
 WHERE grantor='SYS';

1.11 권한 관리 프로시져[편집]

• . dbm_privs_obj , dbm_privs_role 테이블 생성 후 사용

CREATE OR REPLACE PROCEDURE DBADM.sp_dbm_privs(p_grantee varchar2 default '%', p_exec boolean default FALSE)
/*******************************************************************************
-- authid current_user
-- grant grant any object privilege to dbadm;
-- grant grant any role to dbadm;
create table dbadm.dbm_privs_obj (
   dbname      varchar2(30) not null  ,obj_type    varchar2(15) not null
  ,grant_type  varchar2(15) not null  ,grantee     varchar2(30) not null
  ,owner       varchar2(30) not null  ,obj_nm      varchar2(30) not null
  ,privs       varchar2(30) not null  ,syn         varchar2(30)
  ,logdt       date                   ,rmk         varchar2(30)
  ,constraint pk_dbm_privs_obj primary key(dbname, obj_type, grant_type, grantee, owner, obj_nm)
);
create table dbadm.dbm_privs_role (
   dbname       varchar2(30) not null  ,grantee      varchar2(30) not null
  ,granted_role varchar2(30) not null  ,admin_option varchar2(3), default_role varchar2(3)
  ,constraint pk_dbm_privs_role primary key(dbname, grantee, granted_role)
);
insert into dbadm.dbm_privs_obj(dbname, obj_type, grant_type, grantee, owner, obj_nm, privs, syn, logdt, rmk)
                       values ('DCSSC1','TABLE','ROLE','RL_SC_ALL','SCADM','%','C,R,U,D','PUBLIC',SYSDATE,null);
insert into dbadm.dbm_privs_role(dbname, grantee, granted_role, admin_option, default_role)
                          values('DCSSC1','SCAPP','RL_SC_ALL', NULL, NULL);
insert into dbadm.dbm_privs_obj(dbname, obj_type, grant_type, grantee, owner, obj_nm, privs, syn, logdt, rmk)
   select --+ rule
          'DCSSC', o.object_type, nvl2(u.username,'USER','ROLE'), p.grantee, p.grantor, p.table_name, 'E','PUBLIC',sysdate, null
   from dba_tab_privs p, dba_objects o, dba_users u
   where p.privilege = 'EXECUTE'
     and p.grantor in ('SCADM','SCAPP')
     and o.owner = p.grantor
     and o.object_name = p.table_name
     and u.username (+)= p.grantee
insert into dbadm.dbm_privs_role(dbname, grantee, granted_role, admin_option, default_role)
  select 'DCSSC1', grantee, granted_role, admin_option, default_role
  from dba_role_privs where granted_role like 'RL_%';

********************************************************************************/
as
  v_dbname  varchar2(100);
  v_grantee varchar2(100);
  v_priva   varchar2(10);
  v_privs   varchar2(100);
  v_objnm   varchar2(100);
  v_sqlcmd  varchar2(2000);
  lc    number;
  ix    number;
  iy    number;
begin
  -- if p_grantee is null then 
    dbms_output.put_line('-- usage: exec dbadm.sp_dbm_privs(p_grantee => ''%'', p_exec=>FALSE)');
  -- end if;
  v_grantee := upper(p_grantee);
  select sys_context('USERENV','DB_NAME') into v_dbname from dual;
  -- if    v_dbname = 'DBPIBS' then v_dbname := 'DBDIBS';
  -- elsif v_dbname = 'DBPUBS' then v_dbname := 'DBDUBS';
  -- end if;
  
  for ic1 in (
        select /*+ rule */ distinct p.*
        from dbadm.dbm_privs_obj p 
        where p.dbname=v_dbname
          and (p.grantee like v_grantee
               or p.grantee in (select granted_role from dbadm.dbm_privs_role where grantee like v_grantee)
              )
          and p.grantee in (select username from dba_users
                            union all
                            select role from dba_roles)
  )
  loop
    lc := length(ic1.privs)-length(replace(ic1.privs,',','')) +1;
    ix :=0;
    for i in 1..lc
    loop
      iy := ix+1;
      ix := instr(ic1.privs,',', iy);
      if ix=0 then ix:=length(ic1.privs)+1; end if;
      v_privs := substr(ic1.privs, iy, ix-iy);
      select case v_privs when 'C' then 'INSERT'
                          when 'R' then 'SELECT'
                          when 'U' then 'UPDATE'
                          when 'D' then 'DELETE'
                          when 'E' then 'EXECUTE'
                          else v_privs end
             into v_privs
      from dual;

      --dbms_output.enable(8000);
      --dbms_output.put_line(ic1.privs||';'||iy||','||ix||':'||v_privs);
      --dbms_output.put_line('--------------');
      for ic2 in (
          select /*+ rule */ owner, object_name
          from dba_objects o
          where o.owner=ic1.owner
            and o.object_name like ic1.obj_nm
            and o.object_name not like 'BIN$%'
            and o.object_type = ic1.obj_type
            --and o.object_name like decode(o.object_type, 'FUNCTION','FC%', 'PROCEDURE','SP%','SEQUENCE','%SQ','%')
            and (o.owner, o.object_name) not in
                (select p.owner, p.table_name from dba_tab_privs p where p.grantee=ic1.grantee and p.privilege = v_privs)
      ) loop
        v_sqlcmd := 'grant ' ||v_privs|| ' on '||ic2.owner||'.'||ic2.object_name||' to '|| ic1.grantee;
        begin
          if p_exec then 
            execute immediate v_sqlcmd; 
            v_sqlcmd:=v_sqlcmd||' -- done'; 
          end if;
          dbms_output.enable(1000);
          dbms_output.put_line(v_sqlcmd);
        exception when others then
          dbms_output.put_line(v_sqlcmd);
          dbms_output.put_line(sqlerrm);
        end;
        --insert into dbadm.tb_dev_msg values('grant','1',v_sqlcmd, sysdate);
      end loop; -- loop for ic2
    end loop; -- loop for v_privs
  end loop; -- loop for ic1

  -- role grant
  for ic in (
    select /*+ rule */ r.* from dbadm.dbm_privs_role r
    where r.dbname=v_dbname and r.grantee like v_grantee
      and r.grantee in (select username from dba_users union all select role from dba_roles)
      and (r.grantee, r.granted_role) not in (select grantee, granted_role from dba_role_privs)
  ) loop
  begin
    v_sqlcmd := 'grant '||ic.granted_role||' to '||ic.grantee;
    dbms_output.enable(1000);
    if p_exec then 
        execute immediate v_sqlcmd; 
        v_sqlcmd := v_sqlcmd||'; -- done';
    else
        v_sqlcmd := v_sqlcmd||';';
    end if;
    dbms_output.put_line(v_sqlcmd);
  exception when others then
        dbms_output.put_line(v_sqlcmd);
        dbms_output.put_line(sqlerrm);
  end;
  end loop;

  exception when others then
      dbms_output.put_line(v_sqlcmd);
      dbms_output.put_line(sqlerrm);
/********************************************************************************
-- for reverse 
declare
   r_prv  dbadm.dbm_privs_obj%ROWTYPE;
   v_cnt  number := 0;
begin
  for ic in (
     select --+ rule ordered
            o.object_type, nvl2(u.username,'USER','ROLE') grant_type
          , p.grantee, p.grantor, p.table_name
          , decode(p.privilege,'INSERT','C','SELECT','R','UPDATE','U','DELETE','D','EXECUTE','E', p.privilege) privs
          , count(*) over() cnt
     from dba_tab_privs p, dba_objects o, dba_users u
     where p.grantor in ('SCADM','SCAPP') and p.table_name not like 'BIN$%'
       and o.owner = p.grantor
       and o.object_name = p.table_name
       and o.object_type in ('FUNCTION','PACKAGE','PROCEDURE','SEQUENCE' ,'TABLE','VIEW')
       and u.username (+)= p.grantee
     order by p.grantor, p.table_name, p.grantee
            , decode(p.privilege,'INSERT',1,'SELECT',2,'UPDATE',3,'DELETE',4,'EXECUTE',5, 6)
  )
  loop
     v_cnt := v_cnt + 1;
     if r_prv.grantee = ic.grantee and r_prv.owner=ic.grantor and r_prv.obj_nm=ic.table_name
     then
        r_prv.privs := r_prv.privs||','||ic.privs;
     else
        if (r_prv.privs is not null and v_cnt < ic.cnt) then
            insert into dbadm.dbm_privs_obj(dbname, obj_type, grant_type, grantee, owner, obj_nm, privs, syn, logdt, rmk)
            values ('DCSSC', r_prv.obj_type, r_prv.grant_type, r_prv.grantee, r_prv.owner, r_prv.obj_nm, r_prv.privs, 'PUBLIC',SYSDATE,NULL);
        end if;
        r_prv.obj_type   := ic.object_type;
        r_prv.grant_type := ic.grant_type;
        r_prv.grantee    := ic.grantee;
        r_prv.owner      := ic.grantor;
        r_prv.obj_nm     := ic.table_name;
        r_prv.privs      := ic.privs;
     end if;
     if v_cnt = ic.cnt then 
        insert into dbadm.dbm_privs_obj(dbname, obj_type, grant_type, grantee, owner, obj_nm, privs, syn, logdt, rmk)
        values ('DCSSC', r_prv.obj_type, r_prv.grant_type, r_prv.grantee, r_prv.owner, r_prv.obj_nm, r_prv.privs, 'PUBLIC',SYSDATE,NULL);
     end if;
  end loop;
end;
********************************************************************************/
end;

• 씨에스리 송정훈 이사님 제공