MYSQL: How to create a new user and give it full access only to 1 database
To create a new user in MySQL and give it full access only to 1 database, say dbTest, these are the MySQL commands to do that
To create the user:
CREATE USER 'user'@'hostname';
To give it access to the database
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
IDENTIFIED BY 'password' - As you would have guessed, this sets the password for that user.
- How JSON data types work in mysql
- How to Disable Strict SQL Mode in MySQL 5.7 and Ubuntu 16.04
- MySql: How to build a performant search engine
- Install HHVM + Nginx on Ubuntu 14.04
- Optimize MySql On Low Memory Servers
- PHP: Storing arrays in database
- PHP Recursive Backup of MySql Database
- Million of visitors per day with a super cheap php mysql server using nginx and varnish
- Memcache: Installation and Usage on Ubuntu 12.10
- Mysql:Guide To The MySql Query Cache
- MySql: Setup Master Slave Replication in Ubuntu