Oracle database auditing issue !!

[Chris Fung]

Hi all,

We have a security requirement to generate daily login/logoff/fail/success report for both privilege (e.g. DBA) and normal users access to the production database.

I have turned on the auditing feature (setting audit_trail=true and perform audit session). The sys.aud$ table is now propagating entries.

Now I have 2 problems:

1. Space issue for the audit table. As I have read some article in Metalink, relocating the sys.aud$ table to other tablespace may cause problem (something related to backup and upgrade migration).....I am just curio what the problem it may incur. Because I need to explain the impacts to the change management for the endorsement.

2. I have try to write a simple sql script to create a simple report to track the DBA and non DBA users. However, it seems a little bit confuse if I use dba_role_privs as a reference table to obtain the DBA privilege of a user (As roles and userid are both in the grantee column). How can I report all the login/logoff access based on just the dba_users table while I can still distinquish which user owns the "DBA" role privilge and which are ordinary users.

Appreciated for you advice (By the way, we are using Oracle 8.1.7)

Many Thanks,

Chris,

[Tom Geudens]

Hi Chris,
Do you have access to metalink (http://metalink.oracle.com/ you'll need a supportid for it) ? There's a complete set of documents and how-to's on security in general and auditing in specific that should answer all your questions. Your looking for DOCID "Note:207959.1" and my search in metalink was "audit trail".

Regards,
Tom Geudens

[Brian Crabtree]

Chris,

1. This is from Doc ID 1019377.6. I will paste the portion that is relevant, however I would recommend that you read the document. (Note, this is not a supported practice)
====================
create tablespace "AUDIT"
datafile '$HOME/data/aud01.dbf' size 500k
default storage (initial 100k next 100k pctincrease 0)
/
create table audx tablespace "AUDIT"
storage (initial 50k next 50k pctincrease 0)
as select * from aud$ where 1 = 2
/
rename AUD$ to AUD$$
/
rename audx to aud$
/
create index i_aud1
on aud$(sessionid, ses$tid)
tablespace "AUDIT" storage(initial 50k next 50k pctincrease 0)
/
====================

2. Something like this should work:

select * from aud$ where userid not in
(select username from dba_users
where username in
(select grantee from dba_role_privs
where granted_role = 'DBA'));

Brian

[Yogeeraj_1]

hi,

For 2:

The easy way: audit connect.
The hard way: write code.
The end result from both: the same.


Have a look at the following document:
http://technet.oracle.com/doc/oracle8i_816/server.816/a76956/audit.htm#1108


use the built in feature.
and use Rule of thumb: keep everything as simple as possible.

When you audit connects you get:

desc dba_audit_trail
======================
OS_USERNAME
USERNAME
USERHOST
TERMINAL
TIMESTAMP
OWNER
OBJ_NAME
ACTION
ACTION_NAME
NEW_OWNER
NEW_NAME
OBJ_PRIVILEGE
SYS_PRIVILEGE
ADMIN_OPTION
GRANTEE
AUDIT_OPTION
SES_ACTIONS
LOGOFF_TIME
LOGOFF_LREAD
LOGOFF_PREAD
LOGOFF_LWRITE
LOGOFF_DLOCK
COMMENT_TEXT
SESSIONID
ENTRYID
STATEMENTID
RETURNCODE
PRIV_USED

From here, you can create a view as required.

You may be also interested in the following AUDIT tables too:
DBA_AUDIT_EXISTS lists audit trail entries produced by AUDIT NOT EXISTS.
DBA_AUDIT_OBJECT contains audit trail records for all objects in the system.
DBA_AUDIT_SESSION lists all audit trail records concerning CONNECT and DISCONNECT.
DBA_AUDIT_STATEMENT lists audit trail records concerning GRANT, REVOKE, AUDIT, NOAUDIT, and ALTER SYSTEM statements throughout the database.
DBA_AUDIT_TRAIL lists all audit trail entries.
DBA_OBJ_AUDIT_OPTS - describes auditing options on all objects.
DBA_PRIV_AUDIT_OPTS - describes current system privileges being audited across the system and by user.
DBA_STMT_AUDIT_OPTS - describes current system auditing options across the system and by user.


hope this helps!

regards
Yogeeraj

[Chris Fung]

Thanks for the responds.

Some clarifications:

1. I think the procedure for moving the sys.aud$ table out of the system tablespace is rather straight forward. However, I think the impact underlying is much more important and that is what I am concerning about.

2. For the query, I think the criteria would be =>

- All user login and logoff records should be categorized into "DBA" or "USER" (I am using dba_role_privs for reference. I.e those account that granted with "DBA" role or the DBA role is cascaded through other roles should be treated as DBA)

- In the same token, user login and logoff does not inherit the "DBA" role should be treated as USER

Is it possible to create a query so that I can fulfill the above requirements ?

Cheers,

Chris,

[Yogeeraj_1]

hi again,

For 1:

You may wish to move the SYS.AUD$ to another tablespace (LMT)
(NB. SYS.AUD$ is the only SYS object that can be directly modified).


Also, I would consider compressing the data.

Suppose I have an audit trail. Audit trails are write intensive. Obviously they are not a candidate for compression. But we can do as follows:

create audit trail partitioned by month.
current month is write intensive -- old months are read only.

At the end of the month you will:

add new partition for next month.

create table last_month_compressed as select * from audit partition(last_month) order by ;

alter table audit exchange last-months-partition with last_month_compressed;

drop oldest month

This is perfect for a sliding window of data. The old months are packed really tight. The current month is uncompressed. Next month that uncompressed data will be compressed.

This way you can deal with the space issue.

best regards
Yogeeraj

[Brian Crabtree · Accepted solution]

Chris,

1. There is a reason that Oracle does not recommend it. There are triggers that are associated with the table that need to be re-generated. Like I suggested, look at the note listed as well as the notes under it, and remember that Oracle does NOT support moving it, they mearly say how to do it. (ie: If you do it, it breaks something, Oracle might help you fix it, and they might not). The safest approach is to leave it in the SYSTEM tablespace.

2. In a simple answer, no. You can probably write a large SQL statement, but it will not be a simple one. The one I posted before could be modified (change the first 'not in') to list out the DBA's, and put a comment in the SQL to show which is which.

One thing that disturbs me is your mention of "roles that have been granted DBA". This would not be a good pratice to begin, as you lose the ability to quickly show which users have the DBA privilege, and users could unknowingly be granted this privilege. For example, developers should not be granted the DBA privilege under normal circumstances. While this is "easy", code it written by users that have full access to all of the tables and views in the database. Then, when the application gets sent to a normal DBA, a statement similar to "No f-ing way is an application account getting DBA privileges" is heard. The DBA role should be restricted to people that need it for administration purposes, and specific grants or special procedures/packages written for any common changes required. (For example, we have a procedure called "alter_user" that will take input from the user and change thier password, but disallows the password to be changed for system or DBA accounts).

I don't mean to be critical, but security is something that should be taken very seriously, because once you make an exception to something, you will most likely always be making exceptions. The DBA account should only be granted to specific users, and only for very good reasons.

Hope this helps,

Brian

[Chris Fung]

Hi there,

Brian, I totally agree to your point !!

Cheers,

Chris,