Pete Finnigan's Oracle Security Weblog

An interesting question was posted on the comp.databases.oracle.server newsgroup a few days ago that asked a question about why the posters AFTER CREATE ON SCHEMA trigger was no longer firing. A link to the thread can be found here.

So how does this relate to Oracle security? Quite often the system trigger facilities are used as an additional audit trail or as a mechanism to prevent certain actions taking place or as part of a virtual private database solution. Let's give some examples. A logon trigger can record details of each user connecting to the database into an additional audit table created for the purpose. Quite often more detail can be recorded than with the conventional audit trail. A trigger could also fire when any DDL is issued and record that to an audit table for the purposes of managing change and detecting when it occurs. Also for instance a logon trigger could also be used to prevent certain applications or users from connecting to the database by checking certain session values as the user connects for instance for the program name or user name or IP Address. Also logon triggers can be used to set the correct context for a user as part of a virtual private database solution.


As you can see system triggers can be used in many ways to implement security policies either as part of an audit solution or as part of a mechanism to enforce security levels and access.

So the issue for the poster on c.d.o.s and also for anyone using system triggers in any way as part of a Oracle security solution is to ensure that the triggers fire. I posted the following response to the above thread.

Hi,

Check if the hidden parameter _system_trig_enabled is still set to TRUE.
If this is set to false then system triggers will no longer fire. You
cannot use "show parameter" in SQL*Plus or select from v$parameter. You
need to use some x$ views so you need access to the SYS schema. The
following SQL will get the value for you:

SQL> l
1 select x.ksppinm name,
2 y.ksppstvl value,
3 ksppdesc description
4 from x$ksppi x,
5 x$ksppcv y
6 where x.inst_id = userenv('Instance')
7 and y.inst_id = userenv('Instance')
8 and x.indx = y.indx
9* and x.ksppinm = '_system_trig_enabled'
SQL> /

NAME
-------------------------------------------------------------
VALUE
-------------------------------------------------------------
DESCRIPTION
-------------------------------------------------------------
_system_trig_enabled
TRUE
are system triggers enabled


SQL>

hth


This proved to be the issue, as you have probably suspected, in the posters database the hidden parameter had been turned off. Someone in the posters company had changed this parameter to FALSE.

If you use system triggers as part of any security solution in your databases then you need to ensure that this parameter is always set to TRUE. How can this be done? - Let’s first look at how the parameter can be changed. Here is a quick report of the parameters important settings:

Investigating parameter => _system_trig_enabled
====================================================================
Name : _system_trig_enabled
Value : TRUE
Type : BOOLEAN
Is Default : DEFAULT VALUE
Is Session modifiable : FALSE
Is System modifiable : CAN BE CHANGED IMMEDIATLY WITH ALTER SYSTEM
Is Modified : FALSE
Is Adjusted : FALSE
Description : are system triggers enabled
Update Comment :
-------------------------------------------------------------------------


The above is taken from a 9.2.0.1 database. The parameter can be set, as you can see with ALTER SYSTEM so doesn't need to be changed in the init.ora configuration file.

This means simply checking the init.ora is not sufficient. Using a system trigger will not work either! The only practical method is to check the value in the database regularly. A query like the one I used in my post above to c.d.o.s can be used. You could run it from a shell script and schedule from cron or use a database job and write a simple check in PL/SQL and use DBMS_JOB or the 10g scheduler DBMS_SCHEDULER. The query can be enhanced to test if the value is correct or not. Detecting when it changed could be harder. You can use techniques like reading the redo logs with Log Miner or the audit trail.

How about preventing the parameter from being changed? This can be done by restricting any users from getting the privilege ALTER SYSTEM. This can be done using the following SQL:

SQL> select *
2 from dba_sys_privs
3 where privilege='ALTER SYSTEM';

GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
DBA ALTER SYSTEM YES
ODM ALTER SYSTEM NO
SYS ALTER SYSTEM NO
MDSYS ALTER SYSTEM NO
WKSYS ALTER SYSTEM NO
ZULIA ALTER SYSTEM NO
CTXSYS ALTER SYSTEM NO

7 rows selected.

SQL>


Also check for users who have been granted the roles that also have this privilege, for instance the DBA role. Revoke the privilege from all users and roles that do not need to have it. This should be all users except Admin staff. Also use the audit facilities to detect which users have used ALTER SYSTEM.

Above all if you use system triggers as part of a security policy or implementation in your company, then try and reduce the possibilities that they can be turned off, implement regular checks to see if they have been turned off and also use audit to try and detect who turned them off and why.