MySQL-用户和权限管理

用户管理

创建用户：CREATE USER
    CREATE USER ‘USERNAME‘@‘HOST‘ [IDENTIFIED BY ‘password‘]；
    默认权限：USAGE
    create user 创建完用户之后它的权限很有限、创建完用户之后是立即生效的
用户重命名：RENAME USER
    RENAME USER old_user_name TO new_user_name
删除用户：
    DROP USER ‘USERNAME‘@‘HOST‘
    示例：删除默认的空用户
        DROP USER ‘‘@‘localhost‘;
修改密码：
    mysql>SET PASSWORD FOR ‘user‘@‘host‘ = PASSWORD(‘password‘);
    mysql>UPDATE mysql.user SET password=PASSWORD(‘password‘) WHERE clause;
    此方法需要执行下面指令才能生效：
        mysql> FLUSH PRIVILEGES;
    #mysqladmin -u root -poldpass password ‘newpass‘
忘记管理员密码的解决办法：
    启动mysqld进程时，为其使用如下选项：
        --skip-grant-tables --skip-networking
    使用UPDATE命令修改管理员密码
    关闭mysqld进程，移除上述两个选项，重启mysqld

用户管理示例：

查看系统表里面的用户：
    MariaDB [db1]> select user,host,password from mysql.user;
    +---------+---------------+-------------------------------------------+
    | user    | host          | password                                  |
    +---------+---------------+-------------------------------------------+
    | root    | localhost     | *A498955BAB852BEF5B0C78584202F0326BB117A7 |
    | root    | 127.0.0.1     | *A498955BAB852BEF5B0C78584202F0326BB117A7 |
    | cobbler | 192.168.137.% | *128977E278358FF80A246B5046F51043A2B1FCED |
    | mage    | 192.168.137.% | *128977E278358FF80A246B5046F51043A2B1FCED |
    +---------+---------------+-------------------------------------------+
创建一个用户只允许单台远程主机IP能连接
    MariaDB [db1]> create user test@‘192.168.137.56‘ identified by ‘centos‘;
    Query OK, 0 rows affected (0.01 sec)
查看创建好的用户
    MariaDB [db1]> select user,host,password from mysql.user;               
    +---------+----------------+-------------------------------------------+
    | user    | host           | password                                  |
    +---------+----------------+-------------------------------------------+
    | root    | localhost      | *A498955BAB852BEF5B0C78584202F0326BB117A7 |
    | root    | 127.0.0.1      | *A498955BAB852BEF5B0C78584202F0326BB117A7 |
    | test    | 192.168.137.56 | *128977E278358FF80A246B5046F51043A2B1FCED |
    | cobbler | 192.168.137.%  | *128977E278358FF80A246B5046F51043A2B1FCED |
    | mage    | 192.168.137.%  | *128977E278358FF80A246B5046F51043A2B1FCED |
    +---------+----------------+-------------------------------------------+
    5 rows in set (0.00 sec)
客户端验证此用户
    [root@node6 ~mysql -utest -p -h192.168.137.57
    Enter password: 
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MariaDB connection id is 12
    Server version: 10.2.15-MariaDB-log MariaDB Server

    Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

    Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.

    MariaDB [(none)]> select user();
    +---------------------+
    | user()              |
    +---------------------+
    | test@192.168.137.56 |
    +---------------------+
    1 row in set (0.00 sec)

删除用户：
    MariaDB [db1]> drop user mage@‘192.168.137.%‘;
    Query OK, 0 rows affected (0.00 sec)

    MariaDB [db1]> select user,host,password from mysql.user;
    +---------+----------------+-------------------------------------------+
    | user    | host           | password                                  |
    +---------+----------------+-------------------------------------------+
    | root    | localhost      | *A498955BAB852BEF5B0C78584202F0326BB117A7 |
    | root    | 127.0.0.1      | *A498955BAB852BEF5B0C78584202F0326BB117A7 |
    | test    | 192.168.137.56 | *128977E278358FF80A246B5046F51043A2B1FCED |
    | cobbler | 192.168.137.%  | *128977E278358FF80A246B5046F51043A2B1FCED |
    +---------+----------------+-------------------------------------------+
    4 rows in set (0.00 sec)
修改用户密码：
    # set 修改用户密码：
    MariaDB [db1]> set password for test@‘192.168.137.56‘=password(‘123456‘);
    Query OK, 0 rows affected (0.00 sec)

    # update 修改表的方式来修改密码，不过需要手动来刷新
    MariaDB [db1]> update mysql.user set password=password(‘centos‘) where user=‘test‘;
    Query OK, 1 row affected (0.01 sec)
    Rows matched: 1  Changed: 1  Warnings: 0

    MariaDB [db1]> flush privileges;
    Query OK, 0 rows affected (0.00 sec)

MySQL权限管理

权限类别：
    管理类
    程序类
    数据库级别
    表级别
    字段级别
管理类：
    CREATE TEMPORARY TABLES
    CREATE USER
    FILE
    SUPER
    SHOW DATABASES
    RELOAD
    SHUTDOWN
    REPLICATION SLAVE
    REPLICATION CLIENT
    LOCK TABLES
    PROCESS
程序类： 
    FUNCTION
    PROCEDURE
    TRIGGER
    CREATE
    ALTER
    DROP
    EXCUTE
库和表级别：
    DATABASE
    TABLE
    ALTER
    CREATE
    CREATE VIEW
    DROP
    INDEX
    SHOW VIEW
    GRANT OPTION：能将自己获得的权限转赠给其他用户
数据操作：
    SELECT
    INSERT
    DELETE
    UPDATE
字段级别：
    SELECT(col1,col2,...)
    UPDATE(col1,col2,...)
    INSERT(col1,col2,...)
所有权限：
    ALL PRIVILEGES 或 ALL

授权

参考：https://dev.mysql.com/doc/refman/5.7/en/grant.html
GRANT priv_type [(column_list)],... ON [object_type] priv_level TO ‘user‘@‘host‘ [IDENTIFIED BY ‘password‘] [WITH GRANT OPTION];
    priv_type: ALL [PRIVILEGES]
    object_type:TABLE | FUNCTION | PROCEDURE
    priv_level: *(所有库) | *.* | db_name.* | db_name.tbl_name | tbl_name(当前库的表) | db_name.routine_name(指定库的函数,存储过程,触发器)
    with_option: GRANT OPTION
        | MAX_QUERIES_PER_HOUR count     # 限定在每个小时最多查询多少次   
        | MAX_UPDATES_PER_HOUR count     # 限定每个小时最多更新多少次
        | MAX_CONNECTIONS_PER_HOUR count # 限定每个小时最多连接多少次
        | MAX_USER_CONNECTIONS count     # 限定每个小时用户连接多少次
回收授权：
    REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ... ON [object_type] priv_level FROM user [, user] ...
查看指定用户获得的授权：
    Help SHOW GRANTS
    SHOW GRANTS FOR ‘user‘@‘host‘;
    SHOW GRANTS FOR CURRENT_USER[()];
注意：
    MariaDB服务进程启动时会读取mysql库中所有授权表至内存
        (1) GRANT或REVOKE等执行权限操作会保存于系统表中，MariaDB的服务进程通常会自动重读授权表，使之生效
        (2) 对于不能够或不能及时重读授权表的命令，可手动让MariaDB的服务进程重读授权表：mysql> FLUSH PRIVILEGES;

MySQL权限管理示例

创建test用户
MariaDB [db1]> create user test@‘192.168.137.56‘ identified by ‘centos‘;

只授权select权限给test用户
MariaDB [hellodb]> grant select(stuid,name) on hellodb.students to ‘test‘@‘192.168.137.56‘;
Query OK, 0 rows affected (0.00 sec)

在客户端验证：
    MariaDB [hellodb]> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | hellodb            |
    | information_schema |
    +--------------------+
    2 rows in set (0.00 sec)

    MariaDB [hellodb]> desc students;
    +-------+------------------+------+-----+---------+----------------+
    | Field | Type             | Null | Key | Default | Extra          |
    +-------+------------------+------+-----+---------+----------------+
    | StuID | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
    | Name  | varchar(50)      | NO   |     | NULL    |                |
    +-------+------------------+------+-----+------。，---+----------------+
    2 rows in set (0.01 sec)

    MariaDB [hellodb]> select stuid,name from students;
取消权限：
    MariaDB [hellodb]> revoke SELECT (name,stuid) ON `hellodb`.`students` TO ‘test‘@‘192.168.137.56‘;

MySQL-用户和权限管理

标签：所有权   net   查看   远程   procedure   rop   权限   NPU   eve