Task:
Verify and repair user permissions (grants) to a database for a user.
Example User and Database:
User: abc123@localhost
Database: abcdb
Steps:
1. Login to server and MariaDB console
$ ssh myadminid@myserver.mindwatering.net
<enter password if not using private key>
$ sudo su -
# mysql -u root -p <reallyhardpassword>
2. Verify User and Grants:
a. Confirm Users:
MariaDB [none]> SELECT user FROM mysql.user;
<view list of users, and confirm abc123 is in the list>
b. Confirm User Grants:
MariaDB [none]> SHOW GRANTS FOR abc123@localhost
<view grants>
e.g.
+----------------------------------------------------------------------------------------------------------------+
| Grants for abc123@localhost |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `c10dbwp`@`localhost` IDENTIFIED BY PASSWORD '*ABCD...1234F' |
+----------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)
Note:
- In this case, the user's grant to the actual database is not listed.
- Entry missing is: GRANT ALL PRIVILEGES ON `abcdb`.* TO `abc123`@`localhost`
- The user was never added to the db. Is this automation failure, wrong person added, or supposed to be?
2. Add/grant the user access to the database:
a. View Databases and confirm database is listed:
MariaDB [none]> SHOW DATABASES;
<view list>
e.g.
+--------------------+
| Database |
+--------------------+
...
| abc123db |
...
+--------------------+
121 rows in set (0.001 sec)
b. Add the user grant:
- If the user should have most rights to alter normal database objects (power db user or db admin access), modify the list of rights below and grant the rest to the user:
MariaDB [none]> GRANT alter,create,delete,drop,index,insert,select,update,trigger,alter routine,execute, create temporary tables on abcdb.* to 'abc123'@'localhost';
<view output, Query OK, 0 rows affected (0.110 sec)>
MariaDB [none]> FLUSH PRIVILEGES;
- If the user should have ALL rights (not as typical):
MariaDB [none]> GRANT ALL PRIVILEGES ON 'abcdb'.* TO 'abc123'@'localhost';
<view output, Query OK, 0 rows affected (0.110 sec)>
MariaDB [none]> FLUSH PRIVILEGES;
Note:
- If you wish to remove all privileges and reset access, to none, so you can add back limited access:
MariaDB [none]> REVOKE ALL PRIVILEGES ON abcdb.* FROM 'abc123'@'localhost';
<view output, Query OK, 0 rows affected (0.110 sec)>
MariaDB [none]> FLUSH PRIVILEGES;
previous page
|