Home » RDBMS Server » Security » virtual private database, Need Guidance
virtual private database, Need Guidance [message #456226] Sun, 16 May 2010 03:29 Go to next message
hellozishan
Messages: 5
Registered: September 2007
Location: CY
Junior Member
Hello All,

We are using Oracle 11g r2 as a backend database and Java as frontend tool. Our users are created throug application menu like oracle applications. My question is if I want to implement virtual private database security over application users, is it possible ? If yes pls let me know how ?
Because application users are not database users.

Regards,
Re: virtual private database, Need Guidance [message #456232 is a reply to message #456226] Sun, 16 May 2010 05:12 Go to previous messageGo to next message
Michel Cadot
Messages: 66808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
My question is if I want to implement virtual private database security over application users

Why? What is the goal you want to achieve?

Regards
Michel
Re: virtual private database, Need Guidance [message #456611 is a reply to message #456232] Tue, 18 May 2010 13:27 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Yes you can, but it will not be a simple thing.

First off VPD is not a simple concept to understand. Most people don't get it at a basic level. In particular consider what it means to issue a query that requires resticting rows on a column that is hidden from you because of a VPD policy. At this point you are thinking... "what did he just say?". This of course is my point. Within VPD there are simple implementations and advanced implementations. But even the simple thing is not so simple.

VPD is a very advanced feature of the Oracle database. It is something that needs to be considered as part of a database design to start and not a quick hack to get around some problem. As an advanced feature used as an integral part of a database's design, it can be very powerful and save you lots of time and money and give your database a real edge over other databases that don't use it. On the other hand, it can also horribly screw you over if you use it without knowing why you are using it.

If you are looking for a "show me how to do it" you can forget it. To learn VPD is something that cannot possibly be descibed in a simple post sorry. You will have to do a good deal of reading.

But to your initial question, yes it is possible. You will however need your application to provide some data to the database in order to make things work. I suspect you will need to use SYS_CONTEXT in order to tell the database who the active application user is. This is because VPD is NOT part of your specific application and thus has access only to information that the database can see. How does the database and therefore VPD know your application user name if it is not the schema name? Answer: you will have to tell the database the active application user name. This can be done in several ways but I would consider use of SYS_CONTEXT. Once done, your VPD policy function can take advantage of this information.

If you don't know what I mean by VPD policy function then that shows you why you have lots of reading to do. Just do any GOOGLE for ORACLE VPD and you will find what you need.

Good luck, Kevin

[Updated on: Tue, 18 May 2010 14:06]

Report message to a moderator

Re: virtual private database, Need Guidance [message #456614 is a reply to message #456611] Tue, 18 May 2010 14:36 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Let me point you to one web page to start. In particular consider these two queries (these are provided at the link with full details).

select cust_last_name, cust_first_name, credit_limit, account_mgr_id from oe.customers order by account_mgr_id;
select cust_last_name, cust_first_name,               account_mgr_id from oe.customers order by account_mgr_id;

Notice that the second query does not reference the credit_limit column.
Notice also that there is no WHERE clause on these two queries.
Notice also that we are referencing the same rowsource object (eg. table/view/synomym) OE.CUSTOMERS.

Most people would expect these two queries to return the same set of rows. After all, same table, same WHERE clause, only thing different is the column list so the rows returned has to be the same.

But such is not the case. The first query, because it references a "VPD SECURED COLUMN" causes the query to apply a VPD policy. This VPD policy can a likely will for whatever reason decided to restrict the rows that can be seen. Wow... selecting a particular column caused rows to drop from the query? Who would have expected that? Imagine someone tasked with adding the credit_limit column to an app in support of a maintenance ticket, who during testing sees different rows before and after the change. They must now diagnose what looks like a bug. Is it a bug?

This is not at all intuitive but is the nature of VPD. This should scare most people into taking a second look at using VPD; particularly the more advanced use of VPD to do HIDDEN COLUMNS.

I am not knocking VPD. It is to me a beautiful feature which once again shows the superiority of Oracle over all other databases and which emphasizes the power one can gain by teaching your database a new trick rather than trying to teach every application that touches your database the same trick. I am simply pointing out that like ALL Oracle Advanced features, it is very strict in how it works and what it was intended to do. If you do not understand it well or you intend to use the feature for some purpose other than the purpose for which it was intended, then you should not use it. Let me suggest that most of what can be achieved with VPD can be achieved with VIEWS. Indeed we were creating VPD databases 20 years ago using views. For sure it is not as sophisticated as Oracle VPD but it is also very straight forward.

Good luck, Kevin

[Updated on: Tue, 18 May 2010 14:53]

Report message to a moderator

Re: virtual private database, Need Guidance [message #465083 is a reply to message #456226] Mon, 12 July 2010 09:45 Go to previous messageGo to next message
m_maruthu
Messages: 8
Registered: July 2010
Location: UK
Junior Member
Hi All,

I found this posting very useful. As per Kevin's reply, we can pass the application user name to oracle by SYS_CONTEXT.
In that case, can I assume that we don't need to create individual user accounts at oracle level?
We've around 2000 users and thought it's impossible to create user accounts for all of them.

Any suggestion is much appreciated!

-Mark
Re: virtual private database, Need Guidance [message #465084 is a reply to message #465083] Mon, 12 July 2010 09:58 Go to previous messageGo to next message
Michel Cadot
Messages: 66808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It depends of what you want to achieve.
So please describe in details what you want to achieve (not the way you want to achieve it).

Regards
Michel
Re: virtual private database, Need Guidance [message #465088 is a reply to message #456226] Mon, 12 July 2010 10:18 Go to previous messageGo to next message
m_maruthu
Messages: 8
Registered: July 2010
Location: UK
Junior Member
Hi,

Thanks for the quick reply.

We've about 2000 business users who use different reporting tools and toad to access our warehouse data. We have a user table which links to other application tables. Now based on the application user login, we want to apply restriction through VPD policy.

As per VPD reference document, it says that there is a need to create oracle individual account for all users.
I assume only those who need direct access to DB needs to be setup with oracle user account. For reporting users, there is no need to create oracle login. Please confirm if this is correct.
Re: virtual private database, Need Guidance [message #465089 is a reply to message #465083] Mon, 12 July 2010 10:21 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
There are a couple of factors to think about.

1) most databases use one or more database schemas to house the data. These accounts usually have little to do with actual users accessing the data. They are essentially organizing schemas.

2) beyond these organizing schemas, the question arises as to how to track access to the data and objects in these organizing schemas. For that there are two basic approaches I see applied

a) use individual account per user.
b) use context data provided by the application at runtime.

Your goal is to be able to show who changed what, when, and with what code. You should have a firm grasp of what your security and auditing requirements are in order to know what you must do to successfully provide for each.

All this said, my company does not use individual oracle users anymore. All systems use "generic" login accounts. I believe this has to do more with the way WEBLOGIC and its connection pooling works.

In the last five years, our databases have adopted use of what i call SCHEMA MANANAGMENT in order to provide structure to a database. It generally goes like this:

1) core data: one or more schemas for data objects and associated code. This is what most people call THIRD NORMAL FORM DATA MODEL.

2) DBA contructed database features layer: one or more schemas sitting on top of the core data schema(s) that provide specific functionality independent of and transparent to every app. Think auditing and history. This layer provides common database behavior and removes the burden on applications to write code for same. Indeed, this layer requires apps to not write any code for features it provides.

3) Use-ability layer: remappings of underlying objects to common concepts. Think EMPLOYEE for example. This is often a role played by a person with respect to a company. Most people and applications want to interact with one thing called employee instead of the ten or eleven tables one would find in a universal datamodel subject area like PARTY. So, in this layer there are multiple remappings of the data to common concepts.

4) Application schemas: an application's specific view of the data. This layer is very important. It often means the difference between delivering on time or suffering major delays and Quality Assurance issues. For example, online applications like to have a screen to DML interpretation of the data. This layer gives them such a view and additionally translates actions on their objects to lower level objects. By offereing such a layer in our database designs, we afford applications independence in their choice of design strategies, implementations strategies, and tool strategies. In short it allows applications to do what is best for them without worrying about how it affects the database for others.

But as you can see, all this great design stuff, has nothing to do with how you track access to the data. Why would anyone consider one schema per user unless they intended to track each user's activity seperate from others. However, in a system that uses a generic login in order to support connection pooling (or for whatever other reaons), multiple user IDs does not work.

In order to deal with this, when our systems need to track access by user (which is always), we use system contexts. Of course this requires some planning. For example, I have yet to figure out how to get our connection pool manager (WEBLOGIC) to provide values to our system context automatically. Maybe someone can answer that for us.

Kevin

Re: virtual private database, Need Guidance [message #465092 is a reply to message #465089] Mon, 12 July 2010 10:28 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
VPD is a different sort of animal. You should read up on it to make sure it is what you want. In particular you should consider if you are going to allow COLUMN MASKING. Column Masking can have side affects that people do not expect.

Additionally, VPD is an advanced feature. It should be part of your system design strategy which means you should not be using it as an extension of any single application's logic. But from you problem description it sounds like you already know this.

I do not believe it is actually necessary to create individual accounts per user. Your VPD policies can read system context values just like any other piece of code you construct.

Kevin

[Updated on: Mon, 12 July 2010 10:29]

Report message to a moderator

Re: virtual private database, Need Guidance [message #465095 is a reply to message #465088] Mon, 12 July 2010 10:44 Go to previous messageGo to next message
Michel Cadot
Messages: 66808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Now based on the application user login, we want to apply restriction through VPD policy.

You can use a view instead with a part that test an environment variable. It could be a context value, a client_info (see dbms_application_info).. set by the application server.
If you connect through TOAD, that is client-server environment each user must have his specific account to distinguish them unless you can do it via IP address.

Regards
Michel
Re: virtual private database, Need Guidance [message #465096 is a reply to message #465095] Mon, 12 July 2010 10:46 Go to previous messageGo to next message
Michel Cadot
Messages: 66808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Here's an example I posted using a package variable:
http://www.orafaq.com/forum/m/425657/102589/?#msg_425657

Regards
Michel
Re: virtual private database, Need Guidance [message #465102 is a reply to message #456226] Mon, 12 July 2010 11:16 Go to previous messageGo to next message
m_maruthu
Messages: 8
Registered: July 2010
Location: UK
Junior Member
Many Thanks Michel and Kevin for the valuable inputs. I am looking forward to test this end-to-end (from user sigle sign-on to access restricted data) and will come back to you for any further help.

Thanks again!
Mark
Re: virtual private database, Need Guidance [message #465104 is a reply to message #456614] Mon, 12 July 2010 11:53 Go to previous messageGo to next message
John Watson
Messages: 8138
Registered: January 2010
Location: Global Village
Senior Member
Kevin Meade wrote on Tue, 18 May 2010 14:36
Wow... selecting a particular column caused rows to drop from the query? Who would have expected that?
I had never thought of it this way. I suppose one would put it formally as saying that "the column projection impacts on the row selection", which must be a serious breach of the SQL standard - or so it would seem to the user.
My own experience of VPD is that one needs to be very careful with performance. Dynamic policies (the default) can cripple your database because of the repeated evaluation, so if the functions are deterministic, be sure to mark them as POLICY_TYPE=>STATIC. But if you can't do that, at least try for SHARED_CONTEXT_SENSITIVE.

(@kevin - I hope you are still on for that little project we discussed. I'll be sending you a massive email this weekend, which may change your mind....)
Re: virtual private database, Need Guidance [message #469175 is a reply to message #465104] Tue, 03 August 2010 11:19 Go to previous messageGo to next message
m_maruthu
Messages: 8
Registered: July 2010
Location: UK
Junior Member
Hi Experts,

Can anyone please confirm if we need a separate oracle installation/pack to implement VPD? Currently
we have oracle 10 G. Is there a way to check we've all components installed in the environment?

Thanks in advance for the help!
Re: virtual private database, Need Guidance [message #469176 is a reply to message #469175] Tue, 03 August 2010 11:28 Go to previous message
Michel Cadot
Messages: 66808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
VPD is part of Entreprise Edition.

Regards
Michel
Previous Topic: How to find FGA,
Next Topic: TIMESTAMP on FGA_AUDIT
Goto Forum:
  


Current Time: Wed Jan 29 08:34:07 CST 2020