How to implement UNIX like ACL for table rows 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.
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