Home >> FAQs/Tutorials >> MySQL Tutorials >> Index

MySQL FAQs - Managing User Accounts and Access Privileges

By: FYIcenter.com

Part:   1  2   3  4  5  6 

(Continued from previous part...)

How To Test a New User Account and Password?

If you have new user created with a password, you test it using "mysql" program with the "-u" and "-p" options. The following tutorial exercise shows you some interesting use cases of connecting to the server with user names and passwords:

>cd \mysql\bin
>mysql -u dev
ERROR 1045 (28000): Access denied for user 'dev'@'localhost'
 (using password: NO)

>mysql -u dev -pTEST
ERROR 1045 (28000): Access denied for user 'dev'@'localhost'
 (using password: YES)

>mysql -u dev -pretneciyf
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 39 to server version: 5.0.24-community

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test;
Database changed

mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| articles       |
| links          |
+----------------+
2 rows in set (0.04 sec)

How To Change the Password for Your Own User Account?

If you want to change the password of your own user account, you should use your user account connect to the server "mysql" first. You should then use the "SET PASSWORD ..." command to change the password of the current user account. There are two ways to use this command to set a new password and store it in an encrypted format:

SET PASSWORD = 'encryptedPasswordString';
SET PASSWORD = PASSWORD('passwordString');

Notice that PASSWORD() is a system function to encrypt the specified password. Here is good tutorial exercise to show you how to set a new password:

>cd \mysql\bin
>mysql -u root mysql

mysql> SET PASSWORD = 'retneciyf';
ERROR 1372 (HY000): Password hash should be a 41-digit 
 hexadecimal number

mysql> SET PASSWORD = PASSWORD('retneciyf');
Query OK, 0 rows affected (0.56 sec)

mysql> SELECT User, Password, Shutdown_priv FROM user;
+------+-------------------------------------------+-----+
| User | Password                                  | ... |
+------+-------------------------------------------+-----+
| root | *3735F1D8464342BA852E10101E55E422EBAAAF35 | Y   |
| dev  | *3735F1D8464342BA852E10101E55E422EBAAAF35 | N   |
+------+-------------------------------------------+-----+
2 rows in set (0.00 sec)

How To Change the Password of Another User Account?

If you want to change the password of someone else's user account, you can connect to the server as "root" and use the "SET PASSWORD FOR userName ..." command to change the password of another user account. There are two ways to use this command to set a new password and store it in an encrypted format:

SET PASSWORD FOR userName = 'encryptedPasswordString';
SET PASSWORD FOR userName = PASSWORD('passwordString');

Notice that PASSWORD() is a system function to encrypt the specified password. Here is good tutorial exercise to show you how to set a new password:

>cd \mysql\bin
>mysql -u root -pretneciyf mysql

mysql> SET PASSWORD FOR dev = 'iyf';
ERROR 1372 (HY000): Password hash should be a 41-digit 
 hexadecimal number

mysql> SET PASSWORD FOR dev = PASSWORD('iyf');
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT User, Password, Shutdown_priv FROM user;
+------+-------------------------------------------+-----+
| User | Password                                  | ... |
+------+-------------------------------------------+-----+
| root | *3735F1D8464342BA852E10101E55E422EBAAAF35 | Y   |
| dev  | *446BD97F308FBA0B9CBDC962C2842AD338BCE59E | N   |
+------+-------------------------------------------+-----+
2 rows in set (0.00 sec)

(Continued on next part...)

Part:   1  2   3  4  5  6