Create a MySQL user account

Contributed by qmchenry

October 12, 2003

Tagged: MySQL

It is a good security policy to allow least privileges. Allowing all access to a system through a single account with all abilities is typically dangerous. Creating MySQL user accounts allows privileges to be granted as appropriate.

To create a user jsmith with password Secret15 and allow them to do anything with the database named accounts, connect to the database with mysql and issue the command:

grant all on accounts.* to jsmith@localhost identified by 'Secret15';

Previous recipe | Next recipe |

Viewing 8 Comments

Anonymous 4 years ago 1 point
Please login to rate.

Do you already have an account? Log in and claim this comment.

Don't forget to "flush privileges" afterwards.

reply edit reblog flag

Warlock 4 years ago 1 point
Please login to rate.

Do you already have an account? Log in and claim this comment.

grant all on accounts.* to jsmith@localhost identified by 'Secret15';

This only work's for the localhost how do i create an account that can be accesd by any ip and not from the same pc of the server ???? 8O

reply edit reblog flag

qmchenry 4 years ago 1 point
Please login to rate.

Do you already have an account? Log in and claim this comment.

You can specify the hostname or IP address that is allowed access in place of localhost in this command. So, if you want to be able to connect from a host named 'workstation' (10.100.15.2), use one of these:

grant all on accounts.* to jsmith@workstation identified by 'Secret15';
grant all on accounts.* to jsmith@10.100.15.2 identified by 'Secret15';

A dubious alternative, one with serious security consequences (you've been warned) is to allow access to jsmith from anywhere:

grant all on accounts.* to jsmith@% identified by 'Secret15';

In this context, the % symbol is a wildcard permitting access from any host. An important note - from my experience, it is necessary to create the localhost access control in addition to a specific host or global access control. If you do not, connections from localhost may not function properly.

reply edit reblog flag

Anonymous 4 years ago 1 point
Please login to rate.

Do you already have an account? Log in and claim this comment.

<ul id="quote"><h6>Anonymous wrote:</h6>Don't forget to "flush privileges" afterwards.</ul>

I read that flushing privileges is only neccessary if you use insert instead of grant:

"The reason for using FLUSH PRIVILEGES when you create accounts with INSERT is to tell the server to re-read the grant tables. Otherwise, the changes will go unnoticed until you restart the server. With GRANT, FLUSH PRIVILEGES is unnecessary."

Quote from http://dev.mysql.com/doc/mysql/en/Adding_users.html

reply edit reblog flag

upender 4 years ago 1 point
Please login to rate.

Do you already have an account? Log in and claim this comment.

<ul id="quote"><h6>Anonymous wrote:</h6>Don't forget to "flush privileges" afterwards.</ul>

reply edit reblog flag

Anonymous 3 years ago 1 point
Please login to rate.

Do you already have an account? Log in and claim this comment.

<ul id="quote"><h6>Anonymous wrote:</h6></ul><ul id="quote"><h6>Anonymous wrote:</h6>Don't forget to "flush privileges" afterwards.</ul>

I read that flushing privileges is only neccessary if you use insert instead of grant:

"The reason for using FLUSH PRIVILEGES when you create accounts with INSERT is to tell the server to re-read the grant tables. Otherwise, the changes will go unnoticed until you restart the server. With GRANT, FLUSH PRIVILEGES is unnecessary."

Quote from http://dev.mysql.com/doc/mysql/en/Adding_users.html

reply edit reblog flag

Kenny Vadas 3 years ago 1 point
Please login to rate.

Do you already have an account? Log in and claim this comment.

This is very nice.. and good... Kenny Vadas

reply edit reblog flag

Anonymous 3 years ago 1 point
Please login to rate.

Do you already have an account? Log in and claim this comment.

<ul id="quote"><h6>Kenny Vadas wrote:</h6>This is very nice.. and good... Kenny Vadas</ul>

reply edit reblog flag