Linux command: the tenth of the MySQL series-- MySQL user and rights management, mysql administrator password reset
MySQL user account includes: user name @ host name
User name: within 16 characters
Host: the host can be expressed in the following ways
Hostname: mysql
IP address: 172.16.90.111
Network address: 172.16.0.0Universe 255.255.0.0
Wildcard:%, _%: any character _: any one
172.16%.% .magedu.com
Permission levels: global level, library level, table level, column level, stored procedure and stored function level
Global level: SELECT * FROM db\ G; query permissions at the global library level
*
CREATE USER username@host [IDENTIFIED BY 'password'] create users
DROP USER 'username'@'host'; "delete"
RENAME USER old_name TO new_name; renaming usage
SHOW GRANTS FOR 'username'@'host'; views the list of user permissions
FLUSH PRIVILEGES; refresh the permission list
GRANT PRIVILEGES ON [object_type] db.* TO 'username'@'host'; adds permissions to users
REVOKE SELECT ON db.* FROM 'username'@'host'; removes the user's SELECT permission
*
*
MySQL database ROOT user password forgetting solution steps:
First: close the mysqld process and modify the configuration file / etc/my.cnf
[root@lamp ~] # service mysqld stop # stop the mysqld process first
Shutting down MySQL.. [OK]
[root@lamp ~] # vim / etc/init.d/mysqld # modify the startup script of mysqld. Change the content in the red box below, and save and exit.
[root@lamp ~] # service mysqld start # start the mysqld process
Starting MySQL.. [OK]
[root@lamp ~] # mysql # login to mysql at this time does not require a user name and password
Welcome to the MySQL monitor. Commands end with; or\ g.
Your MySQL connection id is 1
Server version: 5.5.28-log Source distribution
Copyright (c) 2000, 2012, 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 > USE mysql
Database changed
Mysql > SELECT User,Host,Password FROM user; # query the three fields of the user table, which requires a password to log in
+-- +
| | User | Host | Password | |
+-- +
| | root | localhost | * A198E6EEE923DA319BBF86C99624479A198E6EEE9 |
| | root | lamp | * A198E6EEE9823DA319BBF86C99624479A198E6EEE9 |
| | root | 127.0.0.1 | * A198E6EEE9DA319BBF86C99624479A198E6EEE9 |
| | root |:: 1 | * A198E6EEE93DA319BBF86C99624479A198E6EEE9 |
| | test | localhost | * 94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 |
+-- +
5 rows in set (0.00 sec)
Mysql > UPDATE user SET Password=PASSWORD ('redhat') WHERE User='root'; # at this time due to skipping
Grant permission list, so the user password can only be changed by changing the value of the Password field in the user table.
Query OK, 0 rows affected (0.00 sec)
Rows matched: 4 Changed: 0 Warnings: 0
Mysql > SELECT User,Host,Password FROM user; # query the three fields of the user table, which requires a password to log in
+-- +
| | User | Host | Password | |
+-- +
| | root | localhost | * 84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
| | root | lamp | * 84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
| | root | 127.0.0.1 | * 84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
| | root |:: 1 | * 84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
| | test | localhost | * 94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 |
+-- +
5 rows in set (0.00 sec)
Mysql >\ Q
[root@lamp ~] # service mysqld stop # stop the mysqld process
Shutting down MySQL. [OK]
[root@lamp ~] # vim / etc/init.d/mysqld # modify the startup script, remove the previously modified content and save and exit
[root@lamp ~] # service mysqld start # start the mysqld process
Starting MySQL.. [OK]
[root@lamp ~] # mysql # directly log in to mysql and prompt for user and password at this time
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@lamp ~] # mysql-uroot-p # specify login through root account-p specify login password required
Enter password: log in to mysql after entering the correct password
Welcome to the MySQL monitor. Commands end with; or\ g.
Your MySQL connection id is 2
Server version: 5.5.28-log Source distribution
Copyright (c) 2000, 2012, 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 > # at this point, the problem of mysql database password forgetting is solved.
*
The following table shows the scope of permission:
Create a user CREATE USER:CREATE USER username@host [IDENTIFIED BY 'password'] create a user and set a password through IDENTIFIED BY' password',.
Usage:
Mysql > CREATE USER test@localhost IDENTIFIED BY 'test'; # create a user test local database account
The password is test.
Query OK, 0 rows affected (0.00 sec)
Mysql > FLUSH PRIVILEGES; # Refresh permission list
Query OK, 0 rows affected (0.00 sec)
Mysql > SHOW GRANTS FOR test@localhost\ G; # View the permission list of the database account test@localhost
* * 1. Row *
Grants for test@localhost: GRANT USAGE ON *. * TO 'test'@'localhost' IDENTIFIED BY PASSWORD' * 94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29'
1 row in set (0.00 sec)
Reopen another client and log in to mysql as a test account
[root@lamp ~] # mysql-utest-p # Log in to mysql database as test user, enter user password
Enter password:
Welcome to the MySQL monitor. Commands end with; or\ g.
Your MySQL connection id is 17
Server version: 5.5.28-log Source distribution
Copyright (c) 2000, 2012, 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.
Log in to mysql > SHOW DATABASES; # successfully. View the database under this account
+-+
| | Database |
+-+
| | information_schema |
| | test |
+-+
2 rows in set (0.00 sec)
Permission setting GRANT:GRANT PRIVILEGES ON [object_type] db.* TO 'username'@'host'; # specify permission PRIVILEGES ON specify object name db.* object_type specify object type TO username@host specify user.
Object_type object types are: TABLE (table) FUNCTION (function) PROCEDURE (program, library)
Usage:GRANT EXECUTE ON FUNCTION db.abc TO 'username'@'host'; # is authorized to username@host users, for
The abc function of the db database has execute permissions.
GRANT UPDATE (Age) ON db.testtb TO 'username'@'host'; # authorizes username@host users to have UPDATE permission for the Age field of the testtb table on the db database.
Cancel permission setting REVOKE: REVOKE PRIVILEGES ON db.* FROM 'username'@'host'; # specify what needs to be canceled
What permission PRIVILEGES ON specifies the object name db.* FROM 'username'@'host'; specifies which user's permissions are revoked.
Usage: REVOKE SELECT ON db.* FROM 'testuser'@'%'; # cancels all tables for testuser users to the db database
The SELECT permissions of the
