向PostgreSQL上的用户授予对表子集的访问权限 [英] Grant access to subset of table to user on PostgreSQL
问题描述
我知道我可以使用视图来授予对表中属性子集的访问权限。但是,如何只授予对特定元组的访问权限?假设我有一个注册学生的表格,一个用户名属性,然后是诸如degree_status之类的其他名称,我如何授予访问权限,以便用户A只能从表格中选择一个与用户名A相对应的元组?我有一个数据库考试,正在研究一些以前的论文,遇到了这个问题,但是我不知道该如何回答,我在《 Dtabase System:数据库设计的实用方法》一书中找不到解决方法。 ,实施和管理
I know that I can use views to grant access to a subset of attributes in a table. But how can I grant access to particular tuples only? Say I have a table of registered students, a username attribute and then some other like degree_status, how do I grant access so that user A can only select from the table a tuple corresponding to username A ? I have a database exam and I'm studying some past papers and I came across this question but I don't know how to answer it and I cant find how to do it from my book "Dtabase System: A practical Approach to Database Design, Implementation and Management'
非常感谢您的帮助!
马特
推荐答案
说您得到了:
Table items (item_id, ...)
Table users (user_id, ...)
Table users_permissions( user_id, item_id, perm_type )
您可以这样创建一个视图:
You could create a VIEW like this :
SELECT i.*, p.perm_type
FROM items JOIN users_permissions USING (item_id)
WHERE user_id = get_current_user_id();
用户可以从此视图中选择,但不能删除WHERE和JOIN来限制权限。
Users can select from this view but not remove the WHERE and JOIN restricting the permissions.
get_current_user_id()函数可能是主要问题;)
The get_current_user_id() function is likely to be the major problem ;)
这篇关于向PostgreSQL上的用户授予对表子集的访问权限的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!