21

I am trying to implement row level security so our application can enforce more stringent access control.

One of the technologies we are looking into is Oracle's Virtual Private Database, which allows row level security by basically augmenting all queries against specific tables with a where clause predicate. Since we are in a web environment, we need to set up a special context within Oracle, inside a single request's thread. We use connection pooling with a service account.

I started to look into Eclipse Link and Hibernate. Eclipse Link seems to have events that fit perfectly into this model.

This would involve us migrating from hibernate, which is not a problem, but we would then be bound to EL for these events.

Oracle seems to imply that they implement at the data source level in Web Logic product.

The context is set and cleared by the WebLogic data source code.

Question: Is it more appropriate to do this at the DataSource level with some series of events. What are the events or methods that I should pay the most attention too?

Added Question: How would I extend a connection pool to safely initialize an oracle context with some custom data? I am digging around in Apache, and it seems like extending BasicDataSource doesn't give me access to anything that would allow me to clean up the connection when Spring is done with it.

I need to set up a connection, and clean up a connection as the exit / enter the connection pool. I am hoping for an implementation that is so simple, no one can mess it up by breaking some delicate balance of products. 

- Specifically we are currently using Apache Commons DBCP Basic Data Source

This would allow us to use various ways to connect to the database and still have our security enforced. But I don't see a great example or set of events to work with, and rolling my own security life cycle is never a good idea.

TheNorthWes
  • 2,661
  • 19
  • 35
  • I would not do this on the connection pool side, as connection pools don't usually have (and I think they should not have) information about the user doing the request or the request at all. – jjmontes Oct 13 '16 at 15:59
  • Shouldn't the oracle JDBC connection eventually maintain the oracle connection context? – TheNorthWes Oct 13 '16 at 16:01
  • I think so. Hence the need to set up Oracle VPD parameters on a per-request basis, and clearing those when returning the connection to the pool, as you stated. To me, Eclipse Link approach is the best in this scenario, however I could not find an equivalent for Hibernate/Hibernate+Spring, but I think that'd be the best tier to resolve this. – jjmontes Oct 13 '16 at 16:11
  • Thank you for the help, I am concerned about binding my security model to the high level frameworks. Do you agree? The problem is, I can extend basic data source, but then I also have to extend GenericObject pool. I could just override GetConnection, and always clear/set information in that method, but then I have no way to clear it on returning it to the pool. – TheNorthWes Oct 13 '16 at 16:18
  • Thinking a bit more about this, perhaps as you say overriding DBCP `BasicDataSource` is a good approach. I'd look into `.getConnection()` and `PoolableConnection.close()` for cleanup, and also check whether using pooled prepared statements could impact behavior (though I don't think so). Caching will definitely impact behavior so you may need to look into that too. I agree it's not ideal to do this at Hibernate level. But since pooling is involved, you'll definitely need to at least alter your connection pool as you describe and I comment here. Seek further advice. – jjmontes Oct 13 '16 at 16:26
  • Yeah I'm surprised I can't find anything about this online, and where to do it safely in my code. All cached data would have to be turned off since it would have no idea about the lower level ACL stuff. – TheNorthWes Oct 13 '16 at 16:36
  • A typical approach is to wrap your underlying connection pool, using containment, not extension, and delegate all calls to the underlying connection pool. The connection checkout and release calls would have an additional hook which sets up the Oracle VPD parameters prior to returning the connection to the client, and the release call would clear them out (remove the proxy user, or, ideally set it to a user without any permissions or otherwise leave the connection in an unusable state if no new parameters are set). – BeeOnRope Oct 14 '16 at 00:50
  • By using a wrapping-by-containment approach, you know exactly which calls you are implementing, since anything you don't implement won't be available in the API. This is must better than trying to extend the existing connection pool, since all the existing methods will be available, and some may offer up a connection without setting the VPD parameters. Furthermore, even if you carefully vet all existing methods, new methods may be available as soon as you update the connection pool library. – BeeOnRope Oct 14 '16 at 00:52
  • Hibernate lets you do query filtering that sounds very similar to what you're getting from this vpd thing. – Nathan Hughes Oct 14 '16 at 13:44
  • Is the issue that the logical app users should be constrained in access as database users using the Oracle VPD? I.e. are users in the web app _also_ users in Oracle? Or are different apps on the app server seeing different slices of the same database? – Andrew Alcock Oct 14 '16 at 14:02
  • @AndrewAlcock we are using a OneBigUser approach. So no users in the web app do not have oracle accounts, and each users access is different so creating a few "real" users is out. – TheNorthWes Oct 14 '16 at 15:38
  • @BeeOnRope can you elaborate? Internal to Apache's BasicDataSource, it constructs the object pool. My plan is to override that call and substitute my own object pool. – TheNorthWes Oct 14 '16 at 18:27
  • @AdmiralAdama: So the problem is how to ensure that the OneBigUser in the app is restricted at row level in the database using Oracle VPD? Presumably other apps (or reporting or users) can connect to the same database instance and see more/different rows. Is that right? – Andrew Alcock Oct 17 '16 at 09:37
  • @AndrewAlcock the VPD policy enforces access control across any users who need to have it enforced. Of course some users (DBA etc) are exempt. – TheNorthWes Oct 17 '16 at 16:33

4 Answers4

2

I eventually solved my problem by extending some of the Apache components.

First I extended org.apache.commons.pool.impl.GenericObjectPool and overrode both borrowObject() and returnObject(). I knew the type of the objects in the pool (java.sql.Connection) so I could safely cast and work with them.

Since for my case I was using Oracle VPD, I was able to set information in the Application context. I recommend you read about that in more depth. It is a little complicated and there are a lot of different options to hide or share data at various contexts level, and across RAC nodes. Start

In essence what I did was generate a nonce and use it to instantiate a session within oracle, and then set the access level of the user to a variable in that session, that the Oracle VPD policy would then read and use to do the row level filtering.

I instantiated and destroyed that information in my overridden borrowObject() and returnObject() The SQL I ran was something like this: 

CallableStatement callStat =
                    conn.prepareCall("{call namespace.cust_ctx_pkg.set_session_id(" + Math.random() + ")}");
                  callStat.execute();

Note math.random() isn't a good nonce.

Next was to simply extend org.apache.commons.dbcp.BasicDataSource and set my object pool by overriding createConnectionPool(). Note that the way I did this disabled some functionality I did not need, so you may need to rewrite more or less than I did.

Community
  • 1
  • 1
TheNorthWes
  • 2,661
  • 19
  • 35
0

You can try any object level security mechanism for simplicity, like Spring Security ACL.

Anil Agrawal
  • 2,748
  • 1
  • 24
  • 31
  • Thanks, Spring ACL runs post query which is too late for my needs. I need to enforce the ACL where the data is held. But i wasn't clear in my question and this is a perfectly valid solution for many people. – TheNorthWes Mar 29 '17 at 14:50
  • Do you want to fetch only those records from database on which user have rights? If yes, then may I ask you that are you talking about permissions for dbUser or application user ? – Anil Agrawal Mar 29 '17 at 16:18
  • The idea was to operate with 1 db user, but apply ACL by the client end user (many unknown varieties) – TheNorthWes Mar 29 '17 at 16:19
  • I don't think there is any such existing solution (I may also be wrong). One thing I'll suggest use Spring ACL for providing permissions to application users on different object (as its easy to use). Then write your own sql function to fetch data from db that will check the permissions for user (from ACL tables) and return only required records from tables. – Anil Agrawal Mar 29 '17 at 16:37
  • There are a multitude of solutions, Oracle VPD for example – TheNorthWes Mar 29 '17 at 16:38
  • Those all are flavors of object level security. If you use these solutions you have to compromise at different levels. Even you can't predict them as of now. Your work will become complex. Its all about level of security you want to achieve. If you require high level security and ready to invest more time and money then you can go with it. Other wise if you require moderate level security and don't want to make things very complex , then you can find lighter alternates. – Anil Agrawal Mar 29 '17 at 16:46
  • Major limitations of Database solutions is that its became very hard to migrate database. Your application will be very tightly coupled with database(which is not good actually). Its hard to use any ORM framework along with such solutions. – Anil Agrawal Mar 29 '17 at 16:53
  • My submitted solution works with ORMs, and row level security is available in many databases. Your point is correct that there becomes a coupling, but you can isolate it to a couple classes. – TheNorthWes Mar 29 '17 at 16:56
  • There may be one more solution for it. If you are using any ORM and have good knowledge about it. You can use java instrumentation to modify the finally generated query to apply your where conditions over it. – Anil Agrawal Mar 29 '17 at 16:57
  • Actually its all about choices :-) There are many solutions for any problem. We use solution, which is more close to our area of interests and fulfills specific requirements or use-case. – Anil Agrawal Mar 29 '17 at 17:05
-1

You will want to do this at the application layer. You will want a pre-commit hook and a post read hook.

The pre-commit hook is used to ensure that data from the client is being presented by a user authorized to modify that data. This prevents an unauthorized user from overwriting data that they shouldn't be able to access.

It's not intuitive, but the post read hook is used to keep the client from accessing data the user shouldn't be allowed to view. This happens post-view because this is being enforced at the application layer, not at the data layer. The application has no way to know if the caller is allowed to access the data until it's been retrieved from the data layer. In the post read hook you evaluate the credential on each row returned against the credential of the logged in user in order to determine whether or not access is allowed. If access is denied on any row then an exception would be raised and the data would not be returned to the client.

Application level security done in this way requires that you have a way to connect each row in a table to a permission/role required to access it and a way to evaluate a user's permissions on the server at runtime.

Hope that helps.

Adam
  • 19
  • 5
  • This doesn't really address how to instantiate the context for Oracle VPD or clean it up. It is another approach to row level security certainly though. – TheNorthWes Feb 14 '17 at 16:39
  • @AdmiralAdama the full question begins with "I am trying to implement row level security so our application can enforce more stringent access control." The stated question is "Is it more appropriate to do this at the DataSource level with some series of events". My reply is intended to suggest that this type of security which is most often associated with a user session managed at the application layer, not at the data layer, can be managed at the application layer with my suggested approach. – Adam Feb 15 '17 at 17:30
  • True. I had intended to focus on how to leverage VPD though. Sorry if that was not clear in my question. My problem with your approach is that no other clients can connect to the data safely. Everyone has to implement ACL policies. We were trying to make it fool proof so no one could break it without knowing what they did. – TheNorthWes Feb 15 '17 at 23:22
-2

You will get better control by using one of the other Commons DBCP Datasources. The Basic one is just that: basic :) The ones in org.apache.commons.dbcp.datasources package gives you more fine-grained control.

Peter Gelderbloem
  • 512
  • 3
  • 12