[Oracle] Using Oracle Database triggers for security

Hi all, in this tutorial we are going to show how to use a trigger to enforce a bit of security in our database.

Triggers can be very useful when a user logins in order to verify permissions and privileges. For example, suppose that in our database we have two different kinds of schemas:

• Personal schemas: these are schemas that users should connect to and normally they should have limited privileges (for example only read privilege)
• Application schemas: these are the owners of the database data. Access to these schemas should be restricted only to specific users

A trigger could simply check if a personal osuser is connecting to a personal schemas and prevent it from connecting to an application schema.

Let’s see a simple test trigger:

CREATE OR REPLACE NONEDITIONABLE TRIGGER "SYS"."DOF_TRIGGER_ON_LOGON" AFTER LOGON ON DATABASE 
DECLARE

CURSOR user_prog IS
	SELECT upper(osuser) as USER_DOF, machine
	FROM v$session
	WHERE username in ('APP_SCHEMA')
		AND upper(machine) like '%USER_MACHINE%'
		AND audsid=sys_context('USERENV','SESSIONID');

--Assign the cursor to a PL/SQL record.
user_rec user_prog%ROWTYPE;

BEGIN
	OPEN user_prog;
	FETCH user_prog INTO user_rec;

	-- List Allowed users
	IF user_rec.USER_DOF not IN ('ALLOWED_USER')
	THEN
		RAISE_APPLICATION_ERROR(-20001, 'User '||user_rec.USER_DOF||' from machine '||user_rec.machine||' is not allowed to login!');
	END IF;

	CLOSE user_prog;
END;
/

The trigger simply uses a cursor to trap all incoming connections and only allows access to the application schemas to those users who are allowed to. These are the parameters:

• APP_SCHEMA: the application schema which we do not want normal users to login to
• USER_MACHINE: the machine from where the normal users login
• ALLOWED_USER: whitelist of users who are allowed to access restricted schemas

Of course this could slow down performances a bit, because each connection has to be checked before it can actually do something.

Remember: do not rely on triggers to ensure security. Triggers are only an additional layer, but not the main one. Always use a firewall and a proper access control policy at the OS layer. And never trust end users!

See you in the next tutorial!