SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    SitePoint Wizard westmich's Avatar
    Join Date
    Mar 2000
    Location
    Muskegon, MI
    Posts
    2,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation Additional security with unique database logins

    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.
    Westmich
    Smart Web Solutions for Smart Clients
    http://www.mindscapecreative.com

  2. #2
    Drupaler bronze trophy greg.harvey's Avatar
    Join Date
    Jul 2002
    Location
    London, UK
    Posts
    3,258
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  3. #3
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?!

  4. #4
    SitePoint Wizard westmich's Avatar
    Join Date
    Mar 2000
    Location
    Muskegon, MI
    Posts
    2,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Westmich
    Smart Web Solutions for Smart Clients
    http://www.mindscapecreative.com

  5. #5
    Drupaler bronze trophy greg.harvey's Avatar
    Join Date
    Jul 2002
    Location
    London, UK
    Posts
    3,258
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.


  6. #6
    SitePoint Wizard westmich's Avatar
    Join Date
    Mar 2000
    Location
    Muskegon, MI
    Posts
    2,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Westmich
    Smart Web Solutions for Smart Clients
    http://www.mindscapecreative.com

  7. #7
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  8. #8
    SitePoint Wizard westmich's Avatar
    Join Date
    Mar 2000
    Location
    Muskegon, MI
    Posts
    2,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Westmich
    Smart Web Solutions for Smart Clients
    http://www.mindscapecreative.com

  9. #9
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    There really is no 'more' security here

  10. #10
    Drupaler bronze trophy greg.harvey's Avatar
    Join Date
    Jul 2002
    Location
    London, UK
    Posts
    3,258
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    [OFF TOPIC]



    Good beard!!!!

    [/OFF TOPIC]

  11. #11
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  12. #12
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  13. #13
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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!

  14. #14
    SitePoint Wizard westmich's Avatar
    Join Date
    Mar 2000
    Location
    Muskegon, MI
    Posts
    2,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Westmich
    Smart Web Solutions for Smart Clients
    http://www.mindscapecreative.com


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •