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 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.
Janeth Kent
Licenciada en Bellas Artes y programadora por pasión. Cuando tengo un rato retoco fotos, edito vídeos y diseño cosas. El resto del tiempo escribo en MA-NO WEB DESIGN AND DEVELOPMENT.
Related Posts
How to fix excessive MySQL CPU usage
What happens if we realise that a series of databases that we thought were optimised through the use of indexes, have begun to consume CPU usage time of a server…
How to connect to MySQL with Node.js
Let's see how you can connect to a MySQL database using Node.js, the popular JavaScript runtime environment. Before we start, it is important to note that you must have Node.js installed…
htaccess Rules to Help Protect from SQL Injections and XSS
This list of rules by no means is a sure bet to secure your web services, but it will help in preventing script-kiddings from doing some basic browsing around. MySQL injection…
MySQL 8.0 is now fully supported in PHP 7.4
MySQL and PHP is a love story that started long time ago. However the love story with MySQL 8.0 was a bit slower to start… but don’t worry it rules…
How JSON data types work in mysql
First introduced in MySQL 5.7.8, the JSON data type is a specialized binary column type, similar to a blob , but with added machinery to optimize the storage and retrieval…
How to Disable Strict SQL Mode in MySQL 5.7 and Ubuntu 16.04
If your app was written for older versions of MySQL and is not compatible with strict SQL mode in MySQL 5.7, you can disable strict SQL mode. For example, apps…
MySql: How to build a performant search engine
In content-heavy websites, it becomes increasingly important to provide capable search possibilities to help your users find exactly what they’re looking for. The most obvious solution is searching your MySQL…
Install HHVM + Nginx on Ubuntu 14.04
The LEMHH software stack is a group of software that can be used to serve dynamic web pages and web applications. This is an acronym that describes a Linux operating…
Optimize MySql On Low Memory Servers
Cloud computing makes it very affordable to get your own private virtual server on the Internet. Digital Ocean offers an entry level droplet for USD$5 per month, and Amazon.com has…
PHP: Storing arrays in database
When working with databases, sometimes it is necessary to store an array in a MySQL field. Unfortunately, there is no way to directly pass in an array as a parameter.…
PHP Recursive Backup of MySql Database
This script can be used to make backup of your MySql database, you can use the script in conjunction with cronjobs $user = 'myuser'; $passwd = 'mypass'; $host = 'myhost'; $db = 'mydb'; // Delete…
Million of visitors per day with a super cheap php mysql server using nginx and varnish
These instructions are the rather verbose, but hopefully easy enough to follow, steps to build a new Linux server using Varnish and Nginx to build a php application on a…