Detailed explanation of how to use the MySQL authorization command grant

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 Permissionson Database objects to User

1. grant ordinary data user, query, insert, update, delete all tables in the database Data Rights.

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 creates 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 statusgrant
alter routine on testdb.* to developer@'192.168.0.%'; -- now, you can drop a proceduregrant
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 can query tables in all databases in MySQL.
grant all on *.* to dba @localhost; -- dba can manage all databases in MySQL

2. Grant works on a single database:

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

#3. Grant works on a single data table:

grant

select, insert, update , delete on testdb.orders to dba@localhost; #Here we authorize multiple photos for one user table, you can execute the above statement 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 works 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 users Permissions:

show grants for dba@localhost;

## 7. Revoke the permissions that have been granted to the 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. Notes on MySQL grant and revoke user permissions

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 Detailed explanation of how to use the MySQL authorization command grant. For more information, please follow other related articles on the PHP Chinese website!