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

(资料图片)

问题描述：往往有些需求，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";

关键词：