25. 기타 오브젝트(3)

[ WITH GRANT/ADMIN OPTION ]
- 중간관리자에게 일부 권한을 일임하여 중간관리자가 제3자에게 권한을 부여하게 할 수 있는 옵션
- GRANT로 부여 시 옵션 전달
- WITH GRANT OPTION은 오브젝트 권한에 대해, WITH ADMIN OPTION은 시스템권한 및 롤에 대해 작업 시 사용

** 정리)
with grant option(테이블권한) : 직접회수 불가, 동시회수 가능
with admin option(시스템권한) : 직접회수 가능, 동시회수 불가


[ 실습 - 중간관리자를 통한 권한 관리 ]
DBA : SYSTEM
중간관리자 : ITWILL
업무계정 : HONG

0. HONG 계정 생성(SYSTEM 계정에서 수행)
create user hong identified by oracle;
grant create session to hong;

1. 중간관리자에게 오브젝트 권한부여 및 일임
 1) SCOTT.MOVIE 테이블에 대한 조회권한 부여
grant select on scott.movie to itwill with grant option;

 2) SCOTT.CARD_HISTORY 테이블에 대한 INSERT,UPDATE,DELETE권한 부여
grant insert,update,delete on scott.card_history to itwill with grant option;

2. 중간관리자에게 시스템 권한부여 및 일임
 1) CREATE TABLE에 대한 권한 부여
grant create any table to itwill with admin option;

 2) CREATE VIEW에 대한 권한 부여 
grant create any view to itwill with admin option;

3. itwill 계정에서 hong에게 권한 부여(itwill 계정에서 수행)
grant select on scott.movie to hong;
grant insert,update,delete on scott.card_history to hong;
grant create any table to hong;
grant create any view to hong;

grant select on scott.emp2 to hong;   -- 에러(일임받지 않은 권한에 대해서는 권한부여 불가)


3. 직접 회수여부 확인
 1) SYSTEM 계정에서 HONG 계정에 부여된 SCOTT.CARD_HISTORY 테이블에 대한 INSERT 권한 회수 시도
revoke insert on scott.card_history from hong;            -- 불가

 2) SYSTEM 계정에서 HONG 계정에 부여된 CREATE TABLE 권한 회수 시도
revoke create any table from hong;                        -- 가능


4. 중간관리자 권한 회수시 동시 회수 여부 확인
 1) ITWILL 계정에 부여된 SCOTT.MOVIE 테이블에 대한 조회 권한 회수시 HONG도 함께 회수되는가? O
revoke select on scott.movie from itwill; 

 2) ITWILL 계정에 부여된 CREATE VIEW 권한 회수시 HONG도 함께 회수되는가?  X
revoke create any view from itwill;   

5. hong 권한 확인
select * from scott.movie;           -- 에러(테이블 또는 뷰가 존재하지 않습니다)

create view view1 
as
select sysdate as todate from dual;  -- 정상수행





[ Role ] 
- 권한의 묶음
- Role을 부여 시 Role안에 포함된 모든 권한이 동시에 부여됨
- Role에서 특정 권한 회수 시 Role을 부여받은 유저에 대한 해당 권한도 회수됨

1. 생성
create role 롤이름;

2. 롤에 권한 담기/빼기
grant 권한 to 롤;
revoke 권한 from 롤;

3. 롤 부여/회수
grant 롤 to 유저;
revoke 롤 from 유저;


[ 실습 - Role을 통한 권한 부여 ]
system 계정에서 수행)
1. role 생성
create role rl_sel1;

2. role 권한 담기
grant select on hr.employees to rl_sel1;

3. role을 hong 유저에게 부여
grant rl_sel1 to hong;
grant select on scott.gogak to hong;


4. hong 계정에서 테스트(권한 부여 전에 미리 접속해둘것)
select * 
  from hr.employees; -- 최초 조회 불가
                     -- 재접속 이후 조회 가능
select * 
  from scott.gogak;  -- 즉시 조회 가능


5. role에 있는 권한 회수
revoke select on hr.employees from rl_sel1; -- 즉시회수됨



[ 권한 조회 뷰 ]
1. role 확인
select *
  from dba_roles;

2. 권한확인
select *
  from dba_views
 where view_name like 'DBA_%PRIVS%';

1) DBA_TAB_PRIVS : 오브젝트 권한 부여 확인
- 직접 부여한 오브젝트 권한 확인(grantee에 유저명 전달)
- 롤을 통해 부여된 오브젝트 권한 확인(grantee에 롤이름 전달)

grant select on scott.emp to itwill;
grant select, insert, update, delete on scott.dept to rl_sel1;

select grantee as 확인유저,
       owner as 테이블소유자,
       table_name as 테이블명,
       grantor as 권한부여자, 
       privilege as 권한,
       grantable as 일임여부
  from dba_tab_privs
 where 1=1
   and table_name = 'EMP' 
   and grantee = 'ITWILL'     -- 유저명 또는 롤이름
;

select grantee as 확인유저,
       owner as 테이블소유자,
       table_name as 테이블명,
       grantor as 권한부여자, 
       privilege as 권한,
       grantable as 일임여부
  from dba_tab_privs
 where 1=1

   and grantee = 'RL_SEL1'      -- 유저명 또는 롤이름
;


2) DBA_SYS_PRIVS
- 직접 부여한 시스템 권한 확인(grantee에 유저명 전달)
- 롤을 통해 부여된 시스템 권한 확인(grantee에 롤이름 전달)
select * 
  from dba_sys_privs
 where grantee = 'DBA';    -- role에 포함된 시스템 권한 확인 가능


3) DBA_ROLE_PRIVS : 유저에게 부여된 롤 확인
select * 
  from dba_role_privs
 where grantee = 'HONG';   -- HONG에게 부여된 롤 확인