오라클 권한 부여 , 롤 생성 ( oracle privileges, create role )

1. 시스템 권한 (System Privileges)

System Privilege	Operations Authorized
CREATE USER	Grantee can create other Oracle users
DROP USER	Grantee can drop another user.
DROP ANY TABLE	Grantee can drop a table in any schema.
BACKUP ANY TABLE	Grantee can back up any table in any schema with the export utility.
SELECT ANY TABLE	Grantee can query tables, views, or materialized views in any schema.
CREATE ANY TABLE	Grantee can create tables in any schema.

EX)

SQL> GRANT CREATE USER, DROP USER TO HR
  2  ;

Grant succeeded.

2. 사용자 시스템 권한(User System Privileges)

System Privilege	Operations Authorized
CREATE SESSION	Connect to the database
CREATE TABLE	Create tables in the user's schema
CREATE SEQUENCE	Create a sequence in the user's schema
CREATE VIEW	Create a view in the user's schema
CREATE PROCEDURE	Create stored procedure, function, or package in the user's schema

EX)

SQL> grant create session, create table to hr;

Grant succeeded.

 

3. role 생성

SQL> create role hr_pri  ;

Role created.

SQL> grant create table, create view to hr_pri;

Grant succeeded.

SQL> grant hr_pri to hr;

Grant succeeded.

4. Object Privileges

Object Privilege	Table	View	Sequence	Procedure
ALTER	O	X	O	X
DELETE	O	O	X	X
EXECUTE	X	X	X	O
INDEX	O	X	X	X
INSERT	O	O	X	X
REFERENCES	O	X	X	X
SELECT	O	O	O	X
UPDATE	O	O	X	X

5. 부여된 권한 확인 (Confirming Privileges Granted)

Data Dictionary View	Description
ROLE_SYS_PRIVS	Ssytem privileges granted to roles
ROLE_TAB_PRIVS	Table privileges granted to roles
USER_ROLE_PRIVS	Roles accessible by the user
USER_TAB_PRIVS_RECD	Object privileges granted to the user
USER_COL_PRIVS_MADE	Object privileges granted on the columns of the users objects
USER_COL_PRIVS_RECD	Object privileges granted to the user on specific columns
USER_SYS_PRIVS	System privileges granted to the user

EX)

  1* select * from user_sys_privs
SQL> /

USERNAME                       PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
HR                             CREATE USER                              NO
HR                             UNLIMITED TABLESPACE                     NO
HR                             CREATE SESSION                           NO
HR                             DROP USER                                NO
HR                             CREATE TABLE                             NO

5 rows selected.

 

6. 권한 제거 (Revoking Object Privileges)

권한 주기
SQL> ed
Wrote file afiedt.buf
  1  grant select
  2  on hr.emp3
  3* to hr
SQL> /
Grant succeeded.



권한 삭제
SQL> ed
Wrote file afiedt.buf
  1  revoke select
  2  on hr.emp3
  3* from hr
SQL> /
Revoke succeeded.