Multiple user database design

I h开发者_高级运维ave to develop a basic social network for an academic purpose; but I need some tips for the users management..

The users are subdivided into 3 groups with different privilege: admins,analysts and standards users. For every user should be stored into the database the following information: name,lastname,e-mail,age,password.

I'm not quite sure how I should design the database between theese two solutions:

1)one table called 'users' with the 'role' attribute that explain what a user can do and what can't do, and the permissions are managed via php

2)every application user is a database user created with the query 'CREATE ROLE' (It's a postgres database) and he has permissions on some tables granted with the 'GRANT' statement

You should take into account that the project is for a database exam..

thanks

---

Don't use the database's authorization mechanism to be your application's authorization system. Three main reasons:

A) You'll never be able to change to a different database without rebuilding the whole app.

B) The types of things you want to grant the users in the app might differ from what the db's ACL system allows.

And most importantly:

C) You don't want to give an application user the ability to do anything directly to your database. Ever.

So your #2 option is right out. Thus, store a user type field with each user record, and then "what that user type allows" becomes part of your business logic that is calculated in PHP.

---

Solution 1 every time as you don't want to restrict yourself to only assign permissions on a per-table basis. Using database users would be cumbersome and not very practical.

---

Go with Option 1. It will be much more flexible in the long run, probably easier to code, and you don't want to tie your application logic too closely to a specific implementation. What if you later on want to port the application to run on SQL-Server? If database users are implemented differently, Option 2 could give you serious pains.

---

Go with your first alternative (manage permissions with PHP). Here are the reasons:

1. The database does not give you enough choices and granularity in the permissions you'll need to manage (who is allowed to send emails, to what groups people are allowed access, etc.)
2. Typically connections to the database are rather expensive so you'll want to connect once and stay connected as long as possible (with the same database user)
3. All databases are not created equal in the way they handle user accounts. By building your own user system above SQL you can hope to be more database independant
4. In the real world the tasks of administering the database and developping programs are done by completely different people and the program does not have the right to create or alter database users