Let's start using MySQL

List All Databases

SHOW DATABASES;
        

Lists only those databases for which the user have some kind of privilege (or if the user have the global SHOW DATABASES privilege).

Create DB

CREATE DATABASE music_db;
        

Delete a Database

DROP DATABASE music_db;
        

Selecting a Database

USE music_db;
        

Before performing any table manipulations, you need to select the database on which they will be performed.

Create a New User

CREATE USER 'music_db_admin'@'localhost' IDENTIFIED BY '1234';
        

If user exists, an error will be thrown.

Delete user

DROP USER 'music_db_admin'@'localhost';
        

If user did not exists, an error will be thrown.

User privileges types

ALL PRIVILEGES- this would allow a MySQL users all access to a designated database (or if no database is selected, across the system)

CREATE- allows them to create new tables or databases

DROP- allows them to them to delete tables or databases

DELETE- allows them to delete rows from tables

INSERT- allows them to insert rows into tables

SELECT- allows them to use the Select command to read through databases

UPDATE- allow them to update table rows

GRANT OPTION- allows them to grant or remove other users' privileges

Grant privileges to a user

GRANT ALL PRIVILEGES ON music_db.* TO 'music_db_admin'@'localhost';
        

Revoke privileges

REVOKE [type of permission] ON [database name].[table name] FROM ‘[username]’@‘localhost’;

Reload all the privileges

Always be sure to reload all the privileges.

FLUSH PRIVILEGES;
        

Show all users

SELECT user FROM mysql.user;
        

Create DB schema dumped in external file

Create a simple DB: music_db, using the SQL statements provided in the file music_db_schema.sql.

You can use the MySQL Workbench to execute the statements by: 'File' => 'Run SQL script...' and selecting the 'music_db_schema.sql' file you have downloaded.

Create users

Create user music_db_admin with full privileges on music_db.

Create user music_db_editor who will be able only to read, insert and update the content of all tables in music_db.

Create user music_db_reader who will be able only to read, the content of all tables in music_db.

Connect to MySQL server with different user

Connect to MySQL server as music_db_admin

Try to execute the statements learned in Manage tables section and Manage databases section

Connect to MySQL server as music_db_editor or music_db_reader

Try to execute the statements learned in Manage tables section and Manage databases section

Write down what the MySQL server response to each of your commands.