SQL Database Management

A. Viewing current databases 

SHOW DATABASES;

B. Viewing current users 

SELECT Host, User, password_expired FROM mysql.user;

C. Viewing user's privileges 

SHOW GRANTS FOR 'username'@'hostname';
  • Replace 'username' with the username.
  • Replace 'hostname' with the user's authentication host.

D. Creating a new Database 

CREATE DATABASE 'new_db_name';

E. Creating a new User

Generate a new random password (since MariaDB 10.10)
SELECT rand.pwd as password, PASSWORD(rand.pwd) as hash
FROM (
    SELECT SUBSTR(TO_BASE64(RANDOM_BYTES(128)), 1, 32) as pwd
) as rand;

Note: This will return a random password string and the associated hash THAT LEAVES NO TRACE IN THE CONSOLE LOGS. Other methods of generating a password will leave a raw copy of the password in the console logs, allowing for potential security risks.

Replace 32 with a different integer to change the length the generated password, up to 128 characters. 

CREATE USER 'username'@'hostname' IDENTIFIED BY PASSWORD 'HASH';
  • Replace 'username' with the username you wish to create (ASCII regex: [0-9,a-z,A-Z$_]).
  • Replace 'hostname' with the host identifier (see below).
  • Replace 'HASH' with the password hash generated in the first step.
Hostname options
  • '%' for any source host (if anonymous block is not enabled)
  • 'localhost' for local connections only (note: this does not apply to loopback connections)
  • '1.2.3.4/32' for a specific IP address only
  • '1.2.3.4/28' for a CDIR range of IP addresses (IP addresses 1.2.3.0 - 1.2.3.15)

F. Granting a user general access to a database 

GRANT ALL PRIVILEGES ON db_name.* TO 'username'@'hostname' WITH GRANT OPTION;
  • Replace 'db_name' with the database name.
  • Replace 'username' with the username.
  • Replace 'hostname' with the user's authentication host.