If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

revoke permission on SYS.AUD$ table

Is there a way to restrict or revoke all permission on SYS.AUD$ table from a user which has DBA role or select any table/delete any table privileges.

As per our company policy apart from auditors/reviwers, no one else should be allowed to view/delete any of the auditing records which are being stored in SYS.AUD$ table.

we need to ensure that the system administrator(except SYS and SYSTEM) with DBA role should not be able to modify/delete/truncated the SYS.AUD$ table. Only the Reviewer should have access to the audit tables?..similar to what we enforce in Sybase. For example, in Sybase you cannot view auditing records until and unless sso_role has been granted to you.

revoke permission on SYS.AUD$ table

Hi Sanjay,

Thanks for your response. We have some third party applications which might need access on SYS schema and setting this value to FALSE (O7_DICTIONARY_ACCESSIBILITY = FALSE) may create more problems for us since it would be applicable for all the users and so far we do not know what would be the impact once this value is changed.

Don't you know any other method which can be implemented only for few selected users, even for the user who has DBA role.

The only effective sollution I can think of for this kind of security restrictions is to create and implement a fine-grained access control policy on that table. Look for terms "Row Level Security", "Fine Grained Acess Controll" or "Virtual Private database" in Oracle documentation and other available resources for more detailes how this feature can be used exactly for the purposes like yours.

The only problem is that RLS can't be applied to any table/view belonging to SYS. So in your case you would first need to move SYS.AUD$ table and its indexes to some other schema (like SYSTEM or something like this) and then create a synonym SYS.AUD$ pointing to this new table (to SYSTEM.AUD$ for example). Verify with Oracle Support if this kind of action on data dictionary would in any way compromise your support contract with them! But I know from the past that they allowed for this kind of AUD$ table move and that AUD$ was the only exception to the rule of "not messing up with database dictionary tables".

Jurij ModicASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?