当前位置:Gxlcms > 数据库问题 > MySQL-用户和权限管理

MySQL-用户和权限管理

时间:2021-07-01 10:21:17 帮助过:28人阅读

元数据数据库:mysql 系统授权表: db, host, user columns_priv, tables_priv, procs_priv, proxies_priv 用户账号: ‘USERNAME‘@‘HOST‘: @‘HOST‘: 主机名; IP地址或Network; 通配符: % _ 示例:172.16.%.%

用户管理

创建用户: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   

人气教程排行