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?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
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.
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....
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: