Until more sophisticated User Role-type controls are added to MySQL, developers will have to use GRANT and REVOKE statements to manage user privileges, or the Administrative Roles options provided in MySQL Workbench. Troubleshooting table-creation glitches in MySQL can be the source of much developer frustration, particularly when trying to assign privileges in a single database.
MySQL is not noted for the ease with which you can determine which users can access which features and functions. As Database Journal’s Rob Gravelle explains in a February 13, 2014, article, SQL-type User Role controls were originally anticipated in MySQL 5.0, but Oracle has postponed the feature to MySQL 7.0.
Gravelle describes three tools that add User Roles to MySQL: Google’s aptly named google_mysql_tools, the Open Source project SecuRich, and MySQL Workbench, whose Administrative Roles feature is described below. (Note that google_msql_tools are written in Python and thus require the MySQLdb connector.)
The MySQL Reference Manual presents the basics on how to use MySQL’s GRANT statements to assign privileges to user accounts, including access to secure connections and server resources. As you might expect, the REVOKE statement is used to revoke privileges. The typical scenario is to create an account using CREATE USER, and then define its privileges and characteristics using GRANT.
The standard method of assigning user privileges in MySQL is to use the GRANT statement. Source: MySQL Reference Manual
Privileges can be granted globally using “ON *.*” syntax, at the database level using “ON db_name.*”, at the table level using “ON db_name.tbl_name”, and at the column level using the following syntax:
Assign user privileges at the column level in MySQL by enclosing the column or columns within parentheses. Source: MySQL Reference Manual
Other privileges apply to stored routines and proxy users. The “WITH” clause is used to allow one user to grant privileges to other users, to limit the user’s access to resources, and to require that a user use secure connections in a specific way.
Assigning Administrative Roles via MySQL Workbench
Applying roles to users in MySQL Workbench is as easy as selecting the user account, choosing the Administrative Roles tab, and checking the boxes, as shown in the image below.
MySQL Workbench’s Administrative Roles tab lets you assign user privileges by checking the appropriate boxes. Source: MySQL Reference Manual
Likewise, choose the Schema Privileges tab to assign such privileges as the ability to create temporary tables.
The inability to create tables can be a thorny problem for MySQL developers. A Stack Overflow post from February 2011 highlights several possible solutions to a recalcitrant create-table command. The first proposed solution is to grant all privileges via “GRANT ALL PRIVILEGES ON mydb* TO ‘myuser’@’%’ WITH GRANT OPTION;”. Such a “Super User” account is not recommended for production databases, however, nor for granting privileges on a single database.
Alternatively, you could use the following syntax to limit the privilege to a particular database:
Grant a MySQL user the ability to create tables in a single database by using the “@%” and “@localhost” qualifiers. Source: Stack Overflow
A similar problem encountered when trying to allow MySQL users to create tables is presented in a Stack Exchange post from July 2014. The developer wants the user to be able to create, update, and delete tables, but to be prevented from changing the password or viewing all the records in the database. (The default setting in MySQL allows users to change their own passwords, but only administrators can change other users’ passwords.)
Using MySQL Workbench, you can open the Users and Privileges options and create a role that has no administrative privileges but “all object rights” and “DLL rights” for the specific schema. Limiting users to a single schema prevents them from viewing or changing any other table except the information_schema administrative schema.
Much of the pain of managing MySQL, MongoDB, Redis, and ElasticSearch databases is mitigated by using the Morpheus database-as-a-service. Morpheus lets you provision, deploy, and host your databases in just seconds using a simple point-and-click interface, backups are provided for MySQL and Redis databases.
Morpheus is the first and only DBaaS that supports SQL, NoSQL, and in-memory databases. The service lets you use a range of database tools for connecting, configuring, and managing your databases. Visit the Morpheus site for to create a free account.
