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

Sandor Laza wrote:
>
> Hi guys,
>
> I need some advice:
>
> I company prepared a security study for us in which they
> advised not to use trusted RDBMS systems (like trusted Oracle
> or Trusted Ingres), but develop an application which capture all the
> SQL querys sent to the server modify them according several security
> rules (for example extend the where clouse somehow) and pass the
> modified query to the RDBMS engine.
>
> Have you ever seen or heard about this kind of solution implemented?
> What do you think, it is feasible?
>
> My personal opinion is, that it can be implemented, but the
> implementation means at least the reimplementation of the SQL
> interpreter of the given RDBMS. Or not?
>
> Any kind of help would be highly appreciated,
>
> P.S. If you know a company which is able to prepare
> a feasibility study on this topic, please contact me!
>
> Sandor Laza
> Security officer
>
> OPCW
> Tel: 31-70 3761700
> Fax: 31-70 3600944
> E-Mail: sandor.laza_at_opcw.nl or slaza_at_worldonline.nl

Interestingly enough "query modification" is actually available in Ingres, if you are willing to use QUEL (Ingres's offering before the days of ANSI SQL but still supported). ANSI SQL has a less flexible permission definition strategy than SQL.

Here is the syntax of the SQL "grant" statement:

        GRANT {priv{,priv} | ALL [PRIVILEGES]}
        ON [TABLE] tablename|viewname {,tablename|viewname}
        TO username{,username} | PUBLIC

Notes:

If you grant permissions on a view, you do not need permissions on the underlying tables. However, update or delete permissions may not be permitted on views involving more than one base table.

Here is the syntax of the "define permit" statement

    DEFINE PERMIT oplist ON|OF|TO var [(columnlist)]

                    TO name [AT term] [FROM time TO time]
                    [ON day TO day] [WHERE qual]

For example you might use the following;

range of e1 is employee_master
range of e2 is vacation_history
define permit retrieve,delete on e2 to all where e2.employee_number = e1.employee_number and e1.ingres_user_name = dbmsinfo("username")

This allows employees to retrieve or delete their own vacation history records. Notice how we used a join condition to join two tables together.

Then, for example, if the query received by the Ingres server was "retrieve(employee_vacation_history.all)", then "query modification" would take place, and only a subset of records would be returned.

"Query modification" offers much granularity and flexibility than SQL views and grants. From a security perspective, both mechanisms are equally rigourous since a restriction once enforced cannot be circumvented.

You should retrieve Document US-13270 titled "Using ESQL and EQUEL Simultaneously in Release 6 3GL Applications" in the Ingres Advisor database. Also Document US-14418 titled "Permits with WHERE Clauses vs. Grants on Views".

Standard disclaimers please!

Ignatius. Received on Tue Nov 19 1996 - 00:00:00 CET