How to View User Privileges of a MySQL Database Using PHP

In this article, we show how to view the privileges of a database user (of MySQL) using PHP

By seeing the privileges that a user has, you can find out many, many things. You can find out whether the user has access to all the databases or maybe just one database. You can find out whether the user has global privileges, privileges to just one database, privileges to just a table on one database, or privilege to simply a column of a certain table on a database. Then you can what Data Manipulation Statements a user can execute such as SELECT, UPDTE, INSERT, and DELTE statements.

All of this can be visible when you view the privileges of a database user.

We can show the privileges of a database user using the following MySQL query.

SHOW GRANTS FOR user;

So the above is the MySQL code to view a database user's privileges.

We'll now show how to tie this in with PHP.

<?php $user = "user"; $password = "password"; $host = "host"; $connection= mysql_connect ($host, $user, $password); if (!$connection) { die ('Could not connect:' . mysql_error()); } $result= mysql_query("SHOW GRANTS FOR username"); while($row=mysql_fetch_array($result)){ echo $row[0]; } ?>

So, first, we must log on to the database server.

After we do this, we create a variable named $result. This $result variable is set equal to a mysql_query() function that has the parameter "SHOW GRANTS FOR username". This allows us to view all the privileges granted to the username. PHP.

If a user has global privileges with no restrictions, you'll see a statement that looks similar to the following shown below.

GRANT USAGE ON *.* TO 'user' IDENTIFIED BY PASSWORD 'password' GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE ON `database`.* TO 'user'

Below is a table that summarizes the 4 types of privileges that a user can have as far as the privilege level is concerned.

Privilege Level	Description	Example
Global	All databases and all tables	*.*
Database	All tables on the specified database	customer_database
Table	All columns on the specified table	CustomerOrders_table
Column	Only the specified column or columns	(email) CustomerOrders_table

Related Resources

How to Show the Structure of a MySQL Table Using PHP

How to Display a MySQL Table of a Database using PHP

How to Display a MySQL Table Without Duplicate Entries Using PHP