Oracle只读账号如何简化权限管理

SQL>grantselectanytableon schema test to norton;

#!/bin/bash# 抓取Oracl环境变量source /home/oracle/.bash_profile# 日志文件路径LOG_FILE="/tmp/oracle_grant.log"
# 清空日志文件并记录开始时间echo "==== 权限刷新开始 $(date) ====" > $LOG_FILE
# 赋予READRPT用户对ODS/CACHE用户下所有table view SELECT权限sqlplus -s / as sysdba <<EOF >> $LOG_FILESET SERVEROUTPUT ONDECLARE    v_sql VARCHAR2(1000);BEGIN    -- 处理ODS用户的表和视图    FOR tbl IN (        SELECT table_name FROM all_tables WHERE owner = 'ODS'        UNION        SELECT view_name AS table_name FROM all_views WHERE owner = 'ODS'    ) LOOP        BEGIN            v_sql := 'GRANT SELECT ON ODS."' || tbl.table_name || '" TO READRPT';            EXECUTE IMMEDIATE v_sql;            DBMS_OUTPUT.PUT_LINE('Granted SELECT on ODS.' || tbl.table_name);        EXCEPTION WHEN OTHERS THEN            DBMS_OUTPUT.PUT_LINE('Failed for table/view: ODS.' || tbl.table_name || ' - ' || SQLERRM);        END;    END LOOP;
    -- 处理CACHE用户的表和视图    FOR tbl IN (        SELECT table_name FROM all_tables WHERE owner = 'CACHE'        UNION        SELECT view_name AS table_name FROM all_views WHERE owner = 'CACHE'    ) LOOP        BEGIN            v_sql := 'GRANT SELECT ON CACHE."' || tbl.table_name || '" TO READRPT';            EXECUTE IMMEDIATE v_sql;            DBMS_OUTPUT.PUT_LINE('Granted SELECT on CACHE.' || tbl.table_name);        EXCEPTION WHEN OTHERS THEN            DBMS_OUTPUT.PUT_LINE('Failed for table/view: CACHE.' || tbl.table_name || ' - ' || SQLERRM);        END;    END LOOP;END;/EXIT;EOF
# 记录完成时间echo "==== 权限刷新完成 $(date) ====" >> $LOG_FILE