ORA-01720

对我来说权限方面是本人最反感的部分之一，但作为开发DBA也是本人比较常遇到的问题,今天在GRANT SELECT ON 某个view时又出现了ORA-01720的问题，ORACLE的解决方法是使用WITH GRANT OPTION，于是自己也做了个实验在公司的测试环境模拟了一下:

--------------------------------------------------------------------------------------------------
SQL> conn own_user/tom@dwteam
SQL> create table FACT_1 as select * from v$log;

Created.

SQL> conn main_user/tom@dwteam
SQL> create synonym FACT_1 on own_user.FACT_1;

Created.

SQL> create view fact_1_view AS SELECT * from FACT_1;

Created.

SQL> conn dba_user/tom@dwteam
Connected.

SQL> revoke select on own_user.FACT_1 from main_user;

Revoke succeeded.

SQL> grant select on own_user.FACT_1 to main_user;

Grant succeeded.


--- Check grant to rstmt without grant option:
SQL> conn main_user/tom@dwteam
Connected.
SQL> GRANT SELECT ON FACT_1 to etl_user;
GRANT SELECT ON FACT_1 to etl_user
                *
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> GRANT SELECT ON fact_1_view to etl_user;
GRANT SELECT ON fact_1_view to etl_user
                *
ERROR at line 1:
ORA-01720: grant option does not exist for 'own_user.FACT_1'


SQL> conn dba_user/tom@dwteam
Connected.

--- Add "with grant option"
SQL> grant select on own_user.FACT_1 to main_user with grant option;

Grant succeeded.

SQL> conn main_user/tom@dwteam
Connected.
SQL> GRANT SELECT ON FACT_1 to etl_user;

Grant succeeded.

SQL> GRANT SELECT ON fact_1_view to etl_user;

Grant succeeded.


--- Can we direct give privilege to etl_user?
1. NO
--- grant select priv on select directly to etl_user:
SQL> conn dba_user/tom@dwteam
Connected.
SQL> grant select on own_user.FACT_1 to etl_user;

Grant succeeded.

SQL> conn main_user/tom@dwteam
Connected.
SQL> GRANT SELECT ON fact_1_view to etl_user;
GRANT SELECT ON fact_1_view to etl_user
                *
ERROR at line 1:
ORA-01720: grant option does not exist for 'own_user.FACT_1'

2. How? -- 1) Create view under fact_1_view, 2) give privilege with grant option;