如何判断Oracle的GRANT命令是否实际执行了变更操作

核心结论 #

Oracle没有类似SQL%ROWCOUNT的内置变量直接判断GRANT命令是否实际修改了权限——因为GRANT属于DDL语句，执行后不会返回受影响的行数，SQL%ROWCOUNT会被重置为0，无法用来检测变更。

可行解决方案 #

要准确判断GRANT是否产生了实际变更，最可靠的方式是在执行GRANT前先查询数据字典，确认权限是否已存在，再决定是否执行GRANT并统计变更次数。以下是针对不同权限类型的具体实现：

1. 处理对象权限（如SELECT、EXECUTE ON对象） #

通过查询DBA_TAB_PRIVS（需拥有SELECT_CATALOG_ROLE权限）或ALL_TAB_PRIVS（普通用户可用）判断权限是否存在：

declare
  v_change_count number := 0;
  v_exists       number;

  procedure grant_object_priv(p_grant_stmt in varchar2) is
    l_privilege varchar2(100);
    l_object    varchar2(100);
    l_owner     varchar2(100);
    l_table     varchar2(100);
  begin
    -- 解析传入的GRANT语句片段（如"select on SCOTT.FOO"）
    l_privilege := trim(substr(p_grant_stmt, 1, instr(p_grant_stmt, ' on ') - 1));
    l_object := trim(substr(p_grant_stmt, instr(p_grant_stmt, ' on ') + 4));

    -- 拆分对象的所有者和名称
    if instr(l_object, '.') > 0 then
      l_owner := trim(upper(substr(l_object, 1, instr(l_object, '.') - 1)));
      l_table := trim(upper(substr(l_object, instr(l_object, '.') + 1)));
    else
      l_owner := upper(user); -- 默认使用当前用户作为所有者
      l_table := upper(l_object);
    end if;

    -- 查询权限是否已存在
    select count(1)
      into v_exists
      from dba_tab_privs
     where grantee = upper('MYROLE')
       and owner = l_owner
       and table_name = l_table
       and privilege = upper(l_privilege);

    -- 权限不存在时执行GRANT并计数
    if v_exists = 0 then
      execute immediate 'grant ' || p_grant_stmt || ' to MYROLE';
      v_change_count := v_change_count + 1;
    end if;
  exception
    when others then
      -- 按需处理异常（如权限不足、对象不存在等）
      raise;
  end grant_object_priv;
begin
  grant_object_priv('select on SCOTT.FOO');
  grant_object_priv('select on BAR');
  grant_object_priv('execute on BAZ');

  dbms_output.put_line('实际变更次数: ' || v_change_count);
end;
/

2. 处理系统权限（如CREATE TABLE、CONNECT） #

通过查询DBA_SYS_PRIVS或USER_SYS_PRIVS判断权限是否存在：

declare
  v_change_count number := 0;
  v_exists       number;

  procedure grant_system_priv(p_privilege in varchar2) is
  begin
    select count(1)
      into v_exists
      from dba_sys_privs
     where grantee = upper('MYROLE')
       and privilege = upper(p_privilege);

    if v_exists = 0 then
      execute immediate 'grant ' || p_privilege || ' to MYROLE';
      v_change_count := v_change_count + 1;
    end if;
  exception
    when others then
      raise;
  end grant_system_priv;
begin
  grant_system_priv('CREATE TABLE');
  grant_system_priv('CONNECT');

  dbms_output.put_line('实际变更次数: ' || v_change_count);
end;
/

3. 处理角色授予（如GRANT DBA TO MYROLE） #

通过查询DBA_ROLE_PRIVS判断角色是否已被授予：

declare
  v_change_count number := 0;
  v_exists       number;

  procedure grant_role(p_role in varchar2) is
  begin
    select count(1)
      into v_exists
      from dba_role_privs
     where grantee = upper('MYROLE')
       and granted_role = upper(p_role);

    if v_exists = 0 then
      execute immediate 'grant ' || p_role || ' to MYROLE';
      v_change_count := v_change_count + 1;
    end if;
  exception
    when others then
      raise;
  end grant_role;
begin
  grant_role('DBA');
  grant_role('RESOURCE');

  dbms_output.put_line('实际变更次数: ' || v_change_count);
end;
/

注意事项 #

• 权限访问：查询DBA_*视图需要用户拥有SELECT_CATALOG_ROLE或对应权限，普通用户可改用ALL_*或USER_*视图，但结果范围会受限。
• 大小写问题：Oracle数据字典中的权限、用户、对象名称均以大写存储，需确保查询时统一转换为大写，避免匹配失败。
• 异常处理：需添加适当的异常捕获逻辑，处理权限不足、对象不存在等错误场景。

内容的提问来源于stack exchange，提问作者Adam