时间戳

首页 > 解决方案 > 在立即执行中使用授权管理选项时如何解决错误

sql - 在立即执行中使用授权管理选项时如何解决错误

问题描述

我正在尝试运行以下脚本。在 EXECUTE IMMEDIATE 中没有“WITH ADMIN OPTION”的情况下，我的脚本可以正常工作。但是当使用“ WITH ADMIN OPTION”时，我得到以下错误。

“错误报告 - ORA-00900：无效的 SQL 语句 ORA-06512：在第 17 00900 行。00000 - “无效的 SQL 语句””。

SET SERVEROUTPUT ON;
DECLARE
    v_Model_UserName VARCHAR2(30) := UPPER('&Model_UserName');
    v_Cloned_UserName VARCHAR2(30) := UPPER('&Cloned_UserName');
    v_dba_role_privs VARCHAR2(3000); -- for dba_role_privs


    --- selecting the roles from model user (from dba_role_privs table)
    CURSOR c_role_privs (var01  Varchar2 )is
    SELECT granted_role from dba_role_privs where grantee = var01;

    BEGIN
--- granting the roles from model user to cloned user (from dba_role_privs table)  
    OPEN c_role_privs (v_Model_UserName);
    LOOP
    FETCH c_role_privs INTO v_dba_role_privs;
    EXIT WHEN c_role_privs%NOTFOUND;
    EXECUTE IMMEDIATE 'grant'||v_dba_role_privs||' to '||v_Cloned_UserName||' WITH ADMIN OPTION';
    END LOOP;
    CLOSE c_role_privs;
    END;
    /

标签： sqloracleplsqlcursorexecute-immediate

解决方案

尝试在grant关键字后添加一个空格：

EXECUTE IMMEDIATE 'grant '||v_dba_role_privs||' to '||v_Cloned_UserName||' WITH ADMIN OPTION';

推荐阅读