MySQL Permission System Analysis

MySQL permission system analysis create user 'lib' @ 'localhost' identified by 'lib' www.2cto.com create table g_user (id varchar (10), username varchar (20 ), userpwd varchar (20); insert into g_user values ('1', 'lib', 'lib'); insert into g_user values ('2', 'lib ', 'lik'); mysql. usermysql. dbmysql. tables_privmysql.columns_priv www.2cto.com select * from g_user; -- column-level permission grant select (id, username) on g_user to 'lib' @ 'localhost ';-- Table-level permission grant select on g_user to 'lib' @ 'localhost'; -- database-level permission grant select on test. * to 'lib' @ 'localhost'; show grants for 'lib' @ 'localhost '; select * from columns_privselect * from tables_privselect * from dbuse test www.2cto.com grant insert on g_user to 'lib' @ 'localhost' select * from mysql. userflush privileges; show grants for 'libub '@ 'localhost' the MySQL permission system is easy to implement. The permission information is mainly stored in several In the system table, mysql. User, mysql. db, mysql. Host, mysql. table_priv, and mysql. column_priv. Because the volume of permission information is small and frequently accessed, Mysql will Load all permission information to the memory to save several specific structures at startup. So every time we manually modify the permission-related table, we need to execute the flush privileges command to reload the permission information of MySQL. Of course, if we use the GRANT, REVOKE, or drop user command to modify related permissions, we do not need to manually execute the flush privileges command because, the permission modification made by the REVOKE or drop user command also updates the permission information in the memory structure while modifying the system table. MySQL also added the create user command in MySQL 5.0.2 or later versions to CREATE users without any special permissions (only with the initial USAGE permission, after a new USER is created using the create user command, the information of the new USER is automatically updated to the memory structure. Therefore, we recommend that you use the GRANT, REVOKE, create user, and DROPUSER commands whenever possible to change users and permissions, minimize the number of user and permission changes by directly modifying grant tables. Show grants for 'Welcome '@' % 'Global LevelGRANT SELECT, UPDATE, DELETE, insert on *. * TO 'def '@ 'localhost'; when verifying all required permissions, MySQL first looks for the permission data stored in the memory structure, and first looks for the Global Level permission, if the required permissions are defined at the Global Level (GRANT or REVOKE), the permission verification is completed (pass or deny). If no definition of all permissions is found, the system will continue to search for the Database Level permissions and verify the required permissions for the undefined Global Level. If no definition of all required permissions is found, mySQL will continue to search for a smaller range of permission definition fields, that is, TableLevel, and finally Column Level or Routine Level. Www.2cto.com

1. CREATE a USER: Command: create user 'username' @ 'host' identified by 'Password'; Description: username-the username you will CREATE, host-specifies the host on which the user can log on. If a local user can use localhost, if you want the user to log on from any remote host, you can use the wildcard %. password-the user's login password. The password can be blank. If it is blank, the user can log on to the server without the password. example: create user 'Dog' @ 'localhost' identified by '000000'; create user 'pig' @ '2017. 168.1.101 'idendified BY '000000'; create user 'pig' @ '% 'identified BY '000000'; create user 'pig' @' % 'identified ''; create user 'pig' @ '%'; 2. Authorization: Command: GRANT privileges ON databasename. tablename TO 'username' @ 'host' Description: privileges-user operation permissions, such as SELECT, INSERT, and UPDATE. use ALL .; databasename-Database Name, tablename-table name. If you want to grant the user the corresponding operation permissions on all databases and tables, it can be represented by *, as shown in *. *. example: grant select, insert on test. user TO 'pig' @ '%'; grant all on *. * TO 'pig' @ '%'; note: the user authorized by the preceding command cannot authorize other users. TO authorize the user, run the following command: GRANT privileges ON databasename. tablename TO 'username' @ 'host' with grant option; www.2cto.com 3. set password for 'username' @ 'host' = PASSWORD ('newpassword '); if the current login user uses set password = PASSWORD ("newpassword"); example: set password for 'pig' @ '%' = PASSWORD ("123456"); 4. command: REVOKE privilege ON databasename. tablename FROM 'username' @ 'host'; Description: privilege, databasename, tablename-same authorization part. example: revoke select on *. * FROM 'pig' @ '%'; Note: if this is the case when you authorize the user 'pig' @ '%' (or similar ): grant select on test. user TO 'pig' @ '%', use revoke select on *. * FROM 'pig' @ '%'; the command does not cancel the SELECT Operation on the user table in the test database. conversely, grant select on * is used for authorization *. * TO 'pig' @ '%'; then revoke select on test. user FROM 'pig' @ '%'; the command cannot revoke this user's Select permission on the user table in the test database. FOR details about www.2cto.com, run the show grants for 'pig' @ '%' command. 5. delete USER command: drop user 'username' @ 'host'; Author: bengda