Q
Problem solve Get help with specific problems with your technologies, process and projects.

Restricting access to database via trigger

I'm trying to restrict access to a database via a trigger after logon. When we connect to the database through a tool like SQL*Plus, 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.

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.
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;

Dig Deeper on Oracle and SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close