MySQL Creating databases Create database, users, and grants
Example
Create a DATABASE. Note that the shortened word SCHEMA can be used as a synonym.
CREATE DATABASE Baseball; -- creates a database named Baseball
If the database already exists, Error 1007 is returned. To get around this error, try:
CREATE DATABASE IF NOT EXISTS Baseball;
Similarly,
DROP DATABASE IF EXISTS Baseball; -- Drops a database if it exists, avoids Error 1008
DROP DATABASE xyz; -- If xyz does not exist, ERROR 1008 will occur
Due to the above Error possibilities, DDL statements are often used with IF EXISTS.
One can create a database with a default CHARACTER SET and collation. For example:
CREATE DATABASE Baseball CHARACTER SET utf8 COLLATE utf8_general_ci;
SHOW CREATE DATABASE Baseball;
+----------+-------------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------------+
| Baseball | CREATE DATABASE `Baseball` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-------------------------------------------------------------------+
See your current databases:
SHOW DATABASES;
+---------------------+
| Database |
+---------------------+
| information_schema |
| ajax_stuff |
| Baseball |
+---------------------+
Set the currently active database, and see some information:
USE Baseball; -- set it as the current database
SELECT @@character_set_database as cset,@@collation_database as col;
+------+-----------------+
| cset | col |
+------+-----------------+
| utf8 | utf8_general_ci |
+------+-----------------+
The above shows the default CHARACTER SET and Collation for the database.
Create a user:
CREATE USER 'John123'@'%' IDENTIFIED BY 'OpenSesame';
The above creates a user John123, able to connect with any hostname due to the % wildcard. The Password for the user is set to 'OpenSesame' which is hashed.
And create another:
CREATE USER 'John456'@'%' IDENTIFIED BY 'somePassword';
Show that the users have been created by examining the special mysql database:
SELECT user,host,password from mysql.user where user in ('John123','John456');
+---------+------+-------------------------------------------+
| user | host | password |
+---------+------+-------------------------------------------+
| John123 | % | *E6531C342ED87 .................... |
| John456 | % | *B04E11FAAAE9A .................... |
+---------+------+-------------------------------------------+
Note that at this point, the users have been created, but without any permissions to use the Baseball database.
Work with permissions for users and databases. Grant rights to user John123 to have full privileges on the Baseball database, and just SELECT rights for the other user:
GRANT ALL ON Baseball.* TO 'John123'@'%';
GRANT SELECT ON Baseball.* TO 'John456'@'%';
Verify the above:
SHOW GRANTS FOR 'John123'@'%';
+--------------------------------------------------------------------------------------------------------+
| Grants for John123@% |
+--------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'John123'@'%' IDENTIFIED BY PASSWORD '*E6531C342ED87 .................... |
| GRANT ALL PRIVILEGES ON `baseball`.* TO 'John123'@'%' |
+--------------------------------------------------------------------------------------------------------+
SHOW GRANTS FOR 'John456'@'%';
+--------------------------------------------------------------------------------------------------------+
| Grants for John456@% |
+--------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'John456'@'%' IDENTIFIED BY PASSWORD '*B04E11FAAAE9A .................... |
| GRANT SELECT ON `baseball`.* TO 'John456'@'%' |
+--------------------------------------------------------------------------------------------------------+
Note that the GRANT USAGE that you will always see means simply that the user may login. That is all that that means.
