0

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.

5
  • 1
    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).
    – Akina
    Feb 5, 2020 at 18:15
  • Row Level Security (RLS) is the first thing that comes to my mind. Feb 5, 2020 at 18:23
  • RLS not exists in MySQL. Table-level and Column-level only.
    – Akina
    Feb 5, 2020 at 18:56
  • @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
    – LightSith
    Feb 5, 2020 at 19:02
  • 1
    See federico-razzoli.com/…
    – Rick James
    Feb 10, 2020 at 19:42

1 Answer 1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.