How to properly implement the security access data on MySQL?


Warning: count(): Parameter must be an array or an object that implements Countable in /home/styllloz/public_html/qa-theme/donut-theme/qa-donut-layer.php on line 274
0 like 0 dislike
4 views
Hi!


There are several signs of important records. There are users, groups, and divisions. Need to organize a security mechanism to access the data. To allow users to read and edit only their records, his group or his unit. Something like the security on the file system. The difficulty is that one object — the entry in the table can be assigned to multiple users, groups or departments.


For example whether to do a separate table with the rights, or add a column. That's just not clear what to write, how to encode the value of the rights, in order to quickly make selections. What are the different approaches how to implement, what to read?


The Java language, MySQL database.
by | 4 views

3 Answers

0 like 0 dislike
If groups a bit of a growth tendencies, you can be the limiting VIEW and distribute user access there.
\r
The other way is a stored procedure, carefully check all settings. Users are given access to these procedures, and access tables directly is not allowed.
The problem is that in mysql, these things are not fully and ideally implemented.
\r
The third way -"trehdverka". additional server program, which communicate with the client application. She does all validation in the language you prefer.
by
0 like 0 dislike
If groups a bit of a growth tendencies, you can be the limiting VIEW and distribute user access there.
\r
The other way is a stored procedure, carefully check all settings. Users are given access to these procedures, and access tables directly is not allowed.
The problem is that in mysql, these things are not fully and ideally implemented.
\r
The third way -"trehdverka". additional server program, which communicate with the client application. She does all validation in the language you prefer.
by
0 like 0 dislike
In MySQL you do not succeed. Without grant on the SELECT CALL fails. In General, there is privilege in the ass done. Example. Make 1 table and one procedure with select from this table. The user is given the grant to execute the procedure. The result: the procedure is not performed since there is no grant SELECT within a procedure. Complete nonsense...
\r
mysql> create database t;
Query OK, 1 row affected (0.00 sec)
\r
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)
\r
mysql> DELIMITER $$
mysql> CREATE
-> PROCEDURE testproc ()
-> SQL SECURITY INVOKER
-> BEGIN
-> SELECT * FROM testtable;
-> END$$
Query OK, 0 rows affected (0.00 sec)
\r
mysql> grant usage on t.* to testuser@localhost identified by 'qwe';
Query OK, 0 rows affected (0.01 sec)
mysql> grant execute on procedure t.testproc to testuser@localhost;
Query OK, 0 rows affected (0.01 sec)
mysql> exit
Bye
\r
# 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'
by

Related questions

0 like 0 dislike
1 answer
0 like 0 dislike
1 answer
0 like 0 dislike
1 answer
asked Apr 13, 2019 by Mr-Governor
0 like 0 dislike
2 answers
110,608 questions
257,186 answers
0 comments
27,911 users