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

What is the security mechanism of MySQL? I believe many inexperienced people don't know what to do about it. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

Improve the security of data by giving appropriate permissions to MySQL users.

MySQL mainly includes root users and ordinary users. Root users have all permissions, while ordinary users can only have partial permissions.

Permission tables are stored in MySQL's system database mysql, mainly mysql.user, mysql.db, table_priv, columns_priv, and procs_ tables.

I. MySQL user rights

The permission information is saved in the user table, including user field, permission field, security field and resource control field, with a total of 39 fields.

II. MySQL user mechanism

Log in to MySQL under DOS complete command:

Mysql-h hostname | hostIP-p port-u username-p DatabaseName-e "SQL statement"

Where:

-h specify the address of the connected MySQL server in two ways: hostname (hostname) and hostIP (host IP address)

-p specifies the port number of the attached MySQL server. The default is 3306.

-u specifies the user.

-p prompts for a password.

DatabaseNmae specifies which database to log in to, and the default is mysql database.

-e specifies the SQL statement to be executed.

Example:

Mysql-h localhost-u root-pNumber@01 school (there is no space between-p and password! )

Mysql-h localhost-u root-pNumber@01 school-e "select * from students"

Exit and login under DOS: exit | quit

Create a user:

1. Create user creates ordinary users and cannot assign weight values.

Create user user_ name [identified by [password] 'password']

User_name [identified by [password] 'password']].

Where the password keyword means to encrypt the password.

Example:

Create user test identified by '123'

2. Insert creates ordinary users and cannot assign weight values.

Insert into user (Host,User,Password) values ('hostname','username',password (' password'))

3. Grant creates ordinary users and can assign weight values.

Grant priv_type on databasename.tablename

To username [identified by [password] 'password']

Username [identified by [password] 'password']].

Change the password:

1. Through the mysqladmin command

Mysqladmin-u username-p password "new_password"

2. Modify the root user password through the set command

Log in to root first

Set password = password ("new_password")

3. Update user data records and modify root and ordinary user passwords

Update user set password = password ("new_password")

Where user = 'root' and host =' localhost'

Example:

Update user set password = password ("234")

Where user = 'test' and host =' localhost'

Delete user account:

Drop user user1 [, user2]

Or

Delete from user

Where user = 'username' and host =' localhost'

III. Authority management

1. Authorization

Grant priv_type [(column_list) on databse.table to

User [identified by [password] 'password']

[, user [identified by [password] 'password']]

[with with-option [with-option].]

Where:

Priv_type represents the permission type; column_list represents the field on which the permission acts, and the entire table when this parameter is omitted.

DATABASE.table represents a table in the database

The with-option parameter can be taken as the following values:

Grant option: authorized users can grant permissions to other users

MAX_QUERIES_PER_HOUR count; setting allows count queries to be executed per hour

MAX_UPDATES_PER_HOUR count: set to perform count updates per hour

MAX_CONNECTIONS_PER_HOUR count: set up count connections per hour

MAX_USER_CONNECTIONS count: sets that a single user can have count connections at the same time

2. View permissions

Show grants for user_name

Show grants for 'test'

3. Take back the authority

Revoke priv_type [(column_list)] on DATABASE.table

From user1 [identified by [password] 'password']

Take back all permissions

Revoke all privileges,grant option

From user1 [identified by [password] 'password']

Revoke all privileges,grant option

From 'test'

Show grants for 'test'

After reading the above, have you mastered the method of MySQL security mechanism? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.