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

Editor to share with you how to use authorization and revocation commands in mysql, I believe most people do not know much about it, so share this article for your reference. I hope you will gain a lot after reading this article. Let's learn about it together.

MySQL's permission system revolves around two concepts:

Authentication-> determine whether the user is allowed to connect to the database server

Authorization-> determine whether the user has sufficient permissions to execute query requests, and so on.

If the authentication is not successful, then authorization must not be carried out.

The syntax of revoke is similar to that of grant, except that you need to replace the keyword "to" with "from"

Table permissions managed by GRANT and REVOKE

Authority

Description

ALL PRIVILEGES

Affects all permissions except WITH GRANT OPTION

ALTER

Affect the use of ALTER TABLE commands

ALTER ROUTINE

Affect the ability to create storage routines

CREATE

Affect the use of CREATE TABLE commands

CREATE ROUTINE

Affect the ability to change and deprecate storage routines

CREATE TEMPORARY TABLES

Affect the use of CREATE TEMPORARY TABLE commands

CREATE USER

Affect the ability to create, discard, rename and revoke user permissions

CREATE VIEW

Affect the use of CREATE VIEW commands

DELETE

Affect the use of DELETE commands

DROP

Affect the use of DROP TABLE commands

EXECUTE

Affect the ability of users to run stored procedures

EVENT

Affect the ability to execute events (starting with MySQL5.1.6)

FILE

Affect the use of SELECT INTO OUTFILE and LOAD DATA INFILE

GRANT OPTION

Affect the ability of users to delegate permissions

INDEX

Affect the use of CREATE INDEX and DROP INDEX commands

INSERT

Affect the use of INSERT commands

LOCK TABLES

Affect the use of LOCK TABLES commands

PROCESS

Affect the use of SHOW PROCESSLIST commands

REFERENCES

Placeholders for future MySQL features

RELOAD

Affect the use of the FLUSH command set

REPLICATION CLIENT

Affect the ability of users to query the locations of slave servers and master servers

(continued)

Authority

Description

REPLICATION SLAVE

The permissions required to copy from the server

SELECT

Affect the use of SELECT commands

SHOW DATABASES

Affect the use of SHOW DATABASES commands

SHOW VIEW

Affect the use of SHOW CREATE VIEW commands

SHUTDOWN

Affect the use of SHUTDOWN commands

SUPER

Affects the use of administrator-level commands such as CHANGE, MASTER, KILL thread, mysqladmindebug, PURGE MASTER LOGS, and SET GLOBAL

TRIGGER

Affect the ability to execute triggers (starting with MySQL5.1.6)

UPDATE

Affect the use of UPDATE commands

USAGE

Connect only, no permissions are granted

1 >. Change the meter. Your account does not allow remote login to MySQL server, only in localhost.

Solution:

On the computer in localhost, after logging in to mysql, change the "host" entry in the "user" table in the "mysql" database from "localhost" to "%".

(1)。 Mysql-u root-pvmwaremysql > use mysql

(2)。 Mysql > update user set host ='% 'where user =' root'

(3)。 Mysql > select host, user from user

2 >. Authorization law. For example, if you want myuser to connect to the mysql server from any host using mypassword.

(1)。 GRANT ALL PRIVILEGES ON *. * TO 'myuser'@'%' IDENTIFIED BY' mypassword' WITH GRANT OPTION

(2)。 FLUSH PRIVILEGES

If you want to allow the user myuser to connect to the mysql server from the host with ip 192.168.1.6, and use mypassword as the password

(1)。 GRANT ALL PRIVILEGES ON *. * TO 'myuser'@'192.168.1.3' IDENTIFIED BY' mypassword' WITH GRANT OPTION

(2)。 FLUSH PRIVILEGES

If you want to allow the user myuser to connect to the dk database of the mysql server from the host with ip 192.168.1.6, and use mypassword as the password

(1)。 GRANT ALL PRIVILEGES ON dk.* TO 'myuser'@'192.168.1.3' IDENTIFIED BY' mypassword' WITH GRANT OPTION

(3)。 FLUSH PRIVILEGES

Note: FLUSH PRIVILEGES; is required after authorization, otherwise it cannot take effect immediately.

Another way:

3 >. Run on the machine where mysql is installed:

1. D: "mysql" bin "> mysql-h localhost-u root

/ / this should allow access to the MySQL server

2. Mysql > GRANT ALL PRIVILEGES ON *. * TO 'root'@'%' WITH GRANT OPTION

/ / Grant any host permission to access data

3. Mysql > FLUSH PRIVILEGES

/ / the modification takes effect

4. Mysql > EXIT

/ / exit the MySQL server

This allows you to log in as root on any other host!

4 >. View MySQL user rights

View current user (own) permissions:

Show grants

View additional MySQL user rights:

Show grants for dba@localhost

5 >. Revoke the permissions that have been granted to the MySQL user.

The syntax of revoke is similar to that of grant, except that you can replace the keyword "to" with "from":

Grant all on *. * to dba@localhost

Revoke all on *. * from dba@localhost

6 > considerations for user rights of .MySQL grant and revoke

1. After the grant, revoke user permission, the user will not take effect until the user reconnects to the MySQL database.

two。 If you want authorized users, you can also grant these permissions to other users. You need the option "grant option".

Grant select on testdb.* to dba@localhost with grant option

This feature is generally not needed. In practice, database permissions are best managed by DBA.

7 >. User and rights management commands:

Create user: used to create a new user account (this command has been available since version 5.0). No permissions are assigned when this user is created.

After creation, you need to use the grant command to assign the appropriate permissions to the changed user.

Eg:create user guest@localhost identified by '123456'

Grant select on mydb.* to guest@localhost

Drop user: delete a user account (note that only accounts without any permissions can be deleted before version 4.1.1, and any account can be deleted after 5.0.2)

Eg:drop user guest

Rename user: you can rename a user account.

Grant: used to manage access rights, that is, to authorize user accounts. Of course, it can also create a new user account.

Eg:grant select, insert, update, delete on new_db.* to guest@'%' identified by '88888888'

Grant permissions on database. Table to user @ access method identified by password

Grant select on mydb.* to guest@localhost identified by '123456'

BTW: if you need an empty password or an account without a password, you must first use the Create User command, and then use the

Grant to assign permissions. Do the following:

Grant all privileges on mydb.* to visitor@'%'; but the visitor is not created first in the database user table

User, a 1133 error "Can't find any matching row in the user table" occurs. Grant can only create

An account with a password.

Revoke: delete an account and view the MySQL document specifically.

8 >. MySQL can grant you one or more of the permissions such as select,insert,update,delete. You can mainly use the grant command in the following format:

Grant permissions on database object to user

Grant ordinary data users, the right to query, insert, update and delete all table data in the database.

Grant select on testdb.* to common_user@'%'

Grant insert on testdb.* to common_user@'%'

Grant update on testdb.* to common_user@'%'

Grant delete on testdb.* to common_user@'%'

Or, replace it with a MySQL command:

Grant select, insert, update, delete on testdb.* to common_user@'%'

9 > .grant database developer, create tables, indexes, views, stored procedures, functions. Wait for permission.

Grant creates, modifies, and deletes MySQL data table structure permissions.

Grant create on testdb.* to developer@'192.168.0.%'

Grant alter on testdb.* to developer@'192.168.0.%'

Grant drop on testdb.* to developer@'192.168.0.%'

Grant manipulates MySQL foreign key permissions.

Grant references on testdb.* to developer@'192.168.0.%'

Grant manipulates MySQL temporary table permissions.

Grant create temporary tables on testdb.* to developer@'192.168.0.%'

Grant manipulates MySQL index permissions.

Grant index on testdb.* to developer@'192.168.0.%'

Grant manipulates MySQL view and view view source code permissions.

Grant create view on testdb.* to developer@'192.168.0.%'

Grant show view on testdb.* to developer@'192.168.0.%'

Grant manipulates MySQL stored procedures and function permissions.

Grant create routine on testdb.* to developer@'192.168.0.%';-- now, can show procedure status

Grant alter routine on testdb.* to developer@'192.168.0.%';-- now, you can drop a procedure

Grant execute on testdb.* to developer@'192.168.0.%'

10 > .grant the permissions of an ordinary DBA to manage a MySQL database.

Grant all privileges on testdb to dba@'localhost'

The keyword "privileges" can be omitted.

11 > .grant Advanced DBA manages permissions for all databases in MySQL.

Grant all on *. * to dba@'localhost'

12 > .MySQL grant permissions, which can be used at multiple levels.

1. Grant acts on the entire MySQL server:

Grant select on *. * to dba@localhost;-- dba can query tables in all databases in MySQL.

Grant all on *. * to dba@localhost;-- dba can manage all databases in MySQL

2. Grant acts on a single database:

Grant select on testdb.* to dba@localhost;-- dba can query tables in testdb.

3. Grant acts on a single data table:

Grant select, insert, update, delete on testdb.orders to dba@localhost

4. Grant acts on the columns in the table:

Grant select (id, se, rank) on testdb.apache_log to dba@localhost

5. Grant acts on stored procedures and functions:

Grant execute on procedure testdb.pr_add to 'dba'@'localhost'

Grant execute on function testdb.fn_add to 'dba'@'localhost'

Note: after modifying permissions, be sure to refresh the service, or restart the service, refresh the service with: FLUSH PRIVILEGES.

These are all the contents of this article entitled "how to use Authorization and revocation commands 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.