rich client db security in an untrusted environment

[Tomi N/A <he...@gmail.com>]

Hi everyone,

I am working on the architecture of a future system and am stuck at security.
The stack is GNU/linux, postgresql, cayenne and tomcat.
Alongside the web application, I will have a WebStart-capable swing
client which accesses the (remote) database.
I don't want the db username or password  ever to be known outside of
the database server.
The only solution I came up with is using ROP (don't know if any of
it's limitations are going to hurt): the user enters his/her
_application_ credentials which are sent to tomcat, tomcat verifies
the credentials using JDBCRealm (reading from the application
database) and allows the client to use the cayenne web service.
Theoretically, a malicious user might write his own swing app and log
in using a valid username/password combination to do whatever he likes
in the database (having the complete db mapping at his disposal). This
worries me, but it might be good enough for now (as the attacker
wouldn't be able to compromise the database without valid
credentials).
In this case I have complete autonomy deciding how we're going to
build the application, but what would I do if I couldn't use ROP (say,
no servlet container available)? Other possibilities?

I very much welcome opinions and comments.
Regards,
Tomislav

Re: rich client db security in an untrusted environment

[Tomi N/A <he...@gmail.com>]

Thank you Kevin, Andrus and everyone who bothered to read my post.
I gather from your responses that my assumptions about my options with
regard to security were correct (God I write complex sentences!).
A colleague I talked to yesterday led me to the same conclusion.

If I get the opportunity to start the project, I'll probably go the
ROP way and see where that leads me.
I have other wild ideas on my mind (like sequoia replication)...gonna
be an interesting project. :)

Cheers,
Tomislav

Re: rich client db security in an untrusted environment

[Andrus Adamchik <an...@objectstyle.org>]

I think Tomislav's assessment of the ROP security is pretty accurate  
in a sense that by default ROP has strong authentication mechanism and  
weak authorization mechanism. So any authenticated user has full  
select and modify access to all mapped entities. Although with some  
planning and a bit of effort, a user can implement a reasonable  
security policy to prevent authenticated users against elevating their  
permission levels... I am thinking along these lines:

* Wrap and/or subclass ClientServerChannel to apply security policies  
to all incoming queries / commit requests.
* Only allow NamedQueries and RelationshipQueries to be passed via ROP  
(most certainly no SQLTemplates or EJBQLQueries).
* For RelationshipQuery check that source ObjectId is in the server- 
side DataContext cache already.
* Disable onSync calls if the application is read-only.

(I wanted to implement a basic security template in Cayenne with  
configurable checks like I described above, just didn't have enough  
motivation to do it yet).


>> In this case I have complete autonomy deciding how we're going to
>> build the application, but what would I do if I couldn't use ROP  
>> (say,
>> no servlet container available)?

Servlet container these days is just a bunch of Java classes. It's not  
like you need to install WebSphere or anything. Take a look at Jetty  
for example. You can instantiate a servlet container in your main  
method with a few lines of code.

>> Other possibilities?

Like Kevin said, any web services/RPC technology would work. You can  
make them very secure, by defining a full set of allowed operations  
via a set of "business" interfaces (and then use something like SOAP  
to expose them). But then you'd lose all the ObjectContext niceties.  
 From the original message looks like there is an understanding of  
this tradeoff.

Andrus


On Apr 26, 2008, at 4:58 PM, Kevin Menard wrote:

> You could always use some other RPC mechanism.  RMI or XML-RPC / SOAP.
> These would serve as another wrapper that would keep all DB  
> operations on
> the server for sure.  At the end of the day though, you'd really  
> have to
> consider what you're actually gaining over ROP.  ROP does have its  
> flaws
> (open up JIRAs as you come across them please), but it doesn't  
> expose your
> DB as a going concern -- only ObjEntities and ObjAttributes are  
> accessible.
> It's a Web service that you can apply all of your JEE security  
> practices to.
>
> -- 
> Kevin
>
> On 4/25/08 8:29 AM, "Tomi N/A" <he...@gmail.com> wrote:
>
>> Hi everyone,
>>
>> I am working on the architecture of a future system and am stuck at  
>> security.
>> The stack is GNU/linux, postgresql, cayenne and tomcat.
>> Alongside the web application, I will have a WebStart-capable swing
>> client which accesses the (remote) database.
>> I don't want the db username or password  ever to be known outside of
>> the database server.
>> The only solution I came up with is using ROP (don't know if any of
>> it's limitations are going to hurt): the user enters his/her
>> _application_ credentials which are sent to tomcat, tomcat verifies
>> the credentials using JDBCRealm (reading from the application
>> database) and allows the client to use the cayenne web service.
>> Theoretically, a malicious user might write his own swing app and log
>> in using a valid username/password combination to do whatever he  
>> likes
>> in the database (having the complete db mapping at his disposal).  
>> This
>> worries me, but it might be good enough for now (as the attacker
>> wouldn't be able to compromise the database without valid
>> credentials).
>> In this case I have complete autonomy deciding how we're going to
>> build the application, but what would I do if I couldn't use ROP  
>> (say,
>> no servlet container available)? Other possibilities?
>>
>> I very much welcome opinions and comments.
>> Regards,
>> Tomislav
>
>

Re: rich client db security in an untrusted environment

[Kevin Menard <km...@servprise.com>]

You could always use some other RPC mechanism.  RMI or XML-RPC / SOAP.
These would serve as another wrapper that would keep all DB operations on
the server for sure.  At the end of the day though, you'd really have to
consider what you're actually gaining over ROP.  ROP does have its flaws
(open up JIRAs as you come across them please), but it doesn't expose your
DB as a going concern -- only ObjEntities and ObjAttributes are accessible.
It's a Web service that you can apply all of your JEE security practices to.

-- 
Kevin

On 4/25/08 8:29 AM, "Tomi N/A" <he...@gmail.com> wrote:

> Hi everyone,
> 
> I am working on the architecture of a future system and am stuck at security.
> The stack is GNU/linux, postgresql, cayenne and tomcat.
> Alongside the web application, I will have a WebStart-capable swing
> client which accesses the (remote) database.
> I don't want the db username or password  ever to be known outside of
> the database server.
> The only solution I came up with is using ROP (don't know if any of
> it's limitations are going to hurt): the user enters his/her
> _application_ credentials which are sent to tomcat, tomcat verifies
> the credentials using JDBCRealm (reading from the application
> database) and allows the client to use the cayenne web service.
> Theoretically, a malicious user might write his own swing app and log
> in using a valid username/password combination to do whatever he likes
> in the database (having the complete db mapping at his disposal). This
> worries me, but it might be good enough for now (as the attacker
> wouldn't be able to compromise the database without valid
> credentials).
> In this case I have complete autonomy deciding how we're going to
> build the application, but what would I do if I couldn't use ROP (say,
> no servlet container available)? Other possibilities?
> 
> I very much welcome opinions and comments.
> Regards,
> Tomislav