Suppose we want to allow user 'dario' to access the server via hosts 'localhost' and 'www.devshed.com', which lies on the 'dv1' host. He wants to access the database 'pasta' strictly from 'localhost', but he wants to access the database 'chicken' from both hosts. Finally, he wants to use the password 'mamamia'.
Step 1: Set up the host table (assuming it has not yet been set up)
The host table is of considerable importance when administering larger networks, yet it needs to be configured for every server. Assuming you have just one server, you will have to insert just two hostnames, the localhost, and your server name. Otherwise, you will have to list each server that you would like to give access to the MySQL server.
Step 2: Update the 'user' table, for reason of granting access to a new host+user combination.
Step 3: Update the database (db) table.
Step 4: Create the necessary tables, using mysqladmin.
Exit from the MySQL server (q). Now, you must use a tool called mysqladmin to create the actual database.
Don't forget to do the same thing for the 'chicken' database!
Step 5: Use mysqladmin again.
After all necessary modifications have been made, the command mysqladmin reload must be executed. If you do not execute this command, the changes will not take effect.
That's all there is to it. Dario, not working on the 'localhost' and entering the server via telnet, should be able to access the pasta and chicken databases. However, if Dario enters via host 'www.devshed.com', he will only be able to enter the chicken database. If he attempts to enter the pasta database, and error will occur.
FAQ: Why do I have to enter "password('mamamia')" instead of just "mamamia" into the password variable of the 'user' table?
This is because MySQL, like any security-minded server, stores the password encrypted. Thus, to allow the user to continue to use the password 'mamamia', it must be entered as above.
FAQ: Previously you stated that there were 10 privileges within the user table, but in the example you only listed 6. Why?
This is because the default of every privilege is 'N'. Thus, if it is not listed within the insert command, it is considered to be 'NO' ('N').
FAQ: What happens if I leave host or db empty?
Both 'host' and 'db' can handle wildcards. Thus, if one or the other are left empty (''), it will be entered as '%'. This is obviously dangerous, unless you really know what you are doing. Therefore, be especially careful when entering data into these tables.
FAQ:What if I want to delete the inserted user and db info?
Easy. Just follow the above instructions, except using the delete syntax instead of the insert. Finally, use mysqladmin to delete the database. Don't forget to execute 'mysqladmin reload' after you're done.
FAQ: Isn't there an easier way to do this?
Believe it or not, yes. There are a number of programs included along with the MySQL distribution (within the contrib directory), including xmysqladmin, mysql_webadmin, mysqladmin and even xmysql to modify values within the privilege tables..
For more tips you may also read our article on MySQL Grant Tables
As noted in the above example, one uses the MySQLAdmin to carry out very important administrative tasks, such as finalizing modifications on the server, and creating databases. At the UNIX command line, try typing:
A list of commands will scroll down the screen. These commands are carried out as the 'reload' or 'create databasename'. BE CAREFUL of these commands, as they are capable of erasing or shutting down the database server. However, be sure to study these commands carefully, as they are indispensable to running MySQL administration.
You now should be able to add (and thus modify and delete information from the privilege tables. Yet how do you enter these databases, and what are some security measures you can take to keep the 'bad guys' out? This is the subject of the next section.
blog comments powered by Disqus