Create a database and a user
35 min · Beginner
Need help? Ask a tutor on WhatsApp (opens in a new tab)The shop app and the school app must not see each other's tables, and neither should log in as the all-powerful root account. How do you give each its own space and its own limited login?
CREATE DATABASE and USE
In MySQL, a database is a named container for tables. One server holds many databases, and each application should get its own.
CREATE DATABASE name; makes a new, empty database on the server. USE name; makes it the current database, and SELECT DATABASE(); tells you which one is current.
Example 1 · Create, switch to and check a database
DROP DATABASE IF EXISTS college_fest;CREATE DATABASE college_fest;USE college_fest;SELECT DATABASE();
CREATE DATABASE college_fest;+--------------+ | DATABASE() | +--------------+ | college_fest | +--------------+ ERROR 1007 (HY000): Can't create database 'college_fest'; database exists
What this code does
DROP DATABASE IF EXISTSclears an old copy,CREATE DATABASEmakes a new one andUSEswitches to it, soDATABASE()returnscollege_fest.- The last line tries to create the same database again and fails with error 1007.
CREATE DATABASE IF NOT EXISTS college_fest;would have skipped it without an error.
Names are usually lower case with underscores, such as college_fest or shoplite. Creating a name that already exists fails with error 1007. CREATE DATABASE IF NOT EXISTS name; skips the work quietly instead, and DROP DATABASE IF EXISTS name; removes it if it is there.
Scripts that you run again and again (like the practice setup in the next lesson) start with these IF EXISTS / IF NOT EXISTS forms, so a second run does not stop with an error.
DROP DATABASE deletes the database and every table and row inside it at once, with no undo. Read the name twice before you press Enter.Character set utf8mb4 for Indian names and emoji
A character set decides which characters can be stored. A collation decides how text is compared and sorted. utf8mb4 stores every Unicode character, and its collation makes 'Ravi' equal 'ravi'.
A character set decides which characters a database can store and how many bytes each one takes. utf8mb4 can store every character in Unicode: English, Hindi, Tamil, Bengali and every other Indian script, plus emoji. It uses 1 to 4 bytes per character.
Example 2 · Name the character set and collation
DROP DATABASE IF EXISTS college_fest;CREATE DATABASE college_fest CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;SHOW CREATE DATABASE college_fest;+--------------+----------------------------------------------------------------------------------------------------------------------------------------+ | Database | Create Database | +--------------+----------------------------------------------------------------------------------------------------------------------------------------+ | college_fest | CREATE DATABASE `college_fest` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ | +--------------+----------------------------------------------------------------------------------------------------------------------------------------+
What this code does
SHOW CREATE DATABASEprints the statement that would create the database again, with its character set and collation filled in.- The
/*!40100 ... */parts are special comments that MySQL reads only on versions at or above the number (4.1.0 and 8.0.16 here). Encryption is off by default.
Example 3 · Emoji: utf8mb3 refuses, utf8mb4 stores
DROP TABLE IF EXISTS fest_wishes;CREATE TABLE fest_wishes ( old_msg VARCHAR(50) CHARACTER SET utf8mb3, new_msg VARCHAR(50) CHARACTER SET utf8mb4);
INSERT INTO fest_wishes (new_msg) VALUES ('Happy Pongal 🎉');SELECT CHAR_LENGTH(new_msg) AS chars, LENGTH(new_msg) AS bytesFROM fest_wishes;
INSERT INTO fest_wishes (old_msg) VALUES ('Happy Pongal 🎉');+-------+-------+ | chars | bytes | +-------+-------+ | 14 | 17 | +-------+-------+ ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x8E\x89' for column 'old_msg' at row 1
What this code does
- The utf8mb4 column stores the message: 14 characters, but 17 bytes, because the emoji alone takes 4 bytes.
CHAR_LENGTHcounts characters andLENGTHcounts bytes. - The utf8mb3 column cannot hold a 4-byte character, so MySQL refuses with error 1366 and shows the emoji's four bytes. Typing emoji in Windows Command Prompt can be unreliable; run this one in Workbench.
MySQL 8.0 and later use utf8mb4 by default, with the collation utf8mb4_0900_ai_ci. (ai_ci means accent-insensitive and case-insensitive.) Writing both in CREATE DATABASE makes your intent clear and protects you on servers with other defaults.
The trap is the old name utf8 (really utf8mb3): it allows only 3 bytes per character, as the emoji example shows. Older tutorials and old servers still use it. Always write utf8mb4.
CREATE USER 'app'@'localhost' with a password
A MySQL user is a login written as 'name'@'host': a name plus the place it may connect from. Each application should have its own user and never log in as root.
A MySQL user is a login name plus the place it may connect from, written 'name'@'host'. CREATE USER 'fest_app'@'localhost' IDENTIFIED BY 'password'; creates one. localhost means "only from this same computer"; '%' would mean "from any computer", which you avoid unless you need it.
Example 4 · Create an application user
DROP USER IF EXISTS 'fest_app'@'localhost';CREATE USER 'fest_app'@'localhost' IDENTIFIED BY 'Fest#2025pass';
SELECT user, hostFROM mysql.userWHERE user = 'fest_app';
CREATE USER 'fest_app'@'localhost' IDENTIFIED BY 'Fest#2025pass';+----------+-----------+ | user | host | +----------+-----------+ | fest_app | localhost | +----------+-----------+ ERROR 1396 (HY000): Operation CREATE USER failed for 'fest_app'@'localhost'
What this code does
- MySQL keeps its users in the system table
mysql.user; the query shows the new user and the host it may connect from. Run this as root: only an administrator can create users. - Creating the same user again fails with error 1396.
DROP USER IF EXISTSat the top is what makes the script safe to run twice.
Why not use root for everything? If an application's password leaks, the attacker gets exactly that user's rights. With root that is every database on the server; with an application user it is one database. Giving each user only what it needs is called least privilege.
If your server has the password-strength check turned on, a weak password fails with ERROR 1819 (HY000): Your password does not satisfy the current policy requirements. The passwords in these lessons pass that check.
GRANT: give a user access to one database
Least privilege means giving each user only the rights it needs. GRANT adds a privilege (a permission) to a user and REVOKE takes it away, so a bug or a leaked password can do only limited harm.
A new user can log in but cannot see or change any data. GRANT gives it privileges (permissions) on a database: GRANT SELECT, INSERT, UPDATE, DELETE ON college_fest.* TO 'fest_app'@'localhost';. college_fest.* means every table in college_fest, including tables created later.
Example 5 · Grant one database and check the grants
DROP USER IF EXISTS 'fest_app'@'localhost';CREATE USER 'fest_app'@'localhost' IDENTIFIED BY 'Fest#2025pass';
GRANT SELECT, INSERT, UPDATE, DELETE ON college_fest.* TO 'fest_app'@'localhost';
SHOW GRANTS FOR 'fest_app'@'localhost';+------------------------------------------------------------------------------------+ | Grants for fest_app@localhost | +------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO `fest_app`@`localhost` | | GRANT SELECT, INSERT, UPDATE, DELETE ON `college_fest`.* TO `fest_app`@`localhost` | +------------------------------------------------------------------------------------+
What this code does
GRANT USAGE ON *.*is the line every user has;USAGEmeans "may log in, nothing else". The second line is the grant you just gave: four privileges on every table incollege_fest.- To take one away later:
REVOKE DELETE ON college_fest.* FROM 'fest_app'@'localhost';. To remove the user completely:DROP USER 'fest_app'@'localhost';.
The four privileges granted above are what a typical application needs to read and change rows. The user does not get CREATE, DROP or ALTER, so a bug or an attacker cannot delete tables. SHOW GRANTS FOR user; lists exactly what a user may do.
Changes made with GRANT and REVOKE take effect at once; the old FLUSH PRIVILEGES step is not needed.
Find the bug
Find the risky line
Each script has one line that breaks the rule of least privilege or fails.
The app only reads and changes rows in college_fest.
Round 1 of 3Connect as that user
Leave the root session with exit, then connect as the new user: mysql -u fest_app -p college_fest. The last word picks the current database as you connect, so no USE is needed.
Example 6 · Log in as the application user
mysql -u fest_app -p college_festSHOW DATABASES;SELECT CURRENT_USER();DROP DATABASE college_fest;Enter password: ************* Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 14 Server version: 8.4.6 MySQL Community Server - GPL Copyright (c) 2000, 2025, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | college_fest | | information_schema | | performance_schema | +--------------------+ 3 rows in set (0.00 sec) mysql> SELECT CURRENT_USER(); +--------------------+ | CURRENT_USER() | +--------------------+ | fest_app@localhost | +--------------------+ 1 row in set (0.00 sec) mysql> DROP DATABASE college_fest; ERROR 1044 (42000): Access denied for user 'fest_app'@'localhost' to database 'college_fest'
What this code does
- The first line is typed in the terminal and the rest at the
mysql>prompt.fest_appsees only its own database and two read-only system databases;mysqlandsysare hidden from it. DROPwas not granted, so the server refuses with error 1044 and the database is safe. This is least privilege doing its job.
The user sees only what it was granted. SHOW DATABASES lists college_fest plus two read-only system databases, and anything outside its privileges, such as dropping the database, fails with error 1044 (access denied to a database).
In Workbench, click + next to MySQL Connections, give the connection a name, set Username to fest_app and Default Schema to college_fest, then click Test Connection and type the password.
Order of execution
Set up an app user
Put the setup steps in the order they must run.
Next step
Check your understanding
10 quick questions