[Oracle] Using Oracle Database triggers for security
- 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:
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!