MySQL for Python : Creating Users and Granting Access - Removing users in MySQL, DROPping users in Python, Granting access in Python

5/8/2013 9:07:51 PM

1. Removing users in MySQL

As with creating databases and tables, the opposite of creating a user is to DROP. As we shall see, removing a user does not revert any changes that they have made to the database(s) to which they had access. If a user had the ability to create users, removing them will not remove the users they created.

Unlike databases and tables, dropping a user requires that you also specify the hostname of the user's record. Therefore, one cannot always enter:

DROP USER exemplar;

This will only work if the user was created without specifying the hostname.

If it exists, one must include the hostname. For best practice, the basic syntax is:

DROP USER <userid>@<hostname>;

Therefore to drop user exemplar, we would pass the following statement:

DROP USER 'exemplar'@@'localhost';

Note that this will not impact that user's ability to log in from another host if that user had permission to connect from the other host.

DROP, by design, only removes the user's account and its privileges. It does not in any way affect any database objects that the user created. Therefore, if a user has created false database structures such as databases, tables, and records, then all of that will persist after the user is removed from the system.

One very important aspect of DROP that is critical to remember is that DROP does not impact on existing user sessions. If a user is logged into the server when the DROP statement is issued, the DROP statement will not take effect until the user has logged out. The user's subsequent attempts at logging in will then fail.

2. DROPping users in Python

Dropping a user in Python is as easy as passing the MySQL statement through Cursor.execute(). So the syntax is:

DROP USER exemplar@localhost;

This previous syntax can be changed to:

mydb = MySQLdb.connect(host = 'localhost',
user = 'root',
passwd = 'rootsecret')
cursor = mydb.cursor()
statement = """DROP USER exemplar@localhost"""

However, any part of the statement can be dynamically created through the use of string formatting conventions.

3. Granting access in Python

Using MySQLdb to enable user privileges is not more difficult than doing so in MySQL itself. As with creating and dropping users, we simply need to form the statement and pass it to MySQL through the appropriate cursor.

As with the native interface to MySQL, we only have as much authority in Python as our login allows. Therefore, if the credentials with which a cursor is created has not been given the GRANT option, an error will be thrown by MySQL and MySQLdb, subsequently.

Assuming that user skipper has the GRANT option as well as the other necessary privileges, we can use the following code to create a new user, set that user's password, and grant that user privileges:

#!/usr/bin/env python
import MySQLdb
host = 'localhost'
user = 'skipper'
passwd = 'secret'
mydb = MySQLdb.connect(host, user, passwd)
cursor = mydb.cursor()
mkuser = 'symphony'
creation = "CREATE USER %s@'%s'" %(mkuser, host)
results = cursor.execute(creation)
print "User creation returned", results
mkpass = 'n0n3wp4ss'
setpass = "SET PASSWORD FOR '%s'@'%s' = PASSWORD('%s')" %(mkuser, host, mkpass)
results = cursor.execute(setpass)
print "Setting of password returned", results
granting = "GRANT ALL ON *.* TO '%s'@'%s'" %(mkuser, host)
results = cursor.execute(granting)
print "Granting of privileges returned", results
except MySQLdb.Error, e:
print e		 
Top 10 Video Game
-   Minecraft Mods - MAD PACK #10 'NETHER DOOM!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
-   Minecraft Mods - MAD PACK #9 'KING SLIME!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
-   Minecraft Mods - MAD PACK #2 'LAVA LOBBERS!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
-   Minecraft Mods - MAD PACK #3 'OBSIDIAN LONGSWORD!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
-   Total War: Warhammer [PC] Demigryph Trailer
-   Minecraft | MINIONS MOVIE MOD! (Despicable Me, Minions Movie)
-   Minecraft | Crazy Craft 3.0 - Ep 3! "TITANS ATTACK"
-   Minecraft | Crazy Craft 3.0 - Ep 2! "THIEVING FROM THE CRAZIES"
-   Minecraft | MORPH HIDE AND SEEK - Minions Despicable Me Mod
-   Minecraft | Dream Craft - Star Wars Modded Survival Ep 92 "IS JOE DEAD?!"
-   Minecraft | Dream Craft - Star Wars Modded Survival Ep 93 "JEDI STRIKE BACK"
-   Minecraft | Dream Craft - Star Wars Modded Survival Ep 94 "TATOOINE PLANET DESTRUCTION"
-   Minecraft | Dream Craft - Star Wars Modded Survival Ep 95 "TATOOINE CAPTIVES"
-   Hitman [PS4/XOne/PC] Alpha Gameplay Trailer
-   Satellite Reign [PC] Release Date Trailer