Create new user in MySQL and give it full access to one database

user_207008 0
Viewed 842552

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?

mysql
8 Answers

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
user_39011380

In case the host part is omitted it defaults to the wildcard symbol %, allowing all hosts.

CREATE USER 'service-api';

GRANT ALL PRIVILEGES ON the_db.* TO 'service-api' IDENTIFIED BY 'the_password'

SELECT * FROM mysql.user;
SHOW GRANTS FOR 'service-api'
user_71358160
Related