Create an APPS READONLY user:

There are 2 ways to achieve this:

1) Create a user and grant select option to that user.
This will have to be done individually for all users that you want to have as read only.

2) Create a readonly role and assign users this role.
This will have to be done once and then assign this role to the users.

1. Create a user and grant select option to that user.

Step 1: Connect as sysdba and create the database user to be used for apps read only schema.

$sqlplus / as sysdba
SQL> create user appsro identified by appsro default tablespace APPS_TS_TX_DATA;
User created.
SQL> grant connect to appsro;
Grant succeeded.
SQL> grant select any table to appsro;
Grant succeeded.

Step 2: Connect with the appsro user and run the SQL commands:
SQL> conn appsro
Enter password:
Connected.
SQL> show user
USER is "APPSRO"

Step 3: Try to access an apps schema table
SQL> select count(*) from apps.po_headers_all;
COUNT(*)
----------
132206

Step 4: Try to update an apps schema table
SQL> update apps.po_headers_all set attribute1 = '1' where po_header_id = 126;
update apps.po_headers_all set attribute1 = '1' where po_header_id = 126
*
ERROR at line 1:
ORA-01031: insufficient privileges

2. Create a read only role and assign to users this role.
Step 1: Connect as sysdba and create the database user to be used for apps read only schema.

SQL> create user appsro identified by appsro default tablespace APPS_TS_TX_DATA;
User created.
SQL> grant connect to appsro;
Grant succeeded.
SQL> create role role_readonly;
Role created.
SQL> grant select any table to role_readonly;
Grant succeeded.
SQL> grant role_readonly to appsro;
Grant succeeded.

Step 2: Connect with the appsro user and run the SQL commands:
SQL> conn appsro
Enter password:
Connected.
SQL> show user
USER is "APPSRO"

Step 3: Try to access an apps schema table
SQL> select count(*) from apps.po_headers_all;
COUNT(*)
----------
132206

Step 4: Try to update an apps schema table
SQL> update apps.po_headers_all set attribute1 = '1' where po_header_id = 126;
update apps.po_headers_all set attribute1 = '1' where po_header_id = 126
*
ERROR at line 1:
ORA-01031: insufficient privileges

Example: Grant another user the 'read only' role

SQL> create user apps_readonly identified by apps_readonly;
User created.
SQL> grant connect to apps_readonly;
Grant succeeded.
SQL> grant role_readonly to apps_readonly;
Grant succeeded.
SQL> conn apps_readonly
Enter password:
Connected.
SQL> show user
USER is "APPS_READONLY"
SQL> select count(*) from apps.fnd_concurrent_requests;
COUNT(*)
----------
75724
SQL> select count(*) from apps.po_headers_all;
COUNT(*)
----------
132206
SQL> update apps.po_headers_all set attribute1 = '1' where po_header_id = 126;
update apps.po_headers_all set attribute1 = '1' where po_header_id = 126
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> exit;

You are done. Now your users can use “appsro” schema to have the read only access to Apps Data.

Prevent users from login into a database within defined period.

Description:

It shows how to set up the database security so that users cannot log into the database outside defined time-window. Here we are using Event Triggers.

Steps to be followed :

Step 1 : Create a user (or existing user) and check, whether is able to connect.

SQL> connect username/*****
Connected.

Now we able to connect, afterthat login as sys user and create a trigger .

Step 2 : Create an event trigger that allow the user to login the database only on Monday to Friday included 9 AM to 6 PM.

Except DBA Privilege user, remaining users connection occurs only within the time frame.

SQL> create or replace trigger prevent_logon_database_trigger after logon on database
begin
if (to_char(sysdate,'D') not between '2' and '6') ------(2 for monday and 6 for friday)
or (to_char(sysdate, 'HH24')  between '09' and '18') 

-----or (to_char(sysdate, 'HH24') not between '09' and '18') 

then RAISE_APPLICATION_ERROR(-20001, 'Please login on your given date');
end if;
end;
/

Trigger created.

OR

SQL>create or replace trigger prevent_logon_database_trigger after logon on database 
declare
current_hour pls_integer;
restricted_user boolean;
restricted_time boolean;
begin
restricted_user := user = 'USERNAME';
current_hour    := extract(hour from systimestamp);
restricted_time := (current_hour < 18 or current_hour >= 9);
if restricted_user and restricted_time 

then raise_application_error(-20001, 'You can''t come in!');
end if;
end ;
/

Trigger created.

Step 3 : Now check whether you are able to connect to the database.

Example : It’s Saturday normal user cannot be able to log into the database. We have created trigger for all the users to login into the database on Monday and Friday included 9AM to 6PM. So the error throws .

SQL> connect username/*****

ERROR:

ORA-00604: error occurred at recursive SQL level 1
ORA-20001: You are not allowed to log into database now.
ORA-06512: at line 3
Warning: You are no longer connected to ORACLE.

Step 4 : Disable trigger.

SQL>Drop trigger prevent_logon_database_trigger;

Trigger dropped.