MYSQL-TOOLS

Comparison of graphical tools for MySQL

Home Managing Users in MySQL

Managing Users in MySQL

In this lesson of the MySQL tutorial, you will learn...
  1. To understand how the Privilege System works in MySQL.
  2. To understand the Privilege Tables and Scopes.
  3. To manage User accounts.
  4. To learn use of GRANT and REVOKE.
  5. To evaluate and set up Privilege Levels.

Just as in normal life, not all information is intended to be accessible to all individuals. Defying the principles of pure democracy, a MySQL database is often set up to provide selective access where not everyone can view entire data store (let alone alter it).



Managing Security and Access Control

Print PDF
User Rating: / 1
PoorBest 

Just like many advanced databases on the market, MySQL offers a fine-grained and meshed system for managing user privileges. MySQL documentation calls this Access Privilege System, and the individual lists of the system are called Access Control Lists (ACLs). We will briefly cover the internal management of these lists in the tables of the mysql database.

In this lesson of the MySQL tutorial, we cover the generalities around MySQL's access-control mechanism along with the technical implementation details of this process.

The Two Stages of Access Control

In order to protect data from unintended access such as one seeing their boss's performance appraisal (or unauthorized, uncontrolled modification such as one's salary), MySQL provides a dual access control mechanism:

  • Authentication: Is the user allowed to connect to the server? At the first level, a user is authenticated for rights to access MySQL system.
  • Authorization: Does the user possess adequate privileges to execute the desired query? At the second level, MySQL identifies what Data defining or accessing actions (via SELECT, UPDATE, INSERT, DROP etc ) are permitted for different databases, tables.

One can set up security at even column levels, if required.

Because authorization cannot take place without successful authentication, you can think of this process as taking place in two stages.

Granting Privileges

You use the GRANT command when you need to assign new privileges to a user or group of users. This privilege assignment could be as trivial as letting a user connect to the database server, or as complex as giving SELECT privilege on a few columns in a certain table to users:

GRANT privilege_type 
[(column_list)] [, privilege_type [(column_list)] ...]
ON {table_name | * | *.* | database_name.*}
TO user_name [IDENTIFIED BY 'password']
[, user_name [IDENTIFIED BY 'password'] ...]
[REQUIRE {SSL|X509} [ISSUER issuer] [SUBJECT subject]]
[WITH GRANT OPTION]

Some examples are presented in the following sections to serve various needs.

As soon as a GRANT command is executed, any privileges granted in that command take effect immediately.

Revoking Privileges

The REVOKE command is responsible for deleting previously granted privileges from a user or group of users:

REVOKE privilege_type [(column_list)] 
[, privilege_type [(column_list)] ...]

ON {table_name | * | *.* | database_name.*}
FROM user_name [, user_name ...]

mysql_setpermission is Perl script for a somewhat wizard-like means for managing permissions. It offers a simple interface but is not as feature-rich as GRANT and REVOKE commands. This script is found in the MYSQL-INSTALL-DIR/bin directory, and assumes that Perl and the DBI and DBD::MySQL modules have been installed. This script is bundled only for the Linux/Unix versions of MySQL.

Managing Users

As permissions are assigned to individual users, this lesson covers the management of user entities.

Adding Users to MySQL Access Control

You can use the GRANT command to create new user accounts. Any time you issue a GRANT statement for a username and host combination that does not currently exist in the mysql.user table, a new user account is created. A row is inserted in the mysql.user table for the username and host specified in your GRANT statement. If the scope of the privileges granted in the statement is global, the user account's global permissions will be set in this new row and no other tables will receive an entry. If the scope of privileges was below the global scope, a new entry will be inserted in the grant table corresponding to the privilege level.

The IDENTIFIED BY clause of the GRANT statement allows you to specify a password for the user account:

Syntax
GRANT <permissions> ON <objects>
TO 'user'@'localhost' IDENTIFIED BY 'password';

The following command gives the user sakilaadmin on the local computer unrestricted privileges. All privileges (including Grant) are set:

Code Sample: ManageUsers/Demos/Grant-All.sql

GRANT All ON sakila.* TO sakilaadmin@'%' IDENTIFIED BY 'sakila' WITH GRANT OPTION;
Code Explanation

Here, we're creating a user sakilaadmin with considerable permissions on sakila database.

Upon execution, two privilege tables will be modified, namely the user and db tables. Because the user table is responsible for both access verification and global privileges, a new row must be inserted, identifying this user. However, all privileges for the user row will be disabled as the GRANT command is specific to the sakila database. The db table will contain the permissions to the sakila database specific to user sakilaadmin.

CREATE USER Command

You can add users with no privileges by using the CREATE USER command.

The CREATE USER command is used to create new user accounts. No privileges are assigned at the time of creation, meaning next step is to GRANT privileges to the user:

Syntax
CREATE USER user [IDENTIFIED BY [PASSWORD] 'password']
[, user [IDENTIFIED BY [PASSWORD] 'password']] ...

Code Sample: ManageUsers/Demos/Create-User.sql

CREATE USER filmupdater@localhost IDENTIFIED BY 'film';
Code Explanation

A user filmupdater is created, with no permissions.

Using this command, one can create more than one user in one command.

Adding Privileges to an Existing User

Now suppose that user filmupdater needs permissions for the sakila database. This is again accomplished with GRANT:

Code Sample: ManageUsers/Demos/Add-To-User.sql

GRANT ALL ON sakila.film TO 'filmupdater';
GRANT ALL ON sakila.film_category TO 'filmupdater';
GRANT SELECT ON sakila.category TO 'filmupdater';
Code Explanation

Add select permissions to an existing user - filmupdater.

Deleting Users

If an account is no longer needed, it is best to remove it to ensure that it can't be used for potentially illicit activity.

A common question regarding REVOKE is how it goes about deleting a user. The simple answer to this question is that it doesn't at all. For example, suppose that you revoke all privileges from a particular user, using the following command:

Code Sample: ManageUsers/Demos/Revoke-User.sql

REVOKE ALL privileges ON sakila.* FROM 'sakilauser2';
Code Explanation

Revoke all permissions from sakilauser2 user.

Although this command does indeed remove the row residing in the db table pertinent to sakilauser2's relationship with the sakila database, it does not remove that user's entry from the user table, presumably so that you could later reinstate this user.

Use the DROP USER command to delete the user and all privileges simultaneously. The DROP USER command removes all traces of the user from the privilege tables:

DROP USER user [, user]...

Code Sample: ManageUsers/Demos/Drop-User.sql

DROP USER sakilauser2;
Code Explanation

The user sakilauser2 is dropped.

Warning: In older versions of MySQL, DROP USER command could only remove accounts with no privileges, so the user may indeed continue to exist even after this command has been issued for it.

Renaming Users

Sometimes, you may want to rename an existing user via RENAME USER command:

RENAME USER old_user TO new_user
[old_user TO new_user]...

Code Sample: ManageUsers/Demos/Rename-User.sql

RENAME USER filmupdater TO filmeditor;
Code Explanation

The user filmupdater is renamed to filmeditor.

MySQL Privilege System

The general privilege control process takes place in two distinct stages: connection authentication and request verification. Let's consider a concrete example.

Tracing A Connection Request

Suppose user sakilauser connecting from a client machine named localhost and using the password sakila would like to insert a new row into the sakila.category table. MySQL first determines whether sakilauser@localhost is authorized to connect to the database, and, if so, then determines whether he's allowed to execute the INSERT request:

  1. Authentication:
    1. Does user sakilauser@localhost (referred to as just user from now on) require a secure connection? If yes, has user connected with a valid certificate? If no, deny the request and end the authentication procedure. If yes, proceed to Step 2.
    2. Determine whether the user account has exceeded the maximum allowable number of hourly connections? If yes, deny the connection. If not, determine whether the maximum number of simultaneous connections has been exceeded. If both conditions are met, proceed to Step 3. Otherwise, deny the request.
    3. Does user possess the necessary privileges to connect to the database server? If yes, proceed to Authorization. If no, deny access. This step ends the authentication component of the privilege control mechanism.
  2. Authorization:
    1. Has user exceeded the maximum number of allowable updates or queries? If no, proceed to step 2 next. Otherwise, deny the request.
    2. Does user possess global INSERT privileges? If yes, accept and execute the insertion request. If no, proceed to step 3.
    3. Does user possess INSERT privileges for the sakila database? If yes, accept and execute the insertion request. If no, proceed to Step 4.
    4. Does user possess INSERT privileges for the category table columns specified in the insertion request? If yes, accept and execute the insertion request. If no, deny the request and end the process.
  3. If all goes well, proceed with the operation.

The image below explains these steps via a picture:

MySQL Trace Security

As you can see, the security system examines privileges by starting with the very broad and ending with the very specific.

Privilege Scope Levels - Where Is Access Information Stored?

  • The MySQL privileges are organized into various scope levels, where a scope is the level at which the permission to do something is applied.
  • Most permissions apply to actions performed to a specific database, table, or even a specific column of a table.
  • MySQL arranges privileges this way to provide security administrators fine-grained control over which users can execute what requests against one database object but not another.
  • MySQL's privilege verification information is stored at five scope levels, with a corresponding table in the mysql database for each level.
  • These tables are also called the grant tables to check incoming connections and requests.
  • Specifically, six tables found in this database play an important role in the authentication and privilege verification process, as shown below.
Scope Table Description
Global scope user Determines which users can log in to the database server from which host
Database scope db,host Determines which users can access which databases . host offering additional hostnames from which a user can connect to the database server
Table scope tables_priv etermines which users can access specific tables of a particular database
Column scope columns_priv Determines which users can access specific columns of a particular table
Routine scope procs_priv Governs the use of stored procedures

Here are some key points to note about these tables:

  • To determine if a user may perform a given request, MySQL looks for privileges at the highest scope level (global) first.
  • If the needed privilege is not granted at that level, MySQL looks for the permission at the next scope level down.
  • If the privilege is found at any level, the request is granted.
  • Some privileges exist only at certain levels; others exist at more than one level.
  • If you wish to change the access privileges for all the tables of a database, the correct form to use is ON database.*. If you wish to alter global privileges, then specify ON *.*. Wild-carded names are not allowed in database names.
  • For user you can specify '' to indicate all users on a particular computer (for example, ''@computername). On the other hand, for host you must use '%' (for example, username@'%').
  • Permission-related commands update the mysql privilege tables, namely user, db, tables_priv, columns_priv, and procs_priv. (The host table remains untouched.)
  • Some privileges pertain to actions that are performed at the server level- such as the PROCESS or SHUTDOWN privilege.

This section digs into the details pertinent to the purpose and structure of each privilege table, and maps the steps above to these tables.

  1. MySQL uses the the user table to match the specified host and the user. MySQL also determines whether the user requires a secure connection to connect, and whether the number of maximum allowable connections per hour for that account has been exceeded. The execution of Step 1 completes the authentication stage of the privilege control process.
  2. Step 2 initiates the authorization stage of the privilege control process. If the connection is accepted, MySQL verifies whether the maximum allowable number of queries or updates per hour for that account has been exceeded. Next, the corresponding privileges as granted within the user table are examined. If any of these privileges are enabled (set to y), then the user has the ability to act in the capacity granted by that privilege for any database residing on that server. Of course, in most cases, all of these privileges are disabled, which causes Step 3 to occur.
  3. The db table is examined, identifying which databases this user is allowed to interact with. Any privileges enabled in this table apply to all tables within those databases that the user is allowed to interact with. If no privileges are enabled, but a matching user and host value are found, then the process jumps to Step 5. If a matching user is found, but no corresponding host value, the process moves on to Step 4.
  4. If a row in the db table is found to have a matching user but an empty host value, the host table is then examined. If a matching host value is found, the user has those privileges for that database as indicated in the host table, and not in the db table. This is intended to allow for host-specific access on a given database.
  5. Finally, if a user attempts to execute a command that has not been granted in the user, db, or host tables, the tables_priv and columns_priv tables are examined, to determine whether the user is able to execute that command on the table(s) or column(s) in question.

Note: As you saw, it is possible to impose maximum hourly connections, updates, and queries for a user, and to set the maximum number of simultaneous connections for a user.

The user Table - Global Privilege Scope

  • All privileges contained in the mysql.user table pertain to privileges available to the user on a global level, i.e. these privileges apply to all databases on the server.
  • If a privilege is granted at the global level, it will override all other scope levels. Therefore, it is imperative to verify that users receiving global privileges should indeed be allowed such access.
  • To grant a user globally scoped privileges, follow the ON keyword of the GRANT statement by *.*. Here is an example of granting the PROCESS privilege (which allows the user to use the SHOW PROCESSLIST command) to a user:
    GRANT PROCESS ON *.* TO 'sakilaadmin'@'localhost';
  • To change a table with an ALTER TABLE statement, the user must actually have the CREATE,ALTER, and INSERT permissions. Additionally, the ALTER permission allows a user to rename a table, and so is a security risk, since the current user might rename system tables (grant tables) used by MySQL in its access control.
  • The user table is the root privilege table and plays a role in both stages of the privilege request procedure. During the authentication stage, the user table is solely responsible for granting user access to the MySQL server.
  • In the request authorization stage, the user table determines whether any user granted access to the server has been assigned global privileges for working with the MySQL server, work in some capacity with all databases on that MySQL server. During this stage, the user table also determines whether the user has exceeded allocated resources, if any.
  • Warning : Avoid or limit users with global privileges in most circumstances.
  • The user table also stores administration privileges for the MySQL server. This table is used to determine which users are allowed to execute commands such as shutting down the server, reloading user privileges, and viewing and killing client processes.
Overview of the user Table

Column

Data Type

Nullable

Default

Host

char(60) binary

No

No default

User

char(16) binary

No

No default

Password

char(41) binary

No

No default

Select_priv

Insert_priv

Update_priv

...

Grant_priv

...

Create_routine_priv

...

Trigger_priv

enum('N','Y')

No

N

User Identification

MySQL identifies a user by combining username and the originating hostname. For example, sakilauser@localhost is entirely different from sakilauser@prodhr. Furthermore, MySQL will always apply the most specific set of permissions that matches the user.

The Privilege Columns

There are around 28 columns that comprise the user privilege columns. These columns represent user's global privileges. More information is available in MySQL documentation.

For example, Drop_priv: Determines whether the user can delete existing databases and tables.

The Remaining Columns

There are other user columns dedicated to SSL and to allocate user limited resources, discussed in another lesson.

Database Privilege Scope - The db Table

  • Privileges applied at the database scope level pertain to the privileges to a user on a per-database basis and all objects contained within it, including tables and routines.
  • It is looked at if the requesting user does not possess global privileges for the task being attempted. Basically, a matching Db entry is sought for the user in the db table.
  • If the User/Host/Db/ task match is not satisfied, one of two events occurs:
    • If a User/Db match is located, but the host is blank, then MySQL looks to the host table for help.
    • If a User/Host/Db triplet is located, but the privilege is disabled, MySQL next looks to the tables_priv table for help. The purpose and structure of the tables_priv table is introduced in a later section.
  • Wildcards, represented by the % and _ characters, may be used in both the Host and Db columns, but not in the User column. Like the user table, the rows are sorted so that the most specific match takes precedence over less-specific matches.

The host Table

  • The host table comes into play only if the db table's Host field is left blank.
  • You might leave the db table's Host field blank if a particular user needs access from various hosts. Rather than reproducing and maintaining several User/Host/Db instances for that user, only one is added (with a blank Host field).
  • The corresponding hosts' addresses are stored in the host table's Host field.

Working with DB Permissions

We cover some operations relating to database priviliges in this sections.

Note : The GRANT command will create a new user if no existing user account is found for the one used in the statement.

Enabling Access to a Database

The following command gives the user sam on the local computer the right to read and alter data in all tables of the database sakila, and also to lock tables, create temporary tables, and execute stored procedures (which is useful in many applications). If sam@localhost is not in the mysql.user table, then this name is added without a password. If sam@localhost already exists, then the password is not changed.

Code Sample: ManageUsers/Demos/Grant-More.sql

GRANT Select, Insert, Update, Delete, Create, Temporary Tables,
Lock Tables, Execute
ON sakila.* TO sam@localhost;

Code Explanation

The user sam is assigned several privileges.

Revoking Previously Assigned Permissions

Sometimes you need to remove one or more previously assigned privileges from a particular user. For example, we remove the INSERT, DELETE privileges from user filmeditor for the sakila database, meaning the user can read or update only:

Code Sample: ManageUsers/Demos/Revoke-DB.sql

REVOKE INSERT, DELETE ON sakila.* FROM 'filmeditor';
Code Explanation

The user filmeditor cannot INSERT or DELETE into sakila tables.

Prohibiting Changes in a Database

The next command takes away from filmreader the right to make changes to mylibrary, but filmreader retains the right to read the database using SELECT.

Code Sample: ManageUsers/Demos/Revoke-Alter.sql

REVOKE Insert, Update, Delete ON sakila.* FROM filmreader;
Code Explanation

The user filmreader can only read table in sakila database, and cannot alter them anymore.

Table Privilege Scope - The tables_priv Table

  • The tables_priv table is intended to store table-specific user privileges where privileges apply only to a specific table.
  • It comes into play only if the user, db, and host tables do not satisfy the user's task request.
  • To specify table- level privileges, you follow the ON keyword with the full name of the database table for which you are granting privileges, in the form db_name.table_name .

Granting All Privileges on a Table

When you want to grant or revoke all available privileges for a user except for the GRANT OPTION privilege at a specific privilege scope level, you can substitute the keyword ALL for the much longer and cumbersome list of privileges.

Code Sample: ManageUsers/Demos/Grant-All-On-Table.sql

GRANT All ON sakila.film TO filmeditor@localhost IDENTIFIED BY 'film';
Code Explanation

The user filmeditor has all permissions on film table in sakila database.

This would affect all table-level privileges: SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, and ALTER.

Select Access to a Table

With the following command the user filmuser on the local computer is given the right to read data from the table sakila.film:

Code Sample: ManageUsers/Demos/Grant-Table.sql

GRANT Select, Insert, Update, Delete ON sakila.film TO filmuser@localhost
 IDENTIFIED BY 'film';
GRANT Select ON sakila.film TO filmreader@localhost IDENTIFIED BY 'film';
Code Explanation

The user filmuser is assigned core privileges on film table.

The user filmreader can only read (and not alter) film table.

Revoking Table-Level Permissions

Now suppose you want to remove both the previously assigned UPDATE and INSERT privileges from user sakilauser@localhost for the table authors located in the database sakila:

Code Sample: ManageUsers/Demos/Revoke-Table.sql

REVOKE INSERT, UPDATE ON sakila.film FROM 'filmreader';
Code Explanation

The user filmuser is assigned core privileges on film table.

The user filmreader can only read (and not alter) film table.

Note: This example assumes that you've granted table-level permissions on film to user filmreader. The REVOKE command will not downgrade a database-level GRANT located in the db table, removing the entry and inserting an entry in the tables_priv table. Instead, in this case it simply removes reference to those privileges from the tables_priv table. If tables_priv table contained only only those two privileges, then the entire row is removed.

Here is another example. Suppose that user filmreader from host macstudio wants to execute an UPDATE on the table film located in the database sakila. Once the request is initiated, MySQL begins by reviewing the user table to see if filmreader@clientmac possesses global INSERT privileges. If this is not the case, the db and host tables are next reviewed for database-specific insertion privileges. If these tables do not satisfy the request, MySQL then looks to the tables_priv table to verify whether user filmreader@clientmac possesses the insertion privilege for the table film found in the sakila database.

The permissions in this table are stored as a set of Select, Insert, Update, Delete, Create, Drop, Grant, References, Index, Alter, Create view, Show view, Trigger. Column- specific privileges are a set of Select, Insert, Update, References.

Column Privilege Scope - The columns_priv Table

Privileges applied at the column scope level pertain to one or more columns within a specific table. To specify column-level privileges, use db_name.table_name along with the columns you are changing privileges for in parentheses after the privilege list.

Enabling Access to Individual Columns

Code Sample: ManageUsers/Demos/Grant-Columns.sql

GRANT SELECT(title, description),UPDATE(title, description),SELECT(rating) ON sakila.film 
TO titleeditor IDENTIFIED BY 'editor';
Code Explanation
  • The user titleeditor is defined to only read or update but not insert or delete title and description columns on film table. User can read the rating column.
  • Login as titleeditor
  • Try to read title, rental_rate from film as shown:
    SELECT title, rental_rate FROM film;
  • You should see an error like this:
    ERROR 1143 (42000): SELECT command denied to user 'titleeditor'@'localhost' for
    column 'title' in table 'film'

Revoking Column-Level Permissions

As a final revocation example, we revoke a previously granted column-level INSERT permission to user filmreader for the column rental_rate in sakila.film:

Code Sample: ManageUsers/Demos/Revoke-Column.sql

REVOKE INSERT (rental_rate) ON sakila.film FROM 'sakilauser2';
Code Explanation

The user filmreader can INSERT rental_rate value anymore.

You may see an error:

ERROR 1147 (42000): There is no such grant defined for user 'filmreader' \
on host '%' on table 'film'

Suppose we decide that the filmreader user should not be able to read the rental_rate column of the film table. Simply using the REVOKE command to remove the SELECT privilege from the user account for this column will result in an error:

MySQL is helpfully informing you that you have not specifically granted any column privileges. You may have already granted table-level rights to the film table. To remove rights to a specific column of the table, you must first revoke the table-level rights you have granted, and then grant column-specific privileges to those fields you wish the user to see.

Code Sample: ManageUsers/Demos/Revoke-Table-Grant-Column.sql

REVOKE SELECT, INSERT, UPDATE, DELETE ON sakila.film
FROM 'sakilauser2';

GRANT SELECT (film_id,title,description,rating)
ON sakila.film TO 'sakilauser2';
Code Explanation

Revoke table-level permissions and grant column-specific permissions.

Note: Each column you wish to assign rights to must be included in the column list after the SELECT privilege keyword in the GRANT statement.

The columns_priv table is responsible for setting column-specific privileges. It comes into play only if the user, db/host, and tables_priv tables are unable to determine whether the requesting user has adequate permissions to execute the requested task.

The Routine scope: The procs_priv Table

The procs_priv table governs the use of stored procedures and functions.

One can specify permissions on procedures as a set of Execute, Alter Routine, Grant.

Routine Privilege Scope

The routine privilege scope level applies to individual stored procedures and functions. You must specify the database name and the routine name when granting rights at the routine level, as in this example:

Syntax
GRANT EXECUTE ON sakila.cut_string TO 'sakilaadmin';

For the routine, column, and table-level privileges, the referenced object must be present in the database before privileges can be granted to a user for the object. This is not the case for database-level privileges. You may assign database-level privileges to a user account for a database that has not yet been created.

More details of these tables is found in MySQL documentation.

Tools for Setting the Access Privileges

MySQL provides several tools to configure access privileges:

  • Use SQL commands GRANT and REVOKE, a SQL standard mechanism which can be easily scripted and reused.
  • Use a GUI administration program such as MySQL Administrator and phpMyAdmin.
  • Alter mysql directly with INSERT and UPDATE statements.
  • For a Perl installation, one can use the Perl script mysql_setpermission.pl, simpler than GRANT and REVOKE. We will not cover this script in this course.

Needless to say, using any of these options will require a conceptual understanding of MySQL access system.

Refreshing Grant Caches

  • For reasons of speed optimization, MySQL maintains copies of the mysql tables in RAM.
  • These grant tables are loaded into memory when the database server starts.
  • One can edit the privilege tables if one has the appropriate access privileges) with the usual SQL commands INSERT, UPDATE, and DELETE commands.
  • When the grant tables are manually changed, changes are not reflected in the in-memory table data on which the server operates.
  • Direct changes to the privilege tables are effective only if they are explicitly reread by MySQL via the SQL command FLUSH PRIVILEGES or the external program mysqladmin reload.
    FLUSH PRIVILEGES;
  • Changes made through the GRANT, REVOKE and CREATE USER commands are reflected in the in-memory data immediately. It is for this reason that we recommend using the GRANT and REVOKE commands over direct manipulation of the grant tables.
  • Manual editing of privilege tables is a tiring and error-prone occupation. It is much more convenient to use the commands GRANT and REVOKE, which are the centerpiece of this section. Also convenient are graphical user interfaces such as MySQL Administrator and phpMyAdmin.
  • Modifying the mysql tables using standard SQL syntax is almost deprecated but you are not prevented from doing so.

Effecting Account Changes

As stated earlier, MySQL keeps privilege information in-memory from when the server is started. When making changes to privileges, you should be aware of when the in-memory copy of the grant tables contains the most up-to-date privilege information and when it does not.

When In-Memory Tables are Updated

In all of the following situations, the in-memory grant tables contain the most up-to-date privilege and access information:

  • After issuing a GRANT, REVOKE, CREATE USER, or DROP USER statement
  • After issuing the FLUSH PRIVILEGES statement
  • Immediately after the server starts and before any requests are made to the mysql database

If, however, you alter the mysql grant tables directly, as is necessary when altering mysql.host or deleting a user account before version 4.1.1 of MySQL, the in- memory copies of the privilege tables will not contain the most current information, and you should immediately issue a FLUSH PRIVILEGES statement to make the changes current.

Insert into user Table

Another way to add new user accounts is to insert rows directly into the mysql.user table. This is a convenient way to add multiple users at once and is a good programmatic aid, but this process is not recommended for general use, where explicit GRANT command serves better.

Code Sample: ManageUsers/Demos/Insert-User.sql

INSERT INTO mysql.user  SET Host='localhost', User='filmreader',
Password=PASSWORD('reader'), Select_priv='Y';
Code Explanation

Here, we're creating a user sakilaadmin with considerable permissions on sakila database.

We use the PASSWORD() function to encrypt the password.

Note: If you do insert directly into mysql.user, note the password supplied in the IDENTIFIED BY clause is actually encrypted in the mysql.user grant table. If you add the row to the user table directly, you must use the PASSWORD() function to encrypt the password. Otherwise, the connecting user would not be able to access the server, as the supplied password would be encrypted and compared to the (plain-text) Password column value in the user table.

If you're sure a user will not be required in the future, you may manually remove the row from mysql.user table, using the DELETE command.

Code Sample: ManageUsers/Demos/Delete-User.sql

DELETE FROM mysql.user WHERE user='sakilauser2' AND Host='%';
FLUSH PRIVILEGES;
Code Explanation

Here, we're manually deleting the entry for sakilauser2, and issuing FLUSH PRIVILEGES to ensure that changes are reflected in the in-memory copy of the grant tables.

Reviewing User Privileges

MySQL provides several methods to obtain information on a user's privileges. Here, we'll cover using the SHOW GRANTS command and querying the grant tables directly. One may use the more modern and compliant INFORMATION_SCHEMA virtual database, which is covered in another lesson.

Using SHOW GRANTS

One way to check a user's grants is to use the SHOW GRANTS statement:

Syntax
SHOW GRANTS FOR username;

This command will list the privileges available to the user as ready-to-execute GRANT statements. This list comprises the user's authorization information with the encrypted password and the privileges granted at the global, database, table, and column levels.

Code Sample: ManageUsers/Demos/Show-Grants.sql

SHOW GRANTS FOR 'sakilauser2';
Code Explanation

Show various permissions for sakilauser2.

Code Sample: ManageUsers/Demos/Show-Current-Grants.sql

SHOW GRANTS FOR CURRENT_USER();
Code Explanation

Show various permissions for currently logged-in user in the session.

As with the GRANT and REVOKE commands, you must make reference to both the username and the originating host in order to uniquely identify the target user when using the SHOW GRANTS command.

Listing Conflicts

You may notice some thing peculiar in the grants list. It is likely that the privileges for a user filmreader@localhost on a global level completely negate the need for the SELECT privilege on the sakilakubili database.

So, why does MySQL list both entries?

This conflict shows as MySQL does not remove grant the more specific entries just because a more generic or relaxed privilege level has been granted to the user. Keep this in mind when changing user privileges, where if you remove the global privileges, the specific privileges will continue to exist.

Querying the Grant Tables

Another option for determining a user's privileges involves querying the actual grant tables . To see global permissions for sakilauser2, query the user grant table:

Code Sample: ManageUsers/Demos/Select-User.sql

SELECT * FROM mysql.user
WHERE User = 'sakilauser2';
Code Explanation

Show user-level permissions for a user.

Here, you can see all user privileges with their status.

Querying other grant tables will produce similar output for each of the privilege scope levels. The user and db tables store privilege information in separate fields of type ENUM('Y','N'). The tables_priv, columns_priv, and procs_priv grant tables store privilege information in a single SET() field containing a list of the available privileges. Here is the output of a SELECT on the tables_priv table:

Code Sample: ManageUsers/Demos/Select-Table-Priv.sql

SELECT Db, Table_name, Table_priv FROM mysql.tables_priv
WHERE User = 'sakilauser2' AND Host LIKE '%';
Code Explanation

Show table-level permissions for sakilauser2.

The list shows table-specific privileges with their status.

The GRANT OPTION Privilege

A special privilege provides users with the ability to grant privileges to other users. If the GRANT statement is issued with the WITH GRANT OPTION clause, it means that the user has the special privilege to grant other users the same privileges they have.

Warning: There are very few reasons to have users with WITH GRANT OPTION clause. It is a security headache and User privileges should be granted by very few individuals, preferably only one. This ensures consistency and conformity to sakila security policies.

Grant to All Users from a Host

In this example, all users from the host *.webucator.com are permitted to link to MySQL if they know the password xxx. The privilege Usage means that all global privileges have been set to N. The users thereby at first have no privileges whatsoever (to the extent that so far no individual databases, tables, or columns have been made accessible to all users who can log into MySQL):

Code Sample: ManageUsers/Demos/Grant-All-To-Host.sql

GRANT SELECT ON sakilakubili.film TO ''@'%.webucator.com' IDENTIFIED BY 'web';
Code Explanation

Grant specific permissions to all users from a given host.

Managing Users in MySQL Conclusion

In this lesson of the MySQL tutorial, we reviewed management of user privileges and access control of MySQL databases.

To continue to learn MySQL go to the top of this page and click on the next lesson in this MySQL Tutorial's Table of Contents.
 


mysql tools