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

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

 

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

SET PAGESIZE 100
SET LINESIZE 200

COLUMN owner FORMAT A20
COLUMN table_name FORMAT A30
COLUMN privilege FORMAT A30

SELECT 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';