How do I hide my Oracle table?

Question

Here's the scenario (simplified example):

I have an Oracle user/schema called ABC. ABC owns a table called TRN. Client side code connects to the database as ABC and selects from ABC.TRN.

So far so good. However, I don't want the client code to specify the Oracle schema name. Now I think I have removed all references in the client code that refer to schema but I want to test this to make sure.

So I want to create a new user/schema called DEF which will be used by the client to connect to the database. When the client application select from ABC.TRN, it must give an error. However if the client application selects from TRN (no schema name), it must return the data.

Is there some way to do this? Note that DEF must be on the same database as ABC, there is only one table TRN table (owned by ABC) and I cannot use database links.

I have tried creating a new XYZ user with a synonym pointing to ABC.TRN and giving it select rights on ABC.TRN. Then I created the DEF user with a synonym pointing to XYZ.TRN and gave DEF has select rights on XYZ.TRN. This works but Oracle is clever enough to know that if DEF has rights to select from XYZ.TRN then it also has rights to select from ABC.TRN, thereby defeating the purpose of this exercise as I want this case to give an error.

Over to you...

Answer 1

There is no easy way to do this.

One approach would be political: institute code reviews, perhaps with automated searches of the code base, and just slap wrists when people do this.

The architectural approach would be similar to your three schema structure, but with a subtle twist: the schema in the middle uses views. So, schema ABC owns tables and grants permissions on them to schema XYZ. Schema XYZ builds simple views against those tables (SELECT *, no WHERE clauses) and grants permissions on the views to schema DEF. Schema DEF can only select from XYZ objects.

Of course, all that effort still won't prevent develoeprs from coding SELECT * FROM xyz.whatever. In which case I refer you to my first suggestion 8-)

---

Actually there is one, really really evil way to do this. Use synonyms in the app facing schema (DEF)and then change the name of the data owning schema (ABC).

Of course, you should only attempt this stratagem if your install scripts are fully paramterized, with no hard-coded schema names of their own.