Shulou(Shulou.com)05/31 Report--

Editor to share with you the example analysis of the permission system in MySQL, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Create user 'liub'@'localhost' identified by' liub'

Create table g_user (

Id varchar (10)

Username varchar (20)

Userpwd varchar (20)

);

Insert into g_user values ('1century, last month, week, month, week, week, month, day, day, week, week

Insert into g_user values ('2 years ago, like girls, girls)

Mysql.user

Mysql.db

Mysql.tables_priv

Mysql.columns_priv

Www.2cto.com

Select * from g_user

-column level permissions

Grant select (id,username) on g_user to 'liub'@'localhost'

-- Table-level permissions

Grant select on g_user to 'liub'@'localhost'

-- Library-level permissions

Grant select on test.* to 'liub'@'localhost'

Show grants for 'liub'@'localhost'

Select * from columns_priv

Select * from tables_priv

Select * from db

Use test www.2cto.com

Grant insert on g_user to 'liub'@'localhost'

Select * from mysql.user

Flush privileges

Show grants for 'liub'@'localhost'

The implementation of the permission system of MySQL is relatively simple, and the relevant permission information is mainly stored in several system tables called grant tables, namely: mysql.User,mysql.db,mysql.Host,mysql.table_priv and mysql.column_priv. Because the amount of permission information data is relatively small and the access is very frequent, Mysql will Load all the permission information into memory and store it in several specific structures when it is started. That's why 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 modify the relevant permissions through the GRANT,REVOKE or DROP USER command, we do not need to execute the FLUSH PRIVILEGES command manually, because the permission changes made through the GRANT,REVOKE or DROP USER command will update the permission information in the memory structure while modifying the system table. In MySQL5.0.2 or later, MySQL also adds the CREATE USER command to create users without any special permissions (only the initial USAGE permissions), and when a new user is created with the CREATE USER command, the new user's information is automatically updated to the memory structure. Therefore, it is recommended that readers generally try to use GRANT,REVOKE,CREATE USER and DROPUSER commands to change users and permissions, so as to minimize the direct modification of grant tables to achieve the operation of changing users and permissions.

Show grants for 'income'@'%'

Global Level

GRANT SELECT,UPDATE,DELETE,INSERT ON *. * TO 'def'@'localhost'

When verifying all the required permissions, MySQL will first look up the permission data stored in the memory structure, first look for Global Level permissions, if all the required permissions are defined in Global Level (GRANT or REVOKE), then complete the permission verification (pass or deny). If the definitions of all permissions are not found, it will continue to look for Database Level permissions later to verify the required permissions that are not defined by Global Level. If you still can't find all the required permission definitions, MySQL continues to look for a smaller domain of permission definitions, that is, TableLevel, and finally Column Level or Routine Level. Www.2cto.com

First, create a user:

Command: CREATE USER 'username'@'host' IDENTIFIED BY' password'

Description: username-the user name you will create, host-specifies the host on which the user can log in, if the local user is available localhost, if you want the user to log in from any remote host, you can use the wildcard%. Password-the user's login password, which can be empty. If it is empty, the user can log on to the server without a password.

Example: CREATE USER 'dog'@'localhost' IDENTIFIED BY' 123456'

CREATE USER 'pig'@'192.168.1.101' IDENDIFIED BY' 123456'

CREATE USER 'pig'@'%' IDENTIFIED BY' 123456'

CREATE USER 'pig'@'%' IDENTIFIED BY''

CREATE USER 'pig'@'%'

Second, authorization:

Command: GRANT privileges ON databasename.tablename TO 'username'@'host'

Description: privileges-user's operation rights, such as SELECT, INSERT, UPDATE, etc. If you want to grant the permission, use ALL.;databasename-name, tablename- table name, and if you want to grant the user the corresponding operation rights to all databases and tables, you can use *, such as *. *.

Example: GRANT SELECT, INSERT ON test.user TO 'pig'@'%'

GRANT ALL ON *. * TO 'pig'@'%'

Note: a user authorized with the above command cannot authorize another user. If you want that user to be authorized, use the following command:

GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION

Www.2cto.com

three。 Set and change user password

Command: SET PASSWORD FOR 'username'@'host' = PASSWORD (' newpassword'); if it is the current login user, use SET PASSWORD = PASSWORD ("newpassword")

Example: SET PASSWORD FOR 'pig'@'%' = PASSWORD ("123456")

four。 Revoke user rights

Command: REVOKE privilege ON databasename.tablename FROM 'username'@'host'

Description: privilege, databasename, tablename-same as the authorization section.

Example: REVOKE SELECT ON *. * FROM 'pig'@'%'

Note: if you authorize the user 'pig'@'%'' like this (or similar): GRANT SELECT ON test.user TO 'pig'@'%', using the REVOKE SELECT ON *. * FROM' pig'@'%'; command does not undo the user's SELECT operation on the user table in the test database. Conversely, if the authorization uses GRANT SELECT ON *. * TO 'pig'@'%';, the REVOKE SELECT ON test.user FROM' pig'@'%'; command cannot revoke the user's Select permission on the user table in the test database. Www.2cto.com

Specific information can be viewed with the command SHOW GRANTS FOR 'pig'@'%';.

five。 Delete user

Command: DROP USER 'username'@'host'

The above is all the content of this article "sample Analysis of permission system in MySQL". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.