MySQL MariaDB Create User

Categories:

  less than a minute  

Create a user similar to root

To create user with all the privileges similar to root.

CREATE USER 'superuser'@'%' IDENTIFIED BY 'password';

GRANT ALL PRIVILEGES ON *.* TO 'superuser'@'%'  WITH GRANT OPTION;

FLUSH PRIVILEGES;

Create user for Backup

A separate user with minimal privileges for taking database dump.

CREATE USER 'backup'@'localhost' IDENTIFIED BY 'mypassword';

GRANT SELECT, SHOW VIEW, RELOAD, REPLICATION CLIENT, EVENT, TRIGGER, LOCK TABLES ON *.* TO 'backup'@'localhost';

Note:

  • LOCK TABLES privilege can be removed if db has only InnoDB

Create role and assign required privileges

-- Create the role
CREATE ROLE testrole;

-- Grant privileges to the role
-- OPTION 1: Grant all privileges on the database
GRANT ALL PRIVILEGES ON testdb.* TO testrole;

-- OPTION 2: Give selected privileges
GRANT index, create, select, insert, update, delete, drop, alter, lock tables  ON testdb.* TO testrole

-- Create the user
CREATE USER testuser@% IDENTIFIED BY 'testuserpassword';

-- Assign role to the user
GRANT testrole TO testuser@%;

-- Set the default role
SET DEFAULT ROLE testrole FOR testuser@%;

FLUSH PRIVILEGES;

Create the using grant command

GRANT index, create, select, insert, update, delete, drop, alter, lock tables
ON mydb.*
TO 'myuser'@'localhost'
IDENTIFIED BY 'myuserpassword';

FLUSH PRIVILEGES;

Note:

  • Replace localhost with % to access the user from all host.

Ref: