MySQL experience 9-user and data security-MySQL

MySQL experience 9-USER and data security bitsCN. comMySQL experience 9-USER and data security 1. you can use the create user syntax to add one or more users and set the corresponding password. Syntax format: create user user [identified BY [PASSWORD] 'password'] [, user [identified by [password] 'password']... the format of user is 'user _ name' @ 'host name'. note: user_name is the user name, host_name is the host name, and password is the password of the user. In most SQL products, the user name and password are only composed of letters and numbers. You can use the self-selected identified by clause to specify a password for your account. In particular, to specify a PASSWORD in plain text, ignore the PASSWORD keyword. If you do not want to send the PASSWORD in plain text, and you know the mixed value returned by the PASSWORD () function to the PASSWORD, you can specify the mixed value, but add the keyword PASSWORD. Create user is used to CREATE a new MySQL account. Create user adds a new record to the user table of the mysql database of the system. To use create user, you must have the global create user permission or INSERT permission for the mysql database. If the account already exists, an error occurs. For example, add two new users. king's password is queen and palo's password is 530415. Create user 'King' @ 'localhost' identified by 'Queen ', 'palo' @ 'localhost' identified by '000000'; note: The keyword localhost is declared after the USER name. This keyword specifies the host from which the MySQL connection is created. If a user name and host name contain special characters such as "_" or wildcards such as "%", you need to enclose them in single quotes. "%" Indicates a group of hosts, which can be accessed by all users. If two users have the same user name but different hosts, MySQL treats them as different users and allows them to assign different permission sets. If no password is entered, MySQL allows related users to log on without a password. However, this approach is not recommended from the security perspective. The created user does not have many permissions. They can log on to MySQL, but they cannot USE the USE statement to make any database you have created into the current database. Therefore, they cannot access the tables of those databases, only operations without permissions are allowed. for example, you can use a SHOW statement to query the list of all storage engines and character sets. 2. syntax format: drop user user [, user_name]... the drop user statement is used to delete one or more MySQL accounts and cancel their permissions. To use DROPUSER, you must have the global create user permission or DELETE permission for the mysql database. For example, delete user tom: drop user tom @ localhost. if the deleted USER has created a table, index, or other database objects, they will be retained, because MySQL does not record who created these objects. 3. you can use the rename USER statement to modify the name of an existing SQL USER. Syntax format: rename user old_user TO new_user, [, old_user TO new_user]... description: old_user is an existing SQL USER. New_user is a new SQL user. The rename user statement is used to RENAME the original MySQL account. To use rename user, you must have the global create user permission or the mysql database UPDATE permission. If the old account does not exist or the new account already exists, an error occurs. For example, change the names of king1 and king2 to ken1 and ken2. Rename user 'king1' @ 'localhost' TO 'ken1' @ 'localhost', 'king2' @ 'localhost' TO 'ken2' @ 'localhost'; 4. you can use the set password statement to change the logon PASSWORD of a user. Syntax format: set password [FOR user] = PASSWORD ('newpassword') description: If you do not add a FOR user, the PASSWORD of the current user is changed. If FORuser is added, the password of a specific user on the current host is modified. the user is the user name. The user value must be given in the format of 'user _ name' @ 'host _ name. For example, change the password of king to queen1. Set password for 'King' @ 'localhost' = PASSWORD ('queen1'); 5. new SQL users are not allowed to access tables belonging to other SQL users, nor can they create their own tables immediately. they must be authorized. You can grant the following permissions. (1) column permission: it is related to a specific column in the table. For example, you can use the UPDATE statement to UPDATE the value of the XS student ID column. (2) table permissions: related to all data in a specific table. For example, you can use the SELECT statement to query all data in the table XS. (3) Database permissions: related to all tables in a specific database. For example, the permission to create a new table in an existing XSCJ database. (4) user permissions: related to all MySQL databases. For example, you can delete an existing database or create a new database. Grant permissions to a user using the grant statement. Use the showgrants statement to view the permissions of the current account. Syntax format: GRANT priv_type [(column_list)] [, priv_type [(column_list)]... ON [object_type] {table name | *. * | database name. *} TO user [identified by [PASSWORD] 'password'] [, user [identified by [password] 'password']... [WITH with_option [with_option]...] here, object_type: TABLE | FUNCTION | PROCEDURE with_option: grant option | MAX_QUERIES_PER_HOUR count | MAX_UPDATES_PER_HOUR count | MAX_CONNECTIONS_PER _ HOUR count | MAX_USER_CONNECTIONS count description: priv_type is the name of the permission, such as SELECT and UPDATE. the values of priv_type granted to different objects are also different. The TO clause is used TO set the user password. The ON keyword is followed by the name of the database or table to be granted permissions. the following sections describe them one by one. (1) when you grant table and column permissions to a table, priv_type can be of the following values: ● SELECT: grant the user the right to access a specific table using the SELECT statement. You can also include a table in a view formula. However, you must have the SELECT permission for each table (or view) specified in the view formula. ● INSERT: you can use the INSERT statement to add row power to a specific table. ● DELETE: you can use the DELETE statement to DELETE rows from a specific table. ● UPDATE: you can use the UPDATE statement to modify the values of a specific table. ● REFERENCES: grant the user the permission to create a foreign key to refer to a specific table. ● CREATE: grant users the right to CREATE a table using a specific name. ● ALTER: give the user the right to use the ALTERTABLE statement to modify the table. ● INDEX: give users the right to define indexes on tables. ● DROP: give the user the right to delete a table. ● ALL or all privileges: indicates ALL permission names. When granting table permissions, the ON keyword is followed by tb1_name, and tb1_name is the table name or View name. For example, grant the SELECT permission on the XS table to king. Use xscj; grant select on xs TOking @ use_name; note: if you specify a password for an existing user in the TO clause, the new password overwrites the original password. If the permission is granted to a USER that does not exist, MySQL will automatically execute a create user statement to CREATE the USER, but the password must be specified for the USER. For example, if user liu and zhang do not exist, grant them SELECT and UPDATE permissions on the XS table. Grant select, update on xs to liu @ localhost identified by 'lpwd', zhang @ localhostIDENTIFIED BY 'zpwd'; for column permissions, priv_type values can only be SELECT, INSERT, and UPDATE. The permission must be followed by the column name column_list. For example, Grant king the UPDATE permission for the student ID column and name column on the XS table. Grant update (name, student ID) on xs to king @ localhost; (2) GRANT the database permission table permission TO apply TO a specific table. MySQL also supports permissions for the entire database. For example, the permission to create tables and views in a specific database. When granting Database permissions, priv_type can be the following values: ● SELECT: grant the user the right to access all tables and views in a specific database using the SELECT statement. ● INSERT: you can use the INSERT statement to add rows to all tables in a specific database. ● DELETE: give the user the right to use the DELETE statement to DELETE rows of all tables in a specific database. ● UPDATE: you can use the UPDATE statement to UPDATE the values of all tables in a specific database. ● REFERENCES: Allows users to create foreign keys pointing to tables in a specific database. ● CREATE: grant the user the right to CREATE a new table in a specific database using the CREATETABLE statement. ● ALTER: grant the user the right to use the ALTERTABLE statement to modify all tables in a specific database. ● INDEX: give users the right to define and delete indexes on all tables in a specific database. ● DROP: give users the right to delete all tables and views in a specific database. ● Create temporary tables: grants users the right to create temporary tables in a specific database. ● Create view: gives users the right to CREATE a VIEW in a specific database. ● Show view: gives users the right to VIEW definitions of existing views in a specific database. ● Create routine: Allows users to CREATE stored procedures and functions for specific databases. ● Alter routine: giving users the power to update and delete existing stored procedures and stored functions in the database. ● Execute routine: gives users the power to call stored procedures and functions of a specific database. ● Lock tables: gives users the right to LOCK existing TABLES in a specific database. ● ALL or all privileges: indicates ALL the above permission names. In the GRANT syntax format, the ON keyword is followed by "*" and "db_name. *" when Database permissions are granted .*". "*" Indicates all tables in the current database; "db_name. *" indicates all tables in a database; *. * indicates all tables in all databases. For example, grant the SELECT permission to all tables in the XSCJ database of king. Grant select on xscj. * TO king @ localhost; Note: this permission applies TO all existing tables and any tables subsequently added TO the XSCJ database. For example, Grant king all Database permissions in the XSCJ database. Use xscj; grant all on * TO king @ localhost; similar TO table permissions, granting one database permission does not mean having another permission. If you are authorized to create new tables and views, you cannot access them. To access them, it also needs to be separately granted the SELECT permission or more permissions. (3) the most efficient permission for granting user permissions is user permissions. all statements that require granting Database permissions can also be defined in user permissions. For example, if you grant the CREATE permission to someone at the user level, you can CREATE a new database or CREATE a new table in all databases (instead of specific databases. When MySQL grants user permission, priv_type can also be the following values. ● Create user: gives users the right to CREATE and delete new users. ● Show databases: grants users the right to view the definitions of all existing DATABASES using the show databases statement. In the GRANT syntax format, "*. *" is used in the ON clause when the user is authorized to indicate all tables of all databases. For example, Grant CREATE, ALTERT, and DROP permissions to all tables in all databases to Peter. Grant create, ALTER, drop on *. * TO Peter @ localhost identified by 'ppwd '; for example, GRANT Peter the power to create a new user. Grant create user on *. * TO Peter @ localhost; 6. transfer permissions and restrict GRANT statements. the WITH clause can be used at the end. If it is specified as with grant option, it means that all users specified in the TO clause have the permission granted TO other users, regardless of whether other users have the permission. For example, grant the SELECT permission on the XS table to David and grant the permission to other users. Grant select on xscj. xs to David @ localhost identified by '000000' with grant option; you can pass this permission TO other users. assume that the user Jim has created: grant select on xscj. xs to Jim @ localhost; Note: after using the with grant option clause, if David has other permissions on the table, he can GRANT other permissions TO Jim, not limited to select. The WITH clause can also be used by a user. MAX_queries_PER_HOURcount indicates the number of times the database can be queried per hour; MAX_connections_PER_HOURcount indicates the number of times the database can be connected per hour; MAX_UPDATES_PER_HOURcount indicates the number of times the database can be modified per hour. Count indicates the maximum number of users connected to MySQL at the same time. Count is a value. for the first three values, if the value of count is 0, the value cannot be limited. For example, Grant Jim the permission to process only one SELECT statement per hour. Grant select on xs to Jim @ localhost WITH MAX_QUERIES_PER_HOUR1; 7. to REVOKE permissions from a USER but not from the USER table, you can use the REVOKE statement. this statement is similar to the format of the GRANT statement, but has the opposite effect. To use REVOKE, you must have the global create user permission or UPDATE permission for the mysql database. Syntax format: REVOKE priv_type [(column_list)] [, priv_type [(column_list)]... ON {table name | *. * | database name. *} FROM user [, user]... or: revoke all privileges, grant option from user [, user]... note: the first format is used to revoke certain permissions, and the second format is used to revoke all permissions of the user. For example, revoke the SELECT permission of user David on the XS table. Revoke select on xs from David @ localhost; because the SELECT permission of the user David ON the XS table is revoked, all the permissions that are directly or indirectly dependent ON the table are also revoked, in this example, Jim also loses the SELECT permission on the XS table. However, after the preceding statement is executed, with grant option is retained. when David is granted the permission for the same table again, it will immediately pass this permission to Jim. (Table maintenance statement) 1. the analyze table statement is a column that defines an index. the number of different values in the column is called the compression of the index column. you can use the showindex from tb_name statement to display it. The compression of an index column is not automatically updated. That is to say, a user creates an index for a column, and the column's compression is not calculated immediately. In this case, you need to use the analyze table statement to update it. Syntax format: ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name]... all updates executed on MySQL will be written to a binary log file. If you use the analyze table statement directly, the result data is also written into the log file. If the NO_eriteTO_binlog option is specified, disable this function (LOCAL is the synonym of NO_ERITE_TO_BINLOG), so that the ANALYZETABLE statement will be completed more quickly. For example, the index of table XS can be compressed and displayed later. Analyze table xs; show index from xs; 2. check table this statement is used to check whether one or more tables have errors. it only applies to MyISAM and InnoDB tables. Syntax format: check table tbl_name [, tbl_name]... [option]... where option is: QUICK | FAST | MEDIUM | EXTENDED | CHANGED description: This statement has multiple options. ● Quick: do not scan rows, do not check the wrong links, this is the fastest way. ● Fast: Check whether the checklist is correctly closed. ● Changed: check the modified table after the last check and the table that has not been properly closed. ● Medium: scans rows to verify that the deleted link is valid. You can also calculate the keyword checksum of each row and use the calculated checksum to verify this. ● Extended: search for all keywords in each row. This ensures that the table is 100% consistent, but it takes a long time. For example, check whether the XS table is correct. Check table xs; 3. the checksum table statement can be used to obtain a CHECKSUM for each TABLE in the database. Syntax format: checksum table tbl_name [, tbl_name]... [QUICK | EXTENDED] description: if the TABLE is a MyISAM TABLE, if QUICK is specified, the table checksum is reported; otherwise, the report is NULL. If EXTENDED is specified, the system only calculates the test and checks whether the table is a MyISAM table or not. 4. optimize table statements if you constantly use DELETE, INSERT, and UPDATE statements to UPDATE a table, the internal structure of the table will contain a lot of fragments and unused space. In this case, you can use the optimize table statement to reuse unused space and sort out data file fragments. The optimize table statement only applies to MyISAM, BDB, and InnoDB tables. Syntax format: OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name]... example: OPTIMIZE the xs table. 5. if a table or index is damaged, use the repair TABLE statement to fix it. Repair table only applies to MyISAM and ARCHIVE tables. REPAIR [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name]... [QUICK] [EXTENDED] [USE_FRM] Note: the repair table statement supports the following options: ● QUICK: If this option is specified, the repair table will try to REPAIR only the index tree. ● EXTENDED: With this option, MySQL creates an index row in one row instead of using the category to create an index at a time. ● USE_FRM: This option is required if the MYI index file is missing or the title is damaged. In addition, there are two TABLE maintenance statements: backup TABLE and restore TABLE. The backup table statement can be used to back up one or more MyISAM tables. Syntax format: backup table tbl_name [, tbl_name]... TO '/path/to/backup/directory' use the restore table statement TO obtain the backup of one or more tables created by BACKUPTABLE and read the data to the database. Syntax format: restore table tbl_name [, tbl_name]... FROM '/path/to/backup/directory', but these two statements are not ideal and are not recommended anymore. here is just a rough introduction. Optimize tablekc; author: tianyazaiheruanbitsCN.com