3

Using Oracle XE 10g.

We have an application that uses a database user with restricted access. Because it is very cumbersome to find out by trial and error which tables this user needs to have access to, we are looking for a way to query the Oracle Data Dictionary for information on table access.

What would be great is a query that gives a list of tables that this user accessed since some point in time, preferably with whether this was write or read-only access.

That way, we could run a test with a user with unrestricted access, find out which tables it used, and then setup this user to only have access to those tables.

I was looking at V$SEGMENT_STATISTICS, but this does not log the user that accessed the object.

wvdz
  • 16,251
  • 4
  • 53
  • 90
  • if you have enough time to collect the data maybe a table trigger would do this for you by logging user's access once per statement in a designated table? – przemo_pl Sep 14 '16 at 12:12
  • You're probably looking for [database audit](http://www.oracle.com/technetwork/database/security/index-085803.html) – mustaccio Sep 14 '16 at 12:13
  • 1
    db audit is only available in enterprise and standard edition. – OldProgrammer Sep 14 '16 at 13:30

1 Answers1

2

You can track access with the AUDIT command. This can generate a lot of data so be careful.

Enable auditing on all statements from a specific user, to capture all access:

audit all statements by jheller;

It doesn't take effect until the next session. After logging back in, run some test commands:

create table test1(a number);
select * from test1;
insert into test1 values(1);
delete from test1;

Now query DBA_AUDIT_TRAIL to find the data. There's a lot of junk in the audit trail so it will take some effort to narrow the results down.

select owner, obj_name, action_name
from dba_audit_trail
where username = 'JHELLER'
    and owner = 'JHELLER'
    and timestamp > sysdate - 1
order by timestamp desc;

Results:

OWNER    OBJ_NAME  ACTION_NAME
-----    --------  --------
JHELLER  TEST1     DELETE
JHELLER  TEST1     INSERT
JHELLER  TEST1     SELECT
JHELLER  TEST1     CREATE TABLE
Jon Heller
  • 34,999
  • 6
  • 74
  • 132
  • I get an error when I try that command. What works for me is `AUDIT ALL BY username`. However, `DBA_AUDIT_TRAIL` remains empty. Do I need to restart the database? Or is this feature not enabled in XE? – wvdz Sep 15 '16 at 07:24
  • The command doesn't take effect in the current session. You'll need to log out and back in. – Jon Heller Sep 15 '16 at 07:26
  • i'm upvoting it because it's a pretty good answer, but I can't accept it as the correct anwer because I've become convinced that this is an EE feature. It's probably part of Fine-grained auditing (https://docs.oracle.com/cd/B28359_01/license.111/b28287/editions.htm) – wvdz Sep 23 '16 at 10:11