如何在sql中确定用户是否可以通过la unix权限系统读取/写入/执行文件？ [英] How to determine in sql if a user can read/write/execute a file a la unix permissions system?
问题描述
我正在尝试编写一个包含文件和用户的应用程序。文件的权限应与Unix中的一样：读/写/执行。每个文件都属于一个所有者和一个组，并且每个文件都有其他用户（基于谁是所有者，以及谁属于一个组）。模式如下所示：
I am trying to write an application where there are files and users. The permissions for the files should be as in unix: read/write/execute. Each file belongs to a owner and a group, and for each file there are "other" users (based on who is owner, and who belongs to a group). The schema looks like this:
create table s_file(
file_id INTEGER PRIMARY KEY,
parent_id INTEGER default NULL,
name,
content BLOB DEFAULT NULL,
owner_id INTEGER NOT NULL,
group_id INTEGER NOT NULL,
read_owner BOOLEAN not null default 1,
write_owner BOOLEAN not null default 1,
execute_owner BOOLEAN not null default 1,
read_group BOOLEAN not null default 1,
write_group BOOLEAN not null default 0,
execute_group BOOLEAN not null default 1,
read_other BOOLEAN not null default 1,
write_other BOOLEAN not null default 0,
execute_other BOOLEAN not null default 1
);
create table s_user( uid INTEGER PRIMARY KEY,
name NOT NULL, password NOT NULL);
create table s_group( gid INTEGER PRIMARY KEY, name NOT NULL);
create table s_user_in_group ( uid INTEGER NOT NULL,
gid INTEGER NOT NULL);
insert into s_user(uid,name,password) values (1,'root','pw');
insert into s_user(uid,name,password) values (2,'gauss', 'pw');
insert into s_user(uid,name,password) values (3,'conway', 'pw');
insert into s_group(gid,name) values (1,'root');
insert into s_group(gid,name) values (2,'producer');
insert into s_group(gid,name) values (3,'gauss');
insert into s_group(gid,name) values (4,'conway');
insert into s_user_in_group(uid,gid) values (2,2),(2,3),(3,4);
insert into s_file(file_id,name,owner_id,group_id) values (1,'galois',1,1);
insert into s_file(file_id,parent_id,name,owner_id,group_id) values (2,1,'home',1,1);
insert into s_file(file_id,parent_id,name,owner_id,group_id,write_group) values (3,1,'models',1,2,1);
insert into s_file(file_id,parent_id,name,owner_id,group_id) values (4,2,'gauss',2,3);
insert into s_file(file_id,parent_id,name,owner_id,group_id) values (5,2,'conway',3,4);
insert into s_file(file_id,parent_id,name,owner_id,group_id,content) values (6,4,'boston.pfa',2,3,'cB');
insert into s_file(file_id,parent_id,name,owner_id,group_id,content) values (7,5,'iris.pfa',3,4,'cI');
insert into s_file(file_id,parent_id,name,owner_id,group_id,write_group,content) values (8,3,'boston.pfa',2,2,1,'CB');
insert into s_file(file_id,parent_id,name,owner_id,group_id,write_group,content) values (9,3,'iris.pfa',2,2,1,'CI');
以下是一些示例数据：
文件树：
Here are some example data: File Tree:
galois
|-- home
| |-- gauss
| | +-- boston.pfa
| +-- conway
| +-- iris.pfa
+-- models
|-- boston.pfa
+-- iris.pfa
我想编写一个查询，如果可以在sqlite中使用，否则使用python，例如，查看用户conway是否可以编写/ galois / models / boston .pfa
根据我的示例数据（您可以在下面找到），这应该是不可能的，因为conway不是所有者，也不是组生产者，并且不允许其他人写。
所以，如果要在sql中，我想做的是下表/条目：
I would like to write a query, if it is possible in sqlite, otherwise with python, to see for example, if the user conway, can write /galois/models/boston.pfa According to my example data, which you can find below, it should not be possible, since conway is not the owner nor in the group producers and others are not allowed to write. So what I am trying to do, if it is possible in sql, is the following table / entry:
file_id, user_id, can_write/can_execute/can_read
请在下面找到一些示例数据：
Please find below some example data:
sqlite> select file_id, parent_id, name, owner_id, group_id, write_owner, write_group,write_other from s_file;
1||galois|1|1|1|0|0
2|1|home|1|1|1|0|0
3|1|models|1|2|1|1|0
4|2|gauss|2|3|1|0|0
5|2|conway|3|4|1|0|0
6|4|boston.pfa|2|3|1|0|0
7|5|iris.pfa|3|4|1|0|0
8|3|boston.pfa|2|2|1|1|0
9|3|iris.pfa|2|2|1|1|0
sqlite> select uid,name from s_user;
1|root
2|gauss
3|conway
sqlite> select gid,name from s_group;
1|root
2|producer
3|gauss
4|conway
sqlite> select uid,gid from s_user_in_group;
2|2
2|3
3|4
有任何建议吗？
感谢您的帮助！
编辑：
根据@Mike的建议，我将按照以下步骤操作：
1.创建一个视图
Edit: On suggestion of @Mike I would proceed as follows: 1. Create a view
file_id, user_id, can_read, can_write, can_execute
具有本地权限
- 使用CTE来确定用户是否可以访问（特定于unix）特定文件：
为此，用户必须有权执行文件上方的每个目录直到根目录。
我的问题是决定第一步是否可以仅在sql中完成（那很好） ，或者如果我必须将其与python混合使用[在这种情况下，我还将在python中实现第2步。]
My problem is to decide, if the first step can be done solely in sql (that would be great), or if I have to mix it with python [In this case I would also implement step 2. in python]
或者也许您对如何构造有更好的想法（schema）实现此目标的数据？
Or maybe you have a better idea on how to structure (schema) the data to achieve this goal?
再次感谢您的帮助！
推荐答案
我找到了解决方案：
create view if not exists user_rights as
select u.uid,f.file_id,
case
when f.owner_id = u.uid then f.read_owner
when u.uid = uig.uid then max(f.read_group) else max(f.read_other) end as can_read,
case
when f.owner_id = u.uid then f.write_owner
when u.uid = uig.uid then max(f.write_group) else max(f.write_other) end as can_write,
case
when f.owner_id = u.uid then f.execute_owner
when u.uid = uig.uid then max(f.execute_group) else max(f.execute_other) end as can_execute
from s_user u,s_file f left join s_user_in_group uig on f.group_id = uig.gid group by u.uid,file_id;
完整的解决方案可以在这里找到：
The complete solution may be found here:
https://github.com/orgesleka/unix-acl-sql
这篇关于如何在sql中确定用户是否可以通过la unix权限系统读取/写入/执行文件？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
