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.

Create User and Add Responsibility from backend

Adding new user in Oracle Apps (EBS) from back end

Application users in Oracle Apps (EBS) can be created from the front end as well as back end. The back end method for user creation can be used when you don’t have sysadmin access to the applciation or also it can be used to speed up the process when there are many userids to be created.

You will require apps access to connect to database  to run the package fnd_user_pkg.

fnd_user_pkg is the seeded generic API provided by Oracle.

Sample code to create a user (FND_USER) from backend and add responsibility.

DECLARE
v_user_name VARCHAR2 (100) := upper('&Enter_User_Name');
v_description VARCHAR2 (100) := 'Put Any description as like New User, etc..';
 v_app_short_name VARCHAR2 (100);
 v_resp_key VARCHAR2 (100);
 CURSOR user_cur IS
 select a.application_short_name, r.responsibility_key
 from fnd_responsibility_vl r, fnd_application_vl a where
 r.application_id =a.application_id
 and R.responsibility_ID IN (SELECT RESPONSIBILITY_ID FROM fnd_user_resp_groups WHERE USER_ID=&from_userid AND END_dATE IS NULL and a.application_short_name not in ('SQLAP','SQLGL','JTF','FND'));
user_rec user_cur%ROWTYPE;
BEGIN
 fnd_user_pkg.createuser
(x_user_name => v_user_name,
 x_owner => NULL,
 x_unencrypted_password => '&input_password',
 x_session_number => 0,
 x_start_date => SYSDATE,
 x_end_date => NULL,
 x_last_logon_date => NULL,
 x_description => v_description,
 x_password_date => NULL,
 x_password_accesses_left => NULL,
 x_password_lifespan_accesses => NULL,
 x_password_lifespan_days => NULL,
 x_employee_id => NULL,
 x_email_address => NULL,
 x_fax => NULL,
 x_customer_id => NULL,
 x_supplier_id => NULL,
 x_user_guid => NULL,
 x_change_source => NULL
 );
 COMMIT;
 OPEN user_cur;
 LOOP
 FETCH user_cur INTO user_rec;
 EXIT WHEN user_cur%NOTFOUND;
 fnd_user_pkg.addresp(username => v_user_name
 ,resp_app => user_rec.application_short_name
 ,resp_key => user_rec.responsibility_key
 ,security_group => 'STANDARD'
 ,description => NULL
 ,start_date => SYSDATE
 ,end_date => null);
 END LOOP;
 CLOSE user_cur;
commit;
END;
/