Oracle数据库创建只读用户并批量授权

授权只读访问某个用户下的数据

一、创建角色

create role select_all_qxphis_table;

二、将qxphis用户的表授权给角色

declare

    cursor c_tabname is select table_name from dba_tables where owner='QXPHIS';
    v_tabname dba_tables.table_name%TYPE;

    sqlstr  varchar2(200);

begin

    open c_tabname;
    
    loop
        fetch c_tabname into v_tabname;
        exit when c_tabname%NOTFOUND;
        sqlstr:='grant select on qxphis.'||v_tabname||' to select_all_qxphis_table';
   
        execute immediate sqlstr;

    end loop;

    close c_tabname;

end;

/

三、将角色授予给qxphis

grant select_all_qxphis_table to qxphis with admin option;

---如果不使用角色，可以使用以下类似SQL将表批量授权给只读用户:
BEGIN
    FOR tbl IN (SELECT table_name FROM all_tables WHERE owner = 'HR') LOOP
        EXECUTE IMMEDIATE 'GRANT SELECT ON HR.' || tbl.table_name || ' TO read_only_user';
    END LOOP;
END;
/

四、创建同义词

declare

    cursor c_tabname is select table_name from dba_tables where owner='QXPHIS';
    v_tabname dba_tables.table_name%TYPE;

    sqlstr  varchar2(200);

begin

    open c_tabname;
    
    loop
        fetch c_tabname into v_tabname;
        exit when c_tabname%NOTFOUND;
        sqlstr:='create or replace synonym qxphis.'||v_tabname||' for qxphis.'||v_tabname||'';

        execute immediate sqlstr;

    end loop;

    close c_tabname;

end;

/

五、授权其它权限

---如果只读用户需要访问数据字典，可以授权访问数据字典权限
GRANT SELECT ANY DICTIONARY TO readonly_user;