Posted by: jasonk2600 | November 13, 2008

Quickie HowTo: Creating a New User in MySQL

Creating a user in MySQL is fairly straight forward, using standard SQL.  Users are created using the GRANT command.  The syntax for GRANT in simple terms in as follows:

GRANT (privileges here) ON (which databases and tables) TO ‘(username)‘@’(hostname of IP address)‘ IDENTIFIED BY ‘(password)‘ WITH (special options);

The IDENTIFIED BY and the WITH options are not required.  Below are a few examples.

First log into the MySQL server with an account that has the necessary rights to create a user; root in this example.

# mysql -u root -p

The following example will create a user named Charlie with all privileges on all tables when connecting from the IP address 10.0.1.1, with a password of ‘MyS3cur3PasswD’, and also having the option to grant other privileges to other users:

mysql> GRANT ALL PRIVILEGES ON *.* TO 'Charlie'@'10.0.1.1' -> IDENTIFIED BY 'MyS3cur3PasswD' WITH GRANT OPTION;

The next example creates a user named Guest with only the USAGE privilege on the guestdb database with no password, connecting from any computer.

mysql> GRANT USAGE ON guestdb.* TO 'Guest'@'%';

For more information on using the GRANT command and creating users in MySQL, see the MySQL Reference Manual by following the link below:

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: