在表1(用户列表)上授予选择权

Question

我的目标是将选择权授予下面三个表中的所有被授予者。我无法运行下面的plsql脚本。

以下政治家作品：在ZIP上向用户授予选择权_0；

我将其放入for each循环中，得到以下错误：中不允许表、视图或序列引用"此上下文">

在这种情况下，我如何引用该表？

CREATE OR REPLACE PROCEDURE granting AS
BEGIN
FOR users IN (SELECT DISTINCT GRANTEE FROM USER_TAB_PRIVS where TABLE_NAME = 'LAND' or TABLE_NAME = 'STREET' or TABLE_NAME = 'ZIP')
LOOP
BEGIN
EXECUTE IMMEDIATE 'GRANT SELECT ON ''' || PLZORT || ''' TO ''' ||  users||'';
EXECUTE IMMEDIATE 'GRANT SELECT ON ''' || STREET || ''' TO ''' ||  users||'';
EXECUTE IMMEDIATE 'GRANT SELECT ON ''' || ZIP || ''' TO ''' ||  users||'';
END;
END LOOP;
END granting ;

Answer 1

如果您只需要授予对这3个表的访问权限，您可以执行下一步：

CREATE OR REPLACE PROCEDURE granting AS
BEGIN
FOR users IN (SELECT DISTINCT GRANTEE FROM USER_TAB_PRIVS where TABLE_NAME = 'LAND' or TABLE_NAME = 'STREET' or TABLE_NAME = 'ZIP')
LOOP
BEGIN
EXECUTE IMMEDIATE 'GRANT SELECT ON PLZORT TO ' ||  users.GRANTEE ;
EXECUTE IMMEDIATE 'GRANT SELECT ON STREET TO ' ||  users.GRANTEE ;
EXECUTE IMMEDIATE 'GRANT SELECT ON ZIP  TO ' ||  users.GRANTEE ;
END;
END LOOP;
END granting ;

但更可能的情况是，您希望管理用户组。为此，您可以选择创建角色。类似的东西：

CREATE ROLE ROLE_ADDRES_RO;

然后，您可以将对此表的访问权限授予此角色：

GRANT SELECT ON PLZORT TO ROLE_ADDRES_RO; 
GRANT SELECT ON STREET TO ROLE_ADDRES_RO; 
GRANT SELECT ON ZIP    TO ROLE_ADDRES_RO; 
GRANT SELECT ON LAND   TO ROLE_ADDRES_RO; 

然后手动或通过类似的脚本将此角色授予用户列表：

DECLARE
BEGIN
FOR users IN (SELECT DISTINCT GRANTEE FROM USER_TAB_PRIVS where TABLE_NAME = 'LAND' or TABLE_NAME = 'STREET' or TABLE_NAME = 'ZIP')
LOOP
BEGIN
EXECUTE IMMEDIATE 'GRANT ROLE_ADDRES_RO TO ' ||  users.GRANTEE ;
END;
END LOOP;
END ;

在这种情况下，角色的下一次更新将更加透明。并且只能通过一个数据库调用授予

Answer 2

请在下面找到我的示例：

CREATE OR REPLACE PROCEDURE granting AS
BEGIN
FOR rec IN (SELECT DISTINCT GRANTEE, TABLE_NAME FROM USER_TAB_PRIVS where TABLE_NAME in ('LAND' , 'STREET' , 'ZIP'))
LOOP
BEGIN
EXECUTE IMMEDIATE 'GRANT SELECT ON ' || rec.TABLE_NAME || ' TO ' || rec.GRANTEE;
END;
END LOOP;
END granting ;