Re: Object privilege at row level?

On Thu, 24 Jul 2003 23:57:35 +0200, Rick Denoire <100.17706_at_germanynet.de> wrote:

> Is there a way (using Oracle 9.2.0) to control access to rows of a
> table? (Instead of granting access to the whole table)
>

No, but Fine-Grained Access Control (Virtual Private Database and Row-Level Security are its other names) can do exactly this. Write a policy for the table which appends a WHERE clause to every select. With a bit of careful coding, you could arrange for the policy to append a where clause that permits the user to see only those rows you want him to see.

> Can one at least monitor access to certain rows of a table?
> That is, is it possible to protocol who and when accessed certain
> rows?

Yes, using Fine-Grained Auditing (which, despite its name, has absolutely nothing to do with Fine-Grained Access Control). Use DBMS_FGA to add an auditing condition to a table, or particular columns in that table. If a select statement (and it only works for selects) meets the auditing conditions specified, then dba_fga_audit_trail will give you the username, the time, and the SQL statement that was issued.

Whether any of that would suit you depends entirely on the nature of your data and the basis on which you want to audit or control access.

~QM Received on Thu Jul 24 2003 - 18:33:49 CDT