How to properly implement data access security in MySQL?

Question

MySQL

Hey!
There are several tablets with important records. There are users, groups and divisions. It is necessary to organize the mechanism of data access security. To allow users to read and edit only their posts, their group, or their division. Something like security on the file system. The difficulty is that one object - a record in the plate can be assigned to several users, groups or departments.
For example, whether to make a separate table with rights, or add a column. It’s just not clear what to write there, how to correctly encode the value of rights, so that later you can quickly make selections. What are the approaches, how to implement it correctly, what to read?
Java language, MySQL database.

Answer 1

If there are few groups without growth trends, you can draw restrictive VIEWs and distribute access to users there.
Another way - stored procedures diligently checking all parameters. Users are given access to these procedures, and access to tables is not directly given.
The problem is that in mysql these things are not fully and conveniently implemented.
The third way is "three-link". an additional server program that client applications communicate with. It does all the checks in the language of your choice.

Answer 2

If there are few groups without growth trends, you can draw restrictive VIEWs and distribute access to users there.
Another way - stored procedures diligently checking all parameters. Users are given access to these procedures, and access to tables is not directly given.
The problem is that in mysql these things are not fully and conveniently implemented.
The third way is "three-link". an additional server program that client applications communicate with. It does all the checks in the language of your choice.

Answer 3

В MySQL вам это сделать не удастся. Без гранта на SELECT, CALL не выполняется. Вообще там привилегии через жопу сделаны. Пример. Делаем 1 таблицу и одну процедуру с селектом из этой таблицы. Юзеру даем грант ТОЛЬКО на выполнение процедуры. Результат: процедура не выполняется, т.к. нет гранта на SELECT внутри процедуры. Полный бред…
mysql> create database t;
Query OK, 1 row affected (0.00 sec)
mysql> use t
Database changed
mysql> CREATE TABLE `testtable` (
-> `id` int(11) NOT NULL,
-> `name` varchar(255) NOT NULL
-> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.06 sec)
mysql> DELIMITER $$
mysql> CREATE
-> PROCEDURE testproc ()
-> SQL SECURITY INVOKER
-> BEGIN
-> SELECT * FROM testtable;
-> END$$
Query OK, 0 rows affected (0.00 sec)
mysql> grant usage on t.* to [email protected] identified by 'qwe';
Query OK, 0 rows affected (0.01 sec)
mysql> grant execute on procedure t.testproc to [email protected];
Query OK, 0 rows affected (0.01 sec)
mysql> exit
Bye
# mysql -u testuser -p
mysql> use t
Database changed
mysql> select * from testtable;
ERROR 1142 (42000): SELECT command denied to user 'testuser'@'localhost' for table 'testtable'
mysql> call testproc();
ERROR 1142 (42000): SELECT command denied to user 'testuser'@'localhost' for table 'testtable'