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