Adding where clauses from database.

[Silvia]

Hello,

We are using MyBatis 3.0.3 & Spring 3.0.5 in our project.

We are trying to implement data level security through a table which
stores where clauses (userid < 200, active == true, ...).

So, the problem comes when we want to add a clause to a select query
dinamically in execution time.

We are thinking about the possibility of using Spring AOP but we are
not sure if it is possible or not, if it is a good solution and appart
from that we don't know how to implement it.

Please share your thoughts.

Thanks in advance,

Silvia.

[Jeff Butler]

IMHO implementing this with database views is far easier than trying
to intercept the sql in mybatis.

Jeff Butler

--
Sent from my mobile device

[Mario Ds Briggs]

Some Database's security features explicitly allow to do this at the DB
level itself.

regards
Mario

[Josh Kamau]

Sylvia;

Unless am missing something, its seems like you just have to do something like this:

//on some where in the service layer

String whereClause = db.getWhereClause(...);

Mapper maper = .... //get your mapper

List<User> users = mapper.getUsers(whereClause);

//In the mapper interface

List<User> users getUsers(String whereClause);

//In the mapper xml
<select id="getUsers">
  SELECT * FROM users $whereClause
</select>


Hope it helps;

Kind regards.
Josh.

[Josh Kamau]

Small Correction:
....

//In the mapper xml
<select id="getUsers">

  SELECT * FROM users ${whereClause}
</select>

Hope you know the difference between #{} and ${}

regards.
Josh.

[Silvia]

Thank you very much to all of you for your replies.

I am going to try to explain the problem in more detail:

I am developing a security application where admin users can define
clauses in a table so as to some users can't access to certain
information.

For example, the application can send this query to the database:

SELECT * FROM OBJECTS WHERE ACTIVE = 'TRUE'

And, on the other hand, the user who is logged in the application, can
have a restrictive clause in the table OBJECTS like this:

WHERE APPLICATION != SECURITY_APP.

So, the big issue is that we will have to add more conditions to the
select query we are processing.

I hope you can help me. If you don't understant anything please don't
hesitate to ask.

Thanks in advance,

Silvia.

> > On Wed, Jul 13, 2011 at 7:54 AM, Mario Ds Briggs <mario.bri...@in.ibm.com>wrote:
>
> >> Some Database's security features explicitly allow to do this at the DB
> >> level itself.
>
> >> regards
> >> Mario
>

[Mario Ds Briggs]

In database world it is called FGAC (Fine Grained Access control)

DB2 10 supports it -
http://ibmsystemsmag.blogs.com/db2utor/2010/11/fine-grained-access-control-strengthens-data-security.html

Oracle also -
http://databases.about.com/b/2008/04/10/oracle-fine-grained-access-control.htm

thanks
Mario

From: Silvia <silvia...@gmail.com>
To: mybatis-user <mybati...@googlegroups.com>

[Silvia]

Thank you very much Mario.

I'm going to investigate that but I'm using DB2 version 8.

Do you know if that version supports (Fine Grained Access control) ?

Thanks in advance,

Silvia.

On Jul 13, 10:24 am, Mario Ds Briggs <mario.bri...@in.ibm.com> wrote:
> In database world it is called FGAC (Fine Grained Access control)
>

> DB2 10 supports it -http://ibmsystemsmag.blogs.com/db2utor/2010/11/fine-grained-access-co...
>
> Oracle also -http://databases.about.com/b/2008/04/10/oracle-fine-grained-access-co...
>
> thanks

[Terényi Balázs]

Hi!

That is what I have hacked into our myBatis fork, described partially in the
mail copied below.

The problems are:
- View based solutions, db vendor's things are limited, for example based on
current db user which makes them useless in one db user environment
(connection pooling).
- You can add parameters to every select, but it's a lot of work (readablility
problems, etc).
- Sometimes you need to pass many fixed parameters to every select (as we have
to)
- You need to pass those parameters to every associated selects fired by the
main statement, in unlimited levels. You can not pass easily many parameters
to associations beside the logically required ones, it will be a mess.
- Sometimes you need to pass different parameters to the associated selects
than the main one! This can only be done if you intercept all the statements
somewhere in myBatis.
- In our project the parameters (where clausules) can not be put into or
generated in the database. It is generated from things not available in the
database!

Our version works superb, but it was done in about a day and has many other
ugly modifications like iBatis2 style caching and removed lazy loading and
local cacheing code :)

It would be great if myBatis could support this out of the box, because we
could get one step further to get rid of maintaining our fork. And it can be
used to address problems other than row based security too.

Best regards,
Balázs Terényi

---------------
I have managed to modify every parameter passed to myBatis on the fly to
extend the "user" given parameters with two fixed ones, that every select can
access, even selects fired by associations and collections (recursive).

I have extended org.apache.ibatis.executor.parameter.ParameterHandler which
replaces every parameter with a HashMap and puts the original param in and the
two common parameters too. It works, but there are two problems with it:

1. EL expressions have no access to the added parameters, only the original
parameters with the original notation. Not a problem for us, but...
2. Cacheing is not working.

Is there an another place in the myBatis workflow where I can intercept those
calls to change the parameters (which occurs somewhere at the beginning)?

Or any other method to modify the parameters or put in somehow other
parameters accessible for every statement with recursion to association and
collection selects?

Think of it like global variables avaiable to every statement (selects), but
those variables are set up exclusivelly for every statement regardless of what
invoked that statement (user or myBatis for associations and collections).
----------------

2011 July 13 Wednesday 09:29:14 Silvia:

[Mario Ds Briggs]

only v10 supports FGAC

v9 supports LBAC i.e. label based security

thanks
Mario

[Mario Ds Briggs]

You are right, just fyi It is not necessary that connection pooling forces
a 1 db user only scenario. Vendors do have things like TrustedContext (DB2)
that remove the need for 1 user only with connection pooling.

Mario

[Silvia Nozal]

I am sorry Mario but I haven't understood you very well.

Could you explain to me with more detail and if it is possible in DB2 version 8?

Thanks in advance.

2011/7/13 Mario Ds Briggs <mario....@in.ibm.com>

[Mario Ds Briggs]

this thread confused you :-)

DB2 version 8 has no support for FGAC or LBAC. So you will need to build
this in the app layer if you're on v8.

regards
Mario

[Silvia]

Could you please tell me how you suggest we face the problem then in
the app layer?

Thanks in advance and regards,

Silvia.

On Jul 13, 12:43 pm, Mario Ds Briggs <mario.bri...@in.ibm.com> wrote:
> this thread confused you :-)
>
> DB2 version 8 has no support for FGAC or LBAC. So you will need to build
> this in the app layer if you're on v8.
>
> regards
> Mario
>

> From:   Silvia Nozal <silvia.no...@gmail.com>
> To:     Mario Ds Briggs/India/IBM@IBMIN
> Cc:     mybati...@googlegroups.com
> Date:   07/13/2011 02:40 PM
> Subject:        Re: Adding where clauses from database.
> Sent by:        mybati...@googlegroups.com
>
> I am sorry Mario but I haven't understood you very well.
>
> Could you explain to me with more detail and if it is possible in DB2
> version 8?
>
> Thanks in advance.
>

> 2011/7/13 Mario Ds Briggs <mario.bri...@in.ibm.com>

>   You are right, just fyi It is not necessary that connection pooling
>   forces
>   a 1 db user only scenario. Vendors do have things like TrustedContext
>   (DB2)
>   that remove the need for 1 user only with connection pooling.
>
>   Mario
>

[Mario Ds Briggs]

Nothing other than i feel it is a not simple feature to build especially
around the flexibility and extensibility you want :-)

From: Silvia <silvia...@gmail.com>
To: mybatis-user <mybati...@googlegroups.com>

[Mario Ds Briggs]

and yes you should thank Jeff for reminding that DB views are still there.
Not as 'simple' (meaning app has to pick the view to use) as
FineGrainedAccessControl or LabelbasedAccessControl, but still there.

[c c]

You can create two views in database base on the same table.

2011/7/12 Silvia <silvia...@gmail.com>

--
Justin

[Silvia]

Thank you very much to all of you.

I have been researching on this and I am not sure about creating views
suits me. I will give you a bacis example of my problem:

I have three tables USERS, PROFILES and OBJECTS.

USER a has PROFILE read-only and the WHERE CLAUSE active = true.

USER b has PROFILE limited and the WHERE CLAUSES active = true, hidden
= false.

How can I create views since the values depend on the logged user and
apart from that, I don't know beforehand de number of where clauses?

Thanks in advance,

Silvia.

On Jul 14, 10:40 am, c c <chinesespider...@gmail.com> wrote:
> You can create two views in database base on the same table.
>

> 2011/7/12 Silvia <silvia.no...@gmail.com>

[Mario Ds Briggs]

i am assuming that while u might have 1000's individual users, they can be
still be logically grouped into a much smaller # of groups. Then you have
views based on the group they belong to, not per individual user.

From: Silvia <silvia...@gmail.com>
To: mybatis-user <mybati...@googlegroups.com>
Date: 07/14/2011 04:19 PM
Subject: Re: Adding where clauses from database.
Sent by: mybati...@googlegroups.com

[Silvia]

I think that would not be very appropriate since each USER can have
several PROFILES and each PROFILE several WHERE CLAUSES associated
with one table, do you understand me?

On Jul 14, 1:08 pm, Mario Ds Briggs <mario.bri...@in.ibm.com> wrote:
> i am assuming that while u might have 1000's individual users, they can be
> still be logically grouped into a much smaller # of groups. Then you have
> views based on the group they belong to, not per individual user.
>

[Eduardo]

Terényi, that sounds interesting, did you open a issue with that
change?

[Terényi Balázs]

I have a list of issues, but didn't had time to open them yet.

2011 July 15 Friday 10:46:28 Eduardo:

[Eduardo]

Ok. It is time to add new features for MyBatis 3.1, and I like the
idea to hold a SessionFactory scope context, visible from all
statements so common parameters are available for all them without the
need to provide them inside statement parameters.

So If you have the chance, please fill an issue with your code.

Thanks in advance.

[Silvia]

Hi again,

After researching for a while the differents options I have, I have accepted Terényi Balázs suggestion and I am overwritting myBatis classes. I know it's tricky and a little bit "dangerous" but I think it is the only clean way I have.

As I am also using the mybatis-spring-1.0.1-jar, I am also overwritting there SqlSessionTemplate class.

I'll let you know when my development is successfully finished.

Thank you very much to all of you for your suggestions.

Regards.

[sylvia]

Hello again,

As I promised, I am back to explain here the solution we have achieved for
future readers.

Thanks to Balázs Terényi's aproach, I start debugging myBatis classes so as
to determine the "interruption points" where I can add my dynamic clauses.

Specifically, in the methods of the DefaultSqlSession class, you can get the
current statement which is being processed. Once here, the mapper statement
can be an instance of ProviderSqlSource class or DynamicSqlSource class.

If the query is being made through a provider, you can add your own
parameters to the bbject parameter and then recover them in the provider
itself and add them to a SqlBuilder.WHERE.

If it is being made through a SQL statement directly, in the
DynamicSqlSource class you have access to the SQL sentence and modify it at
this point:

SqlSource sqlSource =
sqlSourceParser.parse(*transformer.checkSecurity*(context.getSql()),
parameterType);

Thank you very much to all of you who helped me and specially to Terényi.

Kind regards,

Silvia.

--
View this message in context: http://mybatis-user.963551.n3.nabble.com/Adding-where-clauses-from-database-tp3162632p3341715.html
Sent from the mybatis-user mailing list archive at Nabble.com.

[Mike Fotiou]

Did this change every make it into Mybatis 3.1.X??

Mike

[Eduardo Macarron]

Hii Mike. No, I am afraid that there was no progress with that idea.

2012/5/7 Mike Fotiou <mof...@gmail.com>: