How to implement UNIX like ACL for table rows

Question

EDIT: I can't create or use native MySQL users. I have to use users stored in table with their password hash.

I am new sql stuff so please forgive me for asking dumb question. I am creating my first real life application for college project.

At its core, It need to handle more than thousands of users which should not able to read or write to each others data unless given privileges. like Linux does with user and groups.

in below schema which I tried , a user can view(read) and edit(write) other users if they have read permissions.( r=2 w=1 r+w=3 ).

for example if cgroup_1 is admin and cgroup_2 is managers and unixperm is 32 then it means users in admin group can read+write(3) and users in managers group can only read(2)

create table cgroups
(
 id int unsigned primary key auto_increment,
 title varchar(100) not null unique,
 cunixperm tinyint unsigned not null default 32 ,# r=2 w=1
 cgroup_1 int unsigned not null default 1 references cgroups (id) on delete cascade on update cascade,
 cgroup_2 int unsigned references cgroups (id) on delete cascade on update cascade
);
create table users
(
 id int unsigned auto_increment primary key,
 username varchar(255) not null unique,
 cunixperm tinyint unsigned not null default 30, # r=2 w=1 3=r+w
 cgroup_1 int unsigned default 1 not null references cgroups (id) on delete cascade on update cascade ,
 cgroup_2 int unsigned references cgroups (id) on delete cascade on update cascade
);
create table many_users_in_many_cgroups
(
 user_id int unsigned references users(id),
 cgroup_id int unsigned references cgroups(id),
 primary key (user_id,cgroup_id)
);
insert into cgroups(title)
values ('admins'),('managers'),('writers');
insert into users(username, cunixperm, cgroup_1, cgroup_2)
values ('user1',30,1,null),
 ('user2',30,1,2),
 ('user3',22,2,2),
 ('user4',02,3,3);
insert into many_users_in_many_cgroups
values (1,1),(2,2),(3,3),(4,4);

Now suppose user 2 has logged into my app, How can I only show the user rows where he has read (2) or read+write(3) permissions.

if above schema is not (probably) appropriate pls give me an example with appropriate scheme

I am currently using MariaDB but open for solutions for others too.

• Akina: Forget queries. Move all logic (including check permissions and perform only allowed actions on allowed rows) into stored procedures with SECURITY DEFINER as the only way to get/set data (revoke all from users).
• Michael Kutz: Row Level Security (RLS) is the first thing that comes to my mind.
• Akina: RLS not exists in MySQL. Table-level and Column-level only.
• LightSith: @Akina, I searched on net about RLS. it seems it needs native database users (at least of MS SQL) . I cant use native database users. I have users and there password hash in separate table. Also I am open to use another DBMS
• Rick James: See federico-razzoli.com/…

Answer 1

I'd strongly consider using PostgreSQL, as it supports Row Security (as well as Column Security), and is free and well made

You should also consider using real database users, but you don't have to. For example, you could set the current user into a temp table upon sign-in, and reference that username in your RLS policy