Step by Step Create read only user in oracle

Step to Create Read only User in Oracle

create role read_only; --- Give new Role name

Oracle Auditing is configured to use the Database Audit Trail, type the following command:

grant select on DBA_AUDIT_TRAIL to read_only;

create user gaurav identified by password; -- Create User

Oracle Auditing is configured to use the XML Audit Trail, type the following command:

grant select on V_$XML_AUDIT_TRAIL to gaurav;

grant connect to gaurav;

grant read_only to gaurav;

SQL> spool table.sql

SQL> SELECT 'GRANT SELECT ON APPS.' ||TABLE_NAME || ' TO read_only;' FROM DBA_TABLES WHERE OWNER='APPS';
'GRANTSELECTONAPPS.'||TABLE_NAME||'TO read_only;'
----------------------------------------------------------------------
GRANT SELECT ON APPS.HYA_PERSONAL_VAR TO read_only;
GRANT SELECT ON APPS.HFM_ERRORLOG TO read_only;
GRANT SELECT ON APPS.HSX_DATASOURCES TO read_only;

'GRANTSELECTONAPPS.'||TABLE_NAME||'TO read_only;'
----------------------------------------------------------------------
GRANT SELECT ON APPS.HSV_ACTIVITY_USERS TO read_only;
GRANT SELECT ON APPS.HSV_ACTIVITY_SESSIONS TO read_only;
GRANT SELECT ON APPS.HSV_USERS_ON_SYSTEM TO read_only;
GRANT SELECT ON APPS.HSV_ACTIVITY_KILL_USERS TO read_only;
GRANT SELECT ON APPS.HSV_ACTIVITY_NO_ACCESS TO read_only;

8145 rows selected.

SQL> SELECT 'GRANT SELECT ON APPS.' ||VIEW_NAME || ' TO read_only;' FROM DBA_VIEWS WHERE OWNER='APPS';

no rows selected

SQL> spool off

SQL>@table.sql


Creating Trigger:-

create or replace trigger log_on_after_gaurav
after logon ON gaurav.SCHEMA
BEGIN
EXECUTE IMMEDIATE 'alter session set CURRENT_SCHEMA = APPS';
END;
/

It's Done. . . .