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

Editor to share with you the example analysis of permissions 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!

I. Preface

Many articles will say that database permissions are based on the principle of minimum permissions, which is not wrong in itself, but it is empty talk. Because of the minimum permissions, this thing is too abstract, and most of the time you don't know exactly what permissions he needs. At present, many mysql use root accounts to operate, it's not that we don't know that using root permissions is too big and unsafe, but that many people don't know what kind of permissions should be given to them, which is both safe and safe. Therefore, this article is more concerned with how to simply configure a secure mysql in this case. Note: the test environment for this article is mysql-5.6.4.

II. Introduction of Mysql permissions

There are four tables to control permissions in mysql, namely, user table, db table, tables_priv table, and columns_ privilege table.

The verification process for the mysql permissions table is:

1. First, determine whether the connected ip, user name and password exist from the three fields of Host,User,Password in the user table, and then pass the verification.

two。 After passing the authentication, the permissions are assigned and verified in the order of user,db,tables_priv,columns_priv. That is, check the global permissions table user first. If the corresponding permission in user is Y, the user's permissions on all databases are Y and will no longer check db. If tables_priv,columns_priv; is N, check the specific database corresponding to this user in the db table and get the permissions of Y in db. If N in db, check the specific table corresponding to this database in tables_priv and obtain the permissions Y in the table, and so on.

III. What are the permissions of mysql

IV. Analysis of permissions at the database level (db table)

5. Mysql security configuration scheme

1 restrict access to ip of mysql port

Windows can be restricted through windows firewall or ipsec, and under linux through iptables.

2 modify the port of mysql

Windows can modify the configuration file my.ini to achieve, linux can modify the configuration file my.cnf to achieve.

3 set a strong password for all users and strictly specify the access ip of the corresponding account

In mysql, you can specify the user's access to ip in the user table

4. Processing of root privileged account

It is recommended to set a strong password for the root account and specify that only local login is allowed.

5 processing of logs

If you need to open the query log, the query log will record the login and query statements.

6 mysql process running account

It is forbidden to use local system to run the mysql account under windows. You can consider using network service or create a new account yourself, but you must give read permission to the directory where the mysql program resides and read and write permissions to the data directory; under linux, create a new mysql account and specify mysql to run as the mysql account during installation, giving read permission to the directory where the program resides and read and write permissions to the directory where the data is located.

7 disk permissions of mysql running account

1) the mysql account needs to give read permission to the directory where the program resides, as well as read and write permissions to the data directory.

2) write and execute permissions for other directories are not allowed, especially those with websites.

3) cancel the execution authority of the mysql running account for some programs such as cmd,sh.

8 handling of mysql accounts used by websites

Create a new account and give the account all permissions in the database used. This can not only ensure the full operation of the website to the corresponding database, but also ensure that the account will not affect the security because of the high authority. An account that gives all permissions to a single database will not have administrative privileges such as super, process, file, etc. Of course, if you can clearly know what permissions my site needs, or do not give more permissions, because many times publishers do not know what permissions the site needs, I recommend the above configuration. And I mean general-purpose, specific to only a few machines, not many cases, I personally suggest to give only the necessary permissions, specific can refer to the recommendations in the table above.

9 delete useless database

Test database has permission for newly created accounts by default.

VI. Analysis and Preventive measures of mysql intrusion

Generally speaking, there are several ways to raise the right of mysql:

1 udf raises the right

The key to this method is to import a dll file. I think that as long as the write permission of the process account to the directory is reasonably controlled, the dll file can be prevented from being imported; then, in case of breach, as long as the permission of the process account is low enough, no high-risk operations are performed, such as adding accounts.

2 write startup file

In this way, it is the same as above, it is still necessary to reasonably control the process account's write permission to the directory.

3 when the root account is leaked

If the root account is not properly managed and the root account is invaded, there must be no way to guarantee the database information. However, if the permissions of the process account are controlled, as well as its access to the disk, the server can still be guaranteed not to be occupied.

(4) General account disclosure (as mentioned above, only accounts with full access to a certain database)

The ordinary account here refers to the account used by the website. One of the more convenient suggestions I give is to give all permissions to a specific library directly. Account leaks include the existence of injection and the direct access to the database account password after the web server is hacked.

At this point, the data of the corresponding database is not guaranteed, but it does not threaten other databases. Moreover, the ordinary account here does not have file permission, so all files cannot be exported to disk. Of course, the permissions of the process account will be strictly controlled at this time.

What kind of permissions given to ordinary accounts can be seen in the table above, it really does not give all permissions to a library directly.

Common commands required for security configuration

1. Create a new user and give permissions to the corresponding database

Grant select,insert,update,delete,create,drop privileges on database.* to user@localhost identified by 'passwd'; grant all privileges on database.* to user@localhost identified by' passwd'

two。 Refresh permissions

Flush privileges

3. Show authorization

Show grants

4. Remove authorization

Revoke delete on *. * from 'jack'@'localhost'

5. Delete user

Drop user 'jack'@'localhost'

6. Rename a user

Rename user 'jack'@'%' to' jim'@'%'

7. Change the password for the user

SET PASSWORD FOR 'root'@'localhost' = PASSWORD (' 123456')

8. Delete database

Drop database test

9. Export a file from a database

Select * from an into outfile "~ / abc.sql" above are all the contents of this article entitled "sample Analysis of permissions 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.