Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2004
    Posts
    2

    Unanswered: Help with Tablespace and Auditing

    Hi, I have read through the Oracle 9iR2 documentation, but I still can't figure out these 2 questions.

    1. 'select * from dba_ts_quotas' lists all tablespace quotas specifically assigned to each user. However, when I run this query on my db, it doesn't return the tablespace for SYSTEM and USERS (only those tblspace such as EXAMPLE). When I query from dba_users, I can see that some users are assigned to these 2 tablespaces as default. How do i find out their quota on these tablespaces?


    2. I wanted to turn on auditing of all DBA users, to record their actions wheneven they insert, update or delete ANY tables in the db. Since oracle doesn't support auditing by role, I used ' AUDIT ALL BY <DBA_USER>'

    However this stmt only audit system privileges such as create, drop and truncate tables. Modification to the table data are not recorded. I tried 'AUDIT ALL PRIVILEGES BY <DBA USER>', 'AUDIT INSERT ANY TABLE, UPDATE ANY TABLE by <DBA_USER>' but the changes are still not shown in the dba_audit_trail view. (I have turned on AUDIT_TRAIL=DB)

    Thanks for any help in advance.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jul 2004
    Posts
    2
    Read that a few times already, but none of the syntax seems to work...I can only audit actions on specific table if i name the table in my AUDIT stmt, but I can't audit ALL actions on ANY table by DBA role.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    > but I can't audit ALL actions on ANY table by DBA role.
    This statement is nonsensical for multiple reasons.
    After a database is created, no auditing exists.
    You should think of AUDIT as placing a trigger on the object
    which inserts fields into the "audit views" after the desired event occurs.
    AUDIT has NOTHING to do with DBA role or similar nonsense.
    The ONLY way to obtain audit information is to enable AUDIT on a case by base basis.
    Keep in mind you can audit entities; users, database objects, and (some) actions.
    Prior to 10g SELECTs can't be audited, IIRC.
    With only minimal effort, you can write SQL to generate the AUDIT statements
    for many, many entities.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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