권한 부여 삭제(Grant)와 with admin option, with grant option

Test 목적 
1.지원 가능한 대상 ( 유저 등) 프로시저도 가능한가? yes
grant execute on procedure_name to 유저명 으로 사용가능합니다.
2.grant option으로 권한 부여 후 다시 권한 부여 A -> B -> C 에서 A -> B 권한 revoke 시 (grant option 미사용 ) B -> C 가능한지?

결론 : with grant option으로 부여한 권한은 A -> B 회수 시 B -> C 도 자동 회수됩니다.
with admin option은 자동 회수 안됨.

 

TEST

With Grant Option

1.유저생성
SCOTT = A 로 보고 진행하겠습니다.
SYS@ysbae> create user b identified by b account unlock;

User created.

Elapsed: 00:00:00.26
SYS@ysbae> create user c identified by c account unlock;

User created.

신규 우저접속 권한 부여
grant resource,connect to b;
grant resource,connect to c;

 

2.권한 부여 전 확인

SYS@ysbae> select * from dba_tab_privs
where grantee = 'B'   2  ;

no rows selected


SYS@ysbae> select * from dba_tab_privs
where grantee = 'C'

no rows selected

 

3.select scott.emp 권한 부여(B에게만)
SYS@ysbae> grant select on scott.emp to b with grant option ;

 

4.확인
SYS@ysbae> select * from dba_tab_privs
where grantee = 'B'  2    3
  4  ;

GRANTEE    OWNER  TABLE_NAME     GRANTOR           PRIVILEGE        GRA HIE COM TYPE   INH
----------------------------------------------------------------------------------------     
B          SCOTT EMP             SCOTT             SELECT YES NO  NO  TABLE  NO

SYS@ysbae> select * from dba_tab_privs
where grantee = 'C'  2
  3  ;

GRANTEE    OWNER  TABLE_NAME     GRANTOR           PRIVILEGE        GRA HIE COM TYPE   INH
----------------------------------------------------------------------------------------     
C          SCOTT EMP             B             SELECT YES NO  NO  TABLE       NO

 

 

5.권한 회수 (B것만 회수)
SYS@ysbae> revoke select on scott.emp from b;

Revoke succeeded.

 

6.revoke 후 확인

SYS@ysbae> select * from dba_tab_privs
where grantee = 'B' ;
no rows selected

Elapsed: 00:00:00.00
SYS@ysbae> select * from dba_tab_privs
where grantee = 'C' ;
no rows selected

 

- with grant option으로 부여 시 같이 자동회수 됩니다.

 

With Admin Option

1.권한부여

SYS@ysbae> grant create session to b with admin option;

Grant succeeded.

 

B@ysbae> grant create session to c with admin option;

Grant succeeded.

2.확인
SYS@ysbae> select * from dba_sys_privs
where grantee = 'B'  2
  3  ;

G PRIVILEGE                                ADM COM INH
- ---------------------------------------- --- --- ---
B CREATE SESSION                           YES NO  NO

1 row selected.

SYS@ysbae> select * from dba_sys_privs
where grantee = 'C'  ;

G PRIVILEGE                                ADM COM INH
- ---------------------------------------- --- --- ---
C CREATE SESSION                           YES NO  NO

1 row selected.

3. 회수 (B것만)

SYS@ysbae> revoke create session from b;

Revoke succeeded.

4.확인
SYS@ysbae> select * from dba_sys_privs
where grantee = 'B'  2  ;

no rows selected


SYS@ysbae> select * from dba_sys_privs
where grantee = 'C'  ;  2

G PRIVILEGE                                ADM COM INH
- ---------------------------------------- --- --- ---
C CREATE SESSION                           YES NO  NO

1 row selected.

B만 회수되고 C의 권한은 살아있는것을 확인하였습니다.