Mysql database permission Control

Mysql database permission control grant permission on database object to user password remote users have permissions granted to www.2cto.com grant all privileges on *. * to root @ '%' identified by '000000' with grant option; mysql> grant all on *. * to root @ "%" identified by 'abc123'; Query OK, 0 rows affected (0.01 sec) mysql> select host, User, password from user; + -------------- + --------- + ---------------- + | host | User | password | + -------------- + --------- + ------------ ------ + | Localhost | root | selfserv | root | 127.0.0.1 | root | localhost ||| selfserv ||||%| abc | 4b5698aa4603595b | 172.17.15.50 | zsgd | 032c41e8435273a7 | % | sysuser | percent | % | sg | percent | |%| root | percent | + -------------- + --------- + ---------------- + 10 rows in set (0.00 sec) mysql> grant permissions on database objects to users 1. grant common data users, queries, inserts, updates, and deletes Permission for 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 grant select, insert, update, delete on testdb. * to common_user @ '%' www.2cto.com 2. grant database developers to create tables, indexes, views, stored procedures, and functions.... Grant permissions to create, modify, and delete MySQL Data Table structures. Grant create on testdb. * to developer @ '2017. 168.0.% '; grant alter on testdb. * to developer @ '2017. 168.0.% '; grant drop on testdb. * to developer @ '2017. 168.0.% '; grant the permission to operate the MySQL foreign key. Grant references on testdb. * to developer @ '192. 192.% '; grant the temporary table permission for MySQL. Grant create temporary tables on testdb. * to developer @ '192. 192.% '; grant permission to operate MySQL indexes. Grant index on testdb. * to developer @ '192. 192.% '; grant permission to operate MySQL view and view source code. Grant create view on testdb. * to developer @ '2017. 168.0.% '; grant show view on testdb. * to developer @ '2017. 168.0.% '; grant permissions to operate MySQL stored procedures and functions. Grant create routine on testdb. * to developer @ '2017. 168.0.% ';-now, can show procedure statusgrant alter routine on testdb. * to developer @ '2017. 168.0.% ';-now, you can drop a proceduregrant execute on testdb. * to developer @ '2017. 168.0.% '; 3. grant common DBA permission to manage a MySQL database. Grant all privileges on testdb to dba @ 'localhost', where the keyword "privileges" can be omitted. 4. grant senior DBA permission to manage all databases in MySQL. Grant all on *. * to dba @ 'localhost' v. MySQL grant permissions can be applied to multiple levels. 1. grant works on the 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 tables in testdb. 3. grant applies to a single data table: grant select, insert, update, delete on testdb. orders to dba @ localhost; the preceding statements can be executed multiple times when a user is authorized to multiple tables. Example: grant select (user_id, username) on smp. users to mo_user @ '%' identified by '000000'; grant select on smp.mo _ sms to mo_user @ '%' identified by '000000'; 4. grant is applied to columns in the Table: grant select (id, se, rank) on testdb. apache_log to dba @ localhost; 5. grant is used in 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 and view the current user (yourself) Permission: show grants; view other MySQL user permissions: show grants for zhangkh @ localhost; 7. revoke the permissions granted to MySQL users. The revoke syntax is similar to that of grant. You only need to replace the keyword "to" with "from": grant all on *. * to dba @ localhost; revoke all on *. * from dba @ localhost; 8. Considerations for MySQL grant and revoke User Permissions 1. grant and revoke permissions can only take effect after the user reconnects to the MySQL database. 2. if you want the authorized user to grant these permissions to other users, You need to select "grant option" grant select on testdb. * to dba @ localhost with grant option; this feature is generally unavailable. In practice, it is best for DBAs to manage database permissions in a unified manner. There are five mysql authorization tables: user, db, host, tables_priv, and columns_priv. The authorization table has the following purposes: the user table lists the users that can connect to the server and their passwords, and it specifies which global (superuser) permissions they have. All permissions enabled in the user table are global permissions and apply to all databases. For example, if you have enabled the DELETE permission, the users listed here can DELETE records from any table, so you should consider it carefully before doing so. The db table lists databases, and users have the permission to access them. The permission specified here applies to all tables in a database. The host table and the db table are used in combination to control the database access permissions of a specific host at a good level, which may be better than using the database separately. This table is not affected by the GRANT and REVOKE statements, so you may find that you are not using it at all. The table-level permission is specified in the Table tables_priv of www.2cto.com tables_priv. The permission specified here applies to all columns in a table. The columns_priv table columns_priv specifies the column-level permission. The specified Permission applies to specific columns of a table. Note: For grant usage on, check the manual for the following introduction and examples: mysql> grant usage on *. * TO 'zhangkh' @ 'localhost'; an account has the username zhangkh and has no password. This account is only used for local connection. No permission is granted. With the USAGE permission in the GRANT statement, you can create an account without granting any permissions. It can set all global permissions to 'n '. Assume that you will grant specific permissions to this account in the future.