Details of MySQL's Grant command

This article runs MySQL 5.0 and above.

The simple format of the MySQL user authorization command can be summarized as follows:

grant permissions on database object to user

1. grant general data user, 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 an MySQL command:

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

grant database developer, creating tables, indexes, views, stored procedures, functions... Such as permissions.

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 operates the MySQL foreign key permissions.

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

grant operates MySQL temporary table permissions.

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

grant operates the MySQL index permissions.

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

grant operates on MySQL 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 operates on MySQL stored procedure, 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.%';

3. grant Common DBA manages permissions for an MySQL database.

grant all privileges on testdb to dba@'localhost'

The key word "privileges" can be omitted.

4. grant Advanced DBA manages permissions for all databases in MySQL.

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

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

1. grant functions on the entire MySQL server:

grant select on *.* to dba@localhost; -- dba  You can query  MySQL  All tables in the database. 
grant all on *.* to dba@localhost; -- dba  Can manage  MySQL  All the databases in 

2. grant works on a single database:

grant select on testdb.* to dba@localhost; -- dba  You can query  testdb  In the table.  

3. grant works on a single data table:

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

The above statements can be executed multiple times while authorizing multiple tables to a user. Such as:

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 works on the columns in the table:

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

5. grant is used 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 current user (oneself) permissions:

show grants; 

View other MySQL user permissions:

show grants for dba@localhost;

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

revoke has the same syntax as 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 matters needing attention

1. After the permission of grant, revoke user, the permission can only take effect if the user reconnects to the MySQL database.

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

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

8

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

SELECT command denied to user 'user name '@' host name' for table 'table name' this error is encountered, the solution is to need to bar the following table name authorization, that is, to you to authorize the core database also.

I met SELECT command denied to user my'@'%' for table 'proc', is the call to the stored procedure, originally thought that as long as the specified database authorization line, what stored procedure, function, etc do not have to care, who knows also to database mysql proc table authorization

There are five mysql authorization tables: user, db, host, tables_priv and columns_priv.

The contents of the authorization table are used for the following purposes:

user table
The user table lists the users who can connect to the server and their passwords, and it specifies what global (superuser) permissions they have. Any permissions enabled in the user table are global and apply to all databases. For example, if you have DELETE enabled, the users listed here can delete records from any table, so think carefully before you do so.

db table
The db table lists databases that users have access to. The permissions specified here apply to all tables in 1 database.

host table
The host table, in combination with db tables, may be better than using db alone to control the access of a particular host to the database at a good level. This table is unaffected by the GRANT and REVOKE statements, so you may find that you are not using it at all.

tables_priv table
The tables_priv table specifies table-level permissions, where the 1 permission specified applies to all columns of the 1 table.

columns_priv table
The columns_priv table specifies the column-level permissions. The permissions specified here apply to a specific column of a table.