How to View User Privileges of a MySQL Database Using PHP 


               


How to View User Privileges of a MySQL Database Using PHP



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

HTML Comment Box is loading comments...