Oracle's Select Any Table Limitation

[hprasetya]

Hi,

In Oracle we can grant a role to have "Select Any Table", allow user to select any info on any table (including view). Is there any syntax to limit it a bit, to let the user do select on any table but not "Emp" table for example ?

[pclement]

Have you tried doing a REVOKE for the privileges on the table?

[hprasetya]

Yes,
Revoke on that specific table is not working (Revoke On "Emp" From...) cause it never granted explicitly before (seems the "Select Any Table" is so special).
Revoke the "Select Any Table" itself is working, but then I lost all (what I wanted is All but not "Emp" table).
Btw, someone knows the syntax to get the list of all table name ? (I'm thinking that if I can get all the table name dynamically then I can do a Grant loop and excluding "Emp" table, instead of giving "Select Any Table")

[pclement]

How about "select table_name from sys.all_tables"?

You may want to restrict this query based upon "owner" but I think it should work.

[hprasetya]

Thanks.
One more thing, I just read about adding security policy, do you know something about it (if that might apply on specific table, so that in table "Emp" I can just forbid the specific role) ?

[pclement]

If you're referring to VPD (Virtual Private Database), no I'm not really familiar with it.

[hprasetya]

Don't know what's VPD, but I read about the package supplied by Oracle : DBMS_RLS.ADD_POLICY etc (and wonder if we can add a security policy to block access to particular table).