MySQL Console

MySQL creating users and setting permissions using MySQL console

MySQL is a multi-user database, you can set up several different accounts on the same server with many varied permissions given to each user. This ability is part of what makes MySQL so powerful. You can use either MySQL console or phpMyAdmin to create users and grant permissions. This page focuses on using MySQL console. Not intended as a definitive guide but more of an introduction to show you how to access and run MySQL console within the UniServer environment.

MySQL Privileges

MySQL privileges allow each user to access and utilize only areas they need to perform their tasks. This prevents a user from accidentally accessing an area where he or she is not allowed access this adds to the security of the MySQL server.

When connecting to a MySQL server, the host from which connection is made and a user name specified determines that users identity. This identity informs MySQL to allow access to the server and to assign privileges defined for this user.

MySQL console application

MySQL console is an integral application of MYSQL you run this application from a command prompt on the machine that is running your MySQL server.

I have assumed you are using Uniform Server defaults that is drive W and MySQL user name=root and password=root. (Important before going live remember to change your MySQL root password )

With both Apache and MySQL, servers running start a command window and navigate to where mysql.exe is located.

Command window

There are two ways to start a command window:

• Start > click on Run > type cmd click OK

Alternatively

• Click Start > All Programs > Accessories > click Command Prompt

Either method opens a command window the flashing curssor is refered to as the command prompt.

Navigate to mysql.exe

Type following lines into the command prompt: W: cd usr\local\mysql\bin This selects the folder where you run MySQL console

Dialogue in the command prompt looks similar to this: Microsoft Windows XP [Version 5.1.2600] (C) Copyright 1985-2001 Microsoft Corp. C:\Documents and Settings\mpg>w: W:\>cd usr\local\mysql\bin W:\usr\local\mysql\bin>_

Start MySQL console

To start MySQL console, at the command prompt, type the following: mysql –uroot –proot Note 1: Do not copy and paste you must type it in. -u stands for user name. Immediately followed by user name (no spaces) -p stands for user password. Immediately followed by password (no spaces) Note 2: The mysql> prompt indicates you are in MySQL monitor and it is ready to receive your commands. Note 3: Quit the monitor by typing exit

On successful connection you will receive a message similar to this: W:\usr\local\mysql\bin>mysql -uroot -proot Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 216 Server version: 5.0.41-community-nt MySQL Community Edition (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql>

To create users and set permissions, you must have the necessary permissions to do so. By default, Uniform Server's MySQL has such an account named root with password root (remember to change this) and all required permissions. It is constrained to run on local host access only.

Top

Setting up a new user

We are now ready to setup some MySQL user accounts. The statements that follow are entered in at the MySQL monitor (console) prompt. Be sure to include all of the quotations in the statements and to end each statement with a semicolon.

Example 1

For our first example, we have a user named mpg3 with a password mpg3abc we trust this person so will grant all privileges. He wants a database named mpg3_db accessible from local host.

Start MySQL monitor and enter:

create database mpg3_db;

followed by:

grant all privileges on mpg3_db.* to mpg3@"localhost" identified by 'mpg3abc';

• First line creates a database named mpg3_db. This step can be left out if the database already exists.
• Second line sets up the user and what privileges the user is allowed to use. Looking at this line in greater detail.

• grant all privileges on mpg3_db.* Specifies the database (mp3_db) a user will be allowed to access. The .* indicates that this user is permitted to work with all of the tables within the database mpg_db. (You can limit the user to one table, the name of that table should be specified after the period in place of the asterisk.
• mpg3@"localhost" This sets up the user information. The user named mpg3 is being created. The portion after the @ indicates the host from which this user is allowed to connect. In this case, this user can only connect from the localhost.
• mpg3abc This is the password to be used by the user. (Always use a password that contains both random alpha and numeric characters.)

Example 2

For our second example we have a user named mpg4 with a password mpg4abc we trust this person hence will grant all privileges. He wants a database named mpg4_db accessible from any host.

Start MySQL monitor and enter:

create database mpg4_db;

followed by:

grant all privileges on *.* to mpg4@"localhost" identified by 'mpg4abc';

As with the previous example, lets look at the statement piece by piece.

• *.* This indicates that the user will be permitted to connect to all of the MySQL databases and all of the tables contained in those databases.
• mpg4@"%" This sets up the user information. The user named mpg4 is being created. This time a host of "%" is used. This indicates that this user may connect to the database from any host or IP number. A more secure way to do this is to specify the IP number of the user, however sometimes this is not possible.
• 'mpg4abc' As in the previous example sets the password.

Top

Reducing User Privileges

In both of the above examples, we created users with all privileges granted on their respective database(s). If the user is connecting from the Internet or a remote workstation, reduce these privileges or kiss your database good bye. Use the following statement to reduce privileges:

grant select,insert,update,delete,create,drop on mpg3_db.* to mpg3@"%" identified by 'mpg3abc';

With this statement, the user mpg3 is allowed limited access to the database mpg3_db from any host ("%") using the password mpg3abc.

The grant statement specifies that this user is only allowed to run a limited amount of statements on the MySQL server. This user will be allowed to: select records, insert records, update records, delete records, create databases, and DROP (delete) DATABASEs. More importantly, this user is not permitted to create users and set privileges.

You could further reduce a user's privileges by removing other items from the grant statement. Users should only be given permission to access the functions that are necessary to perform their tasks and not to compromise your server.

Top

Refresh the MySQL grant tables

Information you have changed or created when executing the above statements is stored in a database called mysql. this database was automatically created during MySQL installation. It contains several tables that hold all of the information pertaining to each user, the databases they have access to, the hosts they can connect from, and the privileges allowed for each.

The only time MySQL reads data in these tables is when it first boots (runs). It does not read those tables again unless it is told to do so. Without this step, none of your newly created users or changes to existing users will take effect.

There are several ways to reload the privilege tables, you can stop and re-start the MySQL server or issue the following command from MySQL console:

flush privileges;

This instructs the MySQL server to take another look at the user tables and hence puts all of your new users and privileges into operation.

Top

That extra database

Have you discovered an extra database you cannot remove! This page explains why information_schema.

Back to MySQL User Accounts MySQL user accounts

I am not one for typing my preference is to use phpMyAdmin.

There are times when the power of MySQL console comes into it's own Lost Password

Top

---

Ric