1 Reply Latest reply on Dec 3, 2010 8:41 AM by nicolasdavyt1234

    Datasource security

    kilyas

      Like any normal application running on JBoss I am using JBoss managemed datasources in the applications running on the app server for DB interaction.  It has been working fine till now when we came across a new requirement.  The new requirement is such that not all the users should have the permissions to update the data, e.g

       

      A user John logs into an application running on JBoss and requests some information from the database.  Now once the data is returned to him not only does he have the permissions to view it but he could also delete/update the information.  While when another person Bob might just view the information.  So apparently I would to ensure that the connections established with the database in these cases should be as themselves and these users' permissions should be defined in the database while creating these users.  Apparently in such a case we might be bypassing the connection pool(please correct me if I am wrong).  Also is there a way to define such a connection in the datasource xml file where the username/password information is left to the application level?

       

      Secondly we have generic accounts defined in the datasource file.  These accounts have read/write priviliges to the DB.  e.g boatrade/password has beed defined in the ds file as follows:-

       

      <local-tx-datasource>
          <jndi-name>TradeTestOracleDS</jndi-name>
          <connection-url>jdbc:oracle:thin:@trmpd_pkg.bankers.com:1521:trmpd</connection-url>
          <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
          <max-pool-size>100</max-pool-size>
          <user-name>boatrade</user-name>
          <password>test</password>
          <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
          <metadata>
            <type-mapping>Oracle9i</type-mapping>
          </metadata>
        </local-tx-datasource>

       

       

      Since this datasource is defined in the ds file is there a way in JBoss to ensure that this ds is only used for select statements and no updates/deletes are performed using this ds?  Or that only these set of applications can use this datasource and nobody else has access to this DS?

       

      Thanks in advance.

        • 1. Re: Datasource security
          nicolasdavyt1234

          Maybe you cant try with define 2 or 3 <jndi-name> with diferent username and password with respective privileges defined at the database. (only select, full acces, etc)

           

          <local-tx-datasource>
              <jndi-name>TradeTestOracle_ONLY_SELECT_DS</jndi-name>
              <connection-url>jdbc:oracle:thin:@trmpd_pkg.bankers.com:1521:trmpd</connection-url>
              <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
              <max-pool-size>100</max-pool-size>
              <user-name>boatradeONLY_SELECT</user-name>
              <password>test1</password>
              <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
              <metadata>
                <type-mapping>Oracle9i</type-mapping>
              </metadata>
            </local-tx-datasource>

           

          <local-tx-datasource>
              <jndi-name>TradeTestOracle_ALL_ACCESS_DS</jndi-name>
              <connection-url>jdbc:oracle:thin:@trmpd_pkg.bankers.com:1521:trmpd</connection-url>
              <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
              <max-pool-size>100</max-pool-size>
              <user-name>boatrade
          _ALL_ACCESS_</user-name>
              <password>test1</password>
              <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
              <metadata>
                <type-mapping>Oracle9i</type-mapping>
              </metadata>
            </local-tx-datasource>

           

          And in you code like for example:   (in your dataBase Connection class.)

           

          public static Connection getConnection( User user)

           

          ......

          if (user.equals("Bob"))

               ds = (DataSource) iniCtx.lookup("TradeTestOracle_ONLY_SELECT_DS");

          else if (user.equals("John"))

               ds = (DataSource) iniCtx.lookup("TradeTestOracle_ALL_ACCESS_DS");

          .......