对我来说权限方面是本人最反感的部分之一,但作为开发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; |
ORA-01720
最新推荐文章于 2024-05-09 15:21:30 发布