Oracle-如何创建只读用户 [英] Oracle - How to create a readonly user

问题描述

是否可以在Oracle数据库上创建只读数据库用户? 怎么样?

It's possible create a readonly database user at an Oracle Database? How?

推荐答案

Oracle数据库中的用户仅具有您授予的特权.因此，您可以通过不授予任何其他特权来创建只读用户.

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.

创建用户时

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

，然后就可以授予所需的任何读取特权.例如，如果您希望RO_USER能够查询SCHEMA_NAME.TABLE_NAME，则可以执行类似

and then you can go about granting whatever read privileges you want. For example, if you want RO_USER to be able to query SCHEMA_NAME.TABLE_NAME, you would do something like

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 ROLE ro_role;

授予角色在特定模式下的每个表上的SELECT访问权限

Grant the role SELECT access on every table in a particular schema

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;

然后将角色授予用户

GRANT ro_role TO ro_user;

这篇关于Oracle-如何创建只读用户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！