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.

Answer 2

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_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 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.

Answer 3

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.

Answer 4

You can't do it. Synonyms are nothing but pointers to other schemas' objects. You grant access to the actual object, not the synonym. From the Oracle docs:

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

Synonyms themselves are not securable. When you grant object privileges on a synonym, you are really granting privileges on the underlying object, and the synonym is acting only as an alias for the object in the GRANT statement.