[oracle] WITH GRANT OPTION 회수

grant option 만 회수할 수 있는 방법은 없음
해당 권한 회수 후 with grant option 없이 동일 권한 재부여
해당 옵션을 가지고 있는 상위 유적의 권한을 회수하면 하위 권한도 자동 회수됨
role 에는 with grant option 부여 불가

 

USR1에 SELECT 및 WITH GRANT OPTION 부여

> GRANT SELECT ON sk.tab1 TO usr1 WITH GRANT OPTION
0 row(s) modified.

> SELECT * FROM dba_tab_privs
WHERE table_name = 'TAB1'

GRANTEE|OWNER|TABLE_NAME|GRANTOR|PRIVILEGE|GRANTABLE|HIERARCHY|COMMON|TYPE |INHERITED|
-------+-----+----------+-------+---------+---------+---------+------+-----+---------+
USR1   |SK   |TAB1      |SK     |SELECT   |YES      |NO       |NO    |TABLE|NO       |
1 row(s) fetched.

USR2에 SELECT 권한 부여

SQL> show user
USER is "USR1"
SQL> GRANT SELECT ON sk.tab1 TO usr2 ;
Grant succeeded.

-- 권한 조회 시 grantor 부분이 다르게 들어가 있음
> SELECT * FROM dba_tab_privs
WHERE table_name = 'TAB1'

GRANTEE|OWNER|TABLE_NAME|GRANTOR|PRIVILEGE|GRANTABLE|HIERARCHY|COMMON|TYPE |INHERITED|
-------+-----+----------+-------+---------+---------+---------+------+-----+---------+
USR1   |SK   |TAB1      |SK     |SELECT   |YES      |NO       |NO    |TABLE|NO       |
USR2   |SK   |TAB1      |USR1   |SELECT   |NO       |NO       |NO    |TABLE|NO       |
2 row(s) fetched.

-- 조회 테스트 (USR2)
SQL> show user
USER is "USR2"
SQL> select count(*) from sk.tab1 ;
  COUNT(*)
----------
    289495

USR2의 SELECT 권한 회수

권한 부여한 계정이 아니라 실패

SQL> show user
USER is "SYS"
SQL> revoke select on sk.tab1 from usr2 ;
revoke select on sk.tab1 from usr2
*
ERROR at line 1:
ORA-01927: cannot REVOKE privileges you did not grant

-- 권한 부여 계정으로 회수(USR1)
SQL> show user
USER is "USR1"
SQL>
SQL> revoke select on sk.tab1 from usr2 ;
Revoke succeeded.

> SELECT * FROM dba_tab_privs
WHERE table_name = 'TAB1'

GRANTEE|OWNER|TABLE_NAME|GRANTOR|PRIVILEGE|GRANTABLE|HIERARCHY|COMMON|TYPE |INHERITED|
-------+-----+----------+-------+---------+---------+---------+------+-----+---------+
USR1   |SK   |TAB1      |SK     |SELECT   |YES      |NO       |NO    |TABLE|NO       |
1 row(s) fetched.

반응형

상위 계정 권한 회수

grant option 소유한 계정에 대해 권한 회수 진행 시 하위 권한도 모두 회수됨

-- USR2에 select 권한 부여(USR1)
SQL> show user
USER is "USR1"

SQL> grant select on sk.tab1 to usr2 ;
Grant succeeded.

> SELECT * FROM dba_tab_privs
WHERE table_name = 'TAB1'

GRANTEE|OWNER|TABLE_NAME|GRANTOR|PRIVILEGE|GRANTABLE|HIERARCHY|COMMON|TYPE |INHERITED|
-------+-----+----------+-------+---------+---------+---------+------+-----+---------+
USR1   |SK   |TAB1      |SK     |SELECT   |YES      |NO       |NO    |TABLE|NO       |
USR2   |SK   |TAB1      |USR1   |SELECT   |NO       |NO       |NO    |TABLE|NO       |
2 row(s) fetched.


-- USR1 권한 회수(SYS)
> REVOKE SELECT ON sk.tab1 from usr1
0 row(s) modified.

-- 해당 계정으로 부여된 권한 모두 삭제됨
> SELECT * FROM dba_tab_privs
WHERE table_name = 'TAB1'

GRANTEE|OWNER|TABLE_NAME|GRANTOR|PRIVILEGE|GRANTABLE|HIERARCHY|COMMON|TYPE|INHERITED|
-------+-----+----------+-------+---------+---------+---------+------+----+---------+

0 row(s) fetched.

권한 회수 및 재부여

권한 스크립트 추출

> SELECT GRANTEE,OWNER,TABLE_NAME,GRANTOR,PRIVILEGE,GRANTABLE
, 'revoke '||PRIVILEGE||' on '||owner||'.'||table_name||' from '||grantee||' ;' rvk
, 'grant '||PRIVILEGE||' on '||owner||'.'||table_name||' to '||grantee||' ;' grt
FROM DBA_TAB_PRIVS
WHERE 1=1
AND (owner != grantor AND owner NOT IN (SELECT username FROM dba_users WHERE oracle_maintained = 'Y'))
OR (grantable = 'YES' AND owner NOT IN (SELECT username FROM dba_users WHERE oracle_maintained = 'Y'))

GRANTEE|OWNER|TABLE_NAME|GRANTOR|PRIVILEGE|GRANTABLE|RVK                                 |GRT                              |
-------+-----+----------+-------+---------+---------+------------------------------------+---------------------------------+
USR1   |SK   |TAB1      |SK     |SELECT   |YES      |revoke SELECT on SK.TAB1 from USR1 ;|grant SELECT on SK.TAB1 to USR1 ;|
USR2   |SK   |TAB1      |USR1   |SELECT   |NO       |revoke SELECT on SK.TAB1 from USR2 ;|grant SELECT on SK.TAB1 to USR2 ;|

2 row(s) fetched.

> revoke SELECT on SK.TAB1 from USR1
0 row(s) modified.

> revoke SELECT on SK.TAB1 from USR2
SQL Error [1927] [42000]: ORA-01927: 허가하지 않은 권한을 REVOKE할 수 없습니다

> grant SELECT on SK.TAB1 to USR1
0 row(s) modified.

> grant SELECT on SK.TAB1 to USR2
0 row(s) modified.

> SELECT * FROM DBA_TAB_PRIVS
WHERE table_name = 'TAB1'

GRANTEE|OWNER|TABLE_NAME|GRANTOR|PRIVILEGE|GRANTABLE|HIERARCHY|COMMON|TYPE |INHERITED|
-------+-----+----------+-------+---------+---------+---------+------+-----+---------+
USR1   |SK   |TAB1      |SK     |SELECT   |NO       |NO       |NO    |TABLE|NO       |
USR2   |SK   |TAB1      |SK     |SELECT   |NO       |NO       |NO    |TABLE|NO       |

2 row(s) fetched.