1、 背景描述
昨天用戶在給一個視圖授權(quán)的時候出現(xiàn)報錯“ORA-01720:不存在的授權(quán)選項”,根據(jù)報錯提示,這是一個權(quán)限的問題,通過在授權(quán)時增加with grant option解決。

2、問題重新
2.1、準(zhǔn)備三個用戶
(1)HR :存在JOBS表(2)SCOTT :創(chuàng)建視圖 hr_jobs,并授予 select 權(quán)限給 shen
(3)SHEN :無需任何操作
2.2、實驗操作
HR用戶操作:HR用戶將JOBS表的SELECT權(quán)限授權(quán)給SCOTT用戶
SQL> conn hr/hr
Connected.
SQL> grant select on hr.jobs to scott;
SCOTT用戶操作:SCOTT用戶將HR用戶的表JOBS創(chuàng)建名為HR_HOBS的視圖,并授權(quán)給SHEN用戶,此時報錯ORA-01720。
SQL> conn scott/tiger
Connected.
SQL> create or replace view hr_jobs as select t.job_id,t.job_title from hr.jobs t;
SQL> grant select on hr_jobs to shen;
grant select on hr_jobs to shen
*
ERROR at line 1:
ORA-01720: grant option does not exist for ‘HR.JOBS’
3、解決方法
SQL> conn hr/hr
Connected.
SQL> grant select on hr.jobs to scott with grant option;
Grant succeeded.
SQL> conn scott/tiger
Connected.
SQL> grant select on hr_jobs to shen;
Grant succeeded.
4、原因分析
舉個例子,我有一臺筆記本電腦,我授權(quán)給張三使用(SELECT權(quán)限),張三卻想把我筆記本電腦賣掉(明顯行不通,需要筆記本電腦的主人授權(quán)才行)。即我擁有該對象的"SELECT"權(quán)限,但我沒有 ‘支配’ 該對象的權(quán)限(with grant option)
5、附上多個對象授權(quán)腳本
select (case
when t.type = ‘VIEW’ then
'GRANT SELECT ON ’ || t.owner || ‘.’ ||
t.referenced_name || ’ WITH GRANT SELECT;’
else
'GRANT EXECUTE ON ’ || t.owner || ‘.’ ||
t.name || ’ WITH GRANT SELECT;’
end)
from all_dependencies t
where t.owner = ‘SCOTT’
and t.name = ‘HR_JOBS’
and t.type in (‘VIEW’, ‘PROCEDURE’, ‘FUNCTION’, ‘PACKAGE BODY’);




