Step by Step Create read only user in oracle

Step to Create Read only User in Oracle

create role muthu_role;   --- 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 muthu_role;

create user muthu 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 muthu;

grant connect to muthu;

grant muthu_role to muthu;

SQL> spool table.sql
SQL> SELECT 'GRANT SELECT ON APPS.' ||TABLE_NAME || ' TO MUTHU_ROLE;' FROM DBA_TABLES --WHERE OWNER='APPS';

GRANT SELECT ON APPS.HYA_PERSONAL_VAR TO MUTHU_ROLE;
GRANT SELECT ON APPS.HFM_ERRORLOG TO MUTHU_ROLE;
GRANT SELECT ON APPS.HSX_DATASOURCES TO MUTHU_ROLE;

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

8145 rows selected.

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

no rows selected

SQL> spool off
SQL>@table.sql


Creating Trigger:-

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