title: How to implement UNIX like ACL for table rows
body: <p>I am new sql stuff so please forgive me for asking dumb question. I am creating my first real life application for college project.</p>
  
  <p>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.</p>
  
  <p>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 ).</p>
  
  <p>for example if <code>cgroup_1</code> is admin and <code>cgroup_2</code> is managers and <code>unixperm</code> is 32 then it means users in admin group can read+write(3) and users in managers group can only read(2)</p>
  
  <pre><code>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);
  </code></pre>
  
  <p>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.</p>
  
  <p>if above schema is not (probably) appropriate pls give me an example with appropriate scheme</p>
  
  <p>I am currently using MariaDB but open for solutions for others too.</p>