Ask the Expert

Restricting access to database via trigger

Hello Brian. I'm trying to restrict access to a database via a trigger after logon to the database. As you know, when we connect to the database through a tool like SQL*Plus or PL/SQL Developer, the name of that program appears in the column "program" of the view v$session. However, I've got a sly end user and when he connects, the name of the program is not shown in that view, so he can log in normally skipping over the validation. I don't know if the problem is the version of the tools, or maybe something configured in his machine. Give me any suggestions about this and how I can restrict the access. Thanks in advance.

    Requires Free Membership to View

    Login

The PROGRAM column in V$SESSION relies on the application passing that information to Oracle. Oracle is not always able to read the program from the application, so the column's value is NULL. To stop this user from connecting to your database, you can write a simple AFTER LOGON trigger. In the main body of the code, check for the PROGRAM value to see if it is valid. If not, raise an exception. Something similar to the following: 
SELECT program INTO v_program FROM v$session
WHERE audsid=SYS_CONTEXT('USERENV','SESSIONID');
IF (v_program IS NULL) THEN
   RAISE_APPLICATION_ERROR(-20001,'Not a valid program');
END IF;

This was first published in June 2006

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top