2

I am using oracle 11g and need to find out date and time of a specific grant. Is there any way to find out WHEN (date & time) a privilege was granted? dba_tab_privs has no date column, nor has sys.objauth$...

even if there is no way, please answer if you can provide an oracle document saying so. I did not find anything and I am searching for 3hrs now.

thanks in advance

oFace
  • 216
  • 3
  • 5
  • Maybe some kind of flashback query SELECT * FROM sys.objauth$ VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP('2008-12-18 14:00:00', 'YYYY-MM-DD HH24:MI:SS') AND TO_TIMESTAMP('2014-07-24 17:00:00', 'YYYY-MM-DD HH24:MI:SS') – Maksim Sirotkin Jul 24 '14 at 14:28
  • Documenting things you can't do would take forever so it's unlikely to be stated anywhere. [You can audit this activity](http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_4007.htm); but if you're trying to find a grant that's already been made, you aren't already auditing, and it wasn't done within your flashback query window, then... I think you're out of luck as it apparently isn't stored in the data dictionary itself. – Alex Poole Jul 24 '14 at 14:59
  • Can you assume that the last DDL that was done to an object was the `GRANT` you are interested in? If so, you could use the `last_ddl_time` from `dba_objects`. Generally, I'd agree with @AlexPoole that you would want to enable auditing if you want to track those sorts of things. – Justin Cave Jul 24 '14 at 21:21
  • @maksim: unfortunately there is no snapshot (ora-08180) – oFace Jul 25 '14 at 06:23
  • @alex: the db admins do no audit (at least not an this level) and yes - they do have some **DDL** triggers but the grant in question was not recorded or they missed it in a way (could have been some years ago) – oFace Jul 25 '14 at 06:42
  • @justin: no I cant assume this, the object is some years old and we had several dozen of installations since - all with hundreds of DDLstatements. – oFace Jul 25 '14 at 06:43
  • In this particular case we had another installation on a parallel db and this grant was missing. Could not find the grant in the installation scripts so I wanted to know when the privilege was granted. Then I would have been able to check the installations on this particular date. Search is quite tedious since all the installations are archived. Anyway: thanks to all of you. Meanwhile the priv was granted and I will just add the grant to the next installation script. – oFace Jul 25 '14 at 06:45

0 Answers0