First, I'd like to preface this question with a little info about me. I am an oracle dba. I have no experience with any OR mapper. I have been tasked with attempting to resolve this issue by my developers. The issue is: we are interested in using Oracle's VPD functionality. The issue we are running into, is that due to the connection pooling I have no way of identifying a user/client in the database session. So there isn't any easy way to pass an identifier to the VPD function, to facilitate Oracle VPD. Has anybody else run into this issue and/or have suggestions for resolutions?

Thank You

Brian PYle

We're not familiar with VDP, could you give a bit of info what it does or point us to a page where more info is available? thanks.

VPD is an oracle feature. What it does in general is adds a predicate to any sql that comes through the database that queries a specific column in a specific table. This predicate is generated by a function I write in the database. This predicate is attached to the sql as it comes through. This is used to restrict access to tables/columns. So if I have a query that select * from emp. and I don't want anybody to see anything but their own information if they select the salary column. I would write a function that generates a predicate along the lines of " and emp_name = <some user>" then I apply a policy that runs this function and appends this predicate to the end of any sql that queries the salary column in this table. This gives the user access to the table, but only their salary information. The issue here is that the function needs to be able to identify a specific user/client. We are managing users at the application level, and need a way to pass an user/client identifier to this function. The example function I've used takes advantage of the sys_context functionality in the Oracle Database. This can pick up information from the session. With session pooling, I don't know of a way to pass an identifier to the function. All the connections are using the same session.

Hope that helps

Brian Pyle

Thanks for the info.

So if I understand it correctly, you (want to) do the following: - open connection - send user info to VPD function to create specific predicate - execute query -> append VPD predicate - return results

Correct?

If you use our adapter paradigm, (recommended in this case), you can derive a class from DataAccessAdapter, the class which is used to connect to the db and execute queries, and override the method which opens the connect: OpenConnection. You should then in that override first call the base method (so the connection is open) and then execute the call to the VPD function to send the user info. This can for example be done by creating OracleCommand objects in that routine or by creating a new ActionQuery object and filling in the SQL query inside the command.

This makes sure that whenever a connection is opened to the DB, the user info is send over the connection before anything else is done. This means that the user info is tied to the open connection on the server, so any activity executed over the connection is tied to the user.

There are other ways to achieve what this VPD function accomplishes btw, but it's up to you if you want to use these, as they're client side oriented: 1) you can override methods in the derived class of DataAccessAdapter which are executed when a fetch is executed. these methods get the SQL to be executed and you can modify that SQL, e.g. append a predicate 2) you can also use our authorization feature so some users can and some users can't see data/alter data/save data/ modify data, this is all managed using simple authorizer classes. For an example, see the authorization example on our website.

Thanx Otis, That is generally correct. The one thing that the VPD does is that once I set some type of environment variable in the open connection, the functions run based on the column queried in the databases, deriving the predicate from this info and specific to each column. So once the functions are put in place, and the variable is set. The DB should take care of itself. Kinda abstracting this management away from the developer. The idea here being to use it for clients in the same DB/Schema. You set the client, and then don't have to worry about the client filter from there on out for that connection. I don't understand your alternatives, but from your first pass at a resolution, I am going to assume that you are probably right on, and will discuss further with my developers.

Thanx for your help

I'll let you know how it goes

Brian Pyle Confluent Data Systems