1. Removing privileges in MySQL
To remove privileges that have been granted, one uses the REVOKE statement. One uses the same information to revoke privileges as to grant them:
The kinds of privileges to be revoked
The database and table involved
The user ID
The hostname used in granting the privilege
As with dropping and creating a user, a pattern matching hostname of % does not include localhost. That host must be revoked explicitly.
Basic syntax
The REVOKE command has the following basic syntax:
REVOKE <privileges> ON <database>.<table> FROM '<userid>'@'<hostname>';
So to revoke all access for user tempo to the City table of the world database when logged in locally, we would use the following statement:
REVOKE ALL PRIVILEGES ON world.City FROM 'tempo'@'localhost';
If we want to revoke only INSERT privileges for remote access, we would adapt the preceding statement accordingly:
REVOKE INSERT ON world.City FROM 'tempo'@'%';
Again, it is important to remember that the following two lines affect different records in the MySQL user table:
REVOKE ALL PRIVILEGES ON world.City FROM 'tempo'@'localhost';
REVOKE ALL PRIVILEGES ON world.City FROM 'tempo'@'%';
After using REVOKE, the user still has access!?
All administrative changes in MySQL are applied to
MySQL's internal databases. Therefore, any change that is effected, only
takes effect the next time MySQL needs to read those administrative
tables. Consequently, users can still have access to databases or tables
after the revocation statement has been issued.
Often, administrative changes can wait until the user
logs out. However, even then, it can take a while for the changes to
take effect. Depending on how frequently MySQL reads the administrative
tables, the change may not take effect even if you manually remove the
permissions from the administrative tables that govern privileges (columns_priv, procs_priv, and tables_priv). Within MySQL, one can pass the following command:
If one's login has RELOAD privileges.
If time is of the essence, however, and you want to
force MySQL to re-read all of the administrative tables, you may want to
restart it. In Linux and other Unix variants, execute the following
with root privileges:
/etc/init.d/mysql restart
From Windows:
1. Click Start | Control Panel | Administrative Controls | Services.
3. Right click then select Restart under Options.
Currently, there is no interface available to restart MySQL from Python without issuing OS-specific commands (that is, using the os
module). This is not a tenable development strategy as the Python
program would need to run with administrator privileges (an obvious
security problem). However, Mats Kindahl, lead developer at MySQL, has
started MySQL Replicant, a project designed for replicating MySQL
servers, but that incidentally should include administrative tasks such
as starting, restarting, and stopping.
2. Using REVOKE in Python
As with GRANT, revoking privileges in Python
just depends on forming the statement. As seen in this code, a revision
of the earlier example, the REVOKE statement uses similar context to and all the same information as the GRANT statement:
#!/usr/bin/env python
import MySQLdb
host = 'localhost'
user = 'skipper'
passwd = 'secret'
mydb = MySQLdb.connect(host, user, passwd)
cursor = mydb.cursor()
try:
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
granting = "REVOKE ALL PRIVILEGES ON *.* FROM '%s'@'%s'" %(mkuser, host)
results = cursor.execute(granting)
print "Revoking of privileges returned", results
except MySQLdb.Error, e:
print e
