MySQL user accounts and information are stored in a MySQL database named mysql. You usually do not need to access the mysql database and tables directly (as you will soon see), but sometimes you might. One of those times is when you want to obtain a list of all user accounts. To do that, use the following code: • Input USE mysql; SELECT user FROM user; • Output +------+ | user | +------+ | root | +------+ • Analysis The mysql database contains a table named user which contains all user accounts. user contains a column named user that contains the user login name. A newly installed server might have a single user listed (as seen here); established servers will likely have far more. Tip Test Using Multiple Clients The easiest way to test changes made to user accounts and rights is to open multiple database clients (multiple copies of the mysql command-line utility, for example), one logged in with the administrative login and the others logged in as the users being tested. Creating User AccountsTo create a new user account, use the CREATE USER statement, as seen here: • Input CREATE USER ben IDENTIFIED BY 'p@$$w0rd'; • Analysis CREATE USER creates a new user account. A password need not be specified at user account creation time, but this example does specify a password using IDENTIFIED BY 'p@$$w0rd'. If you were to list the user accounts again, you'd see the new account listed in the output. Tip Specifying a Hashed Password The password specified by IDENTIFIED BY is plain text that MySQL will encrypt before saving it in the user table. To specify the password as a hashed value, use IDENTIFIED BY PASSWORD instead. Note Using GRANT or INSERT The GRANT statement (which we will get to shortly) can also create user accounts, but generally CREATE USER is the cleanest and simplest syntax. In addition, it is possible to add users by inserting rows into user directly, but to be safe this is generally not recommended. The tables used by MySQL to store user account information (as well as table schemas and more) are extremely important, and any damage to them could seriously harm the MySQL server. As such, it is always better to use tags and functions to manipulate these tables as opposed to manipulating them directly. To rename a user account, use the RENAME USER statement like this: • Input RENAME USER ben TO bforta; Note Pre MySQL 5 RENAME USER is only supported in MySQL 5 or later. To rename a user in earlier versions of MySQL, use UPDATE to update the user table directly. Deleting User AccountsTo delete a user account (along with any associated rights and privileges), use the DROP USER statement as seen here: • Input DROP USER bforta; Note Pre MySQL 5 As of MySQL 5, DROP USER deletes user accounts and all associated account rights and privileges. Prior to MySQL 5 DROP USER could only be used to drop user accounts with no associated account rights and privileges. As such, if you are using an older version of MySQL you will need to first remove associated account rights and privileges using REVOKE, and then use DROP USER to delete the actual account. Setting Access RightsWith user accounts created, you must next assign access rights and privileges. Newly created user accounts have no access at all. They can log into MySQL but will see no data and will be unable to perform any database operations. To see the rights granted to a user account, use SHOW GRANTS FOR as seen in this example: • Input SHOW GRANTS FOR bforta; • Output +-------------------------------------------------+ | Grants for bforta@% | +-------------------------------------------------+ | GRANT USAGE ON *.* TO 'bforta'@'%' | +-------------------------------------------------+ • Analysis The output shows that user bforta has a single right granted, USAGE ON *.*. USAGE means no rights at all (not overly intuitive, I know), so the results mean no rights to anything on any database and any table. Note Users Are Defined As user@host MySQL privileges are defined using a combination of user name and hostname. If no host name is specified, a default hostname of % is used (effectively granting access to the user regardless of the hostname). To set rights the GRANT statement is used. At a minimum, GRANT requires that you specify
The following example demonstrates the use of GRANT: • Input GRANT SELECT ON crashcourse.* TO beforta; • Analysis This GRANT allows the use of SELECT on crashcourse.* (crashcourse database, all tables). By granting SELECT access only, user bforta has read-only access to all data in the crashcourse database. SHOW GRANTS reflects this change: • Input SHOW GRANTS FOR bforta; • Output +-------------------------------------------------+ | Grants for bforta@% | +-------------------------------------------------+ | GRANT USAGE ON *.* TO 'bforta'@'%' | | GRANT SELECT ON 'crashcourse'.* TO 'bforta'@'%' | +-------------------------------------------------+ • Analysis Each GRANT adds (or updates) a permission statement for the user. MySQL reads all of the grants and determines the rights and permissions based on them. The opposite of GRANT is REVOKE, which is used to revoke specific rights and permissions. Here is an example: • Input REVOKE SELECT ON crashcourse.* FROM beforta; • Analysis This REVOKE statement takes away the SELECT access just granted to user bforta. The access being revoked must exist or an error will be thrown. GRANT and REVOKE can be used to control access at several levels:
Table 28.1 lists each of the rights and privileges that may be granted or revoked.
Using GRANT and REVOKE in conjunction with the privileges listed in Table 28.1, you have complete control over what users can and cannot do with your precious data. Note Granting for the Future When using GRANT and REVOKE, the user account must exist, but the objects being referred to need not. This allows administrators to design and implement security before databases and tables are even created. A side effect of this is that if a database or table is removed (with a DROP statement) any associated access rights will still exist. And if the database or table is re-created in the future, those rights will apply to them. Tip Simplifying Multiple Grants Multiple GRANT statements may be strung together by listing the privileges and comma delimiting them, as seen in this example: GRANT SELECT, INSERT ON crashcourse.* TO beforta; Changing PasswordsTo change user passwords use the SET PASSWORD statement. New passwords must be encrypted as seen here: • Input SET PASSWORD FOR bforta = Password('n3w p@$$w0rd'); • Analysis SET PASSWORD updates a user password. The new password must be encrypted by being passed to the Password() function. SET PASSWORD can also be used to set your own password: • Input SET PASSWORD = Password('n3w p@$$w0rd'); • Analysis When no user name is specified, SET PASSWORD updates the password for the currently logged in user. |