Learning Mysql

$ mysqld_safe --skip-grant-tables
Now, anyone can have access to the server with maximum privileges and without a password. Be very careful—it’s wise to disconnect your system from the network while you’re doing this Connect to the server with the MySQL monitor program mysql
(You don’t need to specify any user or password, since without the grant tables, MySQL
can’t enforce any authentication. Then immediately re-enable the grant tables so that the authentication details will be checked if anyone else tries to connect to the server:
mysql> FLUSH PRIVILEGES;
You can then reset the root user password using the SQL query:
mysql> UPDATE mysql.user SET Password = PASSWORD('new_password') WHERE User = 'root';
or alternatively:
mysql> SET PASSWORD for 'root'@'localhost'=PASSWORD('the_new_mysql_root_password');
Now, tell MySQL to put the new privileges into effect:
mysql> FLUSH PRIVILEGES;
and exit the monitor:
mysql> QUIT
You can now restart the server normally.
Exercises
1. What’s the difference between a local and a remote user. When would you grant only read access to a user. Write a
GRANT
statement to create a user, rowena
, who has privileges to execute
SELECT
, UPDATE, and
INSERT
statements on the contacts and appointment databases.
The user should be allowed to access the server from machines in the domain
invyhome.com.
4. Write a
GRANT
statement that modifies the privileges of the user rowena created in
Question 3. Add privileges to
SELECT
from the customer table in the sales database,
and to
SELECT
the debtor column from the invoice table in the accounts database. Three
GRANT
statements have been issued on your MySQL server:
GRANT ALL ONTO 'hugh'@'hugh.invyhome.com';
GRANT SELECT, UPDATE, INSERT, DELETE ONTO 'hugh'@'*.invyhome.com';
GRANT SELECT ONTO ''@'localhost';
For each of the following attempts to connect to the server, state whether the connection is allowed and, if so, which user the client is connected as. Assume all connections are attempted from localhost
:
Exercises | 349

•
mysql --user=hugh --host=localhost
•
mysql --user=fred
•
mysql
6. You’ve been employed to evaluate the security of a MySQL installation. Assuming that you’re already satisfied with the security configuration from the physical and operating system perspective, list four things that you’d check about the MySQL
server. For each item, explain why you would check it and what you would expect the outcome to be. You’ve recently installed a wireless access point for visitors to your office and configured it so that machines that connect through it have IP addresses in the range to 192.168.1.254. You’ve decided you want users who connect to your
MySQL server from those IP addresses to have only the
SELECT
privilege on the contacts database. What steps do you take in your MySQL privilege tables to set this up?

Download 4.24 Mb.

Share with your friends: