Are you looking for an easy way to check all database grants for a user in your MariaDB or MySQL database server?. If you don’t know allowed IP address for source traffic checking GRANTS can be challenging task.
The GRANT MySQL statement is used by system administrators to grant privileges and roles. This can be granted to a user account and a role.
Example commands used in granting privileges;
GRANT ALL ON db1.* TO 'cloudspinx'@'localhost';
GRANT 'role1', 'role2' TO 'user1'@'localhost', 'user2'@'localhost';
GRANT SELECT ON world.* TO 'role3';To show grants for cloudspinx@localhost , you will run below MySQL command.
mysql> SHOW GRANTS FOR 'cloudspinx'@'localhost';
+---------------------------------------------------------------------+
| Grants for cloudspinx@localhost |
+---------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `cloudspinx`@`localhost` |
| GRANT SELECT, INSERT, UPDATE ON `db1`.* TO `cloudspinx`@`localhost` |
+---------------------------------------------------------------------+In this article, we show you a simpler way to check all database grants, or for a specific user. This operation is simplified by command line tool pt-show-grants which is part of Percona Toolkit.
Percona Toolkit is a collection of advanced command-line tools used by Percona support staff to perform a variety of MySQL support tasks. We will install this toolkit and take advantage of it.
Add Percona Toolkit repository
Configure Percona repositories that contains the Percona Toolkit package.
Ubuntu / Debian:
sudo apt update && sudo apt install curl
curl -O https://repo.percona.com/apt/percona-release_latest.generic_all.deb
sudo apt install gnupg2 lsb-release ./percona-release_latest.generic_all.debRHEL / CentOS / Rocky / AlmaLinux:
sudo yum install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpmInstall Percona Toolkit
Next we install Percona Toolkit from the repositories we just configured.
Ubuntu / Debian:
sudo apt update
sudo apt install percona-toolkitRHEL / CentOS / Rocky / AlmaLinux:
sudo yum -y install percona-toolkitInstallation can also be done manually without configuring the repository.
### RHEL Based Systems ###
wget percona.com/get/percona-toolkit.rpm
sudo yum localinstall ./percona-toolkit.rpm
### Debian Based Systems ###
wget percona.com/get/percona-toolkit.deb
sudo apt install ./percona-toolkit.debBut the advantage of the repository is that it gives you access to all Percona products in the repo.
To test this is working you can run pt-mysql-summary command.
$ sudo pt-mysql-summary
# Percona Toolkit MySQL Summary Report #######################
System time | 2024-06-19 08:12:23 UTC (local TZ: EAT +0300)
# Instances ##################################################
Port Data Directory Nice OOM Socket
===== ========================== ==== === ======
# MySQL Executable ###########################################
# Slave Hosts ################################################
*************************** 1. row ***************************
Server_id: 102
Host: dbslave01
Port: 3306
Master_id: 101
# Report On Port 3306 ########################################
User | root@localhost
Time | 2024-06-19 11:12:23 (EAT)
Hostname | dbmaster01
Version | 10.8.8-MariaDB-log MariaDB Server
Built On | Linux x86_64
Started | 2024-06-14 05:30 (up 5+05:42:17)
Databases | 8
Datadir | /var/lib/mysql/
Processes | 125 connected, 2 running
Replication | Is not a slave, has 1 slaves connected
Pidfile | /var/lib/mysql/dbmaster01.pid (exists)
# Processlist ################################################
Command COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
Binlog Dump 1 1 450000 450000
Query 1 1 0 0
Sleep 125 0 500 60
User COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
mysqld_exporter 2 0 0 0
replica_user 1 1 450000 450000
root 1 1 0 0It gives you a summarize of MySQL information in nicer print out.
Show User Grants using pt-show-grants
The pt-show-grants command is used to canonicalize and print MySQL grants.
To see command usage help, run:
pt-show-grants --helpMost use options are:
--ask-passPrompt for a password when connecting to MySQL--database=s -DThe database to use for the connection--host=s -hConnect to given host IP address or hostname--only=aOnly show grants for this comma-separated list of users--password=s -pPassword to use when connecting, password for given user--port=i -PPort number to use for connection (MySQL/MariaDB listening port)--user=s -uUser for login if not current user
Let’ see some examples:
1 – Show grants for all users in the database
Show grants for all users while connecting to database as root user using socket authentication
pt-show-grants --host localhost --user rootIf you need to pass root user password then add --ask-pass option
pt-show-grants --host localhost --user root --ask-pass
2 – Show grants for a single user in the database
Specify the username using --only option.
$ pt-show-grants --host localhost --user root --ask-pass --only cinder
-- Grants dumped by pt-show-grants
-- Dumped from server Localhost via UNIX socket, MySQL 10.5.22-MariaDB at 2024-06-19 11:27:10
-- Grants for 'cinder'@'%'
CREATE USER IF NOT EXISTS `cinder`@`%`;
ALTER USER `cinder`@`%` IDENTIFIED BY PASSWORD '*E1F098DFF0DBE7EE80AA84D09DF4A35AB27B13D3';
GRANT ALL PRIVILEGES ON `cinder`.* TO `cinder`@`%`;
GRANT USAGE ON *.* TO `cinder`@`%` ;3 – Show grants for a multiple users specified
We user --only but with comma-separated list of users.
$ pt-show-grants --host localhost --user root --ask-pass --only cinder,nova,neutron
-- Grants dumped by pt-show-grants
-- Dumped from server Localhost via UNIX socket, MySQL 10.5.22-MariaDB at 2024-06-19 11:29:56
-- Grants for 'cinder'@'%'
CREATE USER IF NOT EXISTS `cinder`@`%`;
ALTER USER `cinder`@`%` IDENTIFIED BY PASSWORD '*E1F098DFF0DBE7EE80AA84806DC4A35AB27B13D3';
GRANT ALL PRIVILEGES ON `cinder`.* TO `cinder`@`%`;
GRANT USAGE ON *.* TO `cinder`@`%` ;
-- Grants for 'neutron'@'%'
CREATE USER IF NOT EXISTS `neutron`@`%`;
ALTER USER `neutron`@`%` IDENTIFIED BY PASSWORD '*CA2703D066CC858826092CBF87C497C418A1DBA1';
GRANT ALL PRIVILEGES ON `neutron`.* TO `neutron`@`%`;
GRANT USAGE ON *.* TO `neutron`@`%` ;
-- Grants for 'nova'@'%'
CREATE USER IF NOT EXISTS `nova`@`%`;
ALTER USER `nova`@`%` IDENTIFIED BY PASSWORD '*42DBF1FCD0B3FB2D0892DEF11BF14ADEADE973A4';
GRANT ALL PRIVILEGES ON `nova_cell0`.* TO `nova`@`%`;
GRANT ALL PRIVILEGES ON `nova`.* TO `nova`@`%`;
GRANT USAGE ON *.* TO `nova`@`%` ;4 – Show grants on a remote server
Connect to remote host using --host option.
pt-show-grants --host 192.168.1.20 --user root --ask-pass5 – Provide password in command line interface
If you don’t want to be prompted every time you are connecting, provide the user password with the commands.
pt-show-grants --host localhost --user root --password StrongDBPasswordOur Engineers are always available to help you with a day to day management of MySQL or MariaDB database server.
