Grant MySQL table and column permissions using Python

Last Updated :20 May, 2021

MySQL server is an open-source relational database management system that is a major support for web-based applications. Databases and related tables are the main component of many websites and applications as the data is stored and exchanged over the web. In order to access MySQL databases from a web server, we use various modules in Python such as PyMySQL, mysql.connector, etc.

In this article, we are going to grant permissions to a user in accessing a database and its MySQL tables. TheCREATE USER statement creates a user account with no privileges. The statement for creating a user in MySQL is given below.

CREATE USER 'user_name'@'localhost' IDENTIFIED BY 'password';

The above user can log in into MySQL Server, but cannot do anything such as querying data and selecting a database from tables. In our case, user_name isgeeksforgeeks andpassword for login is1234.

To change the user in MySQL client, use the below command:

SYSTEM MYSQL -u geeksforgeeks -p1234;

To check the current user, one can use the below command:

SELECT user();

The above statement could be used to know the permissions of the user.

SHOW GRANTS FOR user_name@localhost;

See the below example:

Note:To grant permissions to the usergeeksforgeesks you must be logged intoroot account. Users can't grant permissions to themselves.

Below is the python program to add table and column permissions to the usergeeksforgeeks:

Python3

# import required moduleimportpymysql# establish connection to MySQLconnection=pymysql.connect(# specify hosthost='localhost',# specify root accountuser='root',# specify password for root accountpassword='1234',# default port number is 3306 fro MySQLport=3306)# make a cursor to run sql queriesmycursor=connection.cursor()# granting all permissions on all databases and their# tables of geeksforgeeks user permission also includes# table and column grantsmycursor.execute("Grant all on *.* to geeksforgeeks@localhost")# print all privileges of geeksforgeeks usermycursor.execute("Show grants for geeksforgeeks@localhost")result=mycursor.fetchall()print(result)# commit privilegesmycursor.execute("Flush Privileges")# close connection to MySQLconnection.close()