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.
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.
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