如何为当日创建的表向指定角色授权？现有SQL执行失败求助

你尝试的SQL语句执行失败，核心原因是Oracle的GRANT命令不支持通过子查询动态指定要授权的对象，必须明确指定具体表名，或者通过动态SQL生成并执行授权语句。

解决方案：使用PL/SQL动态生成授权语句 #

通过PL/SQL块遍历当日创建的符合条件的表，逐个生成GRANT语句并执行，代码如下：

DECLARE
    v_grant_sql VARCHAR2(1000);
BEGIN
    FOR rec IN (
        SELECT owner || '.' || object_name AS full_table_name
        FROM sys.all_objects
        WHERE object_type = 'TABLE'
          AND owner NOT IN ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS', 
                            'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS','OUTLN', 
                            'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST','WKSYS', 
                            'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 
                            'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
                            'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC')  
          AND TRUNC(created) = TRUNC(SYSDATE) -- 精确匹配当日创建的表，替换原SYSDATE-1
    ) LOOP
        v_grant_sql := 'GRANT SELECT ON ' || rec.full_table_name || ' TO PSREAD_ROLE_W';
        EXECUTE IMMEDIATE v_grant_sql;
        DBMS_OUTPUT.PUT_LINE('授权成功: ' || v_grant_sql);
    END LOOP;
END;
/

关键说明 #

• 执行该PL/SQL块的用户需要具备GRANT ANY SELECT权限，或者对每个目标表拥有GRANT SELECT的权限。
• 代码中用TRUNC(created) = TRUNC(SYSDATE)精确匹配当日创建的表，若需匹配最近24小时内创建的表，可改回created > SYSDATE - 1。
• 若当日无符合条件的表，块会直接结束，不会执行任何授权操作。

进阶：自动每日执行授权 #

如果需要每天自动对新创建的表执行授权，可通过DBMS_SCHEDULER创建定时任务，示例如下：

BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
        job_name        => 'GRANT_DAILY_NEW_TABLES',
        job_type        => 'PLSQL_BLOCK',
        job_action      => 'DECLARE
                                v_grant_sql VARCHAR2(1000);
                            BEGIN
                                FOR rec IN (
                                    SELECT owner || ''.'' || object_name AS full_table_name
                                    FROM sys.all_objects
                                    WHERE object_type = ''TABLE''
                                      AND owner NOT IN (''ANONYMOUS'',''CTXSYS'',''DBSNMP'',''EXFSYS'', ''LBACSYS'', 
                                                        ''MDSYS'', ''MGMT_VIEW'',''OLAPSYS'',''OWBSYS'',''ORDPLUGINS'', ''ORDSYS'',''OUTLN'', 
                                                        ''SI_INFORMTN_SCHEMA'',''SYS'',''SYSMAN'',''SYSTEM'', ''TSMSYS'',''WK_TEST'',''WKSYS'', 
                                                        ''WKPROXY'',''WMSYS'',''XDB'',''APEX_040000'', ''APEX_PUBLIC_USER'',''DIP'', 
                                                        ''FLOWS_30000'',''FLOWS_FILES'',''MDDATA'', ''ORACLE_OCM'', ''XS$NULL'',
                                                        ''SPATIAL_CSW_ADMIN_USR'', ''SPATIAL_WFS_ADMIN_USR'', ''PUBLIC'')  
                                      AND TRUNC(created) = TRUNC(SYSDATE)
                                ) LOOP
                                    v_grant_sql := ''GRANT SELECT ON '' || rec.full_table_name || '' TO PSREAD_ROLE_W'';
                                    EXECUTE IMMEDIATE v_grant_sql;
                                END LOOP;
                            END;',
        start_date      => SYSTIMESTAMP,
        repeat_interval => 'FREQ=DAILY; BYHOUR=23; BYMINUTE=0; BYSECOND=0;', -- 每天23点执行
        enabled         => TRUE,
        comments        => '每日向PSREAD_ROLE_W授予当日新创建表的SELECT权限'
    );
END;
/

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