CODEWITHSUNDEEP
sqloracle-databaseroles
LostReality
LostReality

Reputation: 687

Oracle, grant select on any table except 1

I would like to make an user access all tables from my DB except for 1 table which will be 'MY_SECRET_TABLE' as example. My solution is not working currently... :

Grant select any table to public;
Grant select any table to MY_USER;
Revoke all on MY_SECRET_TABLE from MY_USER;

But i can still access the table. Am I doing something wrong ?

Thanks,

Upvotes: 0

Views: 2645

Answers (2)

Srini V
Srini V

Reputation: 11355

One way to do this is by obtaining the executable grants through a SQL

SELECT   'GRANT SELECT ON ' || object_name || ' TO USERNAME;'
FROM     user_objects
WHERE    object_type = 'TABLE' AND object_name != 'EXCEPT_THIS_TABLE'
ORDER BY object_name;

Upvotes: 1

Ted at ORCL.Pro
Ted at ORCL.Pro

Reputation: 1612

GRANT SELECT ON ANY TABLE is a very powerful privilege, so you may want to rethink your strategy.

Anyhow the only solution I see is to use row level security so that anyone except MY_USER can select the table but no rows will be returned.

How does that sound?

Upvotes: 1

Related Questions