oracle 使用动态创建的字符串向用户授予角色

6kkfgxo0  于 2023-06-29  发布在  Oracle
关注(0)|答案(1)|浏览(139)

我在Oracle SQL开发人员中有一个相当基本的函数,它只是将用户和角色作为参数,并运行GRANT role TO user(此函数正在Oracle APEX中使用)。

create or replace FUNCTION GRANT_ROLE_TO(P_USERNAME IN VARCHAR2, P_ROLE IN VARCHAR2) RETURN INTEGER IS 
RESULT INTEGER; 
MY_SQL VARCHAR2(400);
ERROR_CODE NUMBER;
ERROR_MESSAGE VARCHAR2(255);

BEGIN
    
    MY_SQL := 'GRANT ' || P_ROLE || ' TO ' || P_USERNAME;
    DBMS_OUTPUT.put_line(MY_SQL);
    EXECUTE IMMEDIATE MY_SQL;
    DBMS_OUTPUT.put_line('SQL WAS RUN');
    RETURN 1;

    EXCEPTION WHEN OTHERS THEN ERROR_CODE := SQLCODE; ERROR_MESSAGE := SUBSTR(SQLERRM, 1 , 255);
    DBMS_OUTPUT.put_line(ERROR_MESSAGE);
    return 0;
   
END GRANT_ROLE_TO;

例如,我可以运行GRANT TESTING TO JDOE,将TESTING角色赋予JDOE。我证实了这一声明的工作。但是,当我尝试使用SELECT GRANT_ROLE_TO('JDOE', 'TESTING') FROM DUAL时,它输出ORA-01924:角色'TESTING'未被授予或不存在并返回0,指示函数不工作,即使我知道并验证了TESTING存在且可以被授予。这是否与动态创建GRANT字符串以便立即与EXECUTE一起使用有关?

6yoyoihd

6yoyoihd1#

可能是权限问题。一个命名的proc/函数不会荣誉任何角色,你可能有能力授予你的角色,而执行它的ad-hoc,所以有一个特权差异,这是非常熟悉的Oracle。以SYS身份登录,并为函数所有者提供GRANT ANY ROLE权限。请注意,这基本上使其成为DBA级别的帐户,但听起来已经是这样了。
但是,一旦你解决了权限问题,你就会遇到另一个问题。您不能在SQL调用的函数中执行DDL,因此使用SELECT GRANT_ROLE_TO('JDOE', 'TESTING') FROM DUAL调用它将不起作用。你会得到错误:

ORA-14552: cannot perform a DDL, commit or rollback inside a query or DML

尝试在SQL之外正常执行函数:

DECLARE 
  var_result integer;
BEGIN 
  var_result := GRANT_ROLE_TO('JDOE', 'TESTING');
  dbms_output.put_line(var_result);
END;

相关问题