다른 명령
ORACLE 권한
사용자에게 부여된 시스템 권한 확인
SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = '사용자명' ;
사용자에게 부여된 롤 확인(시스템 권한이 롤에 포함됨)
SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = '사용자명' ;
사용자에게 부여된 롤에 부여된 시스템 권한 확인
SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = '롤명' ;
타 사용자에게 부여한 객체(테이블등) 권한 확인
SELECT * FROM DBA_TAB_PRIVS WHERE OWNER = '테이블소유자명' ; -- 또는, SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE = '권한부여자명' ;
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 로 받은 경우 가능
TRUNCATE 권한
- System 권한 중 ‘TRUNCATE TABLE’ 권한 이라는 것은 존재하지 않음.
- Table을 Truncate하기 위해서는 ‘DROP TABLE’ 권한이 있어야 가능함.
TRUNCATE 프로시져
- 사용자에게 프로시져를 만들고 프로시져 실행 권한을 부여함.
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; /
- TRUNCATE 옵션
- DROP STORAGE (디폴트) : 결과 테이블에 할당 된 익스텐트 수를 MINEXTENTS의 원래 설정으로 줄입니다.
- 해제 된 익스텐트는 시스템으로 리턴되어 다른 오브젝트에서 사용할 수 있습니다.
- DROP ALL STORAGE는 세그먼트 삭제.
- TRUNCATE TABLE 문 외에도 DROP ALL STORAGE는 ALTER TABLE TRUNCATE (SUB) PARTITION 문에도 적용됩니다.
- 이 옵션은 또한 절단중인 파티션과 연관된 종속 오브젝트 세그먼트를 삭제합니다.
- DROP ALL STORAGE는 클러스터에서 지원되지 않습니다.
- REUSE STORAGE : 기존 공간을 줄이지 않고 유지토록함.
- DROP STORAGE (디폴트) : 결과 테이블에 할당 된 익스텐트 수를 MINEXTENTS의 원래 설정으로 줄입니다.
시스템 권한
Privilege | Description |
---|---|
ADMIN | Enables a user to perform administrative tasks including checkpointing, backups, migration, and user creation and deletion. |
ALTER ANY CACHE GROUP | Enables a user to alter any cache group in the database. |
ALTER ANY INDEX | Enables a user to alter any index in the database.
Note: There is no ALTER INDEX statement. |
ALTER ANY MATERIALIZED VIEW | Enables a user to alter any materialized view in the database.
Note: There is no ALTER MATERIALIZED VIEW statement |
ALTER ANY PROCEDURE | Enables a user to alter any PL/SQL procedure, function or package in the database. |
ALTER ANY SEQUENCE | Enables a user to alter any sequence in the database.
Note: There is no ALTER SEQUENCE statement. |
ALTER ANY TABLE | Enables a user to alter any table in the database. |
ALTER ANY VIEW | Enables a user to alter any view in the database.
Note: There is no ALTER VIEW statement. |
CACHE_MANAGER | Enables a user to perform operations related to cache groups. |
CREATE ANY CACHE GROUP | Enables a user to create a cache group owned by any user in the database. |
CREATE ANY INDEX | Enables a user to create an index on any table or materialized view in the database. |
CREATE ANY MATERIALIZED VIEW | Enables a user to create a materialized view owned by any user in the database. |
CREATE ANY PROCEDURE | Enables a user to create a PL/SQL procedure, function or package owned by any user in the database. |
CREATE ANY SEQUENCE | Enables a user to create a sequence owned by any user in the database. |
CREATE ANY SYNONYM | Enables a user to create a private synonym owned by any user in the database. |
CREATE ANY TABLE | Enables a user to create a table owned by any user in the database. |
CREATE ANY VIEW | Enables a user to create a view owned by any user in the database. |
CREATE CACHE GROUP | Enables a user to create a cache group owned by that user. |
CREATE MATERIALIZED VIEW | Enables a user to create a materialized view owned by that user. |
CREATE PROCEDURE | Enables a user to create a PL/SQL procedure, function or package owned by that user. |
CREATE PUBLIC SYNONYM | Enables a user to create a public synonym. |
CREATE SEQUENCE | Enables a user to create a sequence owned by that user. |
CREATE SESSION | Enables a user to create a connection to the database. |
CREATE SYNONYM | Enables a user to create a private synonym. |
CREATE TABLE | Enables a user to create a table owned by that user. |
CREATE VIEW | Enables a user to create a view owned by that user. |
DELETE ANY TABLE | Enables a user to delete from any table in the database. |
DROP ANY CACHE GROUP | Enables a user to drop any cache group in the database. |
DROP ANY INDEX | Enables a user to drop any index in the database. |
DROP ANY MATERIALIZED VIEW | Enables a user to drop any materialized view in the database. |
DROP ANY PROCEDURE | Enables a user to drop any PL/SQL procedure, function or package in the database. |
DROP ANY SEQUENCE | Enables a user to drop any sequence in the database. |
DROP ANY SYNONYM | Enables a user to drop a synonym owned by any user in the database. |
DROP ANY TABLE | Enables a user to drop any table in the database. |
DROP ANY VIEW | Enables a user to drop any view in the database. |
DROP PUBLIC SYNONYM | Enables a user to drop a public synonym. |
EXECUTE ANY PROCEDURE | Enables a user to execute any PL/SQL procedure, function or package in the database. |
FLUSH ANY CACHE GROUP | Enables a user to flush any cache group in the database. |
INSERT ANY TABLE | Enables a user to insert into any table in the database. It also enables the user to insert into any table using the synonym, public or private, to that table. |
LOAD ANY CACHE GROUP | Enables a user to load any cache group in the database. |
REFRESH ANY CACHE GROUP | Enables a user to flush any cache group in the database. |
SELECT ANY SEQUENCE | Enables a user to select from any sequence or synonym on a sequence in the database. |
SELECT ANY TABLE | Enables a user to select from any table, view, materialized view, or synonym in the database. |
UNLOAD ANY CACHE GROUP | Enables a user to unload any cache group in the database. |
UPDATE ANY TABLE | Enables a user to update any table or synonym in the database. |
XLA | Enables a user to connect to a database as an XLA reader. |
오브젝트 권한
Privilege | Object type | Description |
---|---|---|
DELETE | Table | Enables a user to delete from a table. |
EXECUTE | PL/SQL package, procedure or function | Enables a user to execute a PL/SQL package, procedure or function directly. |
FLUSH | Cache group | Enables a user to flush a cache group. |
INDEX | Table or materialized view | Enables a user to create an index on a table or materialized view. |
INSERT | Table or synonym | Enables a user to insert into a table or into the table through a synonym. |
LOAD | Cache group | Enables a user to load a cache group. |
REFERENCES | Table or materialized view | Enables a user to create a foreign key dependency on a table or materialized view.
The REFERENCES privilege on a parent table implicitly grants SELECT privilege on the parent table. |
REFRESH | Cache group | Enables a user to refresh a cache group. |
SELECT | Table, sequence, view, materialized view, or synonym | Enables a user to select from a table, sequence, view, materialized view, or synonym
The SELECT privilege enables a user to perform all operations on a sequence.A user can be granted the SELECT privilege on a synonym or a view without being explicitly granted the SELECT privilege on the originating table. |
UNLOAD | Cache group | Enables a user to unload a cache group. |
UPDATE | Table | Enables a user to update a table. |
Privilege hierarchy
Privilege | Confers these privileges |
---|---|
ADMIN |
All other privileges including CACHE_MANAGER |
CREATE ANY INDEX | |
INDEX (any table or materialized view) | |
CREATE ANY MATERIALIZED VIEW |
CREATE MATERIALIZED VIEW |
CREATE ANY PROCEDURE |
CREATE PROCEDURE |
CREATE ANY SEQUENCE |
CREATE SEQUENCE |
CREATE ANY SYNONYM |
CREATE SYNONYM |
CREATE ANY TABLE |
CREATE TABLE |
CREATE ANY VIEW |
CREATE VIEW |
DELETE ANY TABLE | |
DELETE (any table) | |
EXECUTE ANY PROCEDURE | |
EXECUTE (any procedure) | |
INSERT ANY TABLE | |
INSERT (any table) | |
SELECT ANY SEQUENCE | |
SELECT (any sequence) | |
SELECT ANY TABLE | |
SELECT (any table, view or materialized view) | |
UPDATE ANY TABLE | |
UPDATE (any table) |
PUBLIC ROLE
- 데이터베이스의 모든 사용자는 PUBLIC 롤을 가지고 있음
- 데이터베이스에서 기본적으로 PUBLIC은 다양한 시스템 테이블과 뷰, PL/SQL 함수, 프로시저 및 패키지에 대한 SELECT 및 EXECUTE 권한을 갖음.
부여된 PUBLIC 권한 조회
SELECT table_name, privilege FROM sys.dba_tab_privs WHERE grantee='PUBLIC';
- 데이터베이스 생성의 일부로 PUBLIC에 부여된 권한은 취소할 수 없습니다.
SYS가 부여한 권한 목록 조회
SELECT table_name, privilege FROM sys.dba_tab_privs WHERE grantor='SYS';
권한 관리 프로시져
- . 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;
- 씨에스리 송정훈 이사님 제공