多用户数据库设计
我必须出于学术目的开发一个基本的社交网络；但我需要一些用户管理的技巧。
用户分为 3 个具有不同权限的组：管理员、分析师和标准用户。 对于每个用户，应将以下信息存储到数据库中：姓名、姓氏、电子邮件、年龄、密码。
我不太确定应该如何在这两个解决方案之间设计数据库：
1）一个名为“用户”的表，其中包含“角色”属性，解释用户可以做什么和不能做什么，并管理权限通过 php
2）每个应用程序用户都是使用查询“CREATE ROLE”创建的数据库用户（这是一个 postgres 数据库），并且他对使用“GRANT”语句授予的某些表具有权限
您应该考虑到该项目是针对数据库考试..
谢谢
I have 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
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
不要使用数据库的授权机制作为应用程序的授权系统。三个主要原因：
A) 如果不重建整个应用程序，您将永远无法更改到不同的数据库。
B) 您想要在应用程序中授予用户的事物类型可能与数据库的 ACL 系统允许的事物类型不同。
最重要的是：
C) 您不想让应用程序用户直接对数据库执行任何操作。曾经。
所以你的第二个选择是正确的。因此，为每个用户记录存储一个用户类型字段，然后“该用户类型允许的内容”就成为用 PHP 计算的业务逻辑的一部分。
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.
每次都使用解决方案 1，因为您不想限制自己只能在每个表的基础上分配权限。使用数据库用户会很麻烦而且不太实用。
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.
选择选项 1。从长远来看，它会更加灵活，可能更容易编码，并且您不希望将应用程序逻辑与特定实现联系得太紧密。如果您稍后想要移植应用程序以在 SQL-Server 上运行怎么办？如果数据库用户的实现方式不同，选项 2 可能会给您带来严重的痛苦。
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.
选择第一个替代方案（使用 PHP 管理权限）。原因如下：
Go with your first alternative (manage permissions with PHP). Here are the reasons: