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