OraFAQ Forum: Security » track user's select statements

Home » RDBMS Server » Security » track user's select statements

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

track user's select statements [message #204749]

Tue, 21 November 2006 23:19

smartcobra
Messages: 3
Registered: October 2006

Junior Member

Hi:
I want to track select statements executed by a user on some tables,any ideas?
Thanks for the Help.

Report message to a moderator

Re: track user's select statements [message #204753 is a reply to message #204749]

Tue, 21 November 2006 23:29

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member


select sa.sql_text,ss.username
from v$session ss, v$sqlarea sa
where sa.hash_value = ss.prev_hash_value

**********

SQL> /

SQL_TEXT
--------------------------------------------------------------------------------

USERNAME
--------------------
DELETE FROM T
HR

GRANT SELECT ON T TO
HR

SELECT COUNT(*) FROM HR.T
SCOTT


SQL_TEXT
--------------------------------------------------------------------------------

USERNAME
--------------------
select sa.sql_text,ss.username from v$session ss, v$sqlarea sa where sa.hash_val

ue = ss.prev_hash_value
SYS

You want like this
or
not then go for auditing.

For auditing : http://www.securityfocus.com/infocus/1689

hope this helps
Taj

Report message to a moderator

Re: track user's select statements [message #205093 is a reply to message #204753]

Thu, 23 November 2006 05:12

smartcobra
Messages: 3
Registered: October 2006

Junior Member

user52 wrote on Tue, 21 November 2006 23:29


select sa.sql_text,ss.username
from v$session ss, v$sqlarea sa
where sa.hash_value = ss.prev_hash_value

**********

SQL> /

SQL_TEXT
--------------------------------------------------------------------------------

USERNAME
--------------------
DELETE FROM T
HR

GRANT SELECT ON T TO
HR

SELECT COUNT(*) FROM HR.T
SCOTT


SQL_TEXT
--------------------------------------------------------------------------------

USERNAME
--------------------
select sa.sql_text,ss.username from v$session ss, v$sqlarea sa where sa.hash_val

ue = ss.prev_hash_value
SYS

You want like this
or
not then go for auditing.

For auditing : http://www.securityfocus.com/infocus/1689

hope this helps
Taj

thanks
The version is Oracle 8.1.7.4, Can Fine-Grained Auditing work?
I think trigger is a good idea,but how to track select statements using
trigger?
I want to track the whole select statement when a user select some
columns on a table.

Report message to a moderator

Re: track user's select statements [message #205290 is a reply to message #204749]

Fri, 24 November 2006 03:14

bwetkstr
Messages: 114
Registered: August 2005

Senior Member

Hi,

If I'm not mistaken, you can't add a trigger to a select statement. Only an insert,update, delete can be triggered on a table or view.

Normally I would say fine-grained auditing, because you audit on certain columns or rows.

I'm not sure if an 8i database has FGA, check if you have a package called DBMS_FGA.

Database Auditing can capture all access to a table whether it is a select or DML statement.

Metalink : Note:207959.1

Kr
Karel

[Updated on: Fri, 24 November 2006 03:14]

Report message to a moderator

Previous Topic:	ORA-01005 in ORACLE 9i to ACCESS 2000
Next Topic:	Database encryption

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Dec 12 22:26:49 CST 2024