-- Title : [8i] 사용자 접근 제어 관리(OLN)
-- Reference : OLN
-- Key word : privilege grant revoke
********************************************************************************************/
/****************************************************************************************
-- 부여된 권한 확인 뷰
****************************************************************************************/
- ROLE_SYS_PRIVS : 롤에게 부여된 시스템 권한
- ROLE_TAB_PRIVS : 롤에게 부여된 테이블 권한
- USER_ROLE_PRIVS : 사용자에 의해 액세스 가능한 롤
- USER_TAB_PRIVS_MADE : 해당 사용자 객체에 부여된 객체 권한
- USER_TAB_PRIVS_RECD : 사용자에게 부여된 객체 권한
- USER_COL_PRIVS_MADE : 해당 사용자 객체의 특정 열에 부여된 객체 권한
- USER_COL_PRIVS_RECD : 특정 열에 대해 사용자에게 부여된 객체 권한
/****************************************************************************************
-- 사용자에게 부여된 객체 권한 확인 예
****************************************************************************************/
SQL> SELECT *
2 FROM USER_TAB_PRIVS_RECD;
OWNER TABLE_NAME GRANTOR PRIVILEGE GRA
--------------- --------------- --------------- ------------- --------
SCOTT DEPT SCOTT INSERT YES
SCOTT DEPT SCOTT SELECT YES
SCOTT EMP SCOTT SELECT NO
/****************************************************************************************
-- 사용자 생성
****************************************************************************************/
SQL> CREATE USER scott2
2 IDENTIFIED BY tiger2;
User created.
/****************************************************************************************
-- 사용자에 시스템 권한 부여
****************************************************************************************/
SQL> GRANT create table, create sequence, create view
2 TO scott2;
Grant succeeded.
/****************************************************************************************
-- 롤의 생성과 권한 부여
****************************************************************************************/
SQL> CREATE ROLE manager;
Role created.
SQL> GRANT create table, create view
2 TO manager;
Grant succeeded.
SQL> GRANT manager to BLAKE, CLARK;
Grant succeeded.
/****************************************************************************************
-- 사용자 비밀번호 변경
****************************************************************************************/
SQL> ALTER USER scott2
2 IDENTIFIED BY tiger22;
User altered.
/****************************************************************************************
-- 객체의 권한 부여
****************************************************************************************/
SQL> GRANT select
2 ON emp
3 TO scott2;
Grant succeeded.
SQL> GRANT update (dname, loc)
2 ON dept
3 TO scott2, manager;
Grant succeeded.
/****************************************************************************************
-- WITH GRNAT OPTION과 PUBLIC 키워드 사용
****************************************************************************************/
SQL> GRANT select, insert
2 ON dept
3 TO scott2
4 WITH GRANT OPTION
Grant succeeded.
SQL> GRANT select
2 ON alice.dept
3 TO PUBLIC;
Grant succeeded.
/****************************************************************************************
-- 객체 권한 철회
****************************************************************************************/
SQL> revoke select
2 on dept
3 from scott2;
Revoke succeeded.