Create a New User in MySql:
First access the MySQL shell by typing the following command into terminal:
1 |
mysql -u root -p |
After entering the root MySQL password into the prompt, you will be able to enter the MySQL shell.
Now Let’s start by making a new user within the MySQL shell:
1 |
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password'; |
At this point, newuser has no permissions to do anything with the databases. Even if newuser tries to login, and will fail to reach the MySQL shell.
Grant Different User Permissions:
So, provide the user with access to the information they will need.
1 |
GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost'; |
In this command, asterisks refer to the database & table that they command allows to the newuser to read, edit, execute and perform all tasks across all the databases and tables.
Now after applying for permissions, we need to reload all the privileges using the following command.
1 |
FLUSH PRIVILEGES; |
Here is short list of other common possible permissions for MySql ‘newuser’.
ALL PRIVILEGES
– this would allow MySQL user all access to database (or if no database is selected, across the system)CREATE
– allows to create new tables or databasesDROP
– allows to delete tables or databasesDELETE
– allows to delete rows from tablesINSERT
– allows to insert rows into tablesSELECT
– allows to use the Select commandUPDATE
– allow to update table rowsGRANT OPTION
– allows to grant or remove other users’ privileges
Syntax to provide a specific user with a permission:
1 |
GRANT [type of permission] ON [database name].[table name] TO ‘[username]’@'localhost’; |
Put an asterisk (*) in the place of the database name or table name, If need to give the user access to any database or to any table.
Flush Privileges command is required each time when made a change in permission of the user.
Syntax to revoke a permission:
1 |
REVOKE [type of permission] ON [database name].[table name] FROM ‘[username]’@‘localhost’; |
Syntax to delete a user:
1 |
DROP USER ‘demo’@‘localhost’; |
To test out your new user, log out by typing
1 |
quit |
and log back in with this command in terminal:
1 |
mysql -u [username]-p |