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:
0 Comments