Creating Users and Setting Permissions in MySQL - Opening the MySQL console application

(Page 2 of 4 )

There are two major ways to run the MySQL console. You can either sit down at the physical server, or you can telnet into the server and run the application remotely. In either situation, you will need to be at the command prompt of the machine that is running the MySQL server.

The next most logical step is to determine the directory where MySQL is installed. On most Unix and Linux based installations, MySQL is located in the /usr/local/bin directory. On a Windows server, MySQL is most likely found under c:/mysql/. If you are having difficulty locating MySQL, check with your installation’s documentation.

To create users and set permissions, you have to run the console as a user that has permission to do so. By default, MySQL is installed with a root account with these permissions. Typically this account has no password when connecting from the localhost.

To start the MySQL console, at the command prompt, change to the MySQL directory and run the console by typing "mysql –u=root". This opens the MySQL console as the user root. If all goes well, you will be greeted by something similar to the following prompt:

Welcome to the mysql monitor. Commands end with ; or \g.
Your mysql connection id is 4 to server version 3.23.36

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql>

It is not always necessary to specify the user as root, however, it has been included here to help ensure the proper connection to the MySQL console. If you are having trouble or a root password has also been specified you can try to launch mysql with the following revised command: "mysql –u=root –p=passwrd". Keep in mind that you should replace passwrd with your actual root password.

Setting up a new user
Now we are ready to setup some MySQL user accounts. Let's run through a few GRANT statements and discuss in detail what they are doing. These statements should be entered in at the MySQL console prompt. Be sure to include all of the quotations in the statements and to end each statement with a semicolon.

mysql> create database somedb
-> grant all privileges on somedb.* to someusr@"localhost" identified by 'passwrd';

The first part of this statement creates a database called somedb. This step can be left out if the database that you want to use already exists. The second part is the actual statement that sets up the user and the privileges held by that user. Lets look at it in greater detail.

somedb.*

This specifies the database for which the user created will be allowed to access. The .* indicates that this user will be permitted to work with all of the tables within the database somedb. If you are interested in limiting the user to only one table, then the name of that table should be specified after the period in place of the asterisk.

someusr@"localhost"

This is what actually sets up the user information. The someusr is the username that 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.

passwrd

This is the password to be used by the user. It is suggested that you use something more creative than the one indicated here.

Connecting from any host
In the previous example, we created a user that could only connect to a specific database from the local machine. In this example, we loosen things up a bit and allow the user a little more freedom. Here is the statement:

mysql> grant all privileges on *.* to someusr@"%"
> identified by 'passwrd';

As with the previous example, lets take a 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.

someusr@"%"

The user is again specified as someusr, however, 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 your workstation, however, sometimes this is not possible depending on your network setup.

As in the previous example, the password is set as passwrd.

Next: Reducing User Privileges >>
 

More MySQL Articles
More By Ryan Schwiebert

Comments On: Creating Users and Setting Permissions in MySQL

>>> Be the FIRST to comment on this article!