How to hide an Oracle table?

Question

Here's the script (simplified example):

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

So far so good. However, I do not want the client code to indicate the name of the Oracle schema. Now I think that I deleted all the links in the client code that reference the schema, but I want to check this to make sure.

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

Is there any way to do this? Please note that the DEF must be in the same database as the ABC, there is only a TRN table table (owned by ABC), and I cannot use database links.

I tried to create a new XYZ user with a synonym pointing to ABC.TRN and giving him the right to choose ABC.TRN. Then I created a DEF user with a synonym pointing to XYZ.TRN and gave DEF the right to choose XYZ.TRN. This works, but Oracle is smart enough to know that if DEF has the right to choose from XYZ.TRN, then it also has the right to choose from ABC.TRN, thereby defeating the purpose of this exercise, because I want this case to give an error.

For you...

Answer 1

There is no easy way to do this.

One approach would be political: code analysis of the institute, possibly with automatic search for the code base and just tickle your wrists when people do it.

The architectural approach will be similar to your three-structure structure, but with a subtle twist: the scheme in the middle uses views. Thus, the ABC schema owns the tables and grants permissions to them for the XYZ schema. The XYZ schema builds simple views against these tables (SELECT * clauses, no WHERE clauses) and provides view permissions for the DEF schema. The DEF schema can only select XYZ objects.

Of course, all these efforts will still not SELECT * FROM xyz.whatever coding developers. In this case, I refer you to my first sentence 8 -)

---

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

Of course, you should try this strategy only if your installation scripts are fully parameterized, without the proper names of hard-coded circuits.

Answer 2

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

Assuming you are simply interested in verifying that the application does not use fully qualified names, and throwing an error was just a mechanism that you were thinking of notifying you, you can probably verify the code by querying V$SQL while the application works. V$SQL lists all the SQL statements in the shared pool in Oracle. If you regularly query this table while your application is running, you will see all the SQL queries that it issues. You can then register any claims that use fully qualified names.

for instance

 CREATE OR PROCEDURE look_for_abc_trn AS BEGIN 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 this procedure every few minutes while the application is running, you will see any SQL that uses the full name and registers this statement in the LOG_OF_BAD_SQL table. Every few minutes probably overwhelm a well-written system, you just need to make sure that it starts more often than statements come out of the general pool. If you have an application that does not use variable bindings appropriately, it may take every few minutes to avoid anything.

Answer 3

How about ALTER SESSION?

  ALTER SESSION SET CURRENT_SCHEMA = schema 

This will allow you to log in as a user who has been granted access rights to the table belonging to Schema X and run an SP that changes the session to Schema X. External code will not know that this has happened.

However, if your external code indicates the X scheme:

  select * from X.tableName 

I do not think this will cause an error.

Perhaps you could explain why it is important that the client code gets an error when it uses the correct name of the current schema?

Is it possible to create a new schema, pass the ownerhp of the old schema objects, and then delete the old schema, and then use the above approach?

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

PPS Since you developed your requirements:

... a table can be synonymous with a database link, or a table can be placed in several schemas, each for a different release. It must be left in the database to resolve the actual location of the object referenced by the client application.

If the location of the object is not in CURRENT_SCHEMA, but in some other schema that has tables named CUSTOMER, for example, the database engine will not know that the statement sent to it by the client application should refer to another schema if the scoreboard name is not has such qualifications. This implies a level of meta-information that the engine does not possess, although it provides the developer with tools for creating such intelligence in the form of stored procedures and triggers and providing / removing control over objects.

Your best chance of success in this intelligence in the end will be to revoke all direct rights to tables and views and require client applications to access objects through stored procedures, since the database engine itself does not know things like levels release applications. I do not see a purely DECLARATIVE way to achieve it. In many ways, this was supposed to be procedural. Your internal logic should take responsibility for arbitration between objects with the same name in different schemes. However, features similar to the AFTER LOGON and ALTER SCHEMA triggers should be useful to you.

Answer 4

You cannot do this. Synonyms are nothing more than pointers to objects of other circuits. You provide access to the actual object, not a synonym. From Oracle docs:

http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/views003.htm

Synonyms themselves are not protected. When you grant object privileges in a synonym, you really grant privileges for the base object, and the synonym only acts as an alias for the object in the GRANT statement.