SQuirreL security plugin

[Guillaume.Herault]

Hi,

I am looking for a SQL tool we could give to our developpers in order to
access safely our production databases.
Because our databases contain confidential data, we would like to trace or
restrict the actions the developers could perform. This is a requirement
of our Security Dept (I am working in a bank, security is strict). The
tool would be installed on dedicated workstations with restricted
permissions (no administrative rights).

One possibility is to use Squirrel, and develop our plugin to meet our
requirements.
Basicaly, the plugin should :
- prevent the user to export data by disabling some menu actions
- trace in a log-file all sql queries that are executed (not only those
sent by clicking on the "Run SQL" button, but also when the user edits the
results returned in the "Results" tab)


Can you confirm me that it is possible to develop such a plugin
(technically speaking) ?
I have no doubt that the first point (disabling a menu action) could be
easily developed. But about the second point (trace in a logfile), does
IPlugin have a callback method that I could implement and that would be
called before any SQL command is sent through jdbc ?



Thanks for your answer
Guillaume



************************ DISCLAIMER ************************
This message is intended only for use by the person
to whom it is addressed. It may contain information
that is privileged and confidential. Its content does
not constitute a formal commitment by Lombard
Odier Darier Hentsch Group and any of its affiliates.
If you are not the intended recipient of this message,
kindly notify the sender immediately and destroy this
message. Thank You.
*****************************************************************
-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
_______________________________________________
Squirrel-sql-develop mailing list
Squirrel-sql-develop@...
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-develop

[manningr]

On Mon, Jul 14, 2008 at 6:32 AM, <Guillaume.Herault@...> wrote:

> Hi,
>
> I am looking for a SQL tool we could give to our developpers in order to
> access safely our production databases.
> Because our databases contain confidential data, we would like to trace or
> restrict the actions the developers could perform. This is a requirement
> of our Security Dept (I am working in a bank, security is strict). The
> tool would be installed on dedicated workstations with restricted
> permissions (no administrative rights).
>
> One possibility is to use Squirrel, and develop our plugin to meet our
> requirements.
> Basicaly, the plugin should :
> - prevent the user to export data by disabling some menu actions
> - trace in a log-file all sql queries that are executed (not only those
> sent by clicking on the "Run SQL" button, but also when the user edits the
> results returned in the "Results" tab)
>
>
> Can you confirm me that it is possible to develop such a plugin
> (technically speaking) ?
> I have no doubt that the first point (disabling a menu action) could be
> easily developed. But about the second point (trace in a logfile), does
> IPlugin have a callback method that I could implement and that would be
> called before any SQL command is sent through jdbc ?

Our current table editing component (DataSetUpdateableTableModelImpl)
doesn't allow
you to register a listener for it's SQL executing actions.  We would need to
add support for
that.  However, for the SQL tab, your plugin can register a
ISQLExecutionListener  and
get a callback prior to each statement to support auditing.  Of course,
plugins can be
disabled using the plugin summary dialog - if that's a show stopper, that
menu item could
be disabled as well.

Rob
-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
_______________________________________________
Squirrel-sql-develop mailing list
Squirrel-sql-develop@...
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-develop

[Gerd Wagner-3]

Here is a different approach that we use not for security reasons but merely for
logging:

Create a wrapper JDBC driver that wraps your original driver. Then register this
driver instead of the original driver within SQuirreL. Any call that SQuirreL
sends to the database will then pass through the wrapper. Here you'd be in
control of everything. If you don't want a call to execute simply make the
wrapper throw an SQLException. SQuirreL should handle such exceptions the way it
does any other database exceptions.

To create such a driver wrapper can be simplified a lot using JDKs Proxy API.
See http://java.sun.com/j2se/1.5.0/docs/api/java/lang/reflect/Proxy.html

Hope this helps.

Gerd

Robert Manning wrote:

> On Mon, Jul 14, 2008 at 6:32 AM, <Guillaume.Herault@...> wrote:
>
>> Hi,
>>
>> I am looking for a SQL tool we could give to our developpers in order to
>> access safely our production databases.
>> Because our databases contain confidential data, we would like to trace or
>> restrict the actions the developers could perform. This is a requirement
>> of our Security Dept (I am working in a bank, security is strict). The
>> tool would be installed on dedicated workstations with restricted
>> permissions (no administrative rights).
>>
>> One possibility is to use Squirrel, and develop our plugin to meet our
>> requirements.
>> Basicaly, the plugin should :
>> - prevent the user to export data by disabling some menu actions
>> - trace in a log-file all sql queries that are executed (not only those
>> sent by clicking on the "Run SQL" button, but also when the user edits the
>> results returned in the "Results" tab)
>>
>>
>> Can you confirm me that it is possible to develop such a plugin
>> (technically speaking) ?
>> I have no doubt that the first point (disabling a menu action) could be
>> easily developed. But about the second point (trace in a logfile), does
>> IPlugin have a callback method that I could implement and that would be
>> called before any SQL command is sent through jdbc ?
>
>
> Our current table editing component (DataSetUpdateableTableModelImpl)
> doesn't allow
> you to register a listener for it's SQL executing actions.  We would need to
> add support for
> that.  However, for the SQL tab, your plugin can register a
> ISQLExecutionListener  and
> get a callback prior to each statement to support auditing.  Of course,
> plugins can be
> disabled using the plugin summary dialog - if that's a show stopper, that
> menu item could
> be disabled as well.
>
> Rob
> -------------------------------------------------------------------------
> This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
> Build the coolest Linux based applications with Moblin SDK & win great prizes
> Grand prize is a trip for two to an Open Source event anywhere in the world
> http://moblin-contest.org/redirect.php?banner_id=100&url=/
> _______________________________________________
> Squirrel-sql-develop mailing list
> Squirrel-sql-develop@...
> https://lists.sourceforge.net/lists/listinfo/squirrel-sql-develop
>

-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
_______________________________________________
Squirrel-sql-develop mailing list
Squirrel-sql-develop@...
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-develop

[manningr]

On Wed, Jul 23, 2008 at 5:37 AM, Gerd Wagner <gerdhwagner@...>
wrote:

> Here is a different approach that we use not for security reasons but
> merely for logging:
>
> Create a wrapper JDBC driver that wraps your original driver. Then register
> this driver instead of the original driver within SQuirreL. Any call that
> SQuirreL sends to the database will then pass through the wrapper. Here
> you'd be in control of everything. If you don't want a call to execute
> simply make the wrapper throw an SQLException. SQuirreL should handle such
> exceptions the way it does any other database exceptions.
>
> To create such a driver wrapper can be simplified a lot using JDKs Proxy
> API. See
> http://java.sun.com/j2se/1.5.0/docs/api/java/lang/reflect/Proxy.html
>

This has the benefit that it is portable to other JDBC front-ends and you
can use the current version of SQuirreL unmodified.  Nice!

Rob
-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
_______________________________________________
Squirrel-sql-develop mailing list
Squirrel-sql-develop@...
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-develop

[Meier, Stefan]

> > Here is a different approach that we use not for security reasons
but
> > merely for logging:
> >
> > Create a wrapper JDBC driver that wraps your original driver. Then
> > register this driver instead of the original driver within SQuirreL.

> > Any call that SQuirreL sends to the database will then pass through
> > the wrapper. Here you'd be in control of everything. If you don't
want
> > a call to execute simply make the wrapper throw an SQLException.
> > SQuirreL should handle such exceptions the way it does any other
database exceptions.
> >
> > To create such a driver wrapper can be simplified a lot using JDKs
> > Proxy API. See
> > http://java.sun.com/j2se/1.5.0/docs/api/java/lang/reflect/Proxy.html
> >
>
> This has the benefit that it is portable to other JDBC front-ends and
you can use the current version of SQuirreL unmodified.  Nice!

But then you must also prevent the user from changing the driver
configuration in SQuirreL to using the original JDBC driver again or
creating a new alias with this driver. They could also just use a
different SQL-Client that you can download and use without having to
install anythin as Administrator (e.g. SQuirrel SQL-Client). It seems to
me that the "security" features suggested so far all rely on the end
users being not very tech-savvy so they don't know how to deal with JDBC
drivers and stuff.
I think it would be much more secure to use some kind of middleware and
not rely on restrictions built or configured into the client. Perhaps a
SQL-proxy would be sufficient to achieve this (if there exists one for
your RDBMS).

Stefan


-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
_______________________________________________
Squirrel-sql-develop mailing list
Squirrel-sql-develop@...
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-develop

[Gerd Wagner-3]

Meier, Stefan wrote:

>>> Here is a different approach that we use not for security reasons
> but
>>> merely for logging:
>>>
>>> Create a wrapper JDBC driver that wraps your original driver. Then
>>> register this driver instead of the original driver within SQuirreL.
>
>>> Any call that SQuirreL sends to the database will then pass through
>>> the wrapper. Here you'd be in control of everything. If you don't
> want
>>> a call to execute simply make the wrapper throw an SQLException.
>>> SQuirreL should handle such exceptions the way it does any other
> database exceptions.
>>> To create such a driver wrapper can be simplified a lot using JDKs
>>> Proxy API. See
>>> http://java.sun.com/j2se/1.5.0/docs/api/java/lang/reflect/Proxy.html
>>>
>> This has the benefit that it is portable to other JDBC front-ends and
> you can use the current version of SQuirreL unmodified.  Nice!
>
> But then you must also prevent the user from changing the driver
> configuration in SQuirreL to using the original JDBC driver again or
> creating a new alias with this driver. They could also just use a
> different SQL-Client that you can download and use without having to
> install anythin as Administrator (e.g. SQuirrel SQL-Client). It seems to
> me that the "security" features suggested so far all rely on the end
> users being not very tech-savvy so they don't know how to deal with JDBC
> drivers and stuff.
> I think it would be much more secure to use some kind of middleware and
> not rely on restrictions built or configured into the client. Perhaps a
> SQL-proxy would be sufficient to achieve this (if there exists one for
> your RDBMS).

Good point. But perhaps one could equip the wrapper with some log in information
which one would lack that is using the plain driver. Simplest thing could be URL
and/or password hard coded in the wrapper. A more sophisticated and surely more
secure way could be to let the wrapper work with the kind of middleware you suggest.

If an SQL-Proxy exists for the RDBMS in question than sure all security issues
could be handled there. On the Client/SQuirreL side I'm pretty sure the
SQL-Proxy would just behave like a wrapper.

Gerd

-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
_______________________________________________
Squirrel-sql-develop mailing list
Squirrel-sql-develop@...
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-develop