How to create a global readonly user in Oracle

Jephe Wu -

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


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