Additional security with unique database logins

[westmich]

Just something I was considering, didn't know if it would be a waste of time or if anyone else is doing it.

I normally have a single database user account for a web-based applications. I normally implement security through username/password/seesion states with each user record also having a unique id and a security level. Throughout the application, I am verifying whether or not a user should be allowed to do certain functions based on id and security level.

I am condiering taking that one step further and adding multiple database users and groups. During the sign-up process, a CREATE and GRANT statement would be run to add a database user and make them a member of a certain group with certain privalages. The database connection string would be dynamic and use the username and password supplied by the logged in user.

I see benifits in having this: added layer of security, more flexibility in triggers and other database programming, and smoother developemnt into outside applications (wireless, remote client-side, or direct database access).

I also see drawbacks, namely more complications in development and debugging.

[greg.harvey]

Ahhhhhh ... so with each new user you'd create a table for them and then use GRANT to assign privaledges to that table .... hmmmmmm. Probably overkill to be honest. Nice idea though.

G

[jofa]

Maybe I'm paranoid, but the only thing I see in your solution is:
Instead of a db login with select/insert/update permissions for a selection of tables, you want the anonymous web site user to use a login that can create new users and grant permissions?!

[westmich]

Originally posted by greg.harvey
Ahhhhhh ... so with each new user you'd create a table for them and then use GRANT to assign privaledges to that table .... hmmmmmm. Probably overkill to be honest. Nice idea though.

G

No, not a table, just a username/group assignment.

[greg.harvey]

I see. Still, the way you're doing it at the moment with a user table and writing data to a session. There's nothing wrong with that. It's simple but effective and quick.

[westmich]

Originally posted by jofa
Maybe I'm paranoid, but the only thing I see in your solution is:
Instead of a db login with select/insert/update permissions for a selection of tables, you want the anonymous web site user to use a login that can create new users and grant permissions?!

No, it would not be an anonymous user. It would be a user in the sytem as described above, but with a user account in the database as well.

So, once a user logs in, instead of 'connection=dbUser/dbPassword', it would use 'connection=$userLogin/$userPassword'. The database connection string is dynamic.

[jofa]

Originally posted by westmich
No, it would not be an anonymous user.

Not?
Who is the user during the sign-up process then?

Originally posted by westmich
During the sign-up process, a CREATE and GRANT statement would be run to add a database user and make them a member of a certain group

[westmich]

Originally posted by jofa


Not?
Who is the user during the sign-up process then?

There would need to a user account for the application during the sign-up process and any other page of the site that is public, i.e. any page that a login not required.

[MattR]

There really is no 'more' security here

[greg.harvey]

[OFF TOPIC]



Good beard!!!!

[/OFF TOPIC]

[MattR]

LOL thanks. I think that was Mattias' doing!

But back to the topic.

The issue of security is odd in this context. How would creating different user accounts be beneficial? Or another way, what fear is in your mind that this will solve? Crackers gaining access to the web username and password? Not wanting to code logic for your application?

Well, you have to hard-code the login information for the user account *somewhere* -- you know, the login which will be used to generate the user logins. If so you have no more security since the cracker would simply use that full-access login (perhaps, depends on your RDBMS).

Also if you choose to leave your RDBMS listening outside the firewall (a BIG no-no!) then the users now have a key to look around!

Why would this be beneficial? I can think of a couple reasons:
1) Auditing
2) Security

Auditing
When using RDBMS-supplied authentication you can now take advantage of the auditing capabilities of your server, saving you from writing it yourself. This all depends on your server but most Enterprise servers have extensive auditing (and subsequent reporting) tools.

Security
Didn't I just say it did not provide more security? Well, if you 'half' do it (or do not have a capable RDBMS) then so, it would not.

However, with proper useage of VIEWS and STORED PROCEDURES (or functions in PostgreSQL parlance) you can achieve much higher levels of security.

Let's say I had a forum system like this one. As we all know moderators (advisors, etc.) can edit or delete posts, view hidden forums, etc. In a single auth you must first check certain permissions and then execute arbitrary SQL (but can be adapted to stored procs; later). In a multi login system I can limit user accounts to stored procedure execute only. That way even if they *do* find a way in they cannot exec arbitrary SQL but must use the sp interface. Obviously then the stored procs would check the currently logged in user and see if they have particular access. This can typically provide finer-grained access control over the typical table level of granularity.

Sybase ASE, for example, can let you apply row-level access control, so I could have it set up in the permissions that all non superusers cannot access rows in the post table that live in a particular thread, or any other T-SQL based query.

[jofa]

Originally posted by MattR
Sybase ASE, for example, can let you apply row-level access control ...

Now I'm getting really curious
In school they only taught us about column-level access control...
How is this done?

[MattR]

Here is the nitty-gritty:
http://manuals.sybase.com/onlinebook...2;pt=35369/*#X

Quite fun if you have the need. Saves writing it in your application code!

[westmich]

Well, it would be for various reasons including auditing/stats. As far as security, it would be in the context of having a login ID within the database for the database's use in stored procedures and triggers, but also in the context of more of the purest theory of having all constraints and rules within the database itself.

When you mentioned Stored Procedures and Views are you saying with a user ID within a table or something or actually a database login?

I've heard the column and row level locking can really hurt performance (at least in MS SQL), you're beeter off using a View to achieve the same effect.