What permissions does mysql have?

mysql permissions include: 1. USAGE, which can only be used for database login and cannot perform any operations; 2. SELECT, which allows the use of SELECT statements to view table contents; 3. super, which allows the execution of a series of database management Command; 4. process, allowing 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: connection (login) permissions , when you create a user, the usage permission will be automatically granted (granted by default).

This permission can only be used for database login and cannot perform any operations, and this permission cannot be recycled. Even if REVOKE is used, the user permission cannot be deleted.

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

2. Permissions under the data class

2.1. SELECT: Use SELECT to view the table contents. Only with this permission can Execute a SELECT query.

grant select on testdb.* to 'iap'@'%';

2.2, insert

You must have the permission to insert before you can use insert into….. values….

2.3, update

You must have update permissions before you can use update table.

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

2.4, delete

You must have delete permission before you can use delete from ….where….(delete records in the table)

2.5. file (not for a certain database, so on *.* must be used)

file permission refers to the file permission on the server host For access, database users can only perform select ..into outfile, load data infile... operations with file permissions.

But do not grant file, process, and super permissions to accounts other than the administrator. This has serious security risks. Hidden danger.

grant file on *.* to 'iap'@'%';

Export file: select ... into outfile 'File path'

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

##secure-file-privThe parameters are used to limit load data, select ... outfile, and load_file () Which specified directory is passed to.

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

Import file:

load data infile 'Absolute file path' into table Table name;

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

3. Structural permissions

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

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

3.2, alter

must have alter permission to use

alter table.

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

Example:

alter table shop modify dealer char(15);

3.3, index

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

grant index on testdb.* to 'iap'@'%';

create index ix_shop on shop(article);

drop indexix_shop on shop;

##3.4. drop

You must have drop permission to use

grant drop on testdb.* to 'iap'@'%';

drop database

db_name;

drop table

table_name;

drop view

view_name;

drop index

index_name;

3.5, create temporary tables (note that this is tables, not tables)

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

-- 授予iap@localhost 创建临时表权限
grant create temporary tables on testdb.* to 'iap'@'%';

--例 ：  iap 用户登录，创建临时表
create temporary table tt1(id int);

3.6、show view

必须拥有show view权限，才能执行show create view。

grant show view on testdb.* to 'iap'@'%';

show create view view_shop;

3.7、CREATE ROUTINE：创建procedure或function的权限。

如果用户有create routine 权限那么他就可以创建procedure | function 。

create  {procedure|function}

如果用户创建了procedure | function 那么mysql 自动授予EXECUTE, ALTER ROUTINE权限给它的创建者：

grant create routine on testdb.* to 'iap'@'%';

3.8、alter routine

必须具有alter routine的权限，才可以使用

{alter |drop} {procedure|function}

-- 授予 testdb数据库 修改/删除 存储过程/函数的权限
grant alter routine on testdb.* to 'iap'@'%';

3.9、excute

执行存在的Functions、Procedures。

grant execute on testdb.* to 'iap'@'%';

3.10 、create view

必须有create view的权限，才可以使用 create view 创建视图。

-- 授予'iap'@'localhost'创建视图权限
grant create view on testdb.* to 'iap'@'%';

-- 例 ： 'iap'@'localhost' 登录，创建v_shop视图
create view v_shop as select price from shop;

3.11 、event

 event : 允许查询，创建，修改，删除MySQL事件.

事件适用范围：对于每隔一段时间就有固定需求的操作，如创建表，删除数据等操作，可以使用event来处理。

详细说明：https://www.cnblogs.com/langtianya/p/5445528.html

例如：使用event在每月的1日凌晨1点自动创建下个月需要使用的三张表。

3.12 、Trigger

Trigger权限代表允许创建，删除，执行，显示触发器的权限.

4、管理权限

4.1、grant option

拥有grant option，就可以将自己拥有的权限授予其他用户（仅限于自己已经拥有的权限）

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

4.2、super

Super权限代表允许执行一系列数据库管理命令，包括kill强制关闭某个连接命令， change master to创建复制关系命令，以及create/alter/drop server等命令,修改全局变量的SET语句

mysql> grant super on *.* to p1@localhost;
mysql> purge master logs before ‘mysql-bin.000006′;

4.3、 process

Process权限代表允许查看MySQL中的进程信息，比如执行show processlist, mysqladmin processlist, show engine等命令

通过这个权限，用户可以执行SHOW PROCESSLIST和KILL命令。默认情况下，每个用户都可以执行SHOW PROCESSLIST命令，但是只能查询本用户的进程。

show PROCESSLIST

另外，管理权限（如 super， process， file等）不能够指定某个数据库，on后面必须跟*.*

grant super on *.* to p1@localhost;

4.4、Shutdown

shutdown权限代表允许关闭数据库实例，执行语句包括 : mysqladmin shutdown.

4.5、 show database

通过show database只能看到你拥有的某些权限的数据库，除非你拥有全局SHOW DATABASES权限。

对于iap@%t用户来说，没有对mysql数据库的权限，所以以此身份登陆查询时，无法看到mysql数据库：

4.6、reload

必须拥有reload权限，才可以执行flush [tables | logs | privileges]

reload 是 administrative 级的权限，即 server administration；

这类权限包括： create user,  process,  reload,  replication client,  replication slave,  show databases,  shutdown, super
这类权限的授权不是针对某个数据库的，因此须使用on *.* 来进行

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

4.7、lock tables

必须拥有lock tables权限，才可以使用lock tables

grant lock tables on testdb.* to 'iap'@'localhost';

 lock tables a1 read;

unlock tables;

4.8、references

有了references权限，用户就可以将其它表的一个字段作为某一个表的外键约束。

4.9、replication client

Replication client权限代表允许执行show master status,  show slave status,   show binary logs命令

4.10、replication slave

Replication slave permission means that the slave host is allowed to connect to the master through this user to establish a master-slave replication relationship. You can view the slave server and read the binary log 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 creating, modifying, deleting, and renaming users.

5. Permission type table

5.1. When granting database permissions, table:

Permission name	corresponds to the field in the user table	Description
SELECT	Select_priv	Indicates that the user is granted permission to use the SELECT statement to access all tables and views in a specific database.
INSERT	Insert_priv	Indicates that the user is granted permission to use the INSERT statement to add data rows to all tables in a specific database.
DELETE	Delete_priv	Indicates that the user is granted permission to use the DELETE statement to delete data rows from all tables in a specific database.
UPDATE	Update_priv	means granting the user permission to use the UPDATE statement to update the values ​​of all data tables in a specific database.
REFERENCES	References_priv	indicates that the user is granted permission to create foreign keys pointing to tables in a specific database.
CREATE	Create_priv	Represents the authority that authorizes a user to create a new table in a specific database using the CREATE TABLE statement.
ALTER	Alter_priv	Indicates that the user is granted permission to use the ALTER TABLE statement to modify all data tables in a specific database.
SHOW VIEW	Show_view_priv	Indicates that the user is granted permission to view the view definitions of existing views in a specific database.
CREATE ROUTINE	Create_routine_priv	means granting the user permission to create stored procedures and stored functions for a specific database.
ALTER ROUTINE	Alter_routine_priv	indicates that the user is granted permission to update and delete existing stored procedures and stored functions in the database.
INDEX	Index_priv	indicates that the user is granted permission to define and delete indexes on all data tables in a specific database.
DROP	Drop_priv	means granting the user permission to delete all tables and views in a specific database.
CREATE TEMPORARY TABLES	Create_tmp_table_priv	Indicates that the user is granted permission to create temporary tables in a specific database.
CREATE VIEW	Create_view_priv	means granting the user permission to create new views in a specific database.
EXECUTE ROUTINE	Execute_priv	indicates that the user is granted permission to call stored procedures and stored functions of a specific database.
LOCK TABLES	Lock_tables_priv	indicates that the user is granted permission to lock existing data tables of a specific database.
ALL or ALL PRIVILEGES or SUPER	Super_priv	means all the above permissions/super permissions

5.2. When granting table permissions, can be specified as the following value:

##ALTERAlter_priv Grants users permission to use the ALTER TABLE statement to modify the data tableREFERENCESReferences_privGrants the user permission to create a foreign key to reference a specific data tableCREATECreate_privGrants users permission to create a data table using a specific nameINDEXIndex_privGrant the user permission to define indexes on the table##ALL or ALL PRIVILEGES or SUPER

5.3. Grant column permissions

When granting column permissions, the value of can only be specified as SELECT, INSERT and UPDATE, and the permissions need to be followed by Column name list column-list.

5.4. The most efficient permission is user permission.

When granting user permissions, in addition to all the values ​​that can be specified when granting database permissions, the can also be the following values:

• CREATE USER: Indicates that the user is granted permission to create and delete new users.
• SHOW DATABASES: Indicates that the user is granted permission to use the SHOW DATABASES statement to view the definitions of all existing databases.

[Related recommendations: mysql video tutorial]

Permission name	Corresponding fields in the user table	Description
SELECT	Select_priv	Grants users to use the SELECT statement to access specific tables Permission
INSERT	Insert_priv	Grants the user permission to use the INSERT statement to add rows to a specific table
DELETE	Delete_priv	Grants the user permission to delete rows from a specific table using the DELETE statement
DROP	Drop_priv	Grants users the permission to delete data tables
UPDATE	Update_priv	Grants users the permission to use UPDATE statement permission to update a specific data table
Super_priv	All permission names

The above is the detailed content of What permissions does mysql have?. For more information, please follow other related articles on the PHP Chinese website!