Oracle将用户权限移植到另一个用户上
阅读原文时间:2023年08月27日阅读:2

问题描述:往往有些需求,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';