Home > Database > Mysql Tutorial > body text

How to use grant command in MySQL

coldplay.xixi
Release: 2020-07-11 17:33:58
forward
3060 people have browsed it

The example in this article runs on MySQL 5.0 and above.

MySQL The simple format of the command to grant user permissions can be summarized as:

grant 权限 on 数据库对象 to 用户
Copy after login

Related learning recommendations: mysql video tutorial

1. Grant ordinary data users have 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@'%'
Copy after login

Or, use a MySQL command instead:

grant select, insert, update, delete on testdb.* to common_user@'%'
Copy after login

2. Grant database developer to create tables, indexes, views, stored procedures, and functions. . . and other permissions.

grant Permission to create, modify, and delete MySQL data table structures.

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.%';
Copy after login

grant Permission to operate MySQL foreign keys.

grant references on testdb.* to developer@'192.168.0.%';
Copy after login

grant permission to operate MySQL temporary tables.

grant create temporary tables on testdb.* to developer@'192.168.0.%';
Copy after login

grant permission to operate MySQL indexes.

grant index on testdb.* to developer@'192.168.0.%';
Copy after login

grant permission to operate MySQL views and view view source code.

grant create view on testdb.* to developer@'192.168.0.%';
grant show view on testdb.* to developer@'192.168.0.%';
Copy after login

grant permission to operate MySQL stored procedures and functions.

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.%';
Copy after login

3. grant ordinary DBA’s authority to manage a MySQL database.

grant all privileges on testdb to dba@'localhost'
Copy after login

Among them, the keyword "privileges" can be omitted.

4. Grant the senior DBA the authority to manage all databases in MySQL.

grant all on *.* to dba@'localhost'
Copy after login

5. MySQL grant permissions can be applied at multiple levels.

1. Grant applies to the entire MySQL server:

grant select on *.* to dba@localhost; -- dba 可以查询 MySQL 中所有数据库中的表。
grant all on *.* to dba@localhost; -- dba 可以管理 MySQL 中的所有数据库
Copy after login

2. Grant applies to a single database:

grant select on testdb.* to dba@localhost; -- dba 可以查询 testdb 中的表。
Copy after login

3. Grant applies to a single data table Above:

grant select, insert, update, delete on testdb.orders to dba@localhost;
Copy after login

When authorizing multiple tables to a user, the above statement can be executed multiple times. For example:

grant select(user_id,username) on smp.users to mo_user@'%' identified by '123345';
grant select on smp.mo_sms to mo_user@'%' identified by '123345';
Copy after login

4. Grant acts on columns in the table:

grant select(id, se, rank) on testdb.apache_log to dba@localhost;
Copy after login

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'
Copy after login

6. View MySQL user permissions

View the current user (self) permissions:

show grants;
Copy after login

View other MySQL user permissions:

show grants for dba@localhost;
Copy after login

7. Revoke has been granted to Permissions for MySQL user permissions.

revoke has similar syntax to grant, just replace the keyword "to" with "from":

grant all on *.* to dba@localhost;
revoke all on *.* from dba@localhost;
Copy after login

8. MySQL grant, revoke user permissions Notes

1. After granting and revoke user permissions, the permissions can only take effect if the user reconnects to the MySQL database.

2. If you want authorized users to grant these permissions to other users, you need the option "grant option"

grant select on testdb.* to dba@localhost with grant option;
Copy after login

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

The above is the detailed content of How to use grant command in MySQL. For more information, please follow other related articles on the PHP Chinese website!

Related labels:
source:jb51.net
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template