MySQL数据库授权与索引

目录

一、数据库用户授权

1. 授予权限

2. 查看权限

3. 删除权限

4. 全部权限（all privileges）

二、MySQL索引

1. 索引的概念

2. 索引的作用

3. 索引的副作用

4. 创建索引的原则依据

5. 索引的分类和创建

6. 查看索引

7. 删除索引

8. 案例

总结

---

一、数据库用户授权

1. 授予权限

grant语句：专门用来设置数据库用户的访问权限。当指定的用户名不存在时，grant语句将会创建新的用户；当指定的用户名存在时，grant语句用于修改用户信息。

grant 权限列表 on 数据库名.数据表名 to '用户名'@'来源地址' [identified by '密码'];

权限列表：用于列出授权使用的各种数据库操作，以逗号进行分隔，如"select,insert,update"。使用"all"表示所有权限，可授权执行任何操作。
数据库名.表名：用于指定授权操作的数据库和表的名称，其中可以使用通配符"*"。例如，使用"*.*"b表示授权操作的对象为所有数据库中的所有表。
'用户名@来源地址'：用于指定用户名称和允许访问的客户机地址，即谁能连接、能从哪里链接。来源地址可以是域名、IP地址，还可以使用"%"通配符，表示某个区域或网段内的所有地址，如"%.test.com"、"192.168.122.%"等。
identified by：用于设置用户连接数据库时所使用的密码字符串。在新建用户时，若省略"identified by"部分，则用户的密码将为空。

mysql> grant select on test.* to 'zhangsan'@'localhost' identified by '123456';
#允许用户zhangsan在本地查询test数据库中所有表的数据记录，但禁止查询数据库中的表的记录。
Query OK, 0 rows affected, 2 warnings (0.00 sec

mysql> grant all on *.* to 'lisi'@'%' identified by '123456';
#允许用户lisi在所有终端远程连接mysql，并拥有所有权限。
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
#刷新权限
Query OK, 0 rows affected (0.00 sec)

2. 查看权限

方法一：
show grants for '用户名'@'来源地址';

mysql> show grants for zhangsan@localhost;
+----------------------------------------------------+
| Grants for zhangsan@localhost                      |
+----------------------------------------------------+
| GRANT USAGE ON *.* TO 'zhangsan'@'localhost'       |
| GRANT SELECT ON "test".* TO 'zhangsan'@'localhost' |
+----------------------------------------------------+
2 rows in set (0.00 sec)

mysql> show grants for lisi@'%';
+-------------------------------------------+
| Grants for lisi@%                         |
+-------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'lisi'@'%' |
+-------------------------------------------+
1 row in set (0.00 sec)

方法二：
select * from mysql.user where user='用户名' and host='来源地址'/G;

mysql> select * from mysql.user where user='zhangsan' and host='localhost'\G;
*************************** 1. row ***************************
                  Host: localhost
                  User: zhangsan
           Select_priv: N
           Insert_priv: N
           Update_priv: N
           Delete_priv: N
           Create_priv: N
             Drop_priv: N
           Reload_priv: N
         Shutdown_priv: N
          Process_priv: N
             File_priv: N
            Grant_priv: N
       References_priv: N
            Index_priv: N
            Alter_priv: N
          Show_db_priv: N
            Super_priv: N
 Create_tmp_table_priv: N
      Lock_tables_priv: N
          Execute_priv: N
       Repl_slave_priv: N
      Repl_client_priv: N
      Create_view_priv: N
        Show_view_priv: N
   Create_routine_priv: N
    Alter_routine_priv: N
      Create_user_priv: N
            Event_priv: N
          Trigger_priv: N
Create_tablespace_priv: N
              ssl_type: 
            ssl_cipher: 
           x509_issuer: 
          x509_subject: 
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: mysql_native_password
 authentication_string: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
      password_expired: N
 password_last_changed: 2021-08-27 10:56:48
     password_lifetime: NULL
        account_locked: N
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> select * from mysql.user where user='lisi' and host='%'\G;
*************************** 1. row ***************************
                  Host: %
                  User: lisi
           Select_priv: Y
           Insert_priv: Y
           Update_priv: Y
           Delete_priv: Y
           Create_priv: Y
             Drop_priv: Y
           Reload_priv: Y
         Shutdown_priv: Y
          Process_priv: Y
             File_priv: Y
            Grant_priv: N
       References_priv: Y
            Index_priv: Y
            Alter_priv: Y
          Show_db_priv: Y
            Super_priv: Y
 Create_tmp_table_priv: Y
      Lock_tables_priv: Y
          Execute_priv: Y
       Repl_slave_priv: Y
      Repl_client_priv: Y
      Create_view_priv: Y
        Show_view_priv: Y
   Create_routine_priv: Y
    Alter_routine_priv: Y
      Create_user_priv: Y
            Event_priv: Y
          Trigger_priv: Y
Create_tablespace_priv: Y
              ssl_type: 
            ssl_cipher: 
           x509_issuer: 
          x509_subject: 
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: mysql_native_password
 authentication_string: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
      password_expired: N
 password_last_changed: 2021-08-27 10:59:19
     password_lifetime: NULL
        account_locked: N
1 row in set (0.00 sec)

ERROR: 
No query specified

3. 删除权限

revoke 权限 on 数据库名.数据表名 from '用户名'@'来源地址';

mysql> revoke all on test.* from zhangsan@localhost;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show grants for zhangsan@localhost;
+----------------------------------------------+
| Grants for zhangsan@localhost                |
+----------------------------------------------+
| GRANT USAGE ON *.* TO 'zhangsan'@'localhost' |
+----------------------------------------------+
1 row in set (0.00 sec)
#权限删除后，仍会有允许用户登录的权限存在

mysql> revoke update on *.* from lisi@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for lisi@'%';
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for lisi@%                                                                                                                                                                                                                                                                                                                              |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE ON *.* TO 'lisi'@'%' |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

4. 全部权限（all privileges）

all privileges所含权限	功能
select	查询数据
insert	插入数据
update	更新数据
delete	删除数据
create	创建库/表
drop	删除库/表
reload	重载，可使用flush语句进行刷新操作
shutdown	关闭MySQL服务
process	显示或杀死属于其他用户的服务线程
file	在MySQL服务器上读写文件
references	建立外键约束
index	建立索引
alter	更改表属性
show databases	查看全局数据库
super	允许用户终止任何查询；修改全局变量的SET语句；使用CHANGE MASTER，PURGE MASTER LOGS
create temporary tables	创建临时表
lock tables	锁表
execute	执行存在的函数和程序
replication slave	查看从服务器，从主服务器读取二进制日志
replication client	查询主服务器、从服务器状态
create view	创建视图
show view	显示视图
create routine	创建存储过程
create user	创建用户
event	时间
trigger	创建触发器
create tablespace	创建表空间
注：