This forum is currently locked. You can't register or post questions at this time. (read more)

Oracle 11.2 Auditing user with DBA

All posts relating to Oracle database administration.

Moderator: Tim...

mattyb
Member
Posts: 21
Joined: Fri Aug 31, 2012 3:38 pm

Oracle 11.2 Auditing user with DBA

Postby mattyb » Wed Jul 16, 2014 2:43 pm

Our Security department wants to know exactly what a user with the DBA role does during a certain time. The end goal is to propose specific privileges instead of giving the DBA role. I am going to move the AUD$ table

I have several questions :

To make life easier, I would like the info stored in the db. Do I need audit_trail=db,extended ? If I understand correctly when extended is used it writes to the OS.

According to this : http://docs.oracle.com/cd/E11882_01/net ... m#DBSEG371 I don't need BY ACCESS (using 11.2.0.3) - can this be confirmed?

Am I going to capture everything the user with the DBA role does by using the following :

AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY DBA_USER;
AUDIT EXECUTE PROCEDURE BY DBA_USER;
audit all privileges by DBA_USER;
audit all by DBA_USER;

If not, what other commands should be used?

Thankyou.

Matt

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Oracle 11.2 Auditing user with DBA

Postby Tim... » Wed Jul 16, 2014 2:56 pm

Hi.

1) DB,EXTENDED is all about the database. It puts the normal audit records into the DB, but also adds some extended information into the DB, on top of the normal stuff.

http://docs.oracle.com/cd/E11882_01/net ... m#DBSEG352

2) Yes. BY ACCESS is the default, so if you include it or omit it, you get the same result. I still include it because it shows explicitly what I am doing, rather than requiring someone to know the default action.

3) That is a good starting point.

Note. If person is a DBA, I feel they should have the DBA role. There are a lot of one-off tasks that require more privilege that you may not use during the sample period. If the person is not a DBA, they should definitely not have the DBA role...

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

mattyb
Member
Posts: 21
Joined: Fri Aug 31, 2012 3:38 pm

Re: Oracle 11.2 Auditing user with DBA

Postby mattyb » Fri Jul 18, 2014 9:36 am

I agree with you 100% Tim. What we are trying to do is to not give the DBA role that the software supplier insists is needed. Hence the audit to determine exactly what is required.

So far we have captured some info, but using TOAD and their SGA Trace I can see actions that I assumed would be captured but that are not in AUD$.

Examples :

DBMS_STATS.SET_TABLE_STATS
DBMS_STATS.SET_COLUMN_STATS
There is a drop user
A select on SYSTEM.PRODUCT_PRIVS
Selects on
SYS.IMP9COMPAT
SYS.EXU8FUL
SYS.EXU9NLS
SYS.IMP9USR

There are also some procs that I assume are linked to imports - which the user does as part of the migration.

Matt

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Oracle 11.2 Auditing user with DBA

Postby Tim... » Fri Jul 18, 2014 9:58 am

Hi.

We have some 3rd party products that insist on having DBA granted to specific users. In the end we had to relent and grant it, since the vendor refused to support the product if we didn't grant the "required" role...

Sometimes you can't do anything but relent where 3rd party products are concerned...

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

mattyb
Member
Posts: 21
Joined: Fri Aug 31, 2012 3:38 pm

Re: Oracle 11.2 Auditing user with DBA

Postby mattyb » Mon Jul 21, 2014 3:36 pm

Well, I have to say that I'm not very happy with the way that audit info is presented. For my example, which is to audit a user with the DBA role, I'd like to see what 'ALTER SYSTEM/USER etc' commands were run. Yes, I know that I can see the ALTER SYSTEM in the alert.log but it would be nice to have the SQL statement in DBA_AUDIT_TRAIL. If I understand correctly, you can only see this detail if you have done ALTER SYSTEM SET audit_trail=db,extended SCOPE=SPFILE - which writes to the OS.

Is there any way that I can be 100% sure that I have the ALTER SYSTEM (and ALTER USER) statements having only used ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE ?

rgs,

Matt

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Oracle 11.2 Auditing user with DBA

Postby Tim... » Mon Jul 21, 2014 3:38 pm

Hi.

I answered this before. The db,extended does *not* write to the OS. Read the manual entry I linked to. It writes to the DB and includes some extra columns. If you want the SQL and DDL statements in the audit trail (in the database), you need that option.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

mattyb
Member
Posts: 21
Joined: Fri Aug 31, 2012 3:38 pm

Re: Oracle 11.2 Auditing user with DBA

Postby mattyb » Mon Jul 21, 2014 4:01 pm

Sorry Tim, I skimmed over the link you gave and didn't dedicate enough time to ensuring that I grepped it 100%.

I blame the hot weather here.

Thanks,

Matt

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Oracle 11.2 Auditing user with DBA

Postby Tim... » Mon Jul 21, 2014 5:24 pm

:)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

mattyb
Member
Posts: 21
Joined: Fri Aug 31, 2012 3:38 pm

Re: Oracle 11.2 Auditing user with DBA

Postby mattyb » Mon Jul 28, 2014 4:06 pm

This time, 11.2.0.3.6 database, I put audit_trail=db, extended

Same audits on users as before :

AUDIT ALL BY PF00;
AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY PF00;
AUDIT EXECUTE PROCEDURE BY PF00;
audit all privileges by PF00;
audit all by PF00;

I've got about 2000 lines for the users in question. I can see in the SQL_TEXT that the user is selecting from dba_roles, yet in the PRIV_USED there is nothing. When not using extended, I had SELECT ANY TABLE in the PRIV_USED. Both users, different databases, have the DBA role.

I can see, with OBJ_NAME and SES_ACTIONS that the SELECT was successful, but it seems daft to me that I can't have the PRIV_USED. ACTION_NAME is SESSION REC when the audited users are accessing objects that are 'system'.

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Oracle 11.2 Auditing user with DBA

Postby Tim... » Mon Jul 28, 2014 4:11 pm

Hi.

It's been a long day...

What are you actually asking here? :)

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

mattyb
Member
Posts: 21
Joined: Fri Aug 31, 2012 3:38 pm

Re: Oracle 11.2 Auditing user with DBA

Postby mattyb » Tue Jul 29, 2014 3:03 pm

Sorry for my rantings.

2 databases, users with DBA role that I have to audit to reduce privileges.

Auditing in db1 audit_trail=db
Auditing in db2 audit_trail=db,extended

I see in db1 that when a DBA user grants a role to a user, the PRIV_USED column has 'GRANT ANY ROLE'
I see in db2 that when a DBA user grants a role to a user, the PRIV_USED column has nothing.

I don't get why.

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Oracle 11.2 Auditing user with DBA

Postby Tim... » Tue Jul 29, 2014 3:23 pm

Hi.

But you see the actual DDL to perform the grant right?

Sounds like a bug to me. I would raise an SR with Oracle Support asking them what is going on... :)

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

mattyb
Member
Posts: 21
Joined: Fri Aug 31, 2012 3:38 pm

Re: Oracle 11.2 Auditing user with DBA

Postby mattyb » Wed Jul 30, 2014 7:40 am

Yes, I have the DDL. I'll open an SR.

Cheers again Tim.

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Oracle 11.2 Auditing user with DBA

Postby Tim... » Wed Jul 30, 2014 7:47 am

:)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle Database Administration”

Who is online

Users browsing this forum: No registered users and 0 guests

cron