Pete Finnigan's Oracle Security Forum


    
      
        Pete Finnigan's Oracle Security Forum
        (http://www.petefinnigan.com/forum/yabb/YaBB.cgi)
      

        Oracle Security >> Oracle Security >> VPD vs Views
        
(Message started by: Pete Finnigan on Nov 22^nd, 2005, 5:08am)

Title: VPD vs Views
Post by Pete Finnigan on Nov 22^nd, 2005, 5:08am

In all the documentation about Virtual Private Databases, I have not seen any discussion as to why they are better than views. Eg. one could write

CREATE VIEW RESTRICTED_CUSTS AS
SELECT * FROM CUSTOMERS WHERE
CUST_ID IN
(SELECT CUST_ID FROM USER_AUTHS
WHERE USER_ID = USER)

This seems much cleaner than appending code to a WHERE clause.

Also, the methods of generating the clauses shown in the VPD examples are subject to sql injection. Bad things would happen if a CUST_ID contained a ' (single quote) character, say.

Finally, what about the USER. In most applications this is just a generic user passed in the connection. Is there a way to pass/access a fine grained token that represents the real, human user?

Thanks,

Anthony

Title: Re: VPD vs Views
Post by Pete Finnigan on Nov 22^nd, 2005, 5:14pm

Hi Anthony,

Allow me to express some views (no pun intended!). The reason VPD is better than views is that the protection is at the data level and the where clause is appended by the Oracle kernel in the SQL VM. If you simply have views on base tables then it would still be possible to access the base data in the view and bypass the security that you have set up.

I am not sure that you could SQL Inject a predicate in VPD. First you would need permission to craete the predicate in the VPD policy so why bother SQL Injecting, youl would already have permission to access the data. That is not to say that it is not possible!

You do not need to use just USER in VPD, you can use all sorts of values to identify the user running the code, the normal practice is to use contexts.

Have a look at the two part paper I wrote on Row Level Security for security focus. There are links on my [url http://www.petefinnigan.com/orasec.htm]Oracle Security white papers page[/url].

cheers

Pete

Title: Re: VPD vs Views
Post by Pete Finnigan on Nov 22^nd, 2005, 11:43pm

Hello Pete,

Thanks for your reply. I think that you are saying that VPDs are more secure than Views, but that there is no functionality than can be achieved with VPDs that cannot be achieved with Views. I was wondering if that was the case.

Have there been any known bugs in the Oracle kernal that could be used to subvert the View/Table approach that could not also be used to subert the VPD approach? I understand that both the table beneath the view approach and the VPD are protected by the same role based security.

As to SQL injection, it just feels like asking for trouble to *assume* that the concatenated strings never have funny characters. That sort of code gets jumped on in application code reviews. Is there an Oracle provied function that safely quotes funny characters in strings? (And knows exactly what those characters are?)

Thanks,

Anthony

Title: Re: VPD vs Views
Post by Pete Finnigan on Nov 28^th, 2005, 1:21am

Ultimately, they both mechanisms for applying a filter to a query. The main benefit of VPD over Views is that it is easier to combine restrictions.

There's been mention on the XE forum about the lack of VPD in XE. I believe it is also not part of Standard Edition. In most cases, I don't see it as a real barrier, since simple applications can use views just as securely. Its only in the more complex organisations where different groups have users have different restrictions (eg one can only see employees in the same departement, another can see all depratments but only employees of a certain grade etc) that VPD is needed.

As long as grants are made just on the views and not on the underlying objects, views can be just as secure.

Title: Re: VPD vs Views
Post by Pete Finnigan on Dec 4^th, 2005, 9:13pm

Hi Gary,

I am not convinced that a view based approach is always as secure as VPD. VPD is built into the SQL VM and as such cannot be bypassed as easilly as a view based apprach could be. With VPD the data is protected at source. With views there is a layered approach and it is always more easy to come in at a different layer and get to the data.

But as you point out if you use XE then Views would have to be the way forward.

cheers

Pete

Title: Re: VPD vs Views
Post by Pete Finnigan on Dec 5^th, 2005, 1:09am

Thinking more on this (and trying to come up with a more definitive list of the added VPD functionality) :

VPD allows protection from users with SELECT ANY TABLE privileges, and even the SCHEMA owner.
With a view, the owner of the underlying table will always be able to see all data in it, as would a user with SELECT ANY TABLE (or a specific grant on the table).

So VPD could allow, for example, an environment where a person can replace a procedure or function but still not see data in the tables in the same schema (or even truncate a table for which they cannot see the data). I think that's something for bigger organisations where 'DBA' responsibilities may be split between different people.

Title: Re: VPD vs Views
Post by Pete Finnigan on Dec 5^th, 2005, 6:12pm

Hi Gary,

This is the power of VPD over views. you can limit access to the data even from some DBA's. Of course anyone with EXEMPT ACCESS POLICY system privilege or SYS will bypass VPD but its better than views. I would agree to some extent that its bigger companies that will and do use VPD but anyone affected by regulatory needs could also use it.

cheers

Pete