Auditing For Queries With No Where Clause
Jul 31, 2012
I'd like to audit a table for any SELECT queries that are executed against it with no WHERE clause. I've read the documentation on DBMS_FGA carefully, and as close as I can tell, creating a policy with a NULL audit_condition causes all queries against the table to be audited, which isn't what I'm looking for.
What I'd like is something like this:
DBMS_FGA.ADD_POLICY (
object_schema => 'scott',
object_name => 'emp',
policy_name => 'mypolicy1',
audit_condition => 'WHERE CLAUSE IS ABSENT',
audit_column => 'comm,sal',
[code].......
SELECT * FROM EMP;but queries with conditions ('WHERE sal > 400', for instance) are not trapped.
I'm using 11gR2 (11.2.0.2) on OEL.
View 2 Replies
ADVERTISEMENT
Jun 21, 2010
I have 4 select queries.
Query1
Query2
Query3
Query4
First Step:Combine Query1 and Query2,but the requirement is Query2 should only use the Acct_ID and Bill_ID which are output from Query1.
Second Step:Combine Query3 and Query4,but the requirement is Query4 should only use the Acct_ID and Bill_ID which are output from Query3.
Third Step: Is to now combine both the data set from First Step and Second Step.
In order to achieve my First and Second Steps I used WITH clause.
With S1 as ( Query1 ),
S2 as ( Query1 Union Query2)
Select S2.* from S1,S2
where S1.ACCT_ID=S2.ACCT_ID
AND S1.BILL_ID=S2.BILL_ID
With S3 as ( Query3 ),
S4 as ( Query3 Union Query4)
Select S2.* from S3,S4
where S3.ACCT_ID=S4.ACCT_ID
AND S3.BILL_ID=S4.BILL_ID
1. Is that approach right for achieving my First and Second step requirements ?
2. How to achieve Third Step ?
View 3 Replies
View Related
Jan 11, 2012
Using Connect By on a query which has a nested from clause(The from clause fetches around 100k records) gives incorrect results but if the same nested queries are used to build a table and the Connect By is used on the table then the output is correct.
I put the nested queries in a 'WITH' clause and got the correct output also.
I am not sure how to give the code here as you would need dump to make them work. I am giving the a sample
--Non Working Code
SELECT con_item, prod_item, compsite, bcsite, ibrsite, res
FROM (SELECT con_item, prod_item, compsite, bcsite, ibrsite, res
FROM (SELECT bd.item AS con_item, bd.fromid AS compsite,
bd.toid AS bcsite, bd.toid AS ibrsite,
[Code]....
View 1 Replies
View Related
Mar 16, 2013
Is there a way to define a SELECT clause once and reuse it in many other queries?
I have many procedures with same SELECT statement and I'm trying to find a way to not have to write out the SELECT clause in every function or procedure. Same question applies to FROM, WHERE, etc clauses.
View 12 Replies
View Related
Jan 23, 2007
our system has always been running on mysql database and recently we have switched to oracle. As the current system is coded using mysql query syntax, when i run this program using oracle database, i got a error. The language that I'm using is JSP.
this is the error message:
The following query could not run on oracle. To convert these mysql queries to oracle compatible queries.
SELECT productID,productName FROM products order by productName;
select newsID,newsDate,newsHeadLine1 from news order by newsDate Desc limit 3
SELECT fuji_products.productID, productName_Display FROM products,products_availability where products_availability.productID=products.productID and (product_status='enabled' or product_status='all') AND category='12'
SELECT catID, catSub1 from category where catSub = '"+ prodCat +"' AND catSub1 is not null group by catSub1 order by catSub1
View 6 Replies
View Related
Aug 24, 2010
I have a database in which DB extended auditing is enabled but there are no audit specifications in privileges or statements or objects. So what will be audited in that case.
View 12 Replies
View Related
Feb 24, 2011
I have enabled auditing in my oracle9i DB, it is running fine, generating trails and I can capture those. Recently I checked in dba_audit_session table and found os_username, userhost, terminal showing null value whereas username is captured as my own (having dba prvis). Strange thing is that it doesn't occurs everyday.
One of the possibility of running batch files may occurs such issues, but I ran this batch everyday then why it is occurring some days only.
Attached File(s)
dba_audit_session.txt ( 2.71K )
Number of downloads: 4
View 1 Replies
View Related
May 11, 2009
I'm working on a Java-based web application and we have unit tests that we use to test all our all code that interacts with the database or code that interacts with our DB code. The Spring framework allows us to perform some DML within a transaction before each test and then rollback the changes. For the most part, this works, however when I run the full suite of unit tests, it will randomly commit data to the database causing the rest of the tests to fail.
will Oracle's auditing let me see where this odd-ball commit is occurring? Is there another way for me to see when data is being committed?
This does not appear to be happening on any of the systems we've deployed, however this is a bit unsettling and would like to know why this is occurring so that we can prevent it from happening in production.
View 1 Replies
View Related
Mar 31, 2011
I am trying to maintain data audit in the database using triggers where i want to write the row level trigger in an generic way using the following concept .Using USER_TAB_COLUMNS table inside the trigger i want to bind all column values of the row into a single string in the following format
COLUMN_NAME = Value(:new/:old.COLUMN_NAME)=> this value would be bound dynamically is it possible to create a string for each row instance in the trigger at run time using the above mentioned format and user_tab_column table
View 5 Replies
View Related
Mar 8, 2011
how to set up alerts on specific audit log results without using Audit Vault?
View 1 Replies
View Related
Dec 15, 2010
In PL/SQL Plus, i can enable/disable auditing when i connect as sysdba by using these command:
SQL> ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE
SQL> shutdown
SQL> startup
I've done it successfully with PL/SQL Plus command line. But in PHP, how can i do that?How to execute "shutdown" and "startup" from PHP?
I've found this code for connect to oracle as sysdba:
oci_connect("/", "", null, null, OCI_SYSDBA);
From the following link:
[URL]......
But, i still can't execute "shutdown", "startup";
View 4 Replies
View Related
Jul 17, 2012
I would like to be aware of all select statements that are run against the schema I am responsible for (for performance analysis reasons) My privileges are restricted and I think I won't get access to any dba views.
So is there a recomondation how I can solve this requirement?
View 3 Replies
View Related
Jun 8, 2012
I am using Oracle Database 10.2 in No Archive Log Mode. what auditing options are available in No Archive Log mode ?
View 4 Replies
View Related
Sep 3, 2013
I'm attempting to audit unsuccessful Select statements in order to trap a problem we're experiencing with our application. I have set the AUDIT_TRAIL initialization parameter to DB_EXTENDED, and bounced our database.
I've issued the AUDIT SELECT ANY TABLE WHENEVER NOT SUCCESSFUL command, and when I issue a SELECT statement as an application user, nothing appears in SYS.AUD$ even though the application has issued a select statement which returned no rows.
View 3 Replies
View Related
Dec 8, 2011
How to enable auditing in oracle ?
View 2 Replies
View Related
Apr 27, 2012
We have recently upgraded our DR environment from 9i to 11g. We have auditing turned on for 3 tables.
On 9i, AUD$ table size is 11G for 12 months and the upgraded 11g environment has 9G in 2 days....
Below is the sql statement we used to turn on auditing on these tables.
audit select,update,delete on audit_Test2 by session;
Opened SR with Oracle, but no proper response from them...
what changes we need to do in order to reduce the amount of audit data on these tables in 11g?
View 8 Replies
View Related
Feb 15, 2012
I have a problem with a PCI DSS - requirement in Oracle 11.2. (PCI DSS = Payment Card Industry Data Security Standard)
Problem:
we connect via ' ssh -2 -X -l oracle hostname ' to the databaseserver and become os-user 'oracle'. we have also two offshore locations with dba's and each dba comes with his personalized user to the jumphost and then with the above ssh command to the database server.
the problem is that each dba becomes the oracle-os-account and can now connect with '/ as sysdba' to the database.in pci-dss this is not allowed !
now my question:how can I audit these '/ as sysdba'-connections and prove which user connected at which time with the '/ as sysdba' command ?
database is in audit mode. we log to syslog on linus redhat 5. I know one solution could be setting "SQLNET.AUTHENTICATION_SERVICES" parameter to "NONE" in sqlnet.ora file will make it not possible to connect to the database without a password as sysdba. (sqlplus / as sysdba). but we have to many applications and jobs and this is not really the solution in this case.
I think I can only solve this problem with personalized OS-user DBA-accounts in the dba-goup on os-site and os-user oracle should not be used for the future ?? I also need personalized dba-user-accounts in the database. using sys and system is not allowed. this users has to be locked and only for special administration work could it be unlocked.
View 3 Replies
View Related
Oct 10, 2012
i enable auditing on the database 10.2 for users,
once i query SQL> SELECT username,
extended_timestamp,
owner,
obj_name,
action_name
FROM dba_audit_trail
WHERE owner = <Username>
there are many many rows , my question is , are you enable to truncate it from time to time , if not ,is it effect on the performance of the database ?
View 3 Replies
View Related
Oct 26, 2010
It seems that dml trigger doesn't fire when lob field is being updated using dbms_lob package.
As it stated in Oracle documentation:
QUOTE Using OCI functions or the DBMS_LOB package to update LOB values or LOB attributes of object columns does not cause Oracle to fire triggers defined on the table containing the columns or the attributes.
I need to know that table was updated (or is about to be updated), how can I do that in case it is lob field that is being updated?
View 4 Replies
View Related
Mar 9, 2011
I am using Oracle 11g (11.2.0.1) and I want to audit the specific tables in my schema and send to OS syslog.
For example, I have 3 tables A,B and C. If any user is accessing A and B tables (DMLs,select etc) then I want to audit and send it to OS syslog.
View 1 Replies
View Related
Oct 26, 2010
It seems that dml trigger doesn't fire when lob field is being updated using dbms_lob package. As it stated in Oracle documentation:
Quote:Using OCI functions or the DBMS_LOB package to update LOB values or LOB attributes of object columns does not cause Oracle to fire triggers defined on the table containing the columns or the attributes.
I need to know that table was updated (or is about to be updated), how can I do that in case it is lob field that is being updated?
View 1 Replies
View Related
Jan 8, 2013
I have enabled Auditing but when i run the below given statement i get the output with count of 20 null username.I tried to run NOAUDIT ALL but still the same result.
Why does it show auditing for null username and how can i disable it.
select count(*) from DBA_STMT_AUDIT_OPTS where user_name is null;
20
View 3 Replies
View Related
May 15, 2013
For auditing, I need to insert the user, among other data, into different tables. The thing is, I have an application with DB account authentication, so a real database user is connected, when auditing, the user field inserted is "ANONYMOUS".
Apex 4.2
EPG
Oracle Enterprise Linux 5.5
Database 11.2 EE
View 7 Replies
View Related
Aug 22, 2011
I am importing some data from Oracle into another database on a regular basis. It works fine for most of the queries but couple of queries don't work sometimes (random). I don't get any errors or any data.
We switched on the Oracle auditing to find out the queries being sent to oracle db. We can see all the queries in the Audit log. Is it possible to configure Auditing to get the "Number of Rows" returned by Select statements so that we can be sure that some data was returned.
View 8 Replies
View Related
Apr 23, 2010
can we use something like this
"select ... order by emp from emp"
what is to be done? so that this qurey runs. no co-related subquery to be used.
View 6 Replies
View Related
Nov 12, 2012
is there any difference between
- returning from the procedure 2 ref cursors containing result set of 2 queries
- returning from the procedure 1 ref cursor containing result set of that 2 queries as one (with UNION ALL)?
Will 2nd option be faster or similar to 1st?
View 1 Replies
View Related
Jun 16, 2009
I have a table similar to the following,
USER, DETAILS, TYPE, UPDATED
1, user1home1, 1, 01/05/2009
1, user1home2, 1, 02/05/2009
1, user1work, 2, 03/05/2009
1, user1mobile1, 3, 04/05/2009
1, user1mobile2, 3, 05/05/2009
1, user1email, 4, 06/05/2009
1, user1other, 5 ,07/05/2009
2, user2home1, 1, 01/05/2009
2, user2home2, 1, 02/05/2009
[code]...
which contains multiple contact details for users of different types; type 1 is home, type 2 work etc. The following query returns the user's number and the latest home number for that user.
select user, details as latest_home_number from nc_test t
where type = 1
and updated = (select max(updated) from nc_test t2
where t2.user = t.user
and t2.type = t.type)
order by t.user
However I am not very experienced with sql and I am not sure how to create a view which would contain the fields:
user, latest_home_number, latest_work_number
View 3 Replies
View Related
May 1, 2011
I have performance problem with 7 queries involving groupby clauses in OLAP database.These are queries triggered during siebel DAC run
kumar[size="4"][/size][color="#0000FF"][/color]kumardba
View 5 Replies
View Related
Nov 16, 2006
I have to change some queries from SQL to Oracle but I couldn't convert these queries because they use some system tables in SQL that I don't know the equivalent Oracle tables. Following are SQL Queries
1. SELECT name, xtype FROM sysobjects WHERE xtype IN('U', 'V') AND name <> 'dtProperties' AND objectproperty(id, 'IsMSShipped') = 0 ORDER BY name
2. SELECT tS.name FROM sysobjects AS tS WHERE (tS.name IN (SELECT name FROM sysobjects WHERE xtype = 'U') AND xtype ='U') OR (tS.name IN (SELECT name FROM sysobjects WHERE xtype = 'V') AND xtype ='V')
3. SELECT o.name as TableName, c.name as FieldName, c.colid as Field_Ordinal, t.name as FieldType, c.length as FieldLength, c.prec as FieldPrecision, c.scale as FieldScale, c.isnullable, c.iscomputed, CASE WHEN c.status & 0x80 > 0 THEN 1 ELSE 0 END AS isidentity, columnproperty(o.id, c.name, 'IsRowGuidCol') as isrowguidcol FROM (sysobjects o JOIN syscolumns c ON o.id = c.id) JOIN systypes t On c.xtype = t.xtype WHERE o.xtype IN ('U', 'V') AND (t.xtype = t.xusertype)
View 2 Replies
View Related
Feb 2, 2005
On a tab page should be displayed the result of four indifferent queries, each based on a stored procedure.At the moment, the queries are processed serially, by the statements:
GO_BLOCK('one');
CLEAR_BLOCK(No_Validate);
EXECUTE_QUERY;
GO_BLOCK('two');
CLEAR_BLOCK(No_Validate);
EXECUTE_QUERY;
Is there a way to processes the queries parallel ?
View 1 Replies
View Related