Create user and set privileges in MySQL/MariaDB
Connect to MySQL/MariaDB
sudo mysql -u root
Create database
Create database
mysql> CREATE DATABASE IF NOT EXISTS your_database_name;
Add User
Create user
CREATE USER 'set-your-username'@'%' IDENTIFIED BY 'set-your-password';
Note. ‘set-your-username’@’%’ equals ‘username’@’host/domain’. localhost eg. ‘username’@’localhost’ can be used if mysql is only accessed from the same server that’s running the script.
Grant permissions to user ( All databases )
GRANT ALL PRIVILEGES ON *.* TO 'your-username'@'%';
Note.1 *.* means all databases and all tables. use dbname.* to restrict the user to a single database and dbname.tablename to restrict both the database and a table.
Note.2 % is a wildcard matching all domains.
Grant permissions to user ( Single database )
GRANT ALL PRIVILEGES ON dbname.* TO 'your-username'@'%';
Flush privileges
FLUSH PRIVILEGES;
Sources
- (Password) https://stackoverflow.com/questions/31111847/identified-by-password-in-mysql#answer-42998360
- https://stackoverflow.com/questions/1720244/create-new-user-in-mysql-and-give-it-full-access-to-one-database#answer-1720254
- https://askubuntu.com/questions/766334/cant-login-as-mysql-user-root-from-normal-user-account-in-ubuntu-16-04#answer-784347
- (Create and manage DB) https://www.digitalocean.com/community/tutorials/how-to-create-and-manage-databases-in-mysql-and-mariadb-on-a-cloud-server
- (Create DB) https://mariadb.com/kb/en/library/create-database/
- ( Create user ) https://mariadb.com/kb/en/library/create-user/
Helper commands
View databases
SHOW DATABASES;
View users
SELECT host, user, password FROM mysql.user;
View user privileges
SHOW GRANTS FOR 'your-username'@'localhost';