K002004-0x02MySQL权限管理

K002004-0x02MySQL权限管理
工具/原料
MySQL5728,centos7
方法/步骤
1.1 数据库的介绍
你们公司用了哪些数据产品?
请你例举一下你熟悉的数据库产品?
我们公司是制造业对、贷款类、电商
我们公司用了
    RDBMS:MySQL,Oracle,PG,MSSQL
    NoSQL:Redis,MongoDB,ES
    RDS:MySQL,Redis
1.2 MySQL数据库企业版本选择
 
2018 7月份 面试时间
上家公司工作2年
2016-2018
你们公司用什么版本数据库。
5.7.20 , 5.6.38 2017年9月13 GA oldguo 入职老男孩教育
面试官问:5.7.20 这个版本在2016年有吗?
 
1.3 安装
5.7+
mysqld --initialize-insencure
--initialize
--initialize-insencure
5.6-
mysql_install_db
 
1.4 体系结构
1.4.1 CS模型:
    tcpip
    socket
1.4.2 实例
    mysqld + 工作线程 + 预分配内存
 
1.4.3 mysqld工作原理
server:
    连接层:
    1.提供链接协议
        socket
        TCPIP
    2.验证
        user
        password
    3.提供链接线程
        show processlist;
    
    SQL层:
    1.语法
    2.语义:DDL DCL DML
    3.权限:
    4.解析、预处理:执行计划
    5.优化:根据cost代价优化算法,选择执行计划
    6.执行:根据优化器选择对执行计划,执行sql
        得出结果:你需要的数据在xx段,xx区,xx页
    7.query_cache:可以用redis(Tair)
    8.日志记录:二进制日志,通用日志
    
engine:
    段:一个表就是一个段(分区表除外),一个或多个区组成
    区:一个区(簇),连续的64个页组成,默认1M
    页:一个页,连续的4个os block,默认是16KB
 
1.4.4 逻辑结构
库:库名+库属性
表:表名+表属性+列+数据行
 
2.MySQL基础管理
2.1 用户管理
2.1.1 用户的作用
Linux用户:
    登录Linux系统
    管理Linux对象:文件
MySQL用户:
    登录MySQL数据库
    管理MySQL对象:表
2.1.2 用户的定义(长成啥样?)
Linux用户:用户名
MySQL用户:用户名@‘白名单‘
白名单?
    地址列表,允许白名单的IP登录MySQL,管理MySQL
 
huanu@‘localhost‘ :huanu用户能够通过本地登录MySQL(socket)
huanu@‘10.0.11.1‘ :huanu用户能够通过10.0.11.1远程登录MySQL服务器
huanu@‘10.0.11.%‘ :huanu用户能够通过10.0.11.xx/24远程登录MySQL服务器
huanu@‘10.0.11.5%‘ :...50-59...
huanu@‘10.0.11.0/255.255.254.0‘ :
huanu@‘%‘ :huanu用户能通过所有能通过网络连接
huanu@‘db01‘ :
huanu@‘db01.huanu.com‘ :
 
2.1.3 用户管理
mysql> desc user;
mysql> select user,host,authentication_string from user;
+---------------+-----------+-------------------------------------------+
| user          | host      | authentication_string                     |
+---------------+-----------+-------------------------------------------+
| root          | %         |                                           |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+---------------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)
select user,host,authentication_string from mysql.user;
mysql> create user huanu@‘localhost‘;
Query OK, 0 rows affected (0.00 sec)
mysql> create user oldboy@‘10.10.%‘ identified by ‘123‘;
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host,authentication_string from user;
+---------------+-----------+-------------------------------------------+
| user          | host      | authentication_string                     |
+---------------+-----------+-------------------------------------------+
| root          | %         |                                           |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| huanu         | localhost |                                           |
| oldboy        | 10.10.%   | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+---------------+-----------+-------------------------------------------+
5 rows in set (0.00 sec)
#改密码
mysql> alter user huanu@‘localhost‘ identified by ‘123‘;
#改权限
mysql> update user set host =‘%‘where user =‘root‘ and host =‘localhost‘;
mysql> drop user huanu@‘localhost‘;
mysql> drop user oldboy@‘10.10.%‘;
注意:8.0版本以前,可以通过grant命令 建立用户+授权
在8.0以后只能先建用户再授权
 
2.2 权限管理
2.2.1 作用
用户对数据库对象,有哪些管理能力。
2.2.2 权限的表现方式
具体的命令
mysql> show privileges;
+-------------------------+---------------------------------------+-------------------------------------------------------+
| Privilege               | Context                               | Comment                                               |
+-------------------------+---------------------------------------+-------------------------------------------------------+
| Alter                   | Tables                                | To alter the table                                    |
| Alter routine           | Functions,Procedures                  | To alter or drop stored functions/procedures          |
| Create                  | Databases,Tables,Indexes              | To create new databases and tables                    |
| Create routine          | Databases                             | To use CREATE FUNCTION/PROCEDURE                      |
| Create temporary tables | Databases                             | To use CREATE TEMPORARY TABLE                         |
| Create view             | Tables                                | To create new views                                   |
| Create user             | Server Admin                          | To create new users                                   |
| Delete                  | Tables                                | To delete existing rows                               |
| Drop                    | Databases,Tables                      | To drop databases, tables, and views                  |
| Event                   | Server Admin                          | To create, alter, drop and execute events             |
| Execute                 | Functions,Procedures                  | To execute stored routines                            |
| File                    | File access on server                 | To read and write files on the server                 |
| Grant option            | Databases,Tables,Functions,Procedures | To give to other users those privileges you possess   |
| Index                   | Tables                                | To create or drop indexes                             |
| Insert                  | Tables                                | To insert data into tables                            |
| Lock tables             | Databases                             | To use LOCK TABLES (together with SELECT privilege)   |
| Process                 | Server Admin                          | To view the plain text of currently executing queries |
| Proxy                   | Server Admin                          | To make proxy user possible                           |
| References              | Databases,Tables                      | To have references on tables                          |
| Reload                  | Server Admin                          | To reload or refresh tables, logs and privileges      |
| Replication client      | Server Admin                          | To ask where the slave or master servers are          |
| Replication slave       | Server Admin                          | To read binary log events from the master             |
| Select                  | Tables                                | To retrieve rows from table                           |
| Show databases          | Server Admin                          | To see all databases with SHOW DATABASES              |
| Show view               | Tables                                | To see views with SHOW CREATE VIEW                    |
| Shutdown                | Server Admin                          | To shut down the server                               |
| Super                   | Server Admin                          | To use KILL thread, SET GLOBAL, CHANGE MASTER, etc.   |
| Trigger                 | Tables                                | To use triggers                                       |
| Create tablespace       | Server Admin                          | To create/alter/drop tablespaces                      |
| Update                  | Tables                                | To update existing rows                               |
| Usage                   | Server Admin                          | No privileges - allow connect only                    |
+-------------------------+---------------------------------------+-------------------------------------------------------+
31 rows in set (0.00 sec)
 
2.2.3 授权、回收权限操作
(1)语法-中文表示的可以自定义:
    8.0以前:
    grant 权限 on 对象 to 用户 identified by ‘密码‘;
    8.0+:
    create user 用户 identified by ‘密码‘;
    grant 权限 on 对象 to 用户;
    
    grant 权限1,权限2,权限3,... on 对象 to 用户 identified by ‘密码‘ with Grant option;
    
权限:
    ALL    :管理员
    权限1,权限2,权限3,...:普通用户(业务用户,开发用户)
    Grant option:给别的用户授权
对象范围:库,表
*.*      :----> chmod -R 755 /            管理员
huanu.*  :----> chmod -R 755 /huanu    普通用户    *****
huanu.t1 :----> chmod -R 755 /huanu/t1
 
(2)授权对离职
    例子1:创建并授权管理员用户,能够通过10.10.% 网段登录并管理数据库
    mysql> grant all on *.* to huanu@‘10.10.%‘ identified by ‘123‘ with grant option;
    Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show grants for huanu@‘10.10.%‘;
+--------------------------------------------------------------------+
| Grants for huanu@10.10.%                                           |
+--------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO ‘huanu‘@‘10.10.%‘ WITH GRANT OPTION |
+--------------------------------------------------------------------+
1 row in set (0.00 sec)
    mysql> select * from mysql.user\G
    例子2:创建并授权一个app@‘10.10.%‘业务用户,能够对app库下所有对象进行create,select,update,delete,insert操作
    grant create,select,update,delete,insert on app.* to app@‘10.10.%‘ identified by ‘123‘;
    show grant for app@‘10.10.%‘;
    select * from mysql.db;
 
扩展:
    MySQL授权表:
    mysql库下
    user            :*.*
    db                :app.*
    tables_priv        :app.t1
    columns_priv    :列
 
(3)回收权限
    Linux:
    chmod -R 644 /huanu --->chmod -R 755 /huanu
    MySQL:
    注意:mysql中不能通过重复授权修改权限,只能通过回收权限修改
    revoke create on app.* from ‘app‘@‘10.10.%‘;
    show grants for app@‘10.10.%‘;
 
2.3 超级管理员密码忘记了,处理。
--skip-grant-tables     :跳过授权表
--skip-networking        :跳过TCP/IP连接
(1)关闭数据库/etc/initd.mysqld stop  
[root@db01 ~]# systemctl stop mysqld
(2)使用安全模式启动
mysqld_safe --skip-grant-tables --skip-networking&
service mysqld start  --skip-grant-tables  --skip-networking&
(3)登录数据库并修改密码
[root@db01 ~]# mysql
mysql> flush privileges; #手工加载授权表
mysql> alter user root@‘localhost‘ identified by ‘123‘;
(4)重启数据库到正常模式
services mysqld restart

K002004-0x02MySQL权限管理

上一篇:SQLite数据库“Database is locked”解决办法


下一篇:java.sql.SQLException