[DWS-5] Configure a basic, LucidDB catalog based method for authenticating users

[dynamobi-build]

[reporter="ngoodman", created="Fri, 5 Feb 2010 08:24:50 -0800", resolved="Wed, 11 Aug 2010 21:54:50 -0700"]

Enunciate uses Spring security - we need to create a security provider for our Web Services.

I think there's a few ways we can do this, but the first and immediate need I think is to enable a basic HTTP header based auth system.

[dynamobi-build]

[author="ngoodman", created="Tue, 20 Apr 2010 19:11:20 -0700"]

Ray / Nick IM session

Nicholas Goodman
6:44
We need to add "Sessions" + "Security" to our web services
first... security

Ray
6:45
yes login part

Nicholas Goodman
6:45
when the user sees this screen, they will need to logon with a USER/PASSWORD that is valid in the database.

Ray
6:45
yes.

Nicholas Goodman
6:45
when they log in, they will be logged in as that user
right now, we log the user in as "sa"

Ray
6:46
ok

Nicholas Goodman
6:46
or whatever is configured in the .properties file
we need to remove the "Database"
and server
just username/password

Ray
6:47
ok

Nicholas Goodman
6:47
if we log in the user as the actual user, then ALL the service calls will be done as the user
the list of tables/views/etc

Ray
6:48
ok

Nicholas Goodman
6:48
ok... now - more important

Ray
6:48
every user has own schema right

Nicholas Goodman
6:48
yes, but the user may have access to other schemas

Ray
6:48
ok
please go on

Nicholas Goodman
6:49
now... this is the "session" piece
every session that logs in via FlexSQLAdmin or via another way in Web Services
needs to have a SQL Connection per session
if there are 5 people connect to the server via Enunciate web services - there are 5 HTTP sessions (or Spring Security sessions)
and each one will have a session.
that will be the same for a user across calls.
I should be able to execute statements on the session.

Ray
6:52
Can we stop here a little time ?

Nicholas Goodman
6:52
yes

Ray
6:52
can you offer me an example to explain it
just words no need to use code to present it
now we have two users Nick and Ray
you and I log on it now.
so next whats happenning next

Nicholas Goodman
6:59
just a second....

Ray
7:00
Ok

Nicholas Goodman
7:04
ok... I am back
7:04 ok
if two of us log in
Ray and Nic
there are two sessions (either Spring/Enunciate I'm not sure which it will be)
1 for Ray, and 1 for Nick
each session will have their own JDBC connection that will stay open
so... web application will have two sessions
and the database will have two connections

Ray
7:07
yes

Nicholas Goodman
7:08
I can make session based statements that will
affect the session. future SELECT statements will be affected by this.

[dynamobi-build]

[author="ngoodman", created="Tue, 20 Apr 2010 19:14:36 -0700"]

also... Since FlexSQLAdmin will not be the only client, the session method on the server needs to not be tied only to it. Look into "Token" approaches for Spring security, and also HTTP auth headers.

[dynamobi-build]

[author="rzhang", created="Tue, 27 Apr 2010 05:02:22 -0700"]

I have an idea for it. Please check out.

when login page pops up, we will drop username/password , and then press button for auth.

When server side receives this kind of auth request, a service will create a jdbc connection first using username/password from client side.
If auth is passed, we will get an available connection. here we will do two steps below:
1. we create a hashmap key is a token generated by a token generator(keep the token unique),
the value is the related jdbc connection.
2. let the token as an response to client for all kinds of sql operations. ie, if login is successful,
client executes any sql operation using this token. no more need username/password.

Issue:
3. memory issue. if we have a big number of user, the server side maybe throw out of memory.
4. how to release uselss connection of server side.
for example.
5. client is crashed.
6. user clicks close of browse.

[dynamobi-build]

[author="ngoodman", created="Tue, 27 Apr 2010 22:22:44 -0700"]

Ray

Is there anything in Spring Security/HTTP Session based authentication that can help us here?

I think the token approach is good, but I think it's such a basic requirement of most systems that there may already be a solution to it that we just have to configure/use instead of create again. ie, the lifecycle of these sessions may also be considered (so when the session expires, the JDBC connection will also).

Please research the use of HTTP session / Spring Security as a method for keeping (HTTP sessions have a unique ID) the connection.

[dynamobi-build]

[author="ngoodman", created="Tue, 11 May 2010 19:02:34 -0700"]

Simple provider implemented for pentaho security (which is based on Spring security). It may be helpful as a starting point for the catalog based approach to authenticating users.

[dynamobi-build]

[author="rzhang", created="Mon, 24 May 2010 06:44:09 -0700"]

The .xls file just shows auth stuff.
about jdbc connection session, I have no idea for it so far.
I want to do it below:
When calling webservice, we pass user detail in it.
when calling db, we can use user detail to create special connection.

[dynamobi-build]

[author="ngoodman", created="Tue, 25 May 2010 20:01:26 -0700"]

Ray - can you expand on the design and options for the HTTP Filter?

I see some information on the filters, remember me, and digest. Can you please elaborate some more on your plans?
http://static.springsource.org/spring-security/site/docs/3.0.x/reference/core-web-filters.html

What about some sort of SOAP headers to support a session? What web service is used to initiate the session if using SOAP?

Can you please make a detailed design (not with class diagrams) but with a more detailed look, with some examples (how the clients would use it, etc).

[dynamobi-build]

[author="rzhang", created="Wed, 26 May 2010 04:57:22 -0700"]

Re: can you expand on the design and options for the HTTP Filter?
Just use http basic auth filter.

Re:What about some sort of SOAP headers to support a session? What web service is used to initiate the session if using SOAP?
Currently we use JAX-WS 2.x to implement soap service.
I saw some reference to explain session management below. I am playing some round.
https://jax-ws.dev.java.net/guide/Maintaining_State_in_Web_Services.html

Re:Can you please make a detailed design (not with class diagrams) but with a more detailed look, with some examples (how the clients would use it, etc).
Currently, I am modifying flex sql admin app. just use http basic auth to implement login part.

[dynamobi-build]

[author="rzhang", created="Wed, 26 May 2010 05:09:49 -0700"]

I have a question on password encoding.
based on TestingEncoder.java, I saw a inner variate called edrawPass
Does it represent real password not encrypted password (eg. base64)right?
Currently if we use http basic auth, browser will pass the encrypted password thru base64.
So login always is failed. I guess the root cause is the rawpass.

[dynamobi-build]

[author="rzhang", created="Thu, 3 Jun 2010 23:22:49 -0700"]

Ray,

I was unable to get the Web Services war to deploy. It is complaining about a missing class. Can you please review / resolve the issue?

2010-06-03 16:22:20.884:WARN::Nested in org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'enunciate-security-interceptor' defined in ServletContext resource /WEB-INF/security-context.xml: Initialization of bean failed; nested exception is java.lang.NoClassDefFoundError: org/aspectj/lang/Signature:
java.lang.ClassNotFoundException: org.aspectj.lang.Signature

workround.
miss a jar called aspectj*.jar(aspectjrt-1.5.2a.jar.)
check in this jar in lib/modules/spring

[dynamobi-build]

[author="ngoodman", created="Tue, 8 Jun 2010 15:03:18 -0700"]

Ray,

Some comments / questions:

1 - I tried to have a "blank" password and it didn't work. Having a "blank" password is valid in LucidDB (although not a really good idea). Can you see if there's an easy way to allow a password of ""
2 - I noticed the authentication was happening with every single request. Is there any way that we can do this session based (remember me token or like) so that we don't ask the database with every request?

[dynamobi-build]

[author="rzhang", created="Wed, 9 Jun 2010 00:03:50 -0700"]

Re:
1. Here is my way to resolve it.
When we get userdetail from DB, we can set password "" where password is null.
When password is "" from client, we will not encode this password before checking the same.
2. we can use cache to avoid asking DB every time.
The detail is that Spring Security provides ehcache cache to solve it.

Please refer to SVN Revision# 145 and 146.

[dynamobi-build]

[author="rzhang", created="Wed, 9 Jun 2010 21:02:01 -0700"]

Nick,

I saw you disabled the cache. It did not work?
Please show me some detail.

[dynamobi-build]

[author="ngoodman", created="Sun, 13 Jun 2010 22:23:16 -0700"]

Ray,

I disabled it because it was giving an error while I was trying to give a demo.

Can you please describe to me how this works? Is the cache piece session based or time based?