Create new user in MySQL and give it full access t

Question

I want to create a new user in MySQL and give it full access only to one database, say dbTest, that I create with a command like create database dbTest;. What would be the MySQL commands to do that?

Answer 1

To me this worked.

CREATE USER 'spowner'@'localhost' IDENTIFIED BY '1234'; 
GRANT ALL PRIVILEGES ON test.* To 'spowner'@'localhost'; 
FLUSH PRIVILEGES;

where

• spowner : user name
• 1234 : password of spowner
• test : database 'spowner' has access right to

Answer 2

Try this to create the user:

CREATE USER 'user'@'hostname';

Try this to give it access to the database dbTest:

GRANT ALL PRIVILEGES ON dbTest.* To 'user'@'hostname' IDENTIFIED BY 'password';

If you are running the code/site accessing MySQL on the same machine, hostname would be localhost.

Now, the break down.

GRANT - This is the command used to create users and grant rights to databases, tables, etc.

ALL PRIVILEGES - This tells it the user will have all standard privileges. This does not include the privilege to use the GRANT command however.

dbtest.* - This instructions MySQL to apply these rights for use in the entire dbtest database. You can replace the * with specific table names or store routines if you wish.

TO 'user'@'hostname' - 'user' is the username of the user account you are creating. Note: You must have the single quotes in there. 'hostname' tells MySQL what hosts the user can connect from. If you only want it from the same machine, use localhost

IDENTIFIED BY 'password' - As you would have guessed, this sets the password for that user.

Answer 3

You can create new users using the CREATE USER statement, and give rights to them using GRANT.

Answer 4

$ mysql -u root -p -e "grant all privileges on dbTest.* to
`{user}`@`{host}` identified by '{long-password}'; flush privileges;"

ignore -p option, if mysql user has no password or just press "[Enter]" button to by-pass. strings surrounded with curly braces need to replaced with actual values.

Answer 5

To create a user and grant all privileges on a database.

Log in to MySQL:

mysql -u root

Now create and grant

GRANT ALL PRIVILEGES ON dbTest.* To 'user'@'hostname' IDENTIFIED BY 'password';

Anonymous user (for local testing only)

Alternately, if you just want to grant full unrestricted access to a database (e.g. on your local machine for a test instance, you can grant access to the anonymous user, like so:

GRANT ALL PRIVILEGES ON dbTest.* To ''@'hostname'

Be aware

This is fine for junk data in development. Don't do this with anything you care about.

Answer 6

The below command will work if you want create a new user give him all the access to a specific database(not all databases in your Mysql) on your localhost.

GRANT ALL PRIVILEGES ON test_database.* TO 'user'@'localhost' IDENTIFIED BY 'password';

This will grant all privileges to one database test_database (in your case dbTest) to that user on localhost.

Check what permissions that above command issued to that user by running the below command.

SHOW GRANTS FOR 'user'@'localhost'

Just in case, if you want to limit the user access to only one single table

GRANT ALL ON mydb.table_name TO 'someuser'@'host';