Separate data of user A from user B is a must-have of online hosted application in today's world. using separate schemas or database do not make much sense because everyone is using the same application . Point is , why do all the maintenance ?
Everywhere I looked, people just advise against this idea of multiple databases or multiple schemas citing performance reasons. same datamodel, same database. But you can not just let people query the base tables holding everyone's data. we need some form of security. The concept that provided security in a multi tenant database is called virtual private database or VPD.
Google for oracle row level access, oracle FGAC, sys_context and oracle VPD. One question to ask is , why not do this in web server layer (middleware). We can fetch result sets from database and filter it. YMMV but we wanted to do it at database level. our requirements are simple, so we do not want to use FGAC etc. Our scheme is to stripe base tables on a client_id. Then we create views on base table with a predicate
FROM BASE TABLE WHERE CLIENT_ID = GET_CLIENT_ID FROM CONTEXT Everywhere I looked, people just advise against this idea of multiple databases or multiple schemas citing performance reasons. same datamodel, same database. But you can not just let people query the base tables holding everyone's data. we need some form of security. The concept that provided security in a multi tenant database is called virtual private database or VPD.
Google for oracle row level access, oracle FGAC, sys_context and oracle VPD. One question to ask is , why not do this in web server layer (middleware). We can fetch result sets from database and filter it. YMMV but we wanted to do it at database level. our requirements are simple, so we do not want to use FGAC etc. Our scheme is to stripe base tables on a client_id. Then we create views on base table with a predicate
We do not provide select access on base tables, you can only query the views on base tables. Before doing any select queries , you need to set the client_id in sys context by executing a stored procedure. That is essentially how it works with Oracle.
Next question to ask is how do we use this scheme with hibernate ? One idea people come up with is that get connection from hibernate session and then execute stored procedure on this connection. You work with your hibernate session after that. Your client_id would then already be set. This looks like lot of work and discipline. Doing something every time you get a hibernate session may not be the best solution.
so is there a better way? YES. we can provide a custom connectionProvider to hibernate. we use C3P0ConnectionProvider. So we decided to write our custom connection provider by extending C3P0ConnectionProvider and pluggin in required logic.
we override just one method of Connectionprovider interface called getConnection()
Next question to ask is how do we use this scheme with hibernate ? One idea people come up with is that get connection from hibernate session and then execute stored procedure on this connection. You work with your hibernate session after that. Your client_id would then already be set. This looks like lot of work and discipline. Doing something every time you get a hibernate session may not be the best solution.
so is there a better way? YES. we can provide a custom connectionProvider to hibernate. we use C3P0ConnectionProvider. So we decided to write our custom connection provider by extending C3P0ConnectionProvider and pluggin in required logic.
we override just one method of Connectionprovider interface called getConnection()
VPDConnectionProvide extends C3P0ConnectionProvider{
getConnection(){
Connection conn = super.getConnection();
// execute stored procedure to set client_id in sys context for session
return conn ;
}
}
Now whenever hibernate requests a connection from ConnectionProvider ,the stored procedure is executed. Use hibernate mapping over this t_view. Thats it!
see the following resources also
