Shulou(Shulou.com)05/31 Report--

This article mainly talks about "what are the permissions in mysql". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let the editor take you to learn "what are the permissions in mysql"?

Mysql permissions are: 1, USAGE, can only be used for database login, can not perform any operations; 2, SELECT, allows the use of select statements to view the contents of the table; 3, super, allows the execution of a series of database management commands; 4, process, allows to view process information; 5, Shutdown, etc.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

Mysql various permissions:

1. USAGE: connect (login) permission, establish a user, it will automatically grant its usage permission (default grant).

This permission can only be used for database login, no action can be performed, and the permission cannot be reclaimed, and the user right cannot be deleted even with REVOKE.

Grant usage on *. * to 'iap'@'%' identified by' iap123'

2. Permissions under data class

SELECT: use SELECT to view table contents, and only with this permission can you execute SELECT query.

Grant select on testdb.* to 'iap'@'%'

2.2 、 insert

You must have permission to use insert to use insert into. .. Values... .

2.3 、 update

You must have permission for update to use update table.

Update shop set price=3.5 where article=0001 and dealer='A'

2.4 、 delete

You must have permission to use delete to use delete from. .where... . (delete records in the table)

2.5, file (not for a database, so use on *. *)

File permission refers to the access to files on the server host. Database users can only execute select.. into outfile,load data infile... if they have file permissions. Operation.

However, do not grant file, process, super permissions to accounts other than the administrator, which has serious security risks.

Grant file on *. * to 'iap'@'%'

Export file: select... Into outfile 'file path'

View the import and export path settings: show variables like'% sec%'

The secure-file-priv parameter is used to restrict load data, select. Outfile, and load_file () to which specified directory.

Specific modification method: https://blog.csdn.net/weixin_39631030/article/details/79873936

Import file: load data infile 'file absolute path' into table table name

Specific usage: https://www.cnblogs.com/darange/p/10508714.html

3. Structural permissions

CREATE: permission to create tables. You must have permission for create to use create table.

Grant create on testdb.* to 'iap'@'%'

3.2 、 alter

You must have permission for alter to use alter table.

Grant alter on testdb.* to 'iap'@'%'

Example: alter table shop modify dealer char (15)

3.3 、 index

You must have index permission to execute [create | drop] index

Grant index on testdb.* to 'iap'@'%'

Create index ix_shop on shop (article)

Drop index ix_shop on shop

3.4 、 drop

You must have permission for drop to use the

Grant drop on testdb.* to 'iap'@'%'

Drop database db_name

Drop table table_name

Drop view view_name

Drop index index_name

Create temporary tables (note that this is tables, not table)

You must have the permission of create temporary tables before you can use create temporary tables.

-- Grant iap@localhost the right to create a temporary table grant create temporary tables on testdb.* to 'iap'@'%';-- example: iap user login, create temporary table create temporary table tt1 (id int)

3.6 、 show view

You must have show view permissions to execute show create view.

Grant show view on testdb.* to 'iap'@'%';show create view view_shop

CREATE ROUTINE: permission to create procedure or function.

If the user has create routine permission, he can create procedure | function.

Create {procedure | function}

If the user creates procedure | function, then mysql automatically grants EXECUTE, and ALTER ROUTINE permission to its creator:

Grant create routine on testdb.* to 'iap'@'%'

3.8 、 alter routine

You must have permission to alter routine to use the

{alter | drop} {procedure | function}

-- Grant testdb database permission to modify / delete stored procedures / functions grant alter routine on testdb.* to 'iap'@'%'

3.9 、 excute

Execute the existing Functions, Procedures.

Grant execute on testdb.* to 'iap'@'%'

3.10 、 create view

You must have permission from create view before you can create a view using create view.

-- Grant 'iap'@'localhost' to create view permission grant create view on testdb.* to' iap'@'%';-- example: 'iap'@'localhost' login, create v_shop view create view v_shop as select price from shop

3.11 、 event

Event: allows you to query, create, modify, and delete MySQL events.

Event scope: event can be used to handle operations that have fixed requirements at regular intervals, such as creating tables, deleting data, and so on.

Details: https://www.cnblogs.com/langtianya/p/5445528.html

For example, use event to automatically create three tables that you need to use next month at 1: 00 a.m. on the 1st of each month.

3.12 、 Trigger

The Trigger permission represents the permission to create, delete, execute, and display triggers.

4. Administrative authority

4.1 、 grant option

With grant option, you can grant the permissions you have to other users (only those you already have)

Grant grant option on testdb.* to 'iap'@'%'

4.2 、 super

The Super permission representative allows the execution of a series of database management commands, including the kill forced closing of a connection command, the change master to create replication relationship command, and the create/alter/drop server command, which modify the SET statements of global variables

Mysql > grant super on *. * to p1localhost * MySQL > purge master logs before 'mysql-bin.000006'

4.3 、 process

Process permission representative allows you to view process information in MySQL, such as executing show processlist, mysqladmin processlist, show engine, etc.

With this permission, users can execute SHOW PROCESSLIST and KILL commands. By default, each user can execute the SHOW PROCESSLIST command, but only the processes of this user can be queried.

Show PROCESSLIST

In addition, administrative permissions (such as super, process, file, etc.) cannot specify a database, and on must be followed by *. *

Grant super on *. * to p1@localhost

4.4 、 Shutdown

The shutdown permission representative allows you to shut down the database instance, and the execution statements include: mysqladmin shutdown.

4.5 、 show database

Through show database, you can only see databases with certain permissions you have, unless you have global SHOW DATABASES permissions.

For iap@%t users, they do not have permission to the mysql database, so when logging in to query under this identity, they cannot see the mysql database:

4.6 、 reload

You must have reload permission to execute flush [tables | logs | privileges]

Reload is an administrative level permission, that is, server administration

Such permissions include: create user, process, reload, replication client, replication slave, show databases, shutdown, super

The authorization of such permissions is not specific to a database, so it needs to be done using on *. *

Grant reload on *. * to 'iap'@'%'

4.7 、 lock tables

You must have lock tables permission to use lock tables

Grant lock tables on testdb.* to 'iap'@'localhost'

Lock tables a1 read

Unlock tables

4.8 、 references

With references permission, users can use a field of another table as a foreign key constraint of a table.

4.9 、 replication client

Replication client permission representative allows the execution of show master status, show slave status, show binary logs commands

4.10 、 replication slave

The Replication slave permission representative allows the slave host to connect to the master through this user in order to establish a master-slave replication relationship, view the slave server and read the binary logs from the master server.

Grant replication slave on *. * to 'iap'@'%'

Show slave hosts

Show binlog events

4.11 、 Create user

Create user permissions represent permissions that allow you to create, modify, delete, and rename user.

5. Permission type table

5.1. When granting database permissions, the form:

The permission name corresponds to the field description in the user table SELECTSelect_priv means that the user is granted permission to access all tables and views in a particular database using the SELECT statement. INSERTInsert_priv means that the user is granted permission to add rows to all tables in a particular database using the INSERT statement. DELETEDelete_priv means that the user is granted permission to delete data rows for all tables in a particular database using the DELETE statement. UPDATEUpdate_priv means that the user is granted permission to update the values of all data tables in a particular database using the UPDATE statement. REFERENCESReferences_priv means that the user is granted permission to create an off-table key in a specific database. CREATECreate_priv represents the permission of authorized users to create new tables in a specific database using the CREATE TABLE statement. ALTERAlter_priv means that the user is granted permission to modify all data tables in a particular database using the ALTER TABLE statement. SHOW VIEWShow_view_priv means that users are granted permission to view view definitions of views that already exist in a particular database. CREATE ROUTINECreate_routine_priv means that the user is granted permission to create stored procedures and stored functions for a specific database. ALTER ROUTINEAlter_routine_priv means that users are granted permission to update and delete stored procedures and stored functions that already exist in the database. INDEXIndex_priv means that users are granted permission to define and delete indexes on all data tables in a particular database. DROPDrop_priv means that the user is granted permission to delete all tables and views in a particular database. CREATE TEMPORARY TABLESCreate_tmp_table_priv means that the user is granted permission to create temporary tables in a specific database. CREATE VIEWCreate_view_priv means that users are granted permission to create new views in a specific database. EXECUTE ROUTINEExecute_priv means that the user is granted permission to call stored procedures and stored functions of a specific database. LOCK TABLESLock_tables_priv means that the user is granted permission to lock existing tables in a specific database. ALL or ALL PRIVILEGES or SUPERSuper_priv indicates all the above permissions / super permissions

5.2. When granting table permissions, you can specify the following values:

Permission names correspond to fields in the user table that SELECTSelect_priv grants users permission to access specific tables using SELECT statements INSERTInsert_priv grants users permission to add rows to a specific table using INSERT statements DELETEDelete_priv grants users permission to delete rows from a specific table using DELETE statements DROPDrop_priv grants users permission to delete data tables Permission to update a specific data table using the UPDATE statement ALTERAlter_priv grant the user permission to modify the data table using the ALTER TABLE statement REFERENCESReferences_priv grant the user the right to create a foreign key to reference a specific data table CREATECreate_priv grant the user the right to create a data table with a specific name INDEXIndex_priv grant the user the right to define an index on the table ALL or ALL PRIVILEGES or SUPERSuper_priv all permission names

5.3. Grant column permissions

When granting column permissions, the values can only be specified as SELECT, INSERT, and UPDATE, followed by the column name list column-list.

5.4. The most efficient permissions are user rights.

When you grant a user permission, in addition to all the values you can specify when granting database permissions, you can also have the following values:

CREATE USER: means that the user is granted permission to create and delete new users.

SHOW DATABASES: means to grant the user permission to view the definitions of all existing databases using the SHOW DATABASES statement.

At this point, I believe you have a deeper understanding of "what are the permissions in mysql?" you might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.