How to Add and Change MySQL Passwords and Privileges
Passwords in MySQL aren’t set in stone. You can add or change a password for an existing account. Like many procedures, you can add or change passwords with an SQL statement, like this:
SET PASSWORD FOR username@hostname = PASSWORD('password')
The account is set to password for the account username@hostname. If the account currently has a password, the password is changed. You don’t need to specify the FOR clause. If you don’t, the password is set for the account you’re currently using.
You can remove a password by sending the SET PASSWORD statement with an empty password:
SET PASSWORD FOR username@hostname = PASSWORD('')
When you make changes to passwords, you need to refresh the privileges so that MySQL sees the change. This is accomplished with the FLUSH PRIVILEGES statement:
Each account has a set of privileges that specifies what the user of the account can and can’t do. You can set the privileges when you create an account, but you can also change the privileges of an account at any time.
You can see the current privileges for an account by sending the following statement:
SHOW GRANTS ON accountname@hostname
The output is a GRANT statement that would create the current account. The output shows all the current privileges. If you don’t include the ON clause, you see the current privileges for the account that issued the SHOW GRANTS query.
You can change privileges for an account with the GRANT statement, which has the following general format:
GRANT privilege (columns) ON tablename TO accountname@hostname IDENTIFIED BY 'password'
Like other privilege-related changes, you need to refresh the privileges after making changes using FLUSH PRIVILEGES.
You can also create a new account or change a password with the GRANT statement. You need to fill in the following information:
privilege (columns): You must list at least one privilege. You can limit each privilege to one or more columns by listing the column name in parentheses following the privilege. If you don’t list a column name, the privilege is granted on all columns in the table(s). You can list as many privileges and columns as needed, separated by commas. For instance, a GRANT statement might start with this:
GRANT select (firstName,lastName), update, insert (birthdate) ...
tablename: The name (or names) of the table(s) on which the privilege is granted. You need to include at least one table. You can list several tables, separated by commas. The possible values for tablename are
tablename: The entire table named tablename in the current database. You can use an asterisk (*) to mean all tables in the current database. If you use an asterisk and no current database is selected, the privilege is granted to all tables on all databases.
databasename.tablename: The entire table named tablename in databasename. You can use an asterisk (*) for either the database name or the table name to mean all databases or tables. Using *.* grants the privilege on all tables in all databases.
accountname@hostname: If the account already exists, it’s given the indicated privileges. If the account doesn’t exist, it’s added. The account is identified by the accountname and the hostname as a pair. If an account exists with the specified account name but a different hostname, the existing account isn’t changed; a new one is created.
password: The password that you’re adding or changing. A password isn’t required. If you don’t want to add or change a password for this account, leave out the phrase IDENTIFIED BY ‘password’.
For example, the GRANT statement that adds a new account for use in the PHP scripts for an online catalog database named ProductCatalog might be
GRANT select ON ProductCatalog.* TO phpuser@localhost IDENTIFIED BY 'A41!14a!'
To remove privileges, use the REVOKE statement. The general format is
REVOKE privilege (columns) ON tablename FROM accountname@hostname
You need to fill in the appropriate information.
You can remove all the privileges for an account with the following REVOKE statement:
REVOKE all ON *.* FROM accountname@hostname