[OT] Using connection pools - security

[Tamas Szabo <sz...@gmail.com>]

Hi,

I have a question regarding connection pools and DB user rights.

When you use connection pool objects you specify an user who will be the
owner of all the Connection objects in the pool.
I guess that the most usual case is to specify an user which has the DB
permissions to execute all the DB operations that needs to be executed
from the application. You also create users, add them to roles.
The users login to the application and authorization is configured for
the parts of the application, but underneath all the users use
connections with rights to execute all the DB operations.

I don't want to create users and roles in my webapp. I would like to
allow the user to enter a real DB/password which I want to use to make
the DB connection so the rights of the user to make a given DB operation
will be configured in the DB.

Is there a support for this or am I on my own to implement a solution
for it?

Thank you,

Tamas



---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@struts.apache.org
For additional commands, e-mail: user-help@struts.apache.org

Re: [OT] Using connection pools - security

[Michael Rasmussen <ra...@gmail.com>]

You could also just use one pool per user role and look up the db user
and the role that user is in, then pull the connection from the pool
that you have for that role.  If your DB doesn't support roles for
users you could create a user/role table.   I think it might be
overkill to create an actual user in your database for each user in
your application.  If that is your requirement though then that is
your requirement.
Michael

On 7/6/05, Craig McClanahan <cr...@gmail.com> wrote:
> A couple of considerations are relevant here:
> 
> * Is a single user ever going to really use more than one
>   connection from the pool?  If not, wouldn't it be better
>   to create a *connection* for them, instead of a *pool*?
>   (Note in particular that transactions don't cross connection
>   boundaries, so if you want "all or nothing" commits, you need
>   to do them on a single connection anyway).
> 
> * Creating even one connection per user is going to require
>   more active connections to your database than sharing the
>   same pool for all users.  In the cases where you are using
>   the DB to restrict access to things (by username), one approach
>   would be to use something like Oracle's database roles instead.
>   That way, the code that retrieves a generic connection from an
>   application wide pool could also do a SET ROLE (or whatever the
>   appropriate command is -- it's been a few years since I was this
>   deep into databases) that established the restrictions for the
>   currently logged on user.  That way, you could continue to share
>   a single pool across all your users, and minimize the number of
>   connections your DB has to support to just the number of
>   currently active requests.
> 
> Of course, the latter solution depends on your database having the
> capability to do this -- I know Oracle does, but don't know about
> others.
> 
> Craig
> 
> 
> On 7/6/05, Tamas Szabo <sz...@gmail.com> wrote:
> > I guess I could use any implementation of a connection pool for a simple
> > implementation. I just need to use a connection pool for every user (of
> > course using a smaller number of connections than usually).
> > The connection pool object will be created only when the user logs in
> > (if it doesn't exist already). Maybe it would be nice however to return
> > the first connection quickly and then create the others in a background
> > thread.
> > Since there could be a lot of users I should have a max-connection-pools
> > config-param. When is reached I will remove the connection pools of
> > user's who haven't used the object recently.
> >
> > But if there is no implementation of this already, probably this is a
> > not so good idea after all :-).
> > That's why I asked on the list before I want to use it.
> >
> > Tamas
> >
> >
> >
> >
> > On Wed, 2005-07-06 at 21:40 -0600, Larry Meadors wrote:
> > > I do not know of any, but it is possible to create one using something
> > > like proxool.
> > >
> > > Larry
> > >
> > > On 7/6/05, Tamas Szabo <sz...@gmail.com> wrote:
> > > > Hi,
> > > >
> > > > I have a question regarding connection pools and DB user rights.
> > > >
> > > > When you use connection pool objects you specify an user who will be the
> > > > owner of all the Connection objects in the pool.
> > > > I guess that the most usual case is to specify an user which has the DB
> > > > permissions to execute all the DB operations that needs to be executed
> > > > from the application. You also create users, add them to roles.
> > > > The users login to the application and authorization is configured for
> > > > the parts of the application, but underneath all the users use
> > > > connections with rights to execute all the DB operations.
> > > >
> > > > I don't want to create users and roles in my webapp. I would like to
> > > > allow the user to enter a real DB/password which I want to use to make
> > > > the DB connection so the rights of the user to make a given DB operation
> > > > will be configured in the DB.
> > > >
> > > > Is there a support for this or am I on my own to implement a solution
> > > > for it?
> > > >
> > > > Thank you,
> > > >
> > > > Tamas
> > > >
> > > >
> > > >
> > > > ---------------------------------------------------------------------
> > > > To unsubscribe, e-mail: user-unsubscribe@struts.apache.org
> > > > For additional commands, e-mail: user-help@struts.apache.org
> > > >
> > > >
> > >
> > > ---------------------------------------------------------------------
> > > To unsubscribe, e-mail: user-unsubscribe@struts.apache.org
> > > For additional commands, e-mail: user-help@struts.apache.org
> > >
> >
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: user-unsubscribe@struts.apache.org
> > For additional commands, e-mail: user-help@struts.apache.org
> >
> >
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@struts.apache.org
> For additional commands, e-mail: user-help@struts.apache.org
> 
>

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@struts.apache.org
For additional commands, e-mail: user-help@struts.apache.org

Re: [OT] Using connection pools - security

[Craig McClanahan <cr...@gmail.com>]

A couple of considerations are relevant here:

* Is a single user ever going to really use more than one
  connection from the pool?  If not, wouldn't it be better
  to create a *connection* for them, instead of a *pool*?
  (Note in particular that transactions don't cross connection
  boundaries, so if you want "all or nothing" commits, you need
  to do them on a single connection anyway).

* Creating even one connection per user is going to require
  more active connections to your database than sharing the
  same pool for all users.  In the cases where you are using
  the DB to restrict access to things (by username), one approach
  would be to use something like Oracle's database roles instead.
  That way, the code that retrieves a generic connection from an
  application wide pool could also do a SET ROLE (or whatever the
  appropriate command is -- it's been a few years since I was this
  deep into databases) that established the restrictions for the
  currently logged on user.  That way, you could continue to share
  a single pool across all your users, and minimize the number of
  connections your DB has to support to just the number of
  currently active requests.

Of course, the latter solution depends on your database having the
capability to do this -- I know Oracle does, but don't know about
others.

Craig


On 7/6/05, Tamas Szabo <sz...@gmail.com> wrote:
> I guess I could use any implementation of a connection pool for a simple
> implementation. I just need to use a connection pool for every user (of
> course using a smaller number of connections than usually).
> The connection pool object will be created only when the user logs in
> (if it doesn't exist already). Maybe it would be nice however to return
> the first connection quickly and then create the others in a background
> thread.
> Since there could be a lot of users I should have a max-connection-pools
> config-param. When is reached I will remove the connection pools of
> user's who haven't used the object recently.
> 
> But if there is no implementation of this already, probably this is a
> not so good idea after all :-).
> That's why I asked on the list before I want to use it.
> 
> Tamas
> 
> 
> 
> 
> On Wed, 2005-07-06 at 21:40 -0600, Larry Meadors wrote:
> > I do not know of any, but it is possible to create one using something
> > like proxool.
> >
> > Larry
> >
> > On 7/6/05, Tamas Szabo <sz...@gmail.com> wrote:
> > > Hi,
> > >
> > > I have a question regarding connection pools and DB user rights.
> > >
> > > When you use connection pool objects you specify an user who will be the
> > > owner of all the Connection objects in the pool.
> > > I guess that the most usual case is to specify an user which has the DB
> > > permissions to execute all the DB operations that needs to be executed
> > > from the application. You also create users, add them to roles.
> > > The users login to the application and authorization is configured for
> > > the parts of the application, but underneath all the users use
> > > connections with rights to execute all the DB operations.
> > >
> > > I don't want to create users and roles in my webapp. I would like to
> > > allow the user to enter a real DB/password which I want to use to make
> > > the DB connection so the rights of the user to make a given DB operation
> > > will be configured in the DB.
> > >
> > > Is there a support for this or am I on my own to implement a solution
> > > for it?
> > >
> > > Thank you,
> > >
> > > Tamas
> > >
> > >
> > >
> > > ---------------------------------------------------------------------
> > > To unsubscribe, e-mail: user-unsubscribe@struts.apache.org
> > > For additional commands, e-mail: user-help@struts.apache.org
> > >
> > >
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: user-unsubscribe@struts.apache.org
> > For additional commands, e-mail: user-help@struts.apache.org
> >
> 
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@struts.apache.org
> For additional commands, e-mail: user-help@struts.apache.org
> 
>

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@struts.apache.org
For additional commands, e-mail: user-help@struts.apache.org

Re: [OT] Using connection pools - security

[Tamas Szabo <sz...@gmail.com>]

I guess I could use any implementation of a connection pool for a simple
implementation. I just need to use a connection pool for every user (of
course using a smaller number of connections than usually).
The connection pool object will be created only when the user logs in
(if it doesn't exist already). Maybe it would be nice however to return
the first connection quickly and then create the others in a background
thread. 
Since there could be a lot of users I should have a max-connection-pools
config-param. When is reached I will remove the connection pools of
user's who haven't used the object recently.

But if there is no implementation of this already, probably this is a
not so good idea after all :-). 
That's why I asked on the list before I want to use it.

Tamas




On Wed, 2005-07-06 at 21:40 -0600, Larry Meadors wrote:
> I do not know of any, but it is possible to create one using something
> like proxool.
> 
> Larry
> 
> On 7/6/05, Tamas Szabo <sz...@gmail.com> wrote:
> > Hi,
> > 
> > I have a question regarding connection pools and DB user rights.
> > 
> > When you use connection pool objects you specify an user who will be the
> > owner of all the Connection objects in the pool.
> > I guess that the most usual case is to specify an user which has the DB
> > permissions to execute all the DB operations that needs to be executed
> > from the application. You also create users, add them to roles.
> > The users login to the application and authorization is configured for
> > the parts of the application, but underneath all the users use
> > connections with rights to execute all the DB operations.
> > 
> > I don't want to create users and roles in my webapp. I would like to
> > allow the user to enter a real DB/password which I want to use to make
> > the DB connection so the rights of the user to make a given DB operation
> > will be configured in the DB.
> > 
> > Is there a support for this or am I on my own to implement a solution
> > for it?
> > 
> > Thank you,
> > 
> > Tamas
> > 
> > 
> > 
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: user-unsubscribe@struts.apache.org
> > For additional commands, e-mail: user-help@struts.apache.org
> > 
> >
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@struts.apache.org
> For additional commands, e-mail: user-help@struts.apache.org
> 


---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@struts.apache.org
For additional commands, e-mail: user-help@struts.apache.org

Re: [OT] Using connection pools - security

[Larry Meadors <la...@gmail.com>]

I do not know of any, but it is possible to create one using something
like proxool.

Larry

On 7/6/05, Tamas Szabo <sz...@gmail.com> wrote:
> Hi,
> 
> I have a question regarding connection pools and DB user rights.
> 
> When you use connection pool objects you specify an user who will be the
> owner of all the Connection objects in the pool.
> I guess that the most usual case is to specify an user which has the DB
> permissions to execute all the DB operations that needs to be executed
> from the application. You also create users, add them to roles.
> The users login to the application and authorization is configured for
> the parts of the application, but underneath all the users use
> connections with rights to execute all the DB operations.
> 
> I don't want to create users and roles in my webapp. I would like to
> allow the user to enter a real DB/password which I want to use to make
> the DB connection so the rights of the user to make a given DB operation
> will be configured in the DB.
> 
> Is there a support for this or am I on my own to implement a solution
> for it?
> 
> Thank you,
> 
> Tamas
> 
> 
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@struts.apache.org
> For additional commands, e-mail: user-help@struts.apache.org
> 
>

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@struts.apache.org
For additional commands, e-mail: user-help@struts.apache.org