Re: Modifying SQL query for security?? What is your opinion?

sandor.laza_at_opcw.nl wrote:
>
> In some DBMS products additional user defined access control rules
> can be implemented using a technique called "query modification".
>
> Here any SQL query submitted by an application to the DBMS is
> passed through a trusted filter, which modifies the SQL statement
> (and eventually the result of the query) in accordance with user
> defined rules.
>
> In this way access control rules based on the content of specific
> database fields can be implemented easily."
>
> I have no idea, from where did they get this, but I have serious
> doubts about the last sentence.
>

What I would do:

• Create a separate security database modelling all your data driven security requirements and linking specific data element/data value combinations to your users.
• Replace all your tables with views that reference the current user id (suser_name() in sybase) and link that to the security database and the original table.

This works for all systems: new, legacy & third party. It even works for ad-hoc access using other front-end tools.

This works since most DBMS's allow the use of a view by an end user even if the end-user is not granted access to the undelying tables.

You have to make sure your views remain updatable. You can do this by using syntax like:

select ... from YourTable where YourSecuredField in (select FieldValue from UserFieldValues where FieldName = 'YourSecuredField' and UserName = suser_name())

View handle Select and Update security if you use the 'with check option' clause. Assuming the Update Security can be more complex than taht, you can also implement triggers to do update security.

The example I gave here is very simple. I implemented a similar sulution using a security database with 23 entities!

Groetjes,

                    __\/__
                .  / ^  _ \  .
                |\| (o)(o) |/|
#------------.OOOo----oo----oOOO.------------#
#                                            #
#     Kees Stolker                           #
#     TIP Trailer Rental                     #
#     Amsteldijk 166                         #
#     1079 LH Amsterdam                      #
#     The Netherlands                        #
#                                            #
#     tel: +31 20 504 1763                   #
#     fax: +31 20 504 1701                   #
#     e-mail: Kees.Stolker_at_tipeur.ge.com     #
#     voice-mail: +31 20 504 2222            #
#_______________________Oooo.________________#
                 .oooO  (   )
                 (   )   ) /
                  \ (   (_/
                   \_)