Oracle - How to create a readonly user

Question

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

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

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

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