How to create a global readonly user in Oracle

Jephe Wu - http://linuxtechres.blogspot.com


Objective: create a Oracle user to have readonly access for all schemas in the database.


Steps:

• create user and assign tablespace first.

sqlplus / as sysdba
create user jephe identified by password default tablespace users temporary tablespace temp;

• create readonly role  as 'readonly' and assign privileges and users to it
  

sqlplus / as sysdba
drop role readonly;
create role readonly;
grant create session to readonly;
grant select any table to readonly;
grant select any sequence to readonly;
grant select any dictionary to readonly;
grant execute any type to readonly;
grant readonly to jephe;
exit;

•  You can switch current schema after login as jephe
  

select sys_context('USERENV','SESSION_USER') from dual;
select sys_context('USERENV','SESSION_SCHEMA') from dual;
alter session set current_schema=schemaname  
note: in DB2, use 'set schema = ABC' command, but the owner of the table created will be still under the connecting user.

note: you can check your privs after login:

select * from session_privs;
select * from session_roles;