1. Enter the mysql command line, enter root and password
[root@localhost ~]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 19 Server version: 5.7.22 MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
2. User Management and Privilege Settings
// Managing Users
mysql> use mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed
// Query Users
mysql> select host,user from user; +-----------+---------------+ | host | user | +-----------+---------------+ | % | root | | % | test | | localhost | mysql.session | | localhost | mysql.sys | | localhost | root | +-----------+---------------+ 5 rows in set (0.00 sec)
// Create users (user: admin, password: 123456)
mysql> create user admin identified by '123456'; Query OK, 0 rows affected (0.00 sec)
// Delete user admin
mysql> drop user admin; Query OK, 0 rows affected (0.00 sec)
// Recreate user (user: admins, password: 123456)
mysql> create user admins identified by '123456'; Query OK, 0 rows affected (0.00 sec) mysql> select host, user from user; +-----------+---------------+ | host | user | +-----------+---------------+ | % | admins | | % | root | | % | test | | localhost | mysql.session | | localhost | mysql.sys | | localhost | root | +-----------+---------------+ 6 rows in set (0.00 sec) // View the permissions of user admins mysql> show grants for admins; +------------------------------------+ | Grants for admins@% | +------------------------------------+ | GRANT USAGE ON *.* TO 'admins'@'%' | +------------------------------------+ 1 row in set (0.00 sec) // Grant permissions (to admins, to query database test) mysql> grant select on test.* to admins; Query OK, 0 rows affected (0.00 sec) // View the permissions of user admins mysql> show grants for admins; +------------------------------------------+ | Grants for admins@% | +------------------------------------------+ | GRANT USAGE ON *.* TO 'admins'@'%' | | GRANT SELECT ON `test`.* TO 'admins'@'%' | +------------------------------------------+ 2 rows in set (0.00 sec) // Retrieve permissions (retrieve query permissions for user admins and database test) mysql> revoke select on test.* from admins; Query OK, 0 rows affected (0.01 sec) // View the permissions of user admins mysql> show grants for admins; +------------------------------------+ | Grants for admins@% | +------------------------------------+ | GRANT USAGE ON *.* TO 'admins'@'%' | +------------------------------------+ 1 row in set (0.00 sec) // Grant permissions (to admins, to query, update, delete, insert, etc. of database test) mysql> grant select, update, delete, insert on test.* to admins; Query OK, 0 rows affected (0.00 sec) // View the permissions of user admins mysql> show grants for admins; +------------------------------------------------------------------+ | Grants for admins@% | +------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'admins'@'%' | | GRANT SELECT, INSERT, UPDATE, DELETE ON `test`.* TO 'admins'@'%' | +------------------------------------------------------------------+ 2 rows in set (0.01 sec) // Grant permissions (to admins, to create new tables, delete tables or delete databases for database test, etc.) mysql> grant create,drop on test.* to admins; Query OK, 0 rows affected (0.00 sec) // View the permissions of user admins mysql> show grants for admins; +--------------------------------------------------------------------------------+ | Grants for admins@% | +--------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'admins'@'%' | | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON `test`.* TO 'admins'@'%' | +--------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) // Refresh permissions (to make the permissions set effective) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) // Permissions to view root mysql> show grants for root; +-------------------------------------------+ | Grants for root@% | +-------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' | +-------------------------------------------+ 1 row in set (0.00 sec)
The following is a short list of common possible permissions that users can enjoy.
ALL PRIVILEGES - As we saw earlier, this will allow MySQL users to access the specified database (or if the database is not selected in the system)
CREATE - Allows them to create new tables or databases
DROP - Allows them to delete tables or databases
DELETE - Allows them to delete rows from tables
INSERT - Allows them to insert rows into tables
SELECT - Allows them to use the Select command to read the database
UPDATE - Allows them to update table rows
GRANT OPTION - Allows them to grant or delete permissions to other users
To provide privileges to specific users, you can use this framework:
GRANT [type of permission] ON [database name].[table name] TO '[username]'@'localhost';
If you want to grant access to any database or any table, be sure to place an asterisk (*) in the database name or table name.
Be sure to use the Flush Privileges command every time you update or change permissions.
If you need to revoke permissions, the structure is almost the same as that granted:
REVOKE [type of permission] ON [database name].[table name] FROM '[username]'@'localhost';
Just as you can delete databases with DROP, you can delete users completely with DROP:
DROP USER 'demo'@'localhost';
To test your new user, log out by typing
quit
And use this command to re-login in the terminal:
mysql -u [username]-p