Database Reference
In-Depth Information
When you've finished making changes to the initial batch of users, you should flush the
user privileges so that the new passwords will take effect. Enter the following from the
command line:
mysqladmin -u root -p flush-privileges
From this point on, you'll have to use the new password for the user,
root
.
Creating a User
The next step regardingusers is to create at least one user for general use. It's best not to
use therootuser for general database management. To create another user, enter com-
mands like:
mysql -u root -p -e "GRANT USAGE ON *.*
TO '
russell
'@'localhost'
IDENTIFIED BY '
Rover#My_1st_Dog&Not_Yours!
';"
These lines create the user
russell
and allow him to access MySQL from the localhost.
The
*.*
means all databases and all tables. We'll cover this in more depth later in the
book. The statement also sets his password as
Rover#My_1st_Dog&Not_Yours!
.
This user has no privileges, actually: he can't even view the databases, much less enter
data. When you set up a new user, you should consider which privileges to allow the user.
If you want her to be able only to view data, enter something like the following from the
command line:
mysql -u root -p -e "GRANT SELECT ON *.* TO '
russell
'@'localhost';"
In this line, the user
russell
may use only the
SELECT
statement, a command for
viewing data. If you would like to see the privileges granted to a user, you could enter
something like this from the command line:
mysql -u root -p -e "SHOW GRANTS FOR '
russell
@'localhost' \G"
*************************** 1. row ***************************
Grants for russell@localhost:
GRANT SELECT ON *.* TO 'russell'@'localhost'
IDENTIFIED BY PASSWORD '*B1A8D5415ACE5AB4BBAC120EC1D17766B8EFF1A1'
These results show that the user is granted only privileges to use the
SELECT
statement
for viewing data. We'll cover this in more depth later in the topic. Notice that the pass-
word is returned encrypted. There's no way to retrieve someone's password unencrypted
from MySQL.
