当前位置：文江博客话题详情

Oracle - 如何创建只读用户

发布于 2024-12-06 07:17:25 字数 37 浏览 0 评论 0原文

可以在 Oracle 数据库中创建只读数据库用户吗？如何？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

阳光的暖冬 2024-12-13 07:17:25

Oracle 数据库中的用户仅具有您授予的权限。因此，您只需不授予任何其他权限即可创建只读用户。

当您创建用户时，

CREATE USER ro_user
 IDENTIFIED BY ro_user
 DEFAULT TABLESPACE users
 TEMPORARY TABLESPACE temp;

该用户甚至没有登录数据库的权限。您可以授予该权限

GRANT CREATE SESSION to ro_user

，然后您可以继续授予您想要的任何读取权限。例如，如果您希望 RO_USER 能够查询 SCHEMA_NAME.TABLE_NAME，您可以执行类似的操作

GRANT SELECT ON schema_name.table_name TO ro_user

，但是，一般来说，您最好创建一个角色，并授予角色的对象权限，以便您可以将该角色授予不同的用户。类似

创建角色

CREATE ROLE ro_role;

授予角色对特定模式中每个表的 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;

然后将该角色授予用户

GRANT ro_role TO ro_user;

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.

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_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 the role

CREATE ROLE ro_role;

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;

And then grant the role to the user

GRANT ro_role TO ro_user;

回复收藏 0 原文

栖竹 2024-12-13 07:17:25

create user ro_role identified by ro_role;
grant create session, select any table, select any dictionary to ro_role;

create user ro_role identified by ro_role;
grant create session, select any table, select any dictionary to ro_role;

回复收藏 0 原文

眉目亦如画i 2024-12-13 07:17:25

例如，作为用户系统执行以下步骤。

将 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;

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.

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;

回复收藏 0 原文