Skip to content
Related Articles

Related Articles

Grant MySQL table and column permissions using Python
  • Last Updated : 26 Nov, 2020

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. The CREATE 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 is geeksforgeeks and password for login is 1234

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:

Default Privileges

Note: To grant permissions to the user geeksforgeesks you must be logged into root account. Users can’t grant permissions to themselves. 

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

Python3

filter_none

edit
close

play_arrow

link
brightness_4
code

# import reqired module
import pymysql
  
  
# establish connection to MySQL
connection = pymysql.connect(
    
    # specify host
    host='localhost',
    
    # specify root account
    user='root',
      
    # specify password for root account
    password='1234',
      
    # default port number is 3306 fro MySQL
    port=3306
)
  
  
# make a cursor to run sql queries
mycursor = connection.cursor()
  
# granting all permissions on all databases and their 
# tables of geeksforgeeks user permission also includes 
# table and column grants
mycursor.execute("Grant all on *.* to geeksforgeeks@localhost")
  
# print all privileges of geeksforgeeks user
mycursor.execute("Show grants for geeksforgeeks@localhost")
result = mycursor.fetchall()
print(result)
  
# commit privileges
mycursor.execute("Flush Privileges")
  
# close connection to MySQL
connection.close()

chevron_right


Output

Python Output

MySQL Terminal

MySQL output

We could see that permissions to CREATE and ALTER MySQL tables have been provided to user geeksforgeeks.

Attention geek! Strengthen your foundations with the Python Programming Foundation Course and learn the basics.

To begin with, your interview preparations Enhance your Data Structures concepts with the Python DS Course.

My Personal Notes arrow_drop_up
Recommended Articles
Page :