首页 > 头条

Oracle将用户权限移植到另一个用户上

来源:博客园 2023-06-30 17:36:56


(资料图)

问题描述:往往有些需求,A用户依赖于B用户创建,A用户想要获取B用户的权限,oracle没找到有命令可以直接继承,只能写一些语句来代替

1.查询用户下的权限有哪些

SET PAGESIZE 100SET LINESIZE 200COLUMN owner FORMAT A20COLUMN table_name FORMAT A30COLUMN privilege FORMAT A30SELECT owner, table_name, privilege FROM DBA_TAB_PRIVS WHERE grantee = "A_user";SELECT owner, table_name, privilege FROM DBA_TAB_PRIVS WHERE grantee = "B_user";

2.利用程序执行体把A_user用户权限授权给B_user

BEGIN  FOR priv_rec IN (SELECT * FROM DBA_TAB_PRIVS WHERE grantee = "A_user") LOOP    EXECUTE IMMEDIATE "GRANT " || priv_rec.privilege || " ON " || priv_rec.owner || "." || priv_rec.table_name || " TO B_user";  END LOOP;END;/

3.把A_user用户下除了自己本身以外的其他所有权限授权给B_user

BEGIN  FOR priv_rec IN (SELECT * FROM DBA_TAB_PRIVS WHERE grantee = "A_user" AND owner != "A_user") LOOP    EXECUTE IMMEDIATE "GRANT " || priv_rec.privilege || " ON " || priv_rec.owner || "." || priv_rec.table_name || " TO B_user";  END LOOP;END;/

4.在程序执行的过程中,可能会发生一些权限依赖的视图会编译错误,导致执行失败,可以在程序执行体中加入异常处理

BEGIN  FOR priv_rec IN (SELECT * FROM DBA_TAB_PRIVS WHERE grantee = "A_user" AND owner != "A_user") LOOP    BEGIN      EXECUTE IMMEDIATE "GRANT " || priv_rec.privilege || " ON " || priv_rec.owner || "." || priv_rec.table_name || " TO B_user";    EXCEPTION      WHEN OTHERS THEN        NULL; -- Ignore the error and continue to the next iteration    END;  END LOOP;END;/

5.执行完成后,查询B_user与A_user是否有权限关联

SELECT * FROM DBA_TAB_PRIVS WHERE grantee = "B_user" AND owner = "A_user";
上一篇 下一篇
推荐阅读
相关新闻