ORACLE 사용자 권한
DB CAFE
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
1 ORACLE 권한[편집]
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 로 받은 경우 가능
GRANT ANY OBJECT PRIVILEGE 시스템 권한(1, 2번 SQL로 확인 가능)을 가지고 있는 경우 타 사용자의 모든 객체에 대한 권한 부여 가능(DBA도 해당 권한이 있음)
1.6 TRUNCATE 권한[편집]
- System 권한 중 ‘TRUNCATE TABLE’ 권한 이라는 것은 존재하지 않음.
- Table을 Truncate하기 위해서는 ‘DROP TABLE’ 권한이 있어야 가능함.
1.6.1 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의 원래 설정으로 줄입니다.
1.7 시스템 권한[편집]
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. |
1.8 오브젝트 권한[편집]
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. |
1.9 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) |
Cache group privileges have a separate hierarchy except that ADMIN confers the CACHE_MANAGER privilege.
The CACHE_MANAGER privilege confers these privileges:
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)
The CACHE_MANAGER privilege also includes the ability to start and stop the cache agent and the replication agent and to perform cache grid operations.
CREATE ANY CACHE GROUP confers the CREATE CACHE GROUP privilege for any cache group.
1.10 PUBLIC ROLE[편집]
All users of the database have the PUBLIC role. In a newly created TimesTen database, by default PUBLIC has SELECT and EXECUTE privileges on various system tables and views and PL/SQL functions, procedures and packages. You can see the list of objects by using this query:
SELECT table_name, privilege
FROM sys.dba_tab_privs
WHERE grantee='PUBLIC';
Privileges that are granted to PUBLIC as part of database creation cannot be revoked. To see a list of these privileges, use this query:
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;
- 씨에스리 송정훈 이사님 제공