oracle用Authid Current_User 定义的建表过程plsql可执行别的用户对象，但是job却不能调用

说明: procedure 本身可以执行.但是放到job中执行错误

ORA-12012: 自动执行作业 187 出错
ORA-01031: 权限不足
ORA-06512: 在"EMMA.PACK_SMS", line 16
ORA-06512: 在"EMMA.PACK_SMS", line 36

奇怪的就是proc本身能够执行,但是放到job中为什么不能执行了呢,查看proc ,发现里面存在 execute immediate create table ,

说明:在存储过程中使用的到系统权限不能通过rule来授权,需要显示授权,或者使用AUTHID CURRENT_USER

发现次存储过程使用了AUTHID CURRENT_USER ,所以存储过程能够执行.可是在job中就不能执行了.可知job是oracle后台进程启动的.这点就想不通了.

sql>grant create table to user_name;

之后就可以run job了.

这里可以利用10046 , 1031 trace name errorstack 来进行trace

sql>alter session set events '10046 trace name context forever , level 12';
sql>alter session set events '1031 trace name errorstack level 1';
DECLARE
BEGIN
dbms_job.run(187);
END;

/

查看trace ,发现

ksedmp: internal or fatal error
ORA-01031: 权限不足
Current SQL statement for this session:
create table s_tab as select * from stable

由此可知在job中必须显示授权.

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

create or replace procedure p_create_table
    Authid Current_User is
    begin
    Execute Immediate 'create table create_table(id int)';
    end p_create_table;

红色字体为关键  使用户可以使用权限执行execute

 

我们知道，用户拥有的role权限在存储过程是不可用的。遇到这种情况，我们一般需要显式进行系统权限，如grant create table to suk;但这种方法太麻烦，有时候可能需要进行非常多的授权才能执行存储过程，实际上，oracle给我们提供了在存储过程中使用role权限的方法：修改存储过程，加入Authid Current_User时存储过程可以使用role权限。

我们知道，用户拥有的role权限在存储过程是不可用的。如：

    SQL> select * from dba_role_privs where grantee='SUK';

    GRANTEE GRANTED_ROLE ADMIN_OPTION DEFAULT_ROLE
    ------------ ------------ ------------ ------------
    SUK DBA NO YES
    SUK CONNECT NO YES
    SUK RESOURCE NO YES
 

用户SUK拥有DBA这个role

再创建一个测试存储过程：

    create or replace procedure p_create_table
    is
    begin
    Execute Immediate 'create table create_table(id int)';
    end p_create_table;

然后测试

    SQL> exec p_create_table;

    begin p_create_table; end;

    ORA-01031: 权限不足
    ORA-06512: 在"SUK.P_CREATE_TABLE", line 3
    ORA-06512: 在line 1
 

 

 

可以看到，即使拥有DBA role，也不能创建表。role在存储过程中不可用。遇到这种情况，我们一般需要显式进行系统权限，如grant create table to suk;

但这种方法太麻烦，有时候可能需要进行非常多的授权才能执行存储过程。实际上，oracle给我们提供了在存储过程中使用role权限的方法：

修改存储过程，加入Authid Current_User时存储过程可以使用role权限。

    create or replace procedure p_create_table
    Authid Current_User is
    begin
    Execute Immediate 'create table create_table(id int)';
    end p_create_table;

再尝试执行：

    SQL> exec p_create_table;

    PL/SQL procedure successfully completed
 

已经可以执行了。

 

==================================================================================

存储过程默认是用定义者definer 的身份调用的，如果加上AUTHID CURRENT_USER，则用当前登陆的用户权限调用，如果该过程的调用者（而非定义者）被授与系统权限execute any procedure或是被该过程的定义者grant execute on授权的话，不用这个AUTHID CURRENT_USER子句，调用者照样可以使用这个过程。
另外，在Oracle的存儲過程中，如果涉及到操作不同schema下的對象的時候，可以在不同的schema下寫相同的procedure，但這樣帶來的問題是維護和同步帶來了麻煩，可以在procedure中加上authid current_user，來說明procedure中操作的對象是當前連接用戶的對象而并不是procedure所屬用戶下的對象。

-- USER01
USER01@HUIYI>create table t
2 (
3 col1 varchar2(10)
4 )
5 /

Table created.

USER01@HUIYI>insert into t values(user);

1 row created.

USER01@HUIYI>commit;

Commit complete.

USER01@HUIYI>select * from t;

COL1
--------------------
USER01

-- USER02
USER02@HUIYI>create table t
2 (
3 col1 varchar2(10)
4 )
5 /

Table created.

USER02@HUIYI>insert into t values(user);

1 row created.

USER02@HUIYI>commit;

Commit complete.

USER02@HUIYI>select * from t;

COL1
--------------------
USER02

USER02@HUIYI>create or replace procedure pro_01
2 is
3    l_col1 varchar2(10);
4 begin
5    select col1 into l_col1 from t;
6    dbms_output.put_line(l_col1);
7 end;
8 /

Procedure created.

USER02@HUIYI>create or replace procedure pro_02
2    authid current_user
3 is
4    l_col1 varchar2(10);
5 begin
6    select col1 into l_col1 from t;
7    dbms_output.put_line(l_col1);
8 end;
9 /

Procedure created.

USER02@HUIYI>grant all on pro_01 to public;

Grant succeeded.

USER02@HUIYI>grant all on pro_02 to public;

Grant succeeded.

USER02@HUIYI>call pro_01();
USER02

Call completed.

USER02@HUIYI>call pro_02();
USER02

Call completed.

-- USER01
USER01@HUIYI>select * from t;

COL1
--------------------
USER01

USER01@HUIYI>call user02.pro_01();
USER02

Call completed.

USER01@HUIYI>call user02.pro_02();
USER01

Call completed.