Quick Guide: Creating a Database, User, and Assigning Permissions via SQL Command Line

When setting up a new database environment, you often need to create a database, add a user, and grant them the necessary permissions. Here's a quick and efficient way to accomplish all of this from the SQL command line.

Steps to Create a Database, User, and Grant Permissions

1. Log in to the SQL Command Line
   First, log in to your SQL server as a root user or a user with sufficient privileges. For example:

   mysql -u root -p
   

   Enter your password when prompted.

2. Create a New Database
   Use the CREATE DATABASE statement to create a new database. Replace your_database with your desired database name:

   CREATE DATABASE your_database;
   

3. Create a New User
   Create a user and assign a password using the CREATE USER statement. Replace your_user with the username and your_password with a strong password:

   CREATE USER 'your_user'@'localhost' IDENTIFIED BY 'your_password';
   

   If you want the user to connect from any host, replace 'localhost' with '%':

   CREATE USER 'your_user'@'%' IDENTIFIED BY 'your_password';
   

4. Grant Permissions to the User
   Assign full permissions on the database to the user with the GRANT statement:

   GRANT ALL PRIVILEGES ON your_database.* TO 'your_user'@'localhost';
   

   For connections from any host:

   GRANT ALL PRIVILEGES ON your_database.* TO 'your_user'@'%';
   

5. Apply Changes
   Always run the FLUSH PRIVILEGES command to reload the privileges table and ensure your changes take effect:

   FLUSH PRIVILEGES;
   

6. Verify the Setup
   To confirm everything is set up correctly, you can:

   • Switch to the new user:

     mysql -u your_user -p
     

   • Use the new database:

     USE your_database;
     

Complete Command Summary

Here’s the entire process condensed into a single set of commands:

CREATE DATABASE your_database;
CREATE USER 'your_user'@'localhost' IDENTIFIED BY 'your_password';
GRANT ALL PRIVILEGES ON your_database.* TO 'your_user'@'localhost';
FLUSH PRIVILEGES;

For any-host access:

CREATE DATABASE your_database;
CREATE USER 'your_user'@'%' IDENTIFIED BY 'your_password';
GRANT ALL PRIVILEGES ON your_database.* TO 'your_user'@'%';
FLUSH PRIVILEGES;

Tips

• Always use a strong password for your database users to enhance security.
• Restrict user access to specific hosts (localhost or a specific IP) whenever possible to reduce the attack surface.
• If you’re using MySQL 8.0 or newer, consider roles for better permission management.

With these commands, you can quickly set up a database, user, and permissions without hassle.