Integrating Proxy Connections with J2EE Application Servers

Discussions

News: Integrating Proxy Connections with J2EE Application Servers

  1. Consider a banking application where a Teller is permitted to view the balance of an account, but only a Manager is allowed to create, change or delete an account. This can be accomplished by granting the Teller only the SELECT privilege on the ACCOUNT table, while the granting the Manager SELECT, INSERT, UPDATE and DELETE. When a Teller authenticates to the database with his username and password he obtains a database connection that restricts him to performing reads, while when a Manager authenticates, she obtains a different connection that allows her to perform additional actions. Creating a connection is expensive, using significant CPU and network resources. The DBMS must verify the credentials passed in the connection request, and allocate resources to support the connection. It may be several seconds before the connection is ready for use. High performance applications cannot afford to create and destroy connections with each database access, but providing a dedicated connection for each user for the duration of their application session would limit scalability due to the finite nature of database server resources. Connection Pools Application performance and scalability can be significantly improved by the use of a basic connection pool. At startup the application authenticates to the database with the user name and password of a database account that has the set of permissions required and populates the pool with a number of connections based on the applications concurrency requirements. These connections are shared by users of the application.   In our scenario, however, we cannot share the connections between users because users with different roles require different permissions and therefore different connections. We need to ensure that a Teller only gets a connection with Teller privileges while a Manager gets a connection with Manager privileges. One approach would be to have a separate connection pool for each user role. The application could track the role of the user requesting a connection and route that request to the appropriate connection pool.  But what happens if your application dynamically adds users and each user potentially requires different database permissions? Providing a separate connection pool for each quickly becomes a scalability problem. Proxy Connection Ideally, we would like to have just one connection pool, populate it with the appropriate number of connections at application startup. When a user needs a connection the application would get any available connection from the pool and tell the connection which set of privileges it should have. This is exactly the functionality provided by Oracle with OracleOciConnectionPool using the Oracle 9i JDBC OCI driver, also known as the thick driver. The pool is populated with connections established with the permissions of a user that has only CREATE SESSION privileges. This allows them to connect to the database but do nothing else.  Let’s call that user proxyuser. Log into the database as sys and run the following commands to create the proxy user account: create user proxyuser identified by proxypassword; grant create session to proxyuser; The following code creates the pool. The pool’s properties can be configured via its setPoolConfig(java.util.Properties) method. See the javadoc for the available values. OracleOCIConnectionPool pool = new OracleOCIConnectionPool(); pool.setURL("jdbc:oracle:oci:@" + your_connect_string); pool.setUser("proxyuser"); pool.setPassword("proxypassword"); At this point, you have the pool, but you need to grant proxyuser the permission to act as the application users. Let’s say that George is a Teller and has an Oracle account under the user name george. Log into the database as sys and run the following command: alter user george grant connect through proxyuser; Repeat this step for each user name that will use the proxy connection. Now when you need a connection for that Teller you call the following method passing “george” as the username: public Connection getConnection(String username) { java.util.Properties properties = new java.util.Properties(); properties.setProperty( OracleOCIConnectionPool.PROXY_USER_NAME, username); Connection conn = pool.getProxyConnection( OracleOCIConnectionPool.PROXYTYPE_USER_NAME, properties); return conn; } Now the connection has all the privileges that were granted to the george Oracle account. Calling close() on the connection releases it to the pool, but does not actually close the physical connection, so when Mary who is a Manager and has an Oracle account under the user name mary needs a connection, you call the same method passing her user name and that same connection is proxied to use her account privileges. With Oracle 9i this functionality was only available from the OracleOCIConnectionPool which could not be managed by an application server. This means that it could not be used with EJBs that use container managed transactions. Application server providers provide this service by wrapping the database vendor's connection with a proprietary implementation of java.sql.Connection. This changed with the release of Oracle 10g which exposed the proxy connection functionality through both the OCI and Thin drivers via the OracleConnection class. Using these drivers a wrapped OracleConnection returned from a DataSource can be proxied if the application server provides access to it. The following example is specific to WebLogic. Connection wrapper = datasource.getConnection("proxyuser", "proxyuserpassword"); OracleConnection conn = (OracleConnection) ((weblogic.jdbc.extensions.WLConnection)wrapper). getVendorConnection(); Properties proxyProperties = new Properties(); proxyProperties.setProperty(OracleConnection.PROXY_USER_NAME, "mary"); conn.openProxySession( OracleConnection.PROXYTYPE_USER_NAME, proxyProperties)   // use the connection with Mary’s account privileges   // reset the connection user to proxyuser conn.close(OracleConnection.PROXY_SESSION);   // return the connection to the pool conn.close();
  2. Really? Are we still enforcing user-level security rules at the database connection level? Really?
  3. Oh, yes![ Go to top ]

    Really?

    Are we still enforcing user-level security rules at the database connection level?

    Really?
    The mechanism is useful for both user-level and role-level security rules. But either way: I really, really hope so!
  4. Re: Oh, yes![ Go to top ]

    Really?

    Are we still enforcing user-level security rules at the database connection level?

    Really?


    The mechanism is useful for both user-level and role-level security rules. But either way: I really, really hope so!
    I would rather prefer the oracle row level security using Oracle VPD implementation rather than this connections approach as this connections based approach would demand creating number of oracle users with different roles/grants at database. Any comments welcome?
  5. ...making use of db2's CURRENT USER special register. Actually we did not use that for some kind of row level security but for some trigger based audit facility. It increased the overhead to get a connection from the pool and was a little bit tricky to be made portable accross applicationservers but in the end it worked quite well.
  6. We have similar requirements (security must be enforced at database level, i.e. outside of the application), however we went the route of using VPD based on Oracle context variables (secure global variables for each database session). This avoids being tied to any specific driver and also gets rid of App-Server specific code. I imagine you'd end up with code to obtain the underlying Oracle connection object for each App Server, plus more code if using anj JDBC monitoring tools, plus it still is unlikely to work in WebSphere which doesn't allow access to the uderlying connection?
  7. I have implemented the same in my project with Oracle 9iAS. The issue is, if an user closes the browser without pressing the logout link provided to close the connection, the physical connection is maintained in the database for hours or days. The status in db is shown as SNIPED and DBA has to kill the connection everytime. Implementing SessionListener is one way, but not sure in a environment like where an user can have only 4 sessions open and most of the time the user limit exceeds.
  8. Author is in wrong Job[ Go to top ]

    Defintely the author is at 1980 client server era... Wake up it is 2007 already...
  9. 1980 or 2007, same story[ Go to top ]

    Defintely the author is at 1980 client server era... Wake up it is 2007 already...
    Yes, and in 2007 application security has to be enforced at multiple levels in any critical scenarios. The author mentions banking, and certainly when dealing with people's money you would want security enforced at the network, application, and database layers at a bare minimum. Or are you one of those people who's never worked at a company big enough to have more than one app touching the same database?
  10. Loading everything into memory and enforcing data protection there?
  11. Lets burn the concept of SOA, JAAS let the database know about every user in system. As par I know the banking world still they are struggling to move out of mainframes forget adding security to each and every sql call. Biswaranjan Das Enterprise Architect SCEA,SCBCD,SCWCD,SCJP
  12. The Sybase open server concept is already lost. Sybase open server was a perfect implementation of the authors dream. It was originally designed for client server systems and works perfectly fine.
  13. Perhaps the use case cited was too simple. What if the requirement was for row level security, i.e., not simply SELECT vs INSERT, UPDATE and DELETE, but the ability to prevent certain users from viewing or modifying specific rows in a table (or multiple tables), while allowing them greater access to other rows in the same table(s)? Assume there are thousands of users, thousands of possible permission sets and potentially a different set of permissions for each user. So, Biswa, if the fires have burned out, how would you handle this in 2007?
  14. One of the main advantages of proxy authentication is on end-to-end security, by preserving client identities and privileges through all tiers and auditing actions taken on behalf of clients. VPD, on the other hand, allows flexible row-level security policy enforcement at the DB server side. These two really complement each other, and can be combined to provide more versatile multi-level security protection. VPD use is usually independent of Application Servers (for example, via PL/SQL on regular JDBC statements or done by a DBA in the database server), while proxy-authentication support varies with AS products. Oracle Application Server currently provides support for both proxy-authentication and VPD. Proxy-authentication is supported via native data sources in OracleAS (similar to the example in Bob's post). In the coming OracleAS patch releases, such support will be enhanced to provide a better integrated and ease-of-use experience.
  15. This is how things started messing up when moving from client server to web based applications. No database provides the security for row/column level directly. The features are always patchy and add on to the database like Syabse Open Server. And the major problem starts when you must use connection pools and I think every one in this forum will agree to there is no stepping out of "connection pools" today. Where the application admin and the user having lowest level of authorization use same database connection 9 out of 10 times. If you have multiple roles or with in same role there are users viewing different resultsets then obvoius place to keep your logic is in middleware not based on the which user is executing the sql in database.
  16. Has Oracle only just added this facility? We've been happily using SET SESSION AUTHORIZATION in Sybase's Adaptive Server Enterprise for years ;-) And their EAServer app server was able to automatically proxy connection pool connections to an authenticated user's ID back when it was known as Jaguar CTS, around the turn of the century...