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