授予用户对 Oracle 架构的所有权限
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27353642/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Grant all privileges to user on Oracle schema
提问by Frank Martin
Is there a way to grant all privileges to a user on Oracle schema? I tried the following command but it only grants permission on specific tables in a schema. What I want is to give this user all permissions on a given schema.
有没有办法在 Oracle 模式上向用户授予所有权限？我尝试了以下命令，但它只授予对架构中特定表的权限。我想要的是授予此用户对给定架构的所有权限。
GRANT ALL ON MyTable TO MyUser;
回答by Wernfried Domscheit
You can do it in a loop and grant by dynamic SQL:
您可以在循环中执行并通过动态 SQL 授予：
BEGIN
FOR objects IN
(
SELECT 'GRANT ALL ON "'||owner||'"."'||object_name||'" TO MyUser' grantSQL
FROM all_objects
WHERE owner = 'MY_SCHEMA'
AND object_type NOT IN
(
--Ungrantable objects. Your schema may have more.
'SYNONYM', 'INDEX', 'INDEX PARTITION', 'DATABASE LINK',
'LOB', 'TABLE PARTITION', 'TRIGGER'
)
ORDER BY object_type, object_name
) LOOP
BEGIN
EXECUTE IMMEDIATE objects.grantSQL;
EXCEPTION WHEN OTHERS THEN
--Ignore ORA-04063: view "X.Y" has errors.
--(You could potentially workaround this by creating an empty view,
-- granting access to it, and then recreat the original view.)
IF SQLCODE IN (-4063) THEN
NULL;
--Raise exception along with the statement that failed.
ELSE
raise_application_error(-20000, 'Problem with this statement: ' ||
objects.grantSQL || CHR(10) || SQLERRM);
END IF;
END;
END LOOP;
END;
/
回答by user2372670
If you want to grant privileges to all tables in a specific schema:
如果要向特定模式中的所有表授予权限：
BEGIN
FOR x IN (select *from all_tables where OWNER = 'schema name')
LOOP
EXECUTE IMMEDIATE 'GRANT SELECT ON '||x.OWNER||'.'|| x.table_name || ' TO user name';
END LOOP;
END;
