MySQL / MariaDB User Privileges Settings

Hello everyone, I want to share some tips regarding MySQL / MariaDB user privileges settings. If you have read my post about Tips for Creating MySQL / MariaDB Database Users, there I have explained a little about the importance of managing database user privileges. Why is privilege setting important? because if we don’t manage it, aka give full access to the user, we are afraid that there will be abuse of the database user itself. As an example, there is a new developer, he is not very good at querying the database, so he accidentally deletes data or even tables and databases, which is very dangerous. Therefore MySQL / MariaDB provides privilege rules for the user to minimize errors that can occur.

For that, just go ahead, here are the privileges rules in MySQL / MariaDB:

Privileges	Keterangan
ALL [PRIVILEGES]	Grants all access rights, except GRANT OPTION
ALTER	Privilege to change table (query ALTER TABLE)
ALTER ROUTINE	Privileges to modify stored routines
CREATE	Privileges to create tables and databases
CREATE ROUTINE	Privilege for creating and deleting stored routines
CREATE TABLESPACE	Privilege to create, modify and delete tablespaces and log files
CREATE TEMPORARYTABLES	Privilege to create temporary tables
CREATE USER	Privilege to create, delete, and modify users (query: CREATE USER, DROP USER, RENAME USER and REVOKE ALL PRIVILEGES)
CREATE VIEW	Privilege to create and modify views
DELETE	Privilege to delete data (query DELETE)
DROP	Privilege to delete databases, tables and views
EVENT	Privilege to create events
EXECUTE	Privilege for running stored routines
FILE	Privilege to make the server read or create files
GRANT OPTION	Privilege to grant access rights to other users.
INDEX	Privilege to create and delete index
INSERT	Privilege to add data (INSERT query)
LOCK TABLES	Privilege to lock tables (query LOCK TABLES)
PROCESS	Privilege to see the whole process (query SHOW PROCESSLIST)
PROXY	Privilege for the proxy process
REFERENCES	Privilege Creation of foreign key constraints
RELOAD	Privilege for FLUSH operations
REPLICATION CLIENT	Privilege to change the order of master and slave servers
REPLICATION SLAVE	Privilege for replication servers to read binary event logs from the Master server
SELECT	Privilege to view data (SELECT query)
SHOW DATABASES	Privilege to view the entire database (query SHOW DATABASES)
SHOW VIEW	Privilege to view view creation (query SHOW CREATE VIEW)
SHUTDOWN	Privilege for mysqladmin shutdown
SUPER	Privilege for server administration functions, such as CHANGE MASTER TO, KILL, PURGE BINARY LOGS, SET GLOBAL, and mysqladmin debug commands
TRIGGER	Privilege for trigger operations
UPDATE	Privilege to update data (query UPDATE)
USAGE	The same means without access rights

then how to use it, just run a query like this:

GRANT privileges ON `databaseName`.`TableName` TO 'user'@'IPaddress';

example :

#1
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'127.0.0.1';
#2
GRANT SELECT, SHOW VIEW ON `myDatabase`.* TO 'admin'@'127.0.0.1';
#3
GRANT SELECT, INSERT, UPDATE, DELETE ON `myDatabase`.`myTable` TO 'admin'@'127.0.0.1';

Little explanation :

#1 for the *.* Part means that we give that access to all databases and their tables.

#2 we can use a sign , a comma to specify more than one privileges and for the `myDatabase`.* Section means that we grant access only to the myDatabase database with access to all the database tables.

#3 we can also grant access only to certain tables in one database with this example `myDatabase.`myTable`

a tip from me, set the privileges according to the level and needs of each developer. If at my place of work, for example like this:

#Privileges for Senior development / Senior Database Administrators (DBA)
GRANT ALL PRIVILEGES ON *.* TO 'superAdmin'@'127.0.0.1';

#Privileges for DBAs in charge of analyzing / patching data
GRANT SELECT, SHOW VIEW, SHOW DATABASES, PROCESS, INSERT, UPDATE, DELETE ON *.* TO 'adminDBA'@'127.0.0.1';

#Privileges for Data Backup users
GRANT SELECT, LOCK TABLES, CREATE VIEW, SHOW VIEW, CREATE ROUTINE ON *.* TO 'backupUser'@'127.0.0.1';

#Privileges for user only read one of the databases
GRANT SELECT, SHOW VIEW ON `databaseX`.* TO 'userX'@'127.0.0.1';

Yep, that’s a few tips on MySQL / MariaDB user privileges settings that I can share, I hope my writing can be useful. Thank you ^^.

SOURCE:

6.2.2 Privileges Provided by MySQL