In this individual case, i have created Custom Reports for the most common cases in Oracle Enterprise Manager Grid Control. The mentioned reports are sent to the Security and Audit departments in a daily basis. The output of the SQL commands below are also collected by a centralized logging application.
Some assumptions should be made in order to understand the following queries. First of all the application users (database users which are ment to be used by only through the application interface) are grouped into the application profiles. By assigning individual profiles to the application. dblink. admin users etc.. , DBA can force the password complexity and password lifetime values dedicated to the application behaviour. Clearly, nobody wants to have a critical downtime of a frequently used application because of a password lock of the application user in the middle of the daytime (so, yes I prefer password_lifetime value of the application related users to unlimited). But this subject belongs to some other topic about security.
Probably one of the most important audit data to be reported is the connections to the database with the application users. Because of the high amount of the data produced by the application user actions, in most of the cases auditing every operation of the application user is impossible. But this reveals a vulnerability which is; any user connects to the database with the application user can do anything without a trace. So it is very important to audit the connections with the application user and from a source outside of the application server(s) which means from a workstation.
-- all access from application users via workstations
select
OS_USERNAME,
USERNAME,
USERHOST,
TERMINAL,
TIMESTAMP,
OWNER,
OBJ_NAME,
ACTION_NAME,
COMMENT_TEXT,
SQL_TEXT,
RETURNCODE
from sys.dba_audit_trail
where username in
(select username
from dba_users
where profile in
(select
distinct(profile)
from
dba_profiles
where profile like '%APPLICATION%'))
and lower(userhost) not in (select hostname from t_application_servers)
and os_username != 'debug'
and action_name in ('LOGON','LOGOFF')
and timestamp > sysdate-7
order by timestamp desc;
Any drop table operation should be reported if the action user is not an application user. Operation itself may not be risky at all times because any user can drop any object from their own schemas. But still this operation should be reported in timely fashion to be sure that nobody is dropping a vital object/table for the sake of the application avalibility.
-- unknown user drops or truncates any table
select
OS_USERNAME,
USERNAME,
USERHOST,
TERMINAL,
TIMESTAMP,
OWNER,
OBJ_NAME,
ACTION_NAME,
COMMENT_TEXT,
SQL_TEXT,
RETURNCODE
from sys.dba_audit_trail
where username not in
(select username
from dba_users
where profile in
(select
distinct(profile)
from
dba_profiles
where profile like '%APPLICATION%'))
and action_name in ('TRUNCATE TABLE','DROP TABLE','DROP INDEX')
and timestamp > sysdate-7
order by timestamp desc;
Object privileges are very important, when a DBA breaks his/her rules and the rest comes in a very short time. Any privilege granted to any user out of the roles should be reported and investigated. If this is a temporary grant then it should be followed up to be revoked.
-- any grant to unauthorized user
select
OS_USERNAME,
USERNAME,
USERHOST,
TERMINAL,
TIMESTAMP,
OWNER,
OBJ_NAME,
GRANTEE,
ACTION_NAME,
COMMENT_TEXT,
SQL_TEXT,
RETURNCODE
from sys.dba_audit_trail
where grantee not in
(select username
from dba_users
where profile in
(select
distinct(profile)
from
dba_profiles
where profile like '%APPLICATION%'))
and action_name like 'GRANT%'
and timestamp > sysdate-7
order by timestamp desc;
Public synonyms and public dblinks makes the general database operation very easy going. But these objects also cause severe security impact on the operation of the database. Application operation can easly be directed to custom created tables or dblinks and cause unwanted actions and operations. These public object creations should be investigated and followed in a timely fashion.
-- create public synonym or create public dblink where user is not dba
select
OS_USERNAME,
USERNAME,
USERHOST,
TERMINAL,
TIMESTAMP,
OWNER,
OBJ_NAME,
ACTION_NAME,
COMMENT_TEXT,
SQL_TEXT,
RETURNCODE
from sys.dba_audit_trail
where username not in
(select username
from dba_users
where profile in
('DBA_PROFILE'))
and (action_name='CREATE PUBLIC SYNONYM' or action_name='CREATE PUBLIC DBLINK')
and timestamp > sysdate-7
order by timestamp desc;
Alter System or Alter Database operations should only be done by the privileged DBA's (please note that in this database all DBA users are grouped in a profile named DBA_PROFILE). Any user tries or even successes the Alter System or Alter Database command should be reported immediately. The list of the users for the last one week can be obtained from the following query.
-- alter system and alter database operations where user not in dba list
select
OS_USERNAME,
USERNAME,
USERHOST,
TERMINAL,
TIMESTAMP,
OWNER,
OBJ_NAME,
ACTION_NAME,
COMMENT_TEXT,
SQL_TEXT,
RETURNCODE
from sys.dba_audit_trail
where username not in
(select username
from dba_users
where profile in
('DBA_PROFILE'))
and (action_name like 'ALTER SYSTEM' or action_name like 'ALTER DATABASE')
and timestamp > sysdate-7
order by timestamp desc;
Any operation from any user (out of the application users) should be reported if they are trying (or successes) to change/alter an object in another schema. This can be normal development operation but in some cases it can be considered as an unwantend operation.
-- any operation within another schema
select
OS_USERNAME,
USERNAME,
USERHOST,
TERMINAL,
TIMESTAMP,
OWNER,
OBJ_NAME,
GRANTEE,
ACTION_NAME,
COMMENT_TEXT,
SQL_TEXT,
RETURNCODE
from sys.dba_audit_trail
where username not in
(select username
from dba_users
where profile in
(select
distinct(profile)
from
dba_profiles
where profile like '%APPLICATION%'))
and username != owner
and action_name not in ('LOGOFF', 'LOGON', 'LOGOFF BY CLEANUP')
and timestamp > sysdate-7
order by timestamp desc;
Last but not the least failed login attemps may also be reported. Actually there will be some output of this query but in case of the count of the records are rising up inexplicably then the reason of this rise should be investigated as it may be someone is trying to login continously (If "failed login attemps" value is not set in the database profile).
-- failed login attempt select os_username, username, userhost, timestamp, returncode from dba_audit_session where action_name = 'LOGON' and returncode > 0 and timestamp > sysdate-7 order by timestamp;
Here I can provide only my experiences of the further reporting of the Oracla Database Audit option. Of course this list of reports can be widen or broaden according to the audit/security department needs or expects from the database administrators which should be decided all together.
