Okay its the 2nd week of the course and its time to read Hours 4 and 5 of the MySQL course book. So what are these 2 hours about?
- Hour 4 - Using the MySQL Client
- Hour 5 - Securing MySQL
- Hour 4 - Using the MySQL Client
In this hour the aim is to become familiar with the following client tools:
- mysql - Built-in MySQL Monitor
- mysqladmin - Built-in Administrator tool
- mysqldump - Built-in backup utility tool
- phpMyAdmin - 3rd party add-on
- MySQL-Front - 3rd party add-on
- MySQLManager - Add-on
MySQL MonitorThis is a command-line utility that is shipped with MySQL. To access this utilty simply enter the following at a command prompt:mysqlIf you have not done anything to the initial installation of your server this simple command will log you into your MySQL server with full blown access permissions. This is due to the default installtion creates a root user with no password. As previously mentioned in other blog entries this is a security risk and MUST BE ADDRESS ASAP.Once the root user has a valid password, you will still be able to login to the server using the same command, but this time you also include a user and password string. See below for an example of login as the root user:mysql -u root -pThe above command will prompt you to enter the password for the root user, once this has been entered you have full access once again. Once you have logged into your server you will probably want to access a database, this is acheived by issuing a statement to the server telling it what database you wish to use. See the example statement below for the syntax:USE databasename;A full list of commands that are available to you when you are logged in can be displayed using the following:help;
How to exit the MySQL MonitorTo exit the monitor and ensure maximum security for your server always log-out correctly. This is again a simple process - just type in the following:exit;
Using mysqladminThis admin applicatins is used to create and drop databases and viewing the current server status. It should be noted that if you are using this application throught your ISP you may find that you are limited to what commands can be issued. You can obtain help by using the following:mysqladmin --help
To create a database:mysqladmin create databasename
To drop (delete) a database:mysqladmin drop databasename
Using mysqldumpThis application allows you to backup your database table structure and also create the relevant DDL scripts that can be used to recreate your entire database scheme. A extreamly useful option of this particular tool is the ability to export the schema to an external DDL file or sql script. This is performed using the following command:msqldump databasename > filename.sqlI recommend that you always use this function prior to dropping (deleting) a table/database. Like most of the command you can obtain help for this function using the following syntax:mysqldump --help
phpMyAdminThis 3rd party add-on provides all the same functions as previously discussed in this blog. It however, requires not only a MySQL server, but also PHP and a web-server. Its very popular in the web-hosting arena, as it allows users to manage their own web-space, database etc. For the purpose of this course namely TT380, I have not installed this locally, but it's on the 'To-do-list'.
MySQL-Front and MySQLManagerThese are another two excellent frontend GUI interfaces to allow you to manage your MySQL server. Again I have not installd these and its therefore unfair for me to comment on their uses etc. I do know that some course members are however using them. I instead opted to useanother 3rd party application, DBManager Professional v3.0.2. I have blogged seperately about this particular application.
This hour is aimed at teaching your about securing your installation of MySQL. MySQL is continuosly monitoring 3 key things:
- Where you are accessing from (your host)
- Who you are (username and password)
- What you are allowed to do (your privileges)
When MySQL is installed a database called mysql is automatically created and it has several tables defined within it. This database is used to record all privileges that are set on all users, fields, columns, tables and databases.
The 2 Step Authentication ProcessAs previously mention MySQL checks 3 things during its authentication process, These are performed in 2 steps:
- Step 1 -MySQL looks at the host from where you are connecting from and the username and password pair. If the host is allowed to make the connection, your password is correct for the given username and finally the username is assigned as being able to access from the said host MySQl goes onto Step 2. If this step fails an error message is given.
- Step 2 - MySQL then checks that the command that you are trying to issue is infact allowed to be issued by you on the database, table or field.
It is a common practice for applications to utilise this type of authentication, it allows for different levels of users, basic users can select data to view it whilst others are given great access allowing them to insert and delete data. Working with User Privileges Depending on you set-up will determine what permissions you have.For example if you are connecting to your ISP hosting, the chances are you will only have a single user account and one database. Your users will however, have full control allowing you to create, delete, insert as you please on you won database. On the other hand if you are running a local server for say an internal intranet, then you mau have several users and several databases. Running your own server will almost certainly require you to add, amend and even delete users. So let us now look at how you can do just that!
How to Add UsersAdding users is very easy using DBManager Professional or a similar third-party application, however, its also very easy to do via a command-line using the GRANT command. You need to access the MySQL monitor using the root user. Once logged into the server you could use a command similar to the following to create a new user:GRANT privileges
ON databasename.tablename
TO username@host
IDENTIFIED BY 'password';Obviously the privileges given would depend on the users role, a complete list of these will follow this section. The example above uses both databasename and tablename, this would allow you to restrict the users privileges to that particular table, as opposed to allowing privileges across the entire databasename [replace .tablename with .*]. The username and host I think are like the password - self explanatory.However, an additional wildcard is available to you; using % rather than the host would give way to a very large host list.The other way is to use an INSERT command statement.
Removing PrivilegesAt times it is necessary to revoke certain users privileges, again this is easierly done via any of the previously mentions third-party applications. And again its just as easy to issue the command straight from a command-line. The syntax for this command is as follows:PEVOKE privileges
ON databasename.tablename
FROM username@hostname;As with before the privileges to be revoked would depend on the user, the databasename/tablename and hostname again can also be dealt with like those mention in the GRANT command above. Changes to the privileges table take immediate effect but as a safe measure and for the sake of a few extra keystrokes it's advised to issue a FLUSH PRIVILEGES; command statement.To completely remove a user from the database for example, when an employee leaves the company, you would need to issue a special DELETE statement to remove the said user from the user table in the mysql database. So what's this special DELETE statement that I have to issue to delete a user completely I hear you say! The statement below will do just that for you:DELETE FROM mysql.userThat should delete the user specific [replace username with the relevant username] who is connected via localhost from the main mysql.user table.
WHERE User='username'
AND Host != 'localhost';
My Conclussion
Well once again we come to the end of this weeks study from the course book. Please feel free to post any comments. I will attempt to answer any questions to the best of my ability.
All the best Jayson
