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

青年数据库学习互助会 2025-01-10

在日常的运维中，经常会遇到只读账号需求（如报表，数据安全管控等），但是Oracle权限管理一直被诟病，因为在oracle 23ai之前都没有办法做schema级别的授权，如果想让用户A，拥有对用户B所有表的只读权限，需要将B用户下的所有表做单独赋权，这就非常麻烦，如果用户B又经常有新建表或者新建视图等，那么每增加一个表都需要单独赋权非常不方便，每次新增加一个表都需要DBA来介入做赋权，很是繁琐！

Oracle 23ai新特性schema级别授权：将用户test下所有表赋予norton用户只读权限。

SQL> grant select any table on schema test to norton;


复制

针对时不时就有开发同仁找我来处理无法访问新建的表的问题，我做了一个如下的脚本来定时刷权限。

这个脚本实现的是将ods和cache用户下的所有table和view赋予readrpt只读权限，并根据ddl的频率，设定为每日或者每周执行一次，刷新一次赋权即可

#!/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_FILE
SET SERVEROUTPUT ON
DECLARE
    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
复制