How to use grant command in MySQL

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 用户

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@'%'

Or, use a MySQL command instead:

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

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

grant Permission to operate MySQL foreign keys.

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

grant permission to operate MySQL temporary tables.

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

grant permission to operate MySQL indexes.

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

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

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

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

grant all privileges on testdb to dba@'localhost'

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'

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 中的所有数据库

2. Grant applies to a single database:

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

3. Grant applies to a single data table Above:

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

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';

4. Grant acts on 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'

6. View MySQL user permissions

View the current user (self) permissions:

show grants;

View other MySQL user permissions:

show grants for dba@localhost;

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;

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;

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!