"ORACLE 사용자 권한"의 두 판 사이의 차이
DB CAFE
42번째 줄: | 42번째 줄: | ||
GRANT ANY OBJECT PRIVILEGE 시스템 권한(1, 2번 SQL로 확인 가능)을 가지고 있는 경우 | GRANT ANY OBJECT PRIVILEGE 시스템 권한(1, 2번 SQL로 확인 가능)을 가지고 있는 경우 | ||
타 사용자의 모든 객체에 대한 권한 부여 가능(DBA도 해당 권한이 있음) | 타 사용자의 모든 객체에 대한 권한 부여 가능(DBA도 해당 권한이 있음) | ||
+ | |||
+ | |||
+ | {| class="wikitable sortable" | ||
+ | |- | ||
+ | ! 표 제목 !! 표 제목 !! 표 제목 | ||
+ | |- | ||
+ | | 예시 || 예시 || 예시 | ||
+ | |- | ||
+ | | 예시 || 예시 || 예시 | ||
+ | |- | ||
+ | | 예시 || 예시 || 예시 | ||
+ | |} | ||
+ | |||
+ | == 시스템 권한 == | ||
+ | {| class="wikitable sortable" | ||
+ | |- | ||
+ | !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. | ||
+ | |} | ||
+ | == 오브젝트 권한 == | ||
+ | {| class="wikitable sortable" | ||
+ | |- | ||
+ | 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 == | ||
+ | {| class="wikitable sortable" | ||
+ | |- | ||
+ | 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) |
2019년 11월 4일 (월) 15:41 판
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
1. 사용자에게 부여된 시스템 권한 확인
SELECT * FROM DBA_SYS_PRIVS
WHERE GRANTEE = '사용자명' ;
-- 2. 사용자에게 부여된 롤 확인(시스템 권한이 롤에 포함됨)
SELECT * FROM DBA_ROLE_PRIVS
WHERE GRANTEE = '사용자명' ;
-- 3. 사용자에게 부여된 롤에 부여된 시스템 권한 확인
SELECT * FROM DBA_SYS_PRIVS
WHERE GRANTEE = '롤명' ;
-- 4. 타 사용자에게 부여한 객체(테이블등) 권한 확인
SELECT * FROM DBA_TAB_PRIVS
WHERE OWNER = '테이블소유자명' ;
또는,
SELECT * FROM DBA_TAB_PRIVS
WHERE GRANTEE = '권한부여자명' ;
-- 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 시스템 권한[편집]
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. |