1.Get into mysql Command line, entering 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 permission setting // 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 user (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) // Give permissions (give users admins and query permissions of 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) // Reclaim authority (to user admins, reclaim the query authority of 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) // Give permissions (give users admins, query, update, delete, insert and other permissions 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 users admins, grant permissions such as new table, delete table or delete database of database test) 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 (make the set permissions effective) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) // View permissions of root mysql> show grants for root; +-------------------------------------------+ | Grants for root@% | +-------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' | +-------------------------------------------+ 1 row in set (0.00 sec)
Here 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 no database is selected in the system)
- CREATE - allows them to CREATE new tables or databases
- DROP - allow them to delete tables or databases
- DELETE - allow them to DELETE rows from the table
- INSERT - allow them to INSERT rows into the table
- Select - allows them to use the Select command to read the database
- UPDATE - allow them to UPDATE table rows
- GRANT OPTION - allow them to grant or remove permissions to other users
To provide permissions to specific users, you can use this framework:
GRANT [type of permission] ON [database name].[table name] TO '[username]'@'localhost';
If you want to give it access to any database or any table, make sure you place an asterisk (*) in the place of the database 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 use DROP to delete a database, you can use DROP to completely delete users:
DROP USER 'demo'@'localhost';
To test your new user, type sign out
quit
And use this command to log in again in the terminal:
mysql -u [username]-p