SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
DBAudit - initial setup for Oracle - problems

 
Reply to topic    SoftTree Technologies Forum Index » DB Audit, DB Mail, DB Tools View previous topic
View next topic
DBAudit - initial setup for Oracle - problems
Author Message
thurbya



Joined: 13 Mar 2007
Posts: 4
Country: United States

Post DBAudit - initial setup for Oracle - problems Reply with quote
I am trying to set up DbAudit 3.1 to do data change auditing against an Oracle 10g database and am having problems. In our company, we are restricted in lots of ways to what we can do. How I have set this up (as per documentation and info found in this forum) is below. Please note that there will NO system auditing allowed via this tool in our installation.

DB_AUDIT id is set up created with the following access. When I try to create a trigger, I get the ORA-942 table or view does not exist. I have also set up an id to connect to the tool named db_audit_user that has the same set up as the db_audit id.

Privileges:
application_manager role, audit any, create trigger, select any table
Tab privs:
select on dmws.fsitem (table I am trying to audit - plus I created a synonym under db_audit for this)
select on sys.audit$; execute on dbms_job; execute on dbms_lob; execute on dbms_output; execute on dbms_utility;
select on sys.v_$mystat; select on sys.v_$session; created a view named db_audit.v$mystat as select * from sys.v_$mystat; created a view named db_audit.v$session as select * from sys.v_$session

The application_manager role has the following privileges:
alter session; create cluster; create dimension, create indextype, create materialized view, create operator, create procedure, create role, create sequence, create session, create synonym, create table, create trigger, create type, and create view. Note that this is a standard role that is assigned to an application id within our environment.

I've tried logging on to the application as db_audit_user (set up same as db_audit), db_audit, dmws (schema owner), and my id (an application dba id with limited access). On all of these, I get the same oracle error as above. I do not have access to SYS and will not be allowed to use it.

Can you please assist in identifying what the problem in the setup is? Thanks.
Tue Mar 13, 2007 11:35 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7842

Post Reply with quote
At a very minimum, you are missing CREATE ANY TRIGGER privilege, which you need to create triggers on tables in other schemas.

The actual error I think is caused by lack of permissions to read from v_$session and v_$mystat dynamic performance views which DB Audit uses to get certain user session parameters.


Additional permissions might be required for sending email alerts.
Tue Mar 13, 2007 12:02 pm View user's profile Send private message
thurbya



Joined: 13 Mar 2007
Posts: 4
Country: United States

Post Reply with quote
We do have access to v_$session and v_$mystat -- see lines 3&4 under TabPRivs in original email: select on sys.v_$mystat; select on sys.v_$session; created a view named db_audit.v$mystat as select * from sys.v_$mystat; created a view named db_audit.v$session as select * from sys.v_$session --- both granted to db_audit and db_audit_user. I've also tested selecting in sql from the sys view and the db_audit view and both work fine.

I've requested the create any trigger, which was supposed to have been there, but an error was made. The error I'm getting doesn't seem to have anything to do with the ability to create a trigger but seeing a table or view. Any other ideas?
Tue Mar 13, 2007 2:08 pm View user's profile Send private message
thurbya



Joined: 13 Mar 2007
Posts: 4
Country: United States

Post Still having problems... Reply with quote
See prior reply regarding access exists to v$session and v$mystat.

Additionally, I now have create any trigger on db_audit and db_audit user and still get the same Oracle error. Any additional suggestions.
Tue Mar 13, 2007 2:59 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7842

Post Reply with quote
Were the privileges for system performance views granted to application_manager role or directly to the user?
Do you get the same error when you configure data-change auditng using a DBA account?
Tue Mar 13, 2007 3:08 pm View user's profile Send private message
thurbya



Joined: 13 Mar 2007
Posts: 4
Country: United States

Post Reply with quote
Access to the system views are granted directly to the id's. I did get the same error when using the schema owner and when using my app_dba account (which does not have full dba access).
Tue Mar 13, 2007 3:22 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7842

Post Reply with quote
There are really only 2 options:
1. either the db_audit user doesn't have sufficient privileges for the system views, it doesn't actually matter which account you use to create the triggers, because they are always created in db_audit schema.
2. or the account you use to create triggers doesn't have access to tables in db_audit schema or system catalog tables and as a result, the trigger creation process fails.

In any case, you might be wasting time. In order to create audit triggers you are supposed to logon to the system as a DBA user with sufficient privileges. Trying to workaround this is possible in theory, but it may take a long time. If you don't have sufficient privileges, can you ask your DBAs to assist you? This is just a one time process.

If you decide to stick with searching for a workaround method, enable the tracing option on the connections dialog (see "Troubleshooting the database connection" topic in the on-line help for more info) and then after each error analyze the resulting trace file. You should be able to find in the trace which command failed and then run this command manually in SQL*Plus or other tool until you figure out which permissions are missing.
Tue Mar 13, 2007 4:53 pm View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » DB Audit, DB Mail, DB Tools All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.