revoke permission on SYS.AUD$ table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: revoke permission on SYS.AUD$ table

Hybrid View

  1. #1
    Join Date
    Jan 2003
    Posts
    3

    Post revoke permission on SYS.AUD$ table

    Hello,

    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.

    Please advise whether we can comply with this requirement in Oracle.

    Thanks in Advance,
    Dilip

  2. #2
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Which version of Oracle are you using?
    Is parameter 07_DICTIONARY_ACCESSIBILITY set to TRUE ?
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

  3. #3
    Join Date
    Jan 2003
    Posts
    3

    revoke permission on SYS.AUD$ table

    Hi Sanjay,

    SQL> select * from v$version;
    page

    BANNER
    ----------------------------------------------------------------
    Oracle8i Enterprise Edition Release 8.1.7.0.0 - 64bit Production
    PL/SQL Release 8.1.7.0.0 - Production
    CORE 8.1.7.0.0 Production
    TNS for Solaris: Version 8.1.7.0.0 - Production
    NLSRTL Version 3.4.1.0.0 - Production


    SQL> show parameter O7_DICTIONARY_ACCESSIBILITY
    page

    NAME_COL_PLUS_SHOW_PARAM TYPE
    ---------------------------------------------------------------- -------
    VALUE_COL_PLUS_SHOW_PARAM
    ------------------------------------------------------------------------------------------------------------------------------------
    O7_DICTIONARY_ACCESSIBILITY boolean
    TRUE


    Regds..Dilip

  4. #4
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Set O7_DICTIONARY_ACCESSIBILITY to false.
    The users's with SELECT ANY TABLE privileges won't be able to see the dictionnary objects unless granted privileges explicitly.
    HTH
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

  5. #5
    Join Date
    Jan 2003
    Posts
    3

    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.

    Thanks,
    Dilip
    (Certified Sybase Professional DBA 12.5/12.0/11.5/11.0)
    (OCP 8i DBA)

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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 Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width