.::: 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


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