Adding new users to MySQL Databases

Posted by Steve on Fri 5 Nov 2004 at 16:44

Despite lacking a lot of features MySQL is one of the most popular database servers available for GNU/Linux platforms. Part of the attraction is that it's much simpler to setup for a shared hosting system. This small HOWTO shows how to add new users to a MySQL system and keep their databases seperate from each other.

Many webhosting companies will offer people who signup with them a single MySQL database for their use. This would allow their customers to run a single database based application, anything from a DVD database to an online photo gallery.

If you have a server of your own it makes a lot of sense to replicate this setup - for each database application you wish to use create a specific database to hold its data and create a dedicated user to access it.

This means that if the application is vulnerable to a security problem only the single database is compromised.

Other reasons to create new users are to allow other users to share your database - if you have a virtual server for example.

Creating a new database and associated user involves using the mysql client command.

When you install the mysql-server package on Debian you will by default end up with a superuser account setup for the database server root with an empty password.

Hopefully you've changed that afterwards.

If you want to create a new user paul with a database that they have full control over we will run the following commands:

Note that 'mysql>' is the command prompt for the client program mysql, its not something you must type yourself.

#
# Connect to the local database server as user root
# You will be prompted for a password.
#
mysql -h localhost  -u root -p

#
# Now we see the 'mysql>' prompt and we can run
# the following to create a new database for Paul.
#
mysql> create database pauldb;
Query OK, 1 row affected (0.00 sec)

#
# Now we create the user paul and give him full 
# permissions on the new database
mysql> grant CREATE,INSERT,DELETE,UPDATE,SELECT on pauldb.* to paul@localhost;
Query OK, 0 rows affected (0.00 sec)

#
# Next we set a password for this new user
#
mysql> set password for paul = password('mysecretpassword');
Query OK, 0 rows affected (0.00 sec)

#
# Cleanup and ext
mysql> flush privileges;
mysql> exit;


Once all this has done you have created a new user with complete control over the contents of the database pauldb - the user can create new tables, delete them and generally use the database.

Note that this new user will have no other access to the server, only the dabase that you gave them permissions on.

 

 


Posted by Anonymous (127.0.xx.xx) on Tue 30 Nov 2004 at 13:47

If you use

mysql> grant CREATE,INSERT,DELETE,UPDATE,SELECT on pauldb.* to paul@localhost identified by 'secretpassword';

Then you don't need to flush the grant tables.

[ Parent | Reply to this comment ]

Posted by Anonymous (81.170.xx.xx) on Thu 14 Jun 2007 at 02:32
grant CREATE,INSERT,DELETE,UPDATE,SELECT on pauldb.* to paul@localhost;

so fundamental wrong.
Whatif paul want to DROP table he have made?

grant DROP,CREATE,INSERT,DELETE,UPDATE,SELECT on pauldb.* to paul@localhost;

is more nice.

[ Parent | Reply to this comment ]

Posted by Anonymous (95.48.xx.xx) on Wed 11 Aug 2010 at 08:14
You can also use

grant ALL on pauldb.* to paul@localhost;

[ Parent | Reply to this comment ]

Posted by Anonymous (216.138.xx.xx) on Wed 31 May 2006 at 00:04
instead of this -->

mysql> set password for paul = password('mysecretpassword');

use this command-->

mysql> set password for paul@localhost = password('mysecretpassword');

[ Parent | Reply to this comment ]

Posted by Anonymous (192.100.xx.xx) on Wed 10 Oct 2012 at 04:57
Yes, you are right. The author are wrong.

[ Parent | Reply to this comment ]

Posted by Anonymous (149.99.xx.xx) on Thu 1 Jun 2006 at 23:52
Sometimes (most of the time, actually) the only thing people really need is to get their heads oriented -- and this usually means working thru some short, simple, focused, crystal-clear introduction to the subject; with copious examples. They certainly don't need the beginning of a user's manual -- which is what they usually get.

This piece here is a good example of that type of time-saver. Job well done! Please continue.
;)

[ Parent | Reply to this comment ]

Posted by Anonymous (201.37.xx.xx) on Sat 10 Jun 2006 at 02:38
Please consider that tis article is outdated, since with mysql 5 the syntax of the some commands have changed. Please refer to:

http://dev.mysql.com/doc/refman/5.0/en/adding-users.html

[ Parent | Reply to this comment ]

Posted by Anonymous (59.94.xx.xx) on Thu 3 May 2007 at 09:09
I don't know whats the problem, the new user I just created is being denied access by the server for some odd reason.

[ Parent | Reply to this comment ]

Posted by Anonymous (81.170.xx.xx) on Mon 25 Jun 2007 at 02:05
mysql -h localhost -u root -p
mysql> create database paul;
mysql> grant ALL on paul.* to paul@localhost;
mysql> set password for paul = password('foobar');
# 1) This will create a databse named paul
# 2) This grants ALL (all normal stuff) for paul in his database called paul.
# 3) This will set paul's password to foobar.
mysql> exit;

[ Parent | Reply to this comment ]

Posted by Anonymous (125.22.xx.xx) on Thu 13 Mar 2008 at 07:31
very easy to understand and good

[ Parent | Reply to this comment ]

Posted by Anonymous (64.122.xx.xx) on Wed 9 Jul 2008 at 17:44
I believe you need to change the password line to read as follows:
"SET PASSWORD FOR paul@localhost = PASSWORD('mysecretpassword');

Without the "@localhost" it wouldn't work for me.

[ Parent | Reply to this comment ]

Posted by Anonymous (193.191.xx.xx) on Wed 8 Sep 2010 at 08:49
Apparently, even with the "@localhost", it doesn't work for me ...

Answer is still the same : "ERROR 1133 (42000): Can't find any matching row in the user table"

Does the user need to exist at the OS layer ?


[ Parent | Reply to this comment ]

Posted by Anonymous (173.70.xx.xx) on Tue 15 Feb 2011 at 16:28
The user does not need to exist on the system in question. MySQL usernames are relative to whatever you give them permissions to on a database by database level. For instance, giving MySQL access to username "bob" does not mean that there needs to be a system user named "bob", just as long as those permissions are correct in MySQL, "bob" would be good to go.

[ Parent | Reply to this comment ]

Posted by Anonymous (134.84.xx.xx) on Tue 6 Dec 2011 at 21:39
You didn't really answer the question very clearly. So I'm going to post what is totally missing from this entire page. We're creating MySQL users here, not OS users. There is no relation between the two.

Just use the following command. This will grant all permissions for all tables in a given database to a new user, and create the user at the same time. It worked for me:

GRANT ALL ON database.* TO 'username'@'%' IDENTIFIED BY 'password';

I use '%' because, in my case, users need to connect remotely. Use 'localhost' otherwise.

Error 1133 is extremely misleading. The reason this happens is because of this line in my.ini:

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_ SUBSTITUTION"

You can sort of fix that error if you remove NO_AUTO_CREATE_USER -- though in my experience it didn't totally solve the problem. On the other hand, I have no idea why you'd ever want to create a user without a password.

[ Parent | Reply to this comment ]

Posted by Anonymous (86.97.xx.xx) on Mon 4 Jul 2011 at 14:47
Thanks for this post..it took me awhile to figure out the set password thingy but when I added @localhost..it works fine..got your post after almost 2 hours of browsing on what to do with my wordpress install..thank you so much

[ Parent | Reply to this comment ]

Sign In

Username:

Password:

[Register|Advanced]

 

Flattr

 

Current Poll

What do you use for configuration management?








( 234 votes ~ 0 comments )