Home » RDBMS Server » Security » track user's select statements
track user's select statements [message #204749] Tue, 21 November 2006 23:19 Go to next message
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.
Re: track user's select statements [message #204753 is a reply to message #204749] Tue, 21 November 2006 23:29 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

hi


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
Re: track user's select statements [message #205093 is a reply to message #204753] Thu, 23 November 2006 05:12 Go to previous messageGo to next message
smartcobra
Messages: 3
Registered: October 2006
Junior Member
user52 wrote on Tue, 21 November 2006 23:29
hi


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.


Re: track user's select statements [message #205290 is a reply to message #204749] Fri, 24 November 2006 03:14 Go to previous message
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:
  


Current Time: Fri Mar 29 10:38:32 CDT 2024