How to use the MySQL authorization command grant

The examples in this article run on MySQL 5.0 and above.

The simple format of the MySQL command for granting user permissions can be summarized as follows:

Grant permissions on database objects to users

1. Grant ordinary data users the rights to query, insert, update, and delete data in all tables 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 developers to create tables, indexes, views, stored procedures, and functions. . . And other permissions.

Grant permissions to create, modify, and delete MySQL 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 permissions to operate MySQL foreign keys.

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

Grant permissions to operate MySQL temporary tables.

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

Grant permissions to operate MySQL indexes.

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

Grant permissions 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 permissions to operate MySQL stored procedures and functions.

grant create routine on testdb.* to developer@'192.168.0.%'; -- now, you 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 an ordinary DBA the authority to manage a MySQL database.

grant all privileges on testdb to dba@'localhost'

The keyword "privileges" can be omitted.

4. Grant senior DBA permissions 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 can query tables in all databases in MySQL.
grant all on *.* to dba@localhost; -- dba can manage all databases in MySQL

2. Grant is applied to 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;

Here, when authorizing multiple tables for 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 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'

6. Check MySQL user permissions

View the current user's (your own) permissions:

show grants;

View other MySQL user permissions:

show grants for dba@localhost;

7. Revoke the permissions that have been granted to the MySQL user.

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

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

8. Notes on granting and revokeing user permissions in MySQL

1. After granting or revokeing user permissions, the permissions will take effect only when the user reconnects to the MySQL database.

2. If you want the authorized user 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 centrally by the DBA.

The above is the details of how to use the authorization command grant in MySQL. For more information about the MySQL authorization command grant, please pay attention to other related articles on 123WORDPRESS.COM!