[Oracle] Using Oracle Database triggers for security

You are here:
  • Main
  • Oracle
  • [Oracle] Using Oracle Database triggers for security
< All Topics


  • Oracle 12
  • DBA privileges

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:


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;

	OPEN user_prog;
	FETCH user_prog INTO user_rec;

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

	CLOSE user_prog;

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!

Table of Contents