Step to Create Read only User in Oracle
create role read_only; --- Give new Role name
create user gaurav identified by password; -- Create User
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. . . .
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. . . .