Oracle - 如何创建只读用户
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7502438/
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
Oracle - How to create a readonly user
提问by Guilherme Ferreira
It's possible create a readonly database user at an Oracle Database? How?
可以在 Oracle 数据库中创建只读数据库用户吗？如何？
回答by Justin Cave
A user in an Oracle database only has the privileges you grant. So you can create a read-only user by simply not granting any other privileges.
Oracle 数据库中的用户只有您授予的权限。因此，您可以通过简单地不授予任何其他权限来创建只读用户。
When you create a user
创建用户时
CREATE USER ro_user
IDENTIFIED BY ro_user
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;
the user doesn't even have permission to log in to the database. You can grant that
用户甚至没有登录数据库的权限。你可以授予
GRANT CREATE SESSION to ro_user
and then you can go about granting whatever read privileges you want. For example, if you want RO_USERto be able to query SCHEMA_NAME.TABLE_NAME, you would do something like
然后你可以去授予你想要的任何读取权限。例如，如果您希望RO_USER能够查询SCHEMA_NAME.TABLE_NAME，您可以执行类似的操作
GRANT SELECT ON schema_name.table_name TO ro_user
Generally, you're better off creating a role, however, and granting the object privileges to the role so that you can then grant the role to different users. Something like
但是，通常最好创建一个角色，然后将对象权限授予该角色，以便随后可以将该角色授予不同的用户。就像是
Create the role
创建角色
CREATE ROLE ro_role;
Grant the role SELECT access on every table in a particular schema
授予角色 SELECT 对特定模式中每个表的访问权限
BEGIN
FOR x IN (SELECT * FROM dba_tables WHERE owner='SCHEMA_NAME')
LOOP
EXECUTE IMMEDIATE 'GRANT SELECT ON schema_name.' || x.table_name ||
' TO ro_role';
END LOOP;
END;
And then grant the role to the user
然后将角色授予用户
GRANT ro_role TO ro_user;
回答by Okloks
create user ro_role identified by ro_role;
grant create session, select any table, select any dictionary to ro_role;
回答by dwe
Execute the following procedure for example as user system.
例如作为用户系统执行以下步骤。
Set p_owner to the schema owner and p_readonly to the name of the readonly user.
将 p_owner 设置为架构所有者，将 p_readonly 设置为只读用户的名称。
create or replace
procedure createReadOnlyUser(p_owner in varchar2, p_readonly in varchar2)
AUTHID CURRENT_USER is
BEGIN
execute immediate 'create user '||p_readonly||' identified by '||p_readonly;
execute immediate 'grant create session to '||p_readonly;
execute immediate 'grant select any dictionary to '||p_readonly;
execute immediate 'grant create synonym to '||p_readonly;
FOR R IN (SELECT owner, object_name from all_objects where object_type in('TABLE', 'VIEW') and owner=p_owner) LOOP
execute immediate 'grant select on '||p_owner||'.'||R.object_name||' to '||p_readonly;
END LOOP;
FOR R IN (SELECT owner, object_name from all_objects where object_type in('FUNCTION', 'PROCEDURE') and owner=p_owner) LOOP
execute immediate 'grant execute on '||p_owner||'.'||R.object_name||' to '||p_readonly;
END LOOP;
FOR R IN (SELECT owner, object_name FROM all_objects WHERE object_type in('TABLE', 'VIEW') and owner=p_owner) LOOP
EXECUTE IMMEDIATE 'create synonym '||p_readonly||'.'||R.object_name||' for '||R.owner||'."'||R.object_name||'"';
END LOOP;
FOR R IN (SELECT owner, object_name from all_objects where object_type in('FUNCTION', 'PROCEDURE') and owner=p_owner) LOOP
execute immediate 'create synonym '||p_readonly||'.'||R.object_name||' for '||R.owner||'."'||R.object_name||'"';
END LOOP;
END;
回答by user128364
you can create user and grant privilege
您可以创建用户并授予权限
create user read_only identified by read_only; grant create session,select any table to read_only;
创建由 read_only 标识的用户 read_only；授予创建会话，选择任何表为只读；
回答by Paul
It is not strictly possible in default db due to the many public executes that each user gains automatically through public.
由于每个用户通过公共自动获得的许多公共执行，在默认数据库中严格来说是不可能的。
