CODEWITHSUNDEEP
javaspringjdbcdaojdbctemplate
yosh
yosh

Reputation: 3305

Multiple datasources with different permissions to one database

Is it good practice to use separate Data Sources with different user permissions for the same database? It would seem that having 2 separate Data Sources in one application (or even one DAO class) would add more security, as non-admin users would never be able to modify data.

Scenario

Web application with 3 types of users: guest, user, admin. The former two only need READ access. Admin additionally needs WRITE permission. Should the entire app use one dataSource with READ-WRITE access or would it be better to split it into 2 different dataSources?

Spring application, autowired DataSources managed by WildFly. Example:

Servlet-context.xml

<jee:jndi-lookup id="dataSourceR" jndi-name="java:jboss/datasources/SomeAppUserDS" />
<jee:jndi-lookup id="dataSourceW" jndi-name="java:jboss/datasources/SomeAppAdminDS" />

<beans:bean id="SomeSharedDAO" class="com.example.SomeSharedDAO">
    <beans:property name="dataSourceR" ref="dataSourceR"/>
    <beans:property name="dataSourceW" ref="dataSourceW"/>
</beans:bean>

SomeSharedDAO

public class SomeSharedDAO {

    private DataSource dataSourceR; 
    public void setDataSourceR(DataSource dataSourceR) {
        this.dataSourceR = dataSourceR;                 
    }
    private DataSource dataSourceW; 
    public void setDataSourceW(DataSource dataSourceW) {
        this.dataSourceW = dataSourceW;                 
    }

    //Some public method for all users
    public List<Something> list() {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSourceR);  
        //...
        //SQL select 
    }
    //Some method for admin only
    public void change(Something s) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSourceW);  
        //...
        //SQL insert/update
    }
}

What are the disadvantages of above solution?

Upvotes: 1

Views: 391

Answers (1)

Andy Guibert
Andy Guibert

Reputation: 42926

No, unless your application allows users to directly type in SQL to be executed on the server, it doesn't make much sense to have separate DataSources.

Coordinating the two DataSources amongst each other will just add unneeded complexity to your code, and incur additional resource overhead. What if a guest or users generates a record that need to be stored in the database? Such as a simple password change or something. Would they need to forward the request to an admin?

Users can only interact with your Datasource in use cases which you have defined. So you will already be restricting the DataSource access based on what each use case can do, and which users have access to each use case.

Also, don't get the false impression that allowing users to only read data is making your application in any way secure. Depending wha data you have, giving users the ability to read data can be just as dangerous as allowing users to write data.

Upvotes: 1

Related Questions