5 Replies Latest reply on Sep 22, 2009 6:00 PM by Matthias Age

    Seam and Oracle policies

    Matthias Age Newbie

      Hello,


      I'm working on a legacy project which uses Oracle policies.
      This means that on every request I need to set a parameter (e.g. the user-id) for the the database connection (for example by calling an Oracle stored procedure) which is uesd by Oracle to restrict updates/inserts/delete/selects.


      I have to be absolutely sure that my approach is save even with many threads and users on the server i.e. I have to be sure that no user sees data from another user (this could happen I user A uses a connection where the user-id of user B is setted).


      Which approach would be the best one?
      a) a filter which before every request sets the paramter on the connection (inject EntityManager, get session from it, etc...)
      b) a JSF-phase listener which sets the paramter before the apply request values-phase (also here inject entity manager like in a).


      Did anyone face a similiar problem?
      What can I do here?


      Thanks,

      Matthias

        • 1. Re: Seam and Oracle policies
          Jean Luc Apprentice

          I'm not familiar with that Oracle feature, but one thing to consider is when is that user-id set on the connection: when the connection is obtained or at any time afterwards? The reason, obviously, is that connections are pooled so they are established before any user activity takes place.


          Seam maintains the identity of the logged in user on the thread or you can put this information yourself if you want.


          One factor is what are you going to set for anonymous visits.

          • 2. Re: Seam and Oracle policies
            Matthias Age Newbie


            I'm not familiar with that Oracle feature, but one thing to consider is when is that user-id set on the connection: when the connection is obtained or at any time afterwards? The reason, obviously, is that connections are pooled so they are established before any user activity takes place.

            The flow should be as follows:



            1. a user makes a request

            2. seam gets a connection from the pool

            3. before the connection is used I have to call my stored procedure to set the user-id on the connection

            4. proceed (with actions / actionListeners etc.)

            5. at the end seam returns the connection to the pool; the user-id for this connection remains set and has to be overriden the next time the connection is used (step 3) - otherwise chaos is guaranteed




            One factor is what are you going to set for anonymous visits.

            Anonymous visits are not allowed, every user has to log on.

            • 3. Re: Seam and Oracle policies
              Sanne Grinovero Master

              It is possible to configure any connector pool with Hibernate; I'd suggest creating your own connector pool and actually wrap the default one (to still get good connections from JBoss's excellent datasource, using delegation).
              This way you could set variables at each connection acquire, and reset the variables after usage.
              Better ideas anybody?

              • 4. Re: Seam and Oracle policies
                Sanne Grinovero Master

                Some more details where asked in PM about using a custom connector pool:


                Implement org.hibernate.connection.ConnectionProvider (sources in hibernate-core), implementing all methods by using delegation to the ConnectionProvider you would be using normally, and add the code you need at connection opening and returning.
                Then in persistence.xml you select your own implementation as connection provider:


                 hibernate.connection.provider_class = fully.qualified.name.of.MyOwnConnectionProvider 



                • 5. Re: Seam and Oracle policies
                  Matthias Age Newbie

                  Thank you Sanne for your help.


                  This solved my problem. Here's some code if someone is interested in:




                  public class StatefulDatasourceConnectionProvider extends org.hibernate.connection.DatasourceConnectionProvider {
                  
                       private static final String NO_USER_ID = "-9999";
                  
                       @Override
                       public Connection getConnection() throws SQLException {
                  
                            Connection connection = super.getConnection();
                            Identity identity = null;
                            try {
                                 identity = Identity.instance();
                            } catch (IllegalStateException e) {
                                 // if there are components with @Startup annotation
                                 // and do some queries in their initialization there will be an IllegalStateException
                                 // when they are created
                                 log.debug("IllegalStateException: " + e);
                            } 
                            if (identity != null && identity.getCredentials().getUsername()!=null) {
                                          //prepare the connection for use with OraclePolicies   
                                 prepareConnection(connection, identity.getCredentials().getUsername()); 
                            } else {
                                          //if there's no logged user I hide private data
                                 prepareConnection(connection, NO_USER_ID);
                            }
                  
                            return connection;
                       }
                  
                       @Override
                       public void closeConnection(Connection conn) throws SQLException {
                              //TODO: clean up the connection
                                 super.closeConnection(conn);
                       }
                  
                  ....
                  
                  }