ORA-01031 AUTHID CURRENT_USER 获得role中权限及注意事项

jnrjian

已于 2024-07-14 14:11:24 修改

阅读量378

点赞数 8

文章标签： oracle

于 2024-03-10 13:38:36 首次发布

本文链接：https://blog.csdn.net/jnrjian/article/details/136600007

版权

user system:

create or replace procedure test_temp aS
sqlx varchar2(100);
begin
sqlx := 'create global temporary table a(x number)';
dbms_output.put_line(sqlx);
execute immediate sqlx;
end;

create or replace procedure test_temp1 AUTHID CURRENT_USER aS
sqlx varchar2(100);
begin
sqlx := 'create global temporary table a(x number)';
dbms_output.put_line(sqlx);
execute immediate sqlx;
end;

grant execute on test_temp to test;

grant execute on test_temp1 to test;

user test:

begin
system.test_temp;
end;

begin
system.test_temp1;
end;

select * from dba_tables a where a.table_name='A'

建立在不同的用户，

AUTHID CURRENT_USER alter session set current_schema可以改变A的owner

-----------

create or replace procedure test_temp aS
sqlx varchar2(100);
begin
sqlx := 'create global temporary table b(x number)';
dbms_output.put_line(sqlx);
execute immediate sqlx;
end;

grant execute on test_temp to test

conn test

begin
system.test_temp;
end;

select * from all_tables a where a.table_name='B'

这个B 建在system下，未出现权限不足的问题。

Test 也没有create any table的权限。

-- Create the user
create user TEST
default tablespace USERS
temporary tablespace TEMP
profile DEFAULT
password expire;
-- Grant/Revoke object privileges
grant execute on DEFINER_PROC to TEST;
grant execute on INVOKER_PROC to TEST;
grant execute on TEST_TEMP to TEST;
-- Grant/Revoke role privileges
grant connect to TEST;
grant resource to TEST;

---权限问题

package 里面多个schema互相条用，这个时候用哪个用户执行，权限就不同了，

相当于alter session set current_schema 某些用户没有 AXXXXXXX，BTABLEXXX权限。

PROCEDURE REFRESH_TABLEXXXTMP(P_CODE VARCHAR2)
  AS
    vSQL VARCHAR2(4000);
  BEGIN
    BEGIN
       EXECUTE IMMEDIATE 'DROP TABLE AXXXXXXX.TABLEXXX'||P_CODE||'_TMP';
    EXCEPTION
       WHEN OTHERS THEN
          IF SQLCODE != -942 THEN
             RAISE;
          END IF;
    END;
    vSQL := 'CREATE GLOBAL TEMPORARY TABLE AXXXXXXX.TABLEXXX'||P_CODE||'_TMP '||
    'AS SELECT * FROM BTABLEXXX.TABLEXXX'||P_CODE||'_V WHERE 1=0';
    DBMS_OUTPUT.PUT_LINE('DEBUG => '|| vSQL);EXECUTE IMMEDIATE vSQL;
    vSQL := 'CREATE INDEX AXXXXXXX.TABLEXXX'||P_CODE||
    '_TMP_IDX1 ON AXXXXXXX.TABLEXXX'||P_CODE||'_TMP(ID)';
    DBMS_OUTPUT.PUT_LINE('DEBUG => '|| vSQL);EXECUTE IMMEDIATE vSQL;
    vSQL := 'CREATE INDEX AXXXXXXX.TABLEXXX'||P_CODE||
    '_TMP_IDX2 ON AXXXXXXX.TABLEXXX'||P_CODE||'_TMP(PARENT_UUID)';
    DBMS_OUTPUT.PUT_LINE('DEBUG => '|| vSQL);EXECUTE IMMEDIATE vSQL;
    vSQL := 'INSERT INTO AXXXXXXX.TABLEXXX'||P_CODE||'_TMP '||
    'SELECT * FROM BTABLEXXX.TABLEXXX'||P_CODE||'_V';
    DBMS_OUTPUT.PUT_LINE('DEBUG => '|| vSQL);EXECUTE IMMEDIATE vSQL;
  END;
END ;
/

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

oracle中执行execute的时候报异常ORA-01031的解决办法

在做实验的时候,编写关于“在存储过程中使用动态sql，建立一个统计表，并把统计结果，插入这个表中”的PL/sql语句在执行时出现权限不足的问题。

上网查询很多，看到了下面这篇博文（解决了我的问题）

两种解决办法

在编写存储过程时加上authid current_user
通过sys用户显式进行系统权限grant create table to hr(相应的用户名);

转载来的博文：

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>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

已经可以执行了。

--------------------AUTHID CURRENT_USER 的另个注意事项

存储过程默认是用定义者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.