Oracle - How to create a readonly user

Question

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

User · Answer

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;

User · Answer

create user ro role identified by ro role  grant create session  select any table  select any dictionary to ro role

User · Answer

It is not strictly possible in default db due to the many public executes that each user gains automatically through public

User · Answer

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

User · Answer

you can create user and grant privilege  create user read only identified by read only  grant create session select any table to read only

[oracle] Oracle - How to create a readonly user

Examples related to oracle

Examples related to readonly