Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

up vote 3 down vote accepted

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.

share|improve this answer
add comment

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.
share|improve this answer
    
I'll agree you're correct, but it's encouraging bad practice. –  FreshPhilOfSO Feb 18 '12 at 2:01
    
@Phil I certainly don't mean to encourage it and I thought I indicated that with my comment and my first sentence in my answer. So that there is no doubt -- I do not encourage the use of VPD to limit access to a users own data. I strongly encourage such access be limited by the creation of a separate user. In general table owner users should always be separate from application login users. –  Leigh Riffel Feb 19 '12 at 1:50
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.