Home Download Ubuntu

How to add new mysql user

When you want a user to be able to acces only one database, you need to create one database and one user with certain privileges.

To create a database, first we login to mysql server:

mysql -u root -p password


where root is the master username and password is the password of the master

we create a database:
CREATE DATABASE newdatabase;


1. first method to create and grant access to a new user:

we grant some privileges:

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON newdatabase.* TO 'newusername'@'localhost' IDENTIFIED BY 'newpassword';


where newusername is our new username and newpassword is our new password.

to grant all privileges to our new database, use this:

GRANT ALL PRIVILEGES ON newdatabase.* TO 'newusername'@'localhost' IDENTIFIED BY 'newpassword' WITH GRANT OPTION;


other privileges are:
data options: SELECT, INSERT, UPDATE, DELETE, FILE;
structure options: CREATE, ALTER, INDEX, DROP, CREATE TEMPORARY TABLES, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EXECUTE, CREATE VIEW, EVENT, TRIGGER;
administration options: GRANT, SUPER, PROCESS, RELOAD, SHUTDOWN, SHOW DATABASES, LOCK TABLES, REFERENCES, REPLICATION CLIENT, REPLICATION SLAVE, CREATE USER;

2. second method to create and grant access to a new user:

create the new user:
CREATE USER 'newusername'@'localhost' IDENTIFIED BY 'newpassword';


grant limited access to all databases:
GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'newusername'@'localhost';


grant full access to all databases:
GRANT ALL ON *.* TO 'newusername'@'localhost';


before exiting mysql console, we should flush the privileges to be able to have instant access to our new user:

FLUSH PRIVILEGES;


Resources: www.databasef1.com

0 comments:

Post a Comment