.::: Create user limited to access only spesific table or specific view, store procedure,function in MariaDB MySQL :::.

corelation http://teguhth.blogspot.com/2022/08/create-user-admin-readonly-database.html

A. create user only akses 1 table
1. create user only akses 1 table
CREATE USER 'onlyone'@'%' IDENTIFIED BY 'onlyone';
GRANT SELECT, INSERT, UPDATE, DELETE ON teguhth.pembelian TO 'onlyone'@'%';
ALTER USER 'onlyone'@'%' IDENTIFIED BY 'onlyone';

CREATE USER 'onlyone'@'localhost' IDENTIFIED BY 'onlyone';
GRANT SELECT, INSERT, UPDATE, DELETE ON teguhth.pembelian TO 'onlyone'@'localhost';
ALTER USER 'onlyone'@'localhost' IDENTIFIED BY 'onlyone';

FLUSH PRIVILEGES;
 

2. create user only akses 1 table only access 1 database

CREATE USER 'onlydb'@'localhost' IDENTIFIED BY 'onlydb';
GRANT SELECT, INSERT, UPDATE, DELETE ON clasicdb.* TO 'onlydb'@'localhost';
ALTER USER 'onlydb'@'localhost' IDENTIFIED BY 'onlydb';

CREATE USER 'onlydb'@'%' IDENTIFIED BY 'onlydb';
GRANT SELECT, INSERT, UPDATE, DELETE ON clasicdb.* TO 'onlydb'@'%';
ALTER USER 'onlydb'@'%' IDENTIFIED BY 'onlydb';

FLUSH PRIVILEGES;
 

3. create user only access view & store procedure

CREATE USER 'onlyview'@'%' IDENTIFIED BY 'onlyview';
GRANT SELECT, INSERT, UPDATE, DELETE ON teguhth.barang_view TO 'onlyview'@'%';
GRANT EXECUTE ON PROCEDURE teguhth.SimpleCalculator TO 'onlyview'@'%';
GRANT EXECUTE ON FUNCTION teguhth.calculate TO 'onlyview'@'%';

CREATE USER 'onlyview'@'localhost' IDENTIFIED BY 'onlyview';
GRANT SELECT, INSERT, UPDATE, DELETE ON teguhth.barang_view TO 'onlyview'@'localhost';
GRANT EXECUTE ON PROCEDURE teguhth.SimpleCalculator TO 'onlyview'@'localhost';
GRANT EXECUTE ON FUNCTION teguhth.calculate TO 'onlyview'@'localhost';

FLUSH PRIVILEGES;

 4. capture all admin

D. create user with Granting column level permissions

1. create user only akses 1 column
CREATE USER 'onlycolumn'@'%' IDENTIFIED BY 'onlycolumn';
GRANT SELECT (`JUMLAH_PEMBELIAN`), SHOW VIEW ON teguhth.`pembelian` TO 'onlycolumn'@'%';
ALTER USER 'onlycolumn'@'%' IDENTIFIED BY 'onlycolumn';

CREATE USER 'onlycolumn'@'localhost' IDENTIFIED BY 'onlycolumn';
GRANT SELECT (`JUMLAH_PEMBELIAN`), SHOW VIEW ON teguhth.`pembelian` TO 'onlycolumn'@'localhost';
ALTER USER 'onlycolumn'@'localhost' IDENTIFIED BY 'onlycolumn';

FLUSH PRIVILEGES;
 

2. login & check priviledge

SELECT CONCAT('GRANT SELECT (`', COLUMN_NAME, '`), SHOW VIEW ON teguhth.`', TABLE_NAME, '` TO ''admin''@''localhost'';')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'teguhth' AND TABLE_NAME = 'pembelian';

SELECT CONCAT('GRANT SELECT (`', COLUMN_NAME, '`), SHOW VIEW ON teguhth.`', TABLE_NAME, '` TO ''onlycolumn''@''localhost'';')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'teguhth' AND TABLE_NAME = 'pembelian';

[root@teguhth ~]# mysql -uonlycolumn -ponlycolumn
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 7
Server version: 10.11.6-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use teguhth
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [teguhth]> SELECT CONCAT('GRANT SELECT (`', COLUMN_NAME, '`), SHOW VIEW ON teguhth.`', TABLE_NAME, '` TO ''admin''@''localhost'';')
    -> FROM INFORMATION_SCHEMA.COLUMNS
    -> WHERE TABLE_SCHEMA = 'teguhth' AND TABLE_NAME = 'pembelian';
+-------------------------------------------------------------------------------------------------------------------+
| CONCAT('GRANT SELECT (`', COLUMN_NAME, '`), SHOW VIEW ON teguhth.`', TABLE_NAME, '` TO ''admin''@''localhost'';') |
+-------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT (`JUMLAH_PEMBELIAN`), SHOW VIEW ON teguhth.`pembelian` TO 'admin'@'localhost';                       |
+-------------------------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)

MariaDB [teguhth]> SELECT CONCAT('GRANT SELECT (`', COLUMN_NAME, '`), SHOW VIEW ON teguhth.`', TABLE_NAME, '` TO ''onlycolumn''@''localhost'';')
    -> FROM INFORMATION_SCHEMA.COLUMNS
    -> WHERE TABLE_SCHEMA = 'teguhth' AND TABLE_NAME = 'pembelian';
+------------------------------------------------------------------------------------------------------------------------+
| CONCAT('GRANT SELECT (`', COLUMN_NAME, '`), SHOW VIEW ON teguhth.`', TABLE_NAME, '` TO ''onlycolumn''@''localhost'';') |
+------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT (`JUMLAH_PEMBELIAN`), SHOW VIEW ON teguhth.`pembelian` TO 'onlycolumn'@'localhost';                       |
+------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.003 sec)

MariaDB [teguhth]>
 

3. testing user

MariaDB [(none)]> use teguhth
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [teguhth]> select * from barang;
ERROR 1142 (42000): SELECT command denied to user 'onlycolumn'@'localhost' for table `teguhth`.`barang`
MariaDB [teguhth]> select * from pembelian;
ERROR 1143 (42000): SELECT command denied to user 'onlycolumn'@'localhost' for column 'KODE_PEMBELIAN' in table 'pembelian'
MariaDB [teguhth]>
MariaDB [teguhth]> select jumlah_pembelian from pembelian;
+------------------+
| jumlah_pembelian |
+------------------+
|                5 |
|                4 |
|                2 |
|                2 |
|                3 |
|                1 |
|                1 |
|                2 |
|                2 |
|                5 |
|                4 |
|                6 |
|               10 |
|               12 |
|               22 |
|               17 |
+------------------+
16 rows in set (0.015 sec)

MariaDB [teguhth]>