管理MySQL用户重要有哪些功能
发布时间：2023-09-12 14:43:34 所属栏目：MySql教程 来源：
导读：下文主要给大家带来管理MySQL用户主要有哪些功能，希望这些文字能够带给大家实际用处，这也是我编辑管理MySQL用户主要有哪些功能这篇文章的主要目的。好了，废话不多说，大家直接看下文吧。
1. MySQL用户管理
1. MySQL用户管理
|
下文主要给大家带来管理MySQL用户主要有哪些功能，希望这些文字能够带给大家实际用处，这也是我编辑管理MySQL用户主要有哪些功能这篇文章的主要目的。好了，废话不多说，大家直接看下文吧。 1. MySQL用户管理 'user'@'host'; host:IP、主机名、NETWORK、%(任意长字符),_(任意单个字符) skip_name_resolve={ON|OFF} 跳过主机名解析 [root@node2 ~]# vim /etc/my.cnf skip_name_resolve=ON 2.查看用户 示例： 1 MariaDB [mysql]> SELECT User,Host,Password FROM user; 3.创建用户 CREATE USER 'user'@'host' [IDENTIFIED BY [PASSWORD] 'password'] [,'user'@'host' [IDENTIFIED BY [PASSWORD] 'password']...] 示例： 1 MariaDB [(none)]> CREATE USER 'tom'@'127.0.0.1' IDENTIFIED BY 'liumanlin' , 'jerry'@'%' IDENTIFIED BY 'liumanlin'; 4.重命名：RENAME USER RENAME USER old_user TO new_user[, old_user TO new_user] ... 示例： 1 MariaDB [mysql]> RENAME USER 'tom'@'127.0.0.1' TO 'jerry'@'172.18.%.%'; 5.删除用户 DROP USER 'user'@'host' [, 'user'@'host'] ... 示例： MariaDB [mysql]> DROP USER 'jerry'@'%'; MariaDB [mysql]> DROP USER ''@'localhost'; 6.让MySQL重新加载授权列表 FLUSH PRIVILEGES; 示例： 1 MariaDB [mysql]> FLUSH PRIVILEGES; 7.修改用户密码 (1) SET PASSWORD [FOR 'user'@'host'] = PASSWORD('cleartext password'); PASSWORD是MySQL内建加密函数 示例： MariaDB [mysql]> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('liumanlin'); MariaDB [mysql]> FLUSH PRIVILEGES; (2) UPDATE mysql.user SET Password=PASSWORD('cleartext password') WHERE User='USERNAME' AND Host='HOST'; 示例： MariaDB [mysql]> UPDATE user SET Password=PASSWORD('liumanlin') WHERE User='root' AND Host='127.0.0.1'; MariaDB [mysql]> FLUSH PRIVILEGES; (3) mysqladmin -uUSERNAME -hHOST -p password 'NEW_PASS' 示例： 1 [root@node2 ~]# mysqladmin -h227.0.0.1 -uroot -p password 'liumanlin'; 8.忘记管理员密码的解决办法 (1) 启动mysqld进程时，使用--skip-grant-tables和--skip-networking选项 示例： CentOS 7： [root@node2 ~]# vim /usr/lib/systemd/system/mariadb.service ExecStart=/usr/bin/mysqld_safe --basedir=/usr --skip-grant-tables --skip-networking [root@node2 ~]# systemctl daemon-reload [root@node2 ~]# systemctl restart mariadb.service CentOS 6： 1 [root@node2 ~]# vim /etc/init.d/mysqld 同理 (2) 通过UPDATE命令修改管理员密码 示例： MariaDB [mysql]> UPDATE user SET Password=PASSWORD('liumanlin') WHERE User='root' AND Host='127.0.0.1'; [root@node2 ~]# vim /usr/lib/systemd/system/mariadb.service ExecStart=/usr/bin/mysqld_safe --basedir=/usr (3) 以正常方式启动mysqld进程； 示例： [root@node2 ~]# systemctl daemon-reload [root@node2 ~]# systemctl restart mariadb.service 9.授权：GRANT GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ... ON [object_type] priv_level TO user_specification [, user_specification] ... [REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}] [WITH with_option ...] object_type(对象类型): TABLE | FUNCTION | PROCEDURE priv_level: * | *.*(所有库的所有表) | db_name.*(指定库的所有表) | db_name.tbl_name(指定库的指定表) | tbl_name(指定表) | db_name.routine_name(指定库的指定函数) ssl_option: SSL | X509 | CIPHER 'cipher' | ISSUER 'issuer' | SUBJECT 'subject' with_option: GRANT OPTION | MAX_QUERIES_PER_HOUR count | MAX_UPDATES_PER_HOUR count | MAX_CONNECTIONS_PER_HOUR count | MAX_USER_CONNECTIONS count 示例1： MariaDB [mysql]> GRANT CREATE ON hidb.* TO 'jerry'@'172.18.%.%'; [root@node2 ~]# mysql -ujerry -h272.18.67.12 -p MariaDB [(none)]> CREATE DATABASE hidb; MariaDB [(none)]> use hidb; MariaDB [hidb]> CREATE TABLE tbl1 (name CHAR(20)); MariaDB [hidb]> CREATE INDEX test ON tbl1(name); ERROR 1142 (42000): INDEX command denied to user 'jerry'@'172.18.67.12' for table 'tbl1' (无权创建索引，用以下方法) MariaDB [mysql]> GRANT INDEX ON hidb.* TO 'jerry'@'172.18.%.%'; MariaDB [mysql]> SHOW GRANTS FOR 'jerry'@'172.18.%.%'; MariaDB [hidb]> CREATE INDEX test ON tbl1(name); (授权成功) 示例2： MariaDB [mysql]> CREATE USER 'tom'@'172.18.%.%' IDENTIFIED BY 'liumanlin'; [root@node2 ~]# mysql -utom -h272.18.67.12 -p (可正常登录) MariaDB [mysql]> GRANT ALL ON hidb.* TO 'tom'@'172.18.%.%' REQUIRE SSL; (使用ssl授权登录) MariaDB [mysql]> SHOW GRANTS FOR 'tom'@'172.18.%.%'; MariaDB [mysql]> FLUSH PRIVILEGES; [root@node2 ~]# mysql -utom -h272.18.67.12 -p Enter password: ERROR 1045 (28000): Access denied for user 'tom'@'172.18.67.12' (using password: YES) (无法连接，需指明ssl证书) 示例3： MariaDB [mysql]> SHOW GLOBAL VARIABLES LIKE '%ssl%'; +---------------+----------+ | Variable_name | Value | +---------------+----------+ | have_openssl | DISABLED | | have_ssl | DISABLED | | ssl_ca | | | ssl_capath | | | ssl_cert | | | ssl_cipher | | | ssl_key | | +---------------+----------+ 10.查看授权：SHOW GRANTS SHOW GRANTS [FOR 'user'@'host'] 示例： MariaDB [mysql]> SHOW GRANTS FOR 'tom'@'172.18.%.%'; 11.取消授权：REVOKE REVOKE priv_type [(column_list)][, priv_type [(column_list)]] ... ON [object_type] priv_level FROM 'user'@'host' [, 'user'@'host'] ... REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ... 示例： MariaDB [mysql]> REVOKE CREATE VIEW ON hidb.* FROM 'tom'@'172.18.%.%'; MariaDB [mysql]> SHOW GRANTS FOR 'tom'@'172.18.%.%'; MariaDB [mysql]> FLUSH PRIVILEGES; （编辑：聊城站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
推荐文章
站长推荐
