Shulou(Shulou.com)06/01 Report--

The following is mainly to bring you how to effectively manage mysql permissions, I hope these contents can bring you practical use, this is also the main purpose of my editing mysql permissions how to effectively manage this article. Okay, no more nonsense, let's go straight to the following.

I. Create and delete users

create user 'username' identified by 'user password';

Delete: drop user 'username'@'local or remote';

For example, delete local user kkk: drop user 'kkk'@'host';

Note: No matter whether it is new or deleted, the operation will only take effect after refreshing permissions.

II. Set and update passwords in an encrypted manner

Set password (default): update mysql.user set password=password ('***') where user=' root';

Update password (specify user privilege identity): update mysql.user set password=password ('new password') where user="username" and host="local or remote";

Example:update mysql.user set password=password ('new password') where user="test" and host="localhost";

Note: Settings take effect after flushing privileges;

Forgot root password (centos for example):

I: Modify mysql configuration file: vim /etc/my.cnf

Add skip-grant-tables under [mysqld] Save exit, restart mysql service

II: execute mysql -uroot -p to enter with empty password, and then reset password by modifying password

III. Permission grant and retraction (for users, for libraries)

Database permissions are:

* ALTER: Modify tables and indexes.

* CREATE: Create databases and tables.

* Delete: Delete existing records from the table.

* DROP: Drop databases and tables.

* INDEX: Creates or discards an index.

* INSERT: Insert a new row into a table.

* REFERENCE: Not used.

* SELECT: Retrieves records in a table.

* UPDATE: Modify an existing table record.

* FILE: Read or write files on the Cloud Virtual Machine.

* PROCESS: View thread information executing in Cloud Virtual Machine or kill thread.

* RELOAD: Overload authorization tables or empty logs, host cache, or table cache.

* SHUTDOWN: Shut down the Cloud Virtual Machine.

* ALL: ALL PRIVILEGES Synonyms.

* USAGE: Special "no permissions" permissions.

Permission granted:

Example 1-Restricted network segment: All privileges are open to root user, but only remote connections of hosts in 10.x.x.x network segment are allowed. For example: grant all privileges on *.* to 'root'@'10.% ' identified by '****' with grant option;

Example 2-Limit IP:

grant all privileges on *.* to 'root'@'10.12.1.23' identified by '****' with grant option;

Example 3-Limit multiple databases (for the case where the database name is the same in front, e.g., databases that all start with sm_):

grant select,update,insert on `sm_%`.* to test@'%' identified by 'test';

Note: The symbol above sm_% here is not a single quotation mark, but a small dot command character in the upper left corner of the keyboard.

Authority withdrawn:

revokePermission granted on *.* from 'username'@'local or remote identity';

For example: withdraw all root privileges granted to remote login

revoke all on *.* from 'root'@'%';###Note that after the authorization is cancelled, the user can still connect if the user's record is in mysql. At this time, only by further clearing the record in mysql.user table can the user be denied login.

Note: Refresh permissions to make the operation effective

IV. Clear user records in mysql.user table

delete from mysql.user where user='username' and host='local or remote identifier';

For example: clear the record of remote user root in mysql.user table

delete from mysql.user where user='root' and host='%';

flush privileges;

v. Query user permissions and passwords:

select user,host,password from mysql.user; ## password can be added as needed

As shown in the figure:

2. Query the current user's situation: select user();

3. Query the currently used version of mysql: select version();

For the above on how to effectively manage mysql permissions, do you think it is very helpful? If you need to know more, please continue to pay attention to our industry information, I believe you will like these contents.

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.