mysql之DCL(GRANT、REVOKE)和mysql用户密码相关

658uhr

DCL：数据控制语言
    GRANT
    REVOKE

用户：虚拟用户。
密码：使用password()函数

用户的账号仅提供认证，要想使用功能需要另外的授权。

用户账号结构：
    USERNAME@HOST

    USERNAME：
        一般不超过16个字符

    HOST：
        HOSTNAME
        IP
        IP/NETMASK
        FQDN
        DOMAIN
        %任意长度任意字符
        _任意单个字符

    默认mysql启动时会正反解主机名，所以启用mysql服务器是可以使用--skip-name-resolv禁用解析，可以 提高用户连接速度。


授权表：
    mysql在启动的时候会读取下列表到内存，并根据这些表生成授权表：
    user
        用户账号，全局权限，其他的非权限字段
    db
        数据库级别的权限定义
    host
        已废弃，已整合进user表中
    tables_priv
        表级别的权限定义
    colums_priv
        列（字段）段级别权限
    procs_priv
        存储过程和存储函数相关的权限
    proxies_priv
        代理用户权限

    查看对应授权表的内容：
        mysql>USE mysql;
        mysql>SELECT * FROM db \G

权限级别：
    全局
    库
    表
    列
    存储过程和存储函数

常见语句的权限应用范围：
    CREATE 数据库 表 索引
    DROP 数据库 表 视图
    GRANT OPTION 数据库 表 存储过程和函数
    LOCK TABLES 数据库
    REFERENCES 数据库 表
    EVENT 数据库

    INSERT 表 字段
    SELECT 表 字段
    UPDATE 表字段

    DELETE 表
    ALTER 表
    INDEX 表

    CREATE TEMPORARY TABLES  表
    TRIGGER 表

    CREATE TABLESPACE 服务器管理
    CREATE USER 服务器管理
    PROCESS 服务器管理
    PROXY 服务器管理
    RELOAD 服务器管理
    REPLICATION CLIENT 服务器管理
    REPLACTION SLAVE 服务器管理
    SHOW DATABASES 服务器管理
    SHUTDONW 服务器管理
    SUPER 服务器管理
    ALL [PRIVILEGES] 服务器管理
    USAGE 服务器管理

    FILE 访问服务所在服务器上的文件

    ALTER ROUTINE 储存过程和函数
    CREATE ROUTINE 储存过程和函数
    EXECUTE 储存过程和函数

    CREATE VIEW 视图
    SHOW VIEW 视图

用户账号和密码：
    创建用户：
        CREATE USER 'USERNAME'@'HOST' [IDENTIFIED BY 'PASSWORD']
        通常创建的用户只有USAGE权限和简单的查询权限。

    查看用户：
        mysql>USE mysql;
        mysql> SELECT User,Host,Password From user;

    删除用户：
        DROP USER 'USERNAME'@‘HOST’

    为用户设定密码：
        1.在mysql交互交互模式下：
            mysql> SET PASSWORD FOR 'USERNAME'@'HOST'=PASSWORD('PASSWORD');

        2.使用mysqladmin：
            #mysqladmin -uUSERNAME -hHOST -p password 'PASSWORD'

        3.直接修改数据库：
            mysql> UPDATE user SET Password=PASSWORD('PASSWORD') WHERE USER='USERNAME' AND HOST='HOST';
            mysql>FLUSH PRIVILEGES;

    重命名用户：
        RENAME USER OLDUSER TO NEWUSER


用户权限：
    指定用户权限：
        若用户不存在则自动创建并授权。
        GRANT PRI1,PRI2,... ON [OBJECT_TYPE] PRIV_LEVEL TO 'USERNMAE'@'HOST' [IDENTIFIED BY 'PASSWORD'] [REQUIRE] [WITH WITH_OPTION];
            PRI：
                ALL PRIVILEGES 所有的权限

            OBJECT_TYPE: 对象类型
                TABLE
                FUNCTION 函数
                PROCEDURE  存储过程或存储函数

            PRIV_LEVEL:
                * 所有库
                *.* 所有表
                DBNAME.* 某个库的所有表
                DBNAME.TBNAME 某个库的某张表
                TBNAME 特定表
                DBNAME,ROUTINNAME 某个数据库的存储过程或存储函数

            REQUIRE：用户在连接的时候必须满足的相关属性
                NONE 无
                SSL_OPTION ssl相关
                    SSL 基于ssl
                    X509 基于x509格式的证书。

            WITH_OPTION：资源使用限定，NUM为0表示不做限定。
                GRANT_OPTION 允许该用户将获得的权限授权给其他用户
                MAX_QUERIES_PER_HOUR NUM 每小时最大查询个数为NUM个
                MAX_UPDATES_PER_HOUR NUM 每小时允许使用NUM次UPDATE
                MAX_CONNECTIONS_PER_HOUR NUM每小时发起NUM个连接请求
                MAX_USER_CONNECTIONS NUM 每小时某个账户连接次数为NUM个

    取消用户权限：
        REVOKE PRT1,PRI2,... ON [OBJECT_TYPE] PRIV_LEVEL FROM 'USERNAME'@'HOST';

    查看用户权限：
        SHOW GRANTS FOR 'USERNAME'@'HOST';

    刷新授权：
        FLUSH PRIVILEGES;

实例：
    1.创建用户test并设定密码为testpwd：
        mysql>CREATE USER 'test'@'%' IDENTIFIED BY 'testpwd';
        或
        mysql>GRANT ...
        或
        mysql>INSERT INTO mysql.user  ;

        mysql>FLUSH PRIVILEGES;


        注意：若信创建用户无法登陆，请删除匿名用户。正常情况下在初始化后就应该做。

        mysql>USE mysql;
        mysql>DELETE FROM user WHERE user='';
        mysql>FLUSH PRIVILEGES;


    2.使用GRANT命令创建账户test和重设密码为testpwd：
        mysql>CRANT CREATE ON test.* TO 'test'@'%' IDENTIFIED BY ‘testpwd’;


    3.给test用户授权CREATE权限并查看：
        mysql>GRANT CREATE ON test.* TO 'test'@'%';
        mysql>FLUSH PRIVILEGES;
        mysql>SHOW GRANTS FOR 'test'@'%';
        mysql>\q

        注意：在设定权限并刷新后，重新登陆即可生效。


    4.字段权限的授权：
        mysql>USE test
        mysql>GRANT UPDATE(Age) ON testdb TO 'test'@'%';
        mysql>UPDATE testdb SET Age=40 WHERE ID=1;


    5.SUPER权限的应用：
        mysql>GRANT SUPER ON *.* TO 'test'@'%';

        SUPER权限比较特殊，可以执行SHUTDOWN，修改全局变量等。

    6.取消test在testdb数据库中的SELECT权限：
        mysql>REVOKE SELECT ON testdb.* FROM 'test'@'%'

    7.数据库管理员密码找回：
        找回思路：
            关闭服务 --> 修改服务脚本增加启动选项 --> 启动服务 -->使用UPDATE命令修改密码 --> 关闭服务 -->修改脚本 --> 重启服务

            --skip-grant-tables 跳过授权表
            --skip-networkding 禁用网络，防止其他用户通过网络登陆。


        #systemctl stop mysqld
        #vim /etc/init.d/mysqld
            ...
            case "$mode" in
                'start')
                    $bindri/mysqld_safe  --skip-grant-tables --skip-networking ...
            ...
        #systemctl start mysqld
        #mysql
        mysql>USE mysql;
        mysql>SELET User,Host,Password FROM user;
        mysql>UPDATE user SET PASSWORD=PASSWORD('123456') WHERE User='root';
        mysql>\q
        #systemctl stop mysqld
        #vim /etc/init.d/mysqld
            ...
           case "$mode" in
                'start')
                    $bindri/mysqld_safe ...
            ...
        #systemctl start mysqld;
        #mysql -uroot -p123456
        mysql>