CODEWITHSUNDEEP
oracle-databasesecuritydatabase-administration
RGRam
RGRam

Reputation: 31

Oracle user who have read/selected data in my tables

How do I know which are all the users have been accessing my table that is available in my schema.

EX: I have a table in oracle myschema.mytable with a public synonym to it. There are other users in the database.

I would like to know who are all other the users who have been accessing "mytable", other than "myschema"

Thanks,

Upvotes: 1

Views: 329

Answers (1)

thatjeffsmith
thatjeffsmith

Reputation: 22437

The only sure-fire way to know for sure is to enable Database Auditing (Docs).

This would record every session that had selected or read data from HR.EMPLOYEES

AUDIT SELECTON "HR"."EMPLOYEES"
BY SESSION
WHENEVER SUCCESSFUL;

Once this rule is set, you can start checking your audit trails - reports of who is doing what in terms of audited events, in this case looking at data in HR.EMPLOYEES.

You can simply query the DBA_AUDIT_OBJECT view.

Note that this feature does come with a cost - it increases the amount of work required of the database. Every session that looks at the data in EMPLOYEES, Oracle will have to record the entry in this trail.

If you want more granular, you can record activity by occurrence instead of by session. That will cost, even more.

Many people have built their own auditing systems with TRIGGERS, but all of them have drawbacks - mainly that you have to build and maintain the system.

I've only ever seen 100% complete auditing systems successful using this built-in feature. You just have to be prepared for the potential performance hit, and decide how often you want to clean up the audit trails.

And yes, SQL Developer has interface for the database auditing feature.

enter image description here

Upvotes: 3

Related Questions