oracle 对特定用户拥有的所有表授予选择权
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/187886/
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 Select on all Tables Owned By Specific User
提问by Mark Roddy
I need to grant select permission for all tables owned by a specific user to another user. Can I do this with a single command along the lines of:
我需要将特定用户拥有的所有表的选择权限授予另一个用户。我可以用一个命令来做到这一点：
Grant Select on OwningUser.* to ReceivingUser
Or do I have to generate the sql for each table with something along the lines of:
或者我是否必须为每个表生成 sql ，其中包含以下内容：
Select 'GRANT SELECT ON OwningUser.'||Table_Name||'TO ReceivingUser'
From All_Tables Where Owner='OWNINGUSER'
回答by DCookie
Well, it's not a single statement, but it's about as close as you can get with oracle:
嗯，这不是一个单一的声明，但它与 oracle 的关系非常接近：
BEGIN
FOR R IN (SELECT owner, table_name FROM all_tables WHERE owner='TheOwner') LOOP
EXECUTE IMMEDIATE 'grant select on '||R.owner||'.'||R.table_name||' to TheUser';
END LOOP;
END;
回答by user2729366
yes, its possible, run this command:
是的，有可能，运行以下命令：
lets say you have user called thoko
假设您有用户调用 thoko
grant select any table, insert any table, delete any table, update any table to thoko;
note: worked on oracle database
注意：在oracle数据库上工作
回答by dcvetkov
tables + views + error reporting
表 + 视图 + 错误报告
SET SERVEROUT ON
DECLARE
o_type VARCHAR2(60) := '';
o_name VARCHAR2(60) := '';
o_owner VARCHAR2(60) := '';
l_error_message VARCHAR2(500) := '';
BEGIN
FOR R IN (SELECT owner, object_type, object_name
FROM all_objects
WHERE owner='SCHEMANAME'
AND object_type IN ('TABLE','VIEW')
ORDER BY 1,2,3) LOOP
BEGIN
o_type := r.object_type;
o_owner := r.owner;
o_name := r.object_name;
DBMS_OUTPUT.PUT_LINE(o_type||' '||o_owner||'.'||o_name);
EXECUTE IMMEDIATE 'grant select on '||o_owner||'.'||o_name||' to USERNAME';
EXCEPTION
WHEN OTHERS THEN
l_error_message := sqlerrm;
DBMS_OUTPUT.PUT_LINE('Error with '||o_type||' '||o_owner||'.'||o_name||': '|| l_error_message);
CONTINUE;
END;
END LOOP;
END;
/
回答by J. Chomel
From http://psoug.org/reference/roles.html, create a procedure on your database for your user to do it:
从http://psoug.org/reference/roles.html，在您的数据库上为您的用户创建一个过程：
CREATE OR REPLACE PROCEDURE GRANT_SELECT(to_user in varchar2) AS
CURSOR ut_cur IS SELECT table_name FROM user_tables;
RetVal NUMBER;
sCursor INT;
sqlstr VARCHAR2(250);
BEGIN
FOR ut_rec IN ut_cur
LOOP
sqlstr := 'GRANT SELECT ON '|| ut_rec.table_name || ' TO ' || to_user;
sCursor := dbms_sql.open_cursor;
dbms_sql.parse(sCursor,sqlstr, dbms_sql.native);
RetVal := dbms_sql.execute(sCursor);
dbms_sql.close_cursor(sCursor);
END LOOP;
END grant_select;
