How to set a role in DataSource from Spring

I have to use a default Role so that after the datasource has been defined and connected that role will allow me to use select statements. I know its little weird but I have to do it due to security concerns. So here is the code in my Spring's applicationContext.xml:

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
        destroy-method="close">
    <property name="driverClassName" value="com.ibm.db2.jcc.DB2Driver" />
    <property name="url"
            value="jdbc:db2://host_name:Port/DB_NAME:INFORMIXSERVER=SERVER_NAME;DELIMIDENT=y;" />
    <property name="username" value="user" />
    <property name="password" value="password" />
    <property name="minIdle" value="2" />
</bean>

Everything is fine I am able to connect to the datasource but I also need to tell the database that all the queries will be through this ROLE so that I do have select rights and stuff: For command prompt I am doing like this:

set role ROLE_ACCESS_SELECT;

before executing sql commands but don't know how to set it up here.

Please advice.

Answers


DBCP's BasicDataSource allows you to pass SQL statements to be executed during initialization of the connection. Something like this:

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" 
    destroy-method="close">
    <property name = "connectionInitSqls">
        <list><value>set role ROLE_ACCESS_SELECT</value></list>
    </property>
    ...
</bean>

Never mind, I have fixed this issue. Thanks to all who looked at my post the solution is like this:

add this line after minIdle property:

<property name="initSQLCommands">
            <list>
                <value>SET ROLE ROLE_WEB_OPERATORS</value>
            </list>
        </property>

and define a class such as:

public class ExtendedBasicDataSource extends BasicDataSource {
    public void setInitSQLCommands(List<String> initSQLCommands) {
        this.initSQLCommands = initSQLCommands;
        setConnectionInitSqls(initSQLCommands);
    }

}

And use that bean instead of org.apache.commons.dbcp.BasicDataSource while defining the datasource bean in Spring's ApplicationContext.xml.


Need Your Help

LINQ query IEnumerable<Object> for IEnumerable<DataRow> results in null

c# linq datatable sqlbulkcopy

Why is my LINQ query ("rows") showing up as a null when it seems like I have plenty of data ("waypoints")?

Manipulating the content of an email message

php email

I am looking for a solution that will enable me to connect to a mailbox, obtain an email, apply specific modifications to the email body (for example, change the content), and then forward the newly