Home » RDBMS Server » Security » When Role Was Granted? (Oracle, 11g, Windows xp)
icon7.gif  When Role Was Granted? [message #654081] Wed, 27 July 2016 00:44 Go to next message
1982Hashmi
Messages: 69
Registered: March 2011
Location: Islamabad
Member
Hi,

Is it possible to get the date/time when a particular grant statement was executed
Re: When Role Was Granted? [message #654087 is a reply to message #654081] Wed, 27 July 2016 01:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

No, if you need this you have to audit it.
Well, there is a possibility: Log Miner but if you have no idea when it was done, I hope you have time (assuming your are in archivelog mode and have kept all these ones).

Note that if your audit is on (AUDIT_TRAIL != NONE), as far I remember, GRANT ROLE is audited by default, so have a look at your audit trail.

Re: When Role Was Granted? [message #654090 is a reply to message #654081] Wed, 27 July 2016 01:12 Go to previous messageGo to next message
Frank Naude
Messages: 4579
Registered: April 1998
Senior Member
If you audit grants, then yes, query aud$.
Re: When Role Was Granted? [message #654096 is a reply to message #654090] Wed, 27 July 2016 01:41 Go to previous messageGo to next message
1982Hashmi
Messages: 69
Registered: March 2011
Location: Islamabad
Member
I've checked DB parameters and found that audit_trail=NONE
Re: When Role Was Granted? [message #654100 is a reply to message #654096] Wed, 27 July 2016 01:51 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So you have no other choice, if you really really need it, than to use Log Miner.
I advise you, if it is a production database, to change this parameter to at least DB. This parameter is static, you need to restart the database to make it active.

You can check what audit will be done querying dba_priv_audit_opts and dba_stmt_audit_opts.
You can add those you want using AUDIT statement.

Previous Topic: SOX compliance
Next Topic: Who / what keeps locking one of database users?
Goto Forum:
  


Current Time: Fri Mar 29 08:33:59 CDT 2024