MySQL Rights Management
- The Rights management information is stored in,,,,
user
db
and in host
table_priv
column_priv
procs_priv
the table
- Through
CREATE
, DROP
permissions can create new database/table, delete database/table
SELECT
,,, to INSERT
UPDATE
increase the DELETE
deletion of existing tables
INDEX
Allow the creation and deletion of indexes on existing tables
MySQL Account management
- Using
GRANT
statements
e.g.:GRANT ALL PRIVILEGES ON *.* TO ‘monkey‘@‘localhost‘ IDENTIFIED BY ‘some_password‘ WITH GRANT OPTION;
GRANT RELOAD,PROCESS ON *.* TO ‘admin‘@‘localhost‘;
GRANT
Command interpretation: PRIVILEGES
represents any permission, or the following can be SELECT
/ UPDATE
; ON
Indicates which tables are affected, the preceding represents the database, and the subsequent representation table; TO
Indicates which user is authorized, the previous user name, the following can fill IP,IP segment, hostname or% (for any), followed by the optional password of the specified user
- Direct operation of the MySQL authorization form (user) Note that you can view the table structure before inserting and insert () at a time,
DESCRIBE
insert the password field to remember to PASSWORD(<密码>)
encrypt, or insert failed; FLUSH PRIVILEGES
Otherwise, changes to the authorization table will only be discovered if the server is restarted.
Remove user
Restricting user account Resources
- You can restrict users to the following resources: 1. Number of queries issued per hour; 2. Number of updates issued per hour; 3. Number of servers that can be connected per hour
- Use
GRANT
statements to implement resource constraints: In fact, the above statement in account management with a GRANT
WITH
clause can be, e.g.:GRANT ALL ON customer TO ‘admin‘@‘localhost‘ IDENTIFIED BY ‘some_password‘ WITH MAX_QUERIES_PER_HOUR 20 MAX_UPDATE_PER_HOUR 15 MAX_CONNECTIONS_PER_HOUR 20 MAX_USER_CONNECTION 10;
Modify User Password
- To add a password for an existing user:
mysqladmin -u root -h hostname password ‘new password‘;
- Change Password:
SET PASSWORD FOR ‘user‘@‘localhost‘ = PASSWORD(‘new password‘);
Database backup and Recovery
MySQL database management