12/11/2001 -- One of the best parts of writing this column on a regular basis is getting e-mails from readers like you asking for help in solving specific Oracle problems, or needing suggestions on how to use certain features of Oracle (and a few other databases). I even get a few readers correcting me, so I must state that in August's column I should have ended it as n'est ce pas instead of n'est pas. I feel doubly dumb considering I live up here in Canada where we're told that both English and French are our official languages (and I took French right up to the end of high school but switched to Russian in college...long story).

Now, with that correction made, we can move on to this month's topic. A couple of months ago, I received an e-mail from a reader asking how he could control access to his Oracle database instance using logon triggers so that certain users would have access only during working hours, while others would not be able to connect. The idea was to create a trigger that would check to see what time the connection attempt was being made and see if the user should have access at that time. If so, no problem and let the user keep going; if not, disconnect the user with an error message. Now, I read the e-mail and promptly got thrown into a rather large project, and, unfortunately, and with apologies, did not respond. Re-reading the e-mail recently, I thought that this would be something that others could be interested in, and so here we are.

Pouring the Concrete
The key elements of solving this problem can be stated as follows:

• Several individuals can make use of the database at the same time.
• Individuals can be grouped together based upon common traits.
• Access to the database needs to be controlled based upon time of day.
• Time of day restrictions apply to groups of individuals and to all individuals in the affected group.
• When a connection attempt is made by an individual, the database should determine whether the connection should be allowed to continue.

Now, in solving this problem we need to make use of two new features in Oracle 8i and later versions - logon event triggers and EXECUTE IMMEDIATE dynamic SQL syntax – as well as one old feature – roles. Roles can be assigned to users and then be verified by the logon event trigger to determine if the user should have access. The rules governing which roles have access at what time of day can be in the application code of the logon event trigger itself, or, if more complex rules are required, can be stored in a table in the SYS schema, which can then be queried to determine what the rules for the role happen to be. While storing rules in a table provides more flexibility, it does make the process slower and requires more precise logic. Remember, flexibility is a double-edged sword because complexity is on the other side: The two can rarely be separated.

Framing the Structure
In putting the framework together to solve this problem, we need to make sure that the following are true:

• Each individual that needs access to the database has a user account.
• Each grouping of users that needs database access will have an associated role created in the database.
• Each individual needs to be granted the corresponding role that would be verified by a database event logon trigger

Let's take these tasks one at a time. The first step is to issue a CREATE USER command for everyone that needs access. This is pretty simple, but then you need to also determine whether these users will be managed in Oracle, by the operating system (EXTERNALLY), or by a directory service (i.e. GLOBALLY). This is important because if you manage your users outside of Oracle, you should also manage your roles in the same way. In the short term, this means that you need to create O/S users and groups (in most environments) with the proper naming structure for the platform you are using, as well as the users and roles in Oracle. The upside of doing this is that any password or group membership change at the O/S level automatically translates to Oracle. In our example, we'll have Oracle itself manage the roles and users.

Let's connect to the instance as a DBA and create a few users and roles that will be managed by Oracle:

SQL> CREATE USER Damir IDENTIFIED BY password;

User created.

SQL> CREATE USER Anthony IDENTIFIED BY newpass;

User created.

SQL> CREATE USER Todd IDENTIFIED BY mypass;

User created.

SQL> CREATE USER Yury IDENTIFIED BY kitsilano;

User created.

SQL> CREATE ROLE DaytimeUser;

Role created.

SQL> CREATE ROLE AppAdminUser;

Role created.

SQL> CREATE ROLE GuestUser;

Role created.

Once you have decided how users and roles will be identified, you assign the roles to the user (if Oracle is managing security) or place the O/S user in the appropriate O/S group to grant them the role, as in the following example:

SQL> GRANT GuestUser TO Todd;

Grant succeeded.

SQL> GRANT DayTimeUser TO Yury;

Grant succeeded.

SQL> GRANT AppAdminUser TO Damir, Anthony;

Grant succeeded.

SQL> GRANT CREATE SESSION TO Damir, Anthony, Todd, Yury;

Grant succeeded.

The last grant is required so that the user can connect to the instance, if everything else is OK.

CREATE OR REPLACE TRIGGER TimeDayCheck
AFTER LOGON ON DATABASE
BEGIN

-- Check to see if the user is a DBA or has been granted
-- the AppAdminUser role. If so, let them continue.
IF DBMS_SESSION.IS_ROLE_ENABLED('DBA') OR
DBMS_SESSION.IS_ROLE_ENABLED('AppAdminUser')
THEN

-- Log the connection attempt in the audit log
INSERT INTO AccountConnections
(UserName, EventAction, EventDate,
IP_Address, InstanceNum, Status)

VALUES
(ora_login_user, ora_sysevent, sysdate,
ora_client_ip_address, ora_instance_num, 'ALLOWED');
EXECUTE IMMEDIATE 'COMMIT';

-- Check to see if the user has the DayTimeUser or
-- GuestUser role assigned and logging on during
-- normal business hours. The format mask of ‘D
-- on the date returns a number for the Day of
-- the week. 2 is Monday and 6 is Friday.

ELSIF (TO_NUMBER(TO_CHAR(SYSDATE,'HH24MI'))
BETWEEN 830 AND 1700 AND
TO_NUMBER(TO_CHAR(SYSDATE,'D')) BETWEEN 2 AND 6) AND (DBMS_SESSION.IS_ROLE_ENABLED('DaytimeUser') OR DBMS_SESSION.IS_ROLE_ENABLED('GuestUser'))
THEN

-- Log the connection attempt in the audit log
INSERT INTO AccountConnections
(UserName, EventAction, EventDate,
IP_Address, InstanceNum, Status)
VALUES
(ora_login_user, ora_sysevent, sysdate,
ora_client_ip_address, ora_instance_num, 'ALLOWED');
EXECUTE IMMEDIATE 'COMMIT';

ELSE
-- Log the connection attempt in the audit log
INSERT INTO AccountConnections
(UserName, EventAction, EventDate,
IP_Address, InstanceNum, Status)
VALUES
(ora_login_user, ora_sysevent, sysdate,
ora_client_ip_address, ora_instance_num, 'DENIED');
EXECUTE IMMEDIATE 'COMMIT';

-- Notify the user that they are not authorized
-- to log on at this time, log the attempt and
-- disconnect the user.
Raise_application_error(-20999,
'You are not authorized to connect at this time.');
EXECUTE IMMEDIATE 'DISCONNECT';

END IF;
END;

One thing needs to be noted about the trigger: In order to disconnect the user outside of allowed logon times, as well as to ensure that the AccountConnections table is updated with the attempt, you need to make use of the EXECUTE IMMEDIATE command to perform a dynamic SQL command. This is because neither COMMIT nor DISCONNECT are valid within a trigger.

In case you were wondering, the statement to create the AccountConnections table is the following:

Finishing Touches
You are probably saying that this is a rather simple example of how to control access using a logon event trigger, or that this can also be enforced outside of Oracle by using the operating system or a directory service, and you would be right. The point here is to demonstrate that Oracle’s event triggers can solve many problems if we remember that they’re there. One thing that should be added is a logoff trigger so you can completely track logon/logoff activity in a log. A simple example is the following:

CREATE OR REPLACE TRIGGER LogoffTrigger
BEFORE LOGOFF ON DATABASE
BEGIN

-- Log the user’s disconnection from the database.
INSERT INTO AccountConnections
(UserName, EventAction, EventDate, IP_Address, InstanceNum)
VALUES
(ora_login_user, ora_sysevent, sysdate,
ora_client_ip_address, ora_instance_num);
END;

So, if you have a problem to solve, remember that Oracle’s event triggers can provide a lot of functionality to track activity within the database, by users and administrators alike. Combined with other features, they provide a wealth of capability.

Is There a Oracle-Related Problem You Need Solved?
If you liked this month’s problem-oriented column let me know, and send me suggestions on future topics that you would like to see tackled here. I may even select your topic for an upcoming column (no promises, though!).