0

An Oracle DB user has SELECT permission on all the tables of a DB schema. Can i restrict the user to view the table data.The user should be able to select the table but should not be able to see the data.

This specific requirement is required for user who reviews DB design and generates ALTER script for DB using Oracle Data Modeler 3.3 where he can just see the table design and can compare it with ERD

Can i achieve it using FGAC or RLS?

Anuj
  • 197
  • 2
  • 11
  • 3
    Might work if you remove the select privilege on the table completely, but grant the necessary privileges on `DBA_TABLES` and `DBA_TAB_COLUMNS` –  Oct 31 '13 at 10:28
  • Thanks!. Oracle Data Datamodeler 3.3 just requires SELECT_CATALOGUE_ROLE. – Anuj Nov 01 '13 at 09:44

1 Answers1

0

You can achieve this by granting only references

GRANT references ON schema_a.table TO erd_user;

the erd_user can then use

DESC schema_a.table

to get a definition but not select any data.

This might be preferred to giving the SELECT CATALOGUE where they can see a lot more information that you might like.