Is to possible to forbid access to tables in own schema - Oracle?

Question

Lets assume that we have the following situation: We have an Oracle schema/user: USER_TEST (with the "create session" privilege). SYS as SYSDBA creates couple Tables on schema USER_TEST. Is it possible to forbid access for the user USER_TEST to tables on his schema?

Answer 1

No, the schema is the user in oracle. In other databases, where the schema and user are actually different things (and appropriately so), then you could have a schema name that matches the user name but where the user does not have rights to the objects under that schema.

Answer 2

While there is no privilege you can revoke, and I'd wonder why you can't just create another user, the answer to your question is....

Yes

If you have the Enterprise Edition of Oracle you can use the Virtual Private Database feature to prevent tables from being selected. Here is an overview from the Oracle Database Security Guide 11g Release 2:

Oracle Virtual Private Database (VPD) enables you to create security policies to control database access at the row and column level. Essentially, Oracle Virtual Private Database adds a dynamic WHERE clause to a SQL statement that is issued against the table, view, or synonym to which an Oracle Virtual Private Database security policy was applied.

Oracle Virtual Private Database enforces security, to a fine level of granularity, directly on database tables, views, or synonyms. Because you attach security policies directly to these database objects, and the policies are automatically applied whenever a user accesses data, there is no way to bypass security.

When a user directly or indirectly accesses a table, view, or synonym that is protected with an Oracle Virtual Private Database policy, Oracle Database dynamically modifies the SQL statement of the user. This modification creates a WHERE condition (called a predicate) returned by a function implementing the security policy. Oracle Database modifies the statement dynamically, transparently to the user, using any condition that can be expressed in or returned by a function. You can apply Oracle Virtual Private Database policies to SELECT, INSERT, UPDATE, INDEX, and DELETE statements.

To prevent SELECT on a table you would need to do the following:

1. Create a function that accepts a schema/object name and returns a WHERE clause style Varchar2. In this case it should return something like '1=2' when the user is the user you don't want to access its own objects.
2. Call the Add_Policy function of the DBMS_RLS package for each table the user owns. This will create a policy attaching the function to the table it will protect.