Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2009
    Posts
    3

    Users Stored as Db Users or Table Rows?

    Anyone tried storing individual users of a system as database users? If so then how did you handle permission errors? Did it impact any connection pooling?

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    That's not a good way of storing users - far too complicated and requires too much management etc. Handling users by the application in a user table is far easier.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by mike_bike_kite
    That's not a good way of storing users - far too complicated and requires too much management etc. Handling users by the application in a user table is far easier.
    I've used both. I think it depends on several other factors. For example, if this is MS and single sign on is a goal of the organisation, then using Windows Authentication might be preferable. Windows Auth also allows access to be tied in with other privileges. Internally developed and maintained systems are more applicable this sort of authentication (I wouldn't expect many commercial systems for example to be built on this principle).
    If you have the RDBMS manage authentication you don't need to write custom code. You don't run the risk of some idiot storing plain text passwords. Those communications are automatically encoded.

    There are pros and cons to both. I don't think there is sufficient information to judge in this case, and I would certainly not say that one is preferable over the other irrespective of circumstance .
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by pootle flump
    There are pros and cons to both. I don't think there is sufficient information to judge in this case, and I would certainly not say that one is preferable over the other irrespective of circumstance .
    I think getting the username and user groups for a user from a central resource is great. But I'd use those details to access a user table in the database. The idea of having 100's or 1000's of separate accounts all logging onto the database would give me the shivers though - I'd always have just one login for an application as I believe it makes life simpler.

    What advantages does having loads of separate logins offer? How do you get round the issues of varying permissions, permission errors etc?

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    In a word "Windows Groups"

    ....all right, two.

    I'm not offering a golden bullet answer, more I'm arguing against one. It depends on circumstance. MS recommend Windows Authentication for SQL Server since it utilises Kerberos. However, a half savvy user with a copy of Access can get at your database. On the other hand, when a user leaves the organisation there is a single account to nullify, not N accounts, one for each application.

    I don't see how the problems of N logins are solved by having a table of N users - the problem is the same, just under the application developer's remit rather than the DBA's.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Apr 2009
    Posts
    3
    Microsoft Windows is not an option in my case. I was considering doing all the connection pooling using a transparent tool like Pgpool, but it pools based on the user-name and database name.

    Storing the common SQL privileges in some base roles (a.k.a. groups) then making user roles members of those group roles should provide the same protection as a role for each application group.

    EDIT: I was curious if anyone who has implemented such an arrangement also used the SQL privileges along with --or instead of-- application access controls for individual rows. This seems possible, but difficult to manage since Postgresql lacks row-level SQL privileges. An arrangement involving views to exclude prohibited rows should be possible. However, I don't think making a view for every combination of user and table is practical. Error handling would also be more complicated.

    EDIT2: My case is also complicated by the fact that I have users with access to many databases and users who are specific to one database. There may also be multiple database installations. So managing the global users and the db-specific users makes the idea of SQL users even more complicated
    Last edited by progerssb; 05-01-09 at 10:22.

  7. #7
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    I haven't used it much, but possibly, PostgreSQL's Rules system may help...

    ***
    I once set up a system with a common user table (name, password, contact data,) and added a linked table for each application with the application specific privileges defined in the child table(s)
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

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