How do I hide my Oracle table?

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.

Do you really need to throw an error? Or do you simply need to verify that the application is not using fully qualified names (i.e. ABC.TRN)?

Assuming that you're merely interested in verifying that the application is not using fully qualified names and that throwing the error was merely the mechanism you thought of to notify you, you can probably verify the code by querying V$SQL while the application is running. V$SQL lists all the SQL statements in the shared pool in Oracle. If you query that table regularly while your application is running, you'll see all the SQL statements it issues. You can then log any statements that use fully qualified names.

For example

CREATE OR PROCEDURE look_for_abc_trnASBEGIN  FOR x IN (SELECT *              FROM v$sql             WHERE upper(sql_fulltext) LIKE '%ABC.TRN%')  LOOP    INSERT INTO log_of_bad_sql( sql_fulltext, <<other columns>> )      VALUES( x.sql_fulltext, <<other columns>> );  END LOOP;END;

If you run that procedure every few minutes while your application is running, you'll see any SQL that is using the fully qualified name and log that statement in the LOG_OF_BAD_SQL table. Every few minutes is probably overkill for a well-written system, you just need to ensure that it is run more frequently than statements are aged out of the shared pool. If you have an application that doesn't use bind variables appropriately, that may need to be every few minutes in order to avoid missing anything.

How about ALTER SESSION?

         ALTER SESSION SET CURRENT_SCHEMA = schema

That would allow you to log in as a user, to whom select rights have been granted to a table owned by schema X, and execute an SP that changes the session to schema X. The front-end code would not know that this had happened.

However, if your front-end code specifies schema X:

           select * from X.tableName

I don't think it will raise an error.

Perhaps you could explain why it's important that the client-code receive an error when it uses the correct current schema name?

Is it possible to create a new schema, transfer ownershp of the old schema's objects, and then drop the old schema, and then use the approach above?

P.S. See AFTER LOGON triggers: http://psoug.org/reference/system_trigger.html

P.P.S. Since you have elaborated upon your requirements:

... the table may be a synonym using a database link or the table might be hosted by in multiple schemas, each for a different release. It should be left to the database to resolve the actual location of the object referred to by client application.

If the location of the object is not in the CURRENT_SCHEMA but in some other schema, both of which happen to have tables called CUSTOMER, for example, the database engine won't know that the statement sent to it by the client app should be referencing the other schema if the tablename is not so qualified. That implies a level of meta-knowledge the engine doesn't have, though it gives the developer the tools to create such intelligence in the form of stored procedures and triggers and grant/revoke control over objects.

Your best chances of success in putting this intelligence in the back end would be to revoke all direct rights to tables and views and require client apps to access objects via stored procedures, because the database engine per se doesn't know about things like application release levels. I see no purely DECLARATIVE way to accomplish it. It would have to be procedural in large part. Your own back-end logic would have to assume responsibility for arbitrating between objects of the same name in different schemas. That said, features llike AFTER LOGON triggers and ALTER SCHEMA should prove helpful to you.