oracle儲存過程許可權繼承小結

一、問題描述：

由於某種原因，我需要定期去從新編譯某個schema下所有INVALID狀態的試圖，於是建立瞭如下儲存過程：

1、首先單獨建立一個使用者,並授權dba許可權;

create user db_admin identified by "passwd";

grant dba to db_admin;

2、使用db_admin使用者登入資料庫，建立如下儲存過程;

create or replace procedure compile_invalid_views(

p_owner varchar2

) as

--編譯某個使用者下的無效檢視

str_sql varchar2(2000);

begin

for invalid_views in (select object_name from all_OBJECTS

where status = 'INVALID' and object_type = 'VIEW' and owner=upper(p_owner))

loop

alter_sql := 'alter view '||p_owner||'.' ||invalid_views.object_name || ' compile';

begin

execute immediate alter_sql;

end;

end loop;

end;

3、指定定時任務,定期呼叫儲存過程來從新編譯sysadm使用者下的無效檢視;

*/5 * * * * sh /home/oracle2/scripts/compile_invalid_views.sh

[oracle2@B-GH4-PSDB-01 ~]$ cat /home/oracle2/scripts/compile_invalid_views.sh

#!/bin/bash

source /home/oracle2/.bash_profile

sql_str=sqlplus -S db_admin/'passwd'@mz <

echo $sql_str

[oracle2@B-GH4-PSDB-01 ~]$

然後過了幾天後，發現有的無效檢視並沒有被從新編譯，業務還是報錯，太奇怪了，db_admin有dba許可權，執行儲存過程沒有報錯;

二、原因分析：

儲存過程分成兩種許可權：

1.定義者許可權 AUTHID DEFINER

2.呼叫者許可權 AUTHID CURRENT_USER

預設的情況下使用的是定義者許可權，也就是儲存過程內部的許可權是繼承的建立者的許可權;

1、定義者許可權使用遇到的問題：

當使用定義者許可權時候，不管是你自己去call，還是其他使用者去call，效果是一樣的，都是用你的許可權去執行。但是，不是你的全部許可權，是把所有Roles的許可權去掉了( 因為我只給db_admin授權了dba的許可權，但是他是個角色，所以儲存過程裡面會去掉dba這個角色，這是問題的關鍵，一般不注意)，當去掉dba角色後，再查詢all_OBJECTS是不能查到別的schema下檢視資訊的，所以也就無法從新編譯別的使用者下的檢視了。

2、呼叫者許可權使用遇到的問題：

當使用呼叫者許可權時候，用什麼使用者去call儲存過程，就用誰的許可權，而且包括角色許可權。請注意，用資料字典

user_xxx, 這個是呼叫者的，所以建議不要用他，而用all_xxx或dba_xxxx. (當然是要求呼叫者有select_catalog_role)

另外，涉及到create table, create index, ...要確認呼叫者是否具備在該儲存過程所在的schema下有此許可權(如果呼叫者就是你自己，肯定沒問題)。

三、解決問題：

方法1：這樣的話需要保證呼叫者的許可權是足夠的，呼叫者只有執行儲存過程的許可權是不行的!因為呼叫者是dba許可權，所以如下這樣讓儲存過程繼承呼叫者的許可權，是可以解決問題的!

新增AUTHID CURRENT_USER 關鍵字，這樣儲存過程就繼承執行者(呼叫者)的所有許可權，包括角色許可權!

create or replace procedure compile_invalid_views(

p_owner varchar2

) AUTHID CURRENT_USER as

--編譯某個使用者下的無效檢視

str_sql varchar2(2000);

begin

for invalid_views in (select object_name from all_OBJECTS

where status = 'INVALID' and object_type = 'VIEW' and owner=upper(p_owner))

loop

alter_sql := 'alter view '||p_owner||'.' ||invalid_views.object_name || ' compile';

begin

execute immediate alter_sql;

end;

end loop;

end;

方法2：建議這種的方式，儲存過程保持預設繼承定義者許可權，依舊使用db_admin使用者建立儲存過程!

如果db_admin只有dba許可權，儲存過程繼承定義者許可權的時候，會去掉其擁有的角色的許可權，所以會去掉dba許可權;所以需要給db_admin使用者dba之外的許可權，不會去掉的許可權，如下所示：

SQL> grant alter any table to db_admin; 因為需要儲存過程需要執行alter view，

Grant succeeded.

SQL> grant select any table to db_admin; #因為需要能檢視別的使用者下的view後，才能在all_objects中檢視到別的使用者的檢視資訊!

SQL> grant connect to db_admin; #因為後面需要收回dba，所以需要加connect許可權

最後還可以回收dba許可權;並且由於儲存過程是自己賬戶下面的，自己的賬號是可以執行的

SQL>revoke dba from db_admin;

方法3：這種方式不完美，因為只能從新編譯sysadm使用者的試圖，當你傳別的schema的時候，還是有問題!

在sysadm下建立對應的儲存過程，這樣繼承了sysadm這個使用者的許可權，儲存過程中也是可以查到自己的下面的試圖的，所以問題就解決了，但是一旦別的使用者下的試圖有問題也是不能從新編譯的!

create or replace procedure sysadm.compile_invalid_views(

p_owner varchar2

) as

--編譯某個使用者下的無效檢視

str_sql varchar2(2000);

begin

for invalid_views in (select object_name from all_OBJECTS

where status = 'INVALID' and object_type = 'VIEW' and owner=upper(p_owner))

loop

alter_sql := 'alter view '||p_owner||'.' ||invalid_views.object_name || ' compile';

begin

execute immediate alter_sql;

end;

end loop;

end;

然後使用db_admin使用者去呼叫這個儲存過程!

exec sysadm.compile_invalid_views('sysadm');

綜上所述：Oracle建立儲存過程的時候，儲存過程預設繼承的是定義者，也就是建立者的許可權，但是會去掉定義者擁有的 角色 ，這個需要大家注意!