34,333
edits
Changes
→Changing the Password for a MySQL User
SET PASSWORD FOR 'johnB'@'localhost' = Password('newpassword');
</pre>
== User Privileges ==
A newly created user can log into the MySQL server but by default has no privileges to do anything once connected. The next task after creating a new user account, therefore, is to add privileges to the account. This is achieved using the ''GRANT'' statement.
Before modifying a user's privileges it can be helpful to see what privileges are already set. This can be performed using the ''SHOW GRANTS'' statement in conjunction with the user's account name. For example:
<pre>
SHOW GRANTS FOR 'johnB'@'localhost';
+-------------------------------------------------------------------------------------+
| Grants for johnB@localhost |
+-------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'johnB'@'localhost' IDENTIFIED BY PASSWORD '3a9eb1070a0130ca' |
+-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
</pre>
The statement 'USAGE ON *.*' indicates that the user has no privileges on any database or table. Simply put, the user cannot do anything once logged into the database server.
To add a privilege, for example permission to query any table in a database named MySampleDB we, would issue the following command:
<pre>
GRANT SELECT on MySampleDB.* TO 'johnB'@'localhost';
</pre>
Once executed, the above statement will enable user 'johnB' to perform SELECT statements on any table contained in the MySampleDB database. Similarly we could enable johnB to INSERT rows into a table called product contained in the MySampleDB database as follows:
<pre>
GRANT INSERT on MySampleDB.product TO 'johnB'@'localhost';
</pre>
It is also perfectly valid to specify multiple privileges in a single GRANT statement, for example:
<pre>
GRANT INSERT, UPDATE on MySampleDB.product TO 'johnB'@'localhost';
</pre>
MySQL supports a wide range of privileges which are outlined in the following table:
<table>
<tr>
<td>Setting</td>
<td>Descritpion</td>
</tr>
<tr>
<td>ALL [PRIVILEGES]</td>
<td>Sets all simple privileges except GRANT OPTION
</td>
</tr>
<tr>
<td>ALTER</td>
<td>Enable the use of ALTER TABLE statement
</td>
</tr>
<tr>
<td>ALTER ROUTINE</td>
<td>Enable stored routines to be altered or dropped</td>
</tr>
<tr>
<td>CREATE</td>
<td>Enable the use of CREATE TABLE statement
</td>
</tr>
<tr>
<td>CREATE ROUTINE</td>
<td>Enable creation of stored routines</td>
</tr>
<tr>
<td>CREATE TEMPORARY TABLES</td>
<td>Enable the use of CREATE TEMPORARY TABLE statement
</td>
</tr>
<tr>
<td>CREATE USER</td>
<td>Enable the use of CREATE USER, DROP
USER, RENAME USER, and
REVOKE ALL PRIVILEGES.</td>
</tr>
<tr>
<td>CREATE VIEW</td>
<td>Enable the use of CREATE VIEW statement
</td>
</tr>
<tr>
<td>DELETE</td>
<td>Enable the use of DELETE statement
</td>
</tr>
<tr>
<td>DROP</td>
<td>Enable the use of DROP TABLE statement
</td>
</tr>
<tr>
<td>EXECUTE</td>
<td>Enable the user to execute stored routines</td>
</tr>
<tr>
<td>FILE</td>
<td>Enable the use of SELECT ... INTO OUTFILE and
LOAD DATA INFILE
</td>
</tr>
<tr>
<td>INDEX</td>
<td>Enable the use of CREATE INDEX and DROP
INDEX
</td>
</tr>
<tr>
<td>INSERT</td>
<td>Enable the use of INSERT
</td>
</tr>
<tr>
<td>LOCK TABLES</td>
<td>Enable the use of LOCK TABLES on tables for which the user
has the SELECT privilege</td>
</tr>
<tr>
<td>PROCESS</td>
<td>Enable the user to see all processes with SHOW
PROCESSLIST
</td>
</tr>
<tr>
<td>REFERENCES</td>
<td>Not implemented</td>
</tr>
<tr>
<td>RELOAD</td>
<td>Enable the use of the FLUSH statement
</td>
</tr>
<tr>
<td>REPLICATION CLIENT</td>
<td>Enable the user to ask for slave or master server locations</td>
</tr>
<tr>
<td>REPLICATION SLAVE</td>
<td>Needed for replication slaves (reads binary log events from the
master)</td>
</tr>
<tr>
<td>SELECT</td>
<td>Enable the use of SELECT
</td>
</tr>
<tr>
<td>SHOW DATABASES</td>
<td>
SHOW DATABASES shows all databases</td>
</tr>
<tr>
<td>SHOW VIEW</td>
<td>Enable the use of SHOW CREATE VIEW
</td>
</tr>
<tr>
<td>SHUTDOWN</td>
<td>Enable the use of mysqladmin shutdown
</td>
</tr>
<tr>
<td>SUPER</td>
<td>Enable the use of CHANGE MASTER,
KILL, PURGE MASTER
LOGS, and SET GLOBAL
statements, the mysqladmin debug
command; allows single connection if
max_connections is reached</td>
</tr>
<tr>
<td>UPDATE</td>
<td>Enable the use of UPDATE
</td>
</tr>
<tr>
<td>USAGE</td>
<td>Synonym for no privileges</td>
</tr>
<tr>
<td>GRANT OPTION</td>
<td>Enable privileges to be granted</td>
</tr>
</table>
