Creating an Alert to Warn of Sarbanes-Oxley Non-Compliant User Creation

Sarbanes-Oxley (SOX) compliancy requires controls on database user creation.  Ignite Alerts can be used to monitor the creation of database user accounts for SOX compliancy.  Especially, if user profiles are created specifically for SOX compliancy, the DBA_USERS table can be monitored for any non-compliant profiles being given out. This document describes the steps in setting up such an alert and also explains how to report the history of the alerts for auditing purposes.

Alert Creation

In Ignite, choose Alerts from the Main screen > Manage Alerts > Create A New Alert, and then select the ‘Custom Alert’ button.  In the drop down list, choose the ‘Custom SQL Alert – Multiple Numeric Return’ alert type.

Fill out the information as in the example below.  I have set the ‘Execution Interval’ to run once a day. 
However, you can run this alert more or less frequently depending on the timeframe that you are reviewing in
your SQL statement. In the SQL statement example below, I’m checking ‘where created > sysdate -1.  Also, I’m
only reporting on the ‘DEFAULT’ profile being out of compliancy.  You may want to add the account_status =
‘OPEN’ and other non-compliant profiles to this statement.   Finally, choose all of the databases that you
want this alert to run on.

For units, I’ve entered the ‘count’ of users that are in violation. The alert will email out to the Contact
listed when it sees a value of 1 or when a user is created with a non-compliant profile.   In the description
field, you may want to list the valid profiles for auditing purposes. 

Finally, test the alert to see if it executes successfully by choosing the ‘Test Alert’ button.  Below is an example of how the test fires.

Alerting and Reporting

Once you’ve saved the alert, the contact or group will receive an email when there is a non-compliant user created. An example of the email is listed below:

You can view the history of the alert in Ignite as listed below.

Or you can issue the following query in the repository database to report violations for a given period of
time. In the example below, the ‘Database Query Tool’ option in Ignite is used to report this information or
download the information in Excel format.

Supporting Queries:

Alert Query:

select 'User '||username||' was created on '||created||' with profile '||profile created_user,1

from dba_users where created > sysdate -1 and profile = 'DEFAULT';

Reporting Query:

select c.alertname, a.dbname, a.actiondate, b.parametername violation
from con_alert c, con_alert_history a, con_alert_history_results b
where a.historyid = b.historyid
and a.alertid = c.id