I - Login to MySQLFirst we’ll login to the MySQL server from the command line with the following command:
In this case, I’ve specified the user
root with the -u flag, and then used the -p flag so MySQL prompts for a password. Enter your current password to complete the login.
You should now be at a MySQL prompt that looks very similar to this:
II - Create MySQL UserWe’ll create a user with the name
testuser , and the password
test123testMã:
CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'test123test';
That’s it, congratulations! In just one command you’ve created your first MySQL user. However, this user won’t be able to do anything with MySQL until they are granted additional privileges. In fact, they won’t even be able to login without additional permissions.
III - Grant Permissions to MySQL UserThe basic syntax for granting permissions is as follows:
Mã:
GRANT permission ON database.table TO 'user'@'localhost';
Here is a short list of commonly used
permissions :
- ALL – Allow complete access to a specific database. If a database is not specified, then allow complete access to the entirety of MySQL.
- CREATE – Allow a user to create databases and tables.
- DELETE – Allow a user to delete rows from a table.
- DROP – Allow a user to drop databases and tables.
- EXECUTE – Allow a user to execute stored routines.
- GRANT OPTION – Allow a user to grant or remove another user’s privileges.
- INSERT – Allow a user to insert rows from a table.
- SELECT – Allow a user to select data from a database.
- SHOW DATABASES- Allow a user to view a list of all databases.
- UPDATE – Allow a user to update rows in a table.
Example: To grant
ALL permissions for
all databases and
all tables to the testuser, use the following command:
Mã:
GRANT ALL ON *.* TO 'testuser'@'localhost';
Using an asterisk (*) in the place of the
database or
table is a completely valid option, and implies all databases or all tables.
IV - View a List of MySQL UsersViewing a full list of MySQL users, including the host they’re associated with, can be done with the following select statement:
Mã:
SELECT User,Host FROM mysql.user;
V - Drop MySQL UserWe’ll drop user with the name
testuserMã:
DROP USER 'testuser'@'localhost';