mariadb数据库用户管理（创建、赋权、）
2024-09-08 19:31:24
数据库查看当前用户 select user（）；
MariaDB [(none)]> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
数据库新建用户 （--创建了一个名为：test 密码为：1234 的用户）
MariaDB [(none)]> create user 'test'@'localhost' identified by '1234';
Query OK, 0 rows affected (0.00 sec)
查询用户
MariaDB [zabbix]> select user,host from mysql.user;
+--------+-----------+
| user | host |
+--------+-----------+
| root | 127.0.0.1 |
| root | ::1 |
| root | localhost |
| test | localhost |
| zabbix | localhost |
+--------+-----------+
5 rows in set (0.00 sec)
修改用户密码
方法1，密码实时更新；修改用户“test”的密码为“5678”
MariaDB [mysql]> set password for test@localhost =password('5678');
Query OK, 0 rows affected (0.00 sec)
MariaDB [mysql]> exit
Bye
[root@192 ~]# mysql -utest -p5678
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 271
Server version: 10.2.36-MariaDB MariaDB Server
Copyright (c) 2000, 2018, 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@localhost |
+----------------+
1 row in set (0.00 sec)
方法2 ，需要刷新；修改用户“test”的密码为“1234”
MariaDB [(none)]> update mysql.user set password =password('root') where user='test';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [(none)]> flush privileges; 刷新权限
Query OK, 0 rows affected (0.00 sec)
方法3，命令行修改 mysqladmin -u用户名 -p旧密码 password 新密码
[root@192 ~]# mysqladmin -uroot -p1 password root
[root@192 ~]# mysql -uroot -proot
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 95
Server version: 10.2.36-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]>
分配用户权限
授予用户test通过外网IP对所有数据库所有表的全部权限
MariaDB [(none)]> grant all privileges on *.* to 'test'@'%' identified by '1234';
Query OK, 0 rows affected (0.00 sec)
注意：修改完权限以后 一定要刷新服务，或者重启服务，刷新服务用：flush privileges;
查看用户权限 show grants for test；
MariaDB [(none)]> show grants for test;
+--------------------------------------------------------------------------------------------------------------+
| Grants for test@% |
+--------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'test'@'%' IDENTIFIED BY PASSWORD '*A4B6157319038724E3560894F7F932C8886EBFCF' |
+--------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
查看自己的权限 show grants
MariaDB [(none)]> show grants;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B' WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
回收用户权限 revoke
MariaDB [(none)]> show grants for test;
+--------------------------------------------------------------------------------------------------------------+
| Grants for test@% |
+--------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'test'@'%' IDENTIFIED BY PASSWORD '*A4B6157319038724E3560894F7F932C8886EBFCF' |
+--------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec) MariaDB [(none)]> revoke all on *.* from test@'%';
Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> show grants for test;
+-----------------------------------------------------------------------------------------------------+
| Grants for test@% |
+-----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'%' IDENTIFIED BY PASSWORD '*A4B6157319038724E3560894F7F932C8886EBFCF' |
+-----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
删除用户 drop user test；
MariaDB [(none)]> drop user test;
Query OK, 0 rows affected (0.00 sec)
最新文章
- json 数据 添加 删除 排序
- ccc 多点触控2
- 【转】PowerShell入门（三）：如何快速地掌握PowerShell？
- 将Python脚本封装成exe可执行文件 转
- mysql 表空间及索引的查看方法
- android学习——popupWindow 在指定位置上的显示
- Day11 线程、进程、协程
- jquery1.11 操作checkbox：全选、取消全选、获取选择元素、获取取消选择元素（总结）
- HashMap实现分析
- jQuery smartMenu右键自定义上下文菜单插件
- jenkins配置小结
- CTPN项目部分代码学习
- 关于Error during managed flush [Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1]错误
- php开启慢日志
- mysql 建立索引的原则（转）
- 8 Productivity hacks for Data Scientists & Business Analysts
- django使用email进行身份验证(转载)
- 关于window.onload和body onload冲突的解决办法
- 同时对view延时执行两个动画时候的现象
- Python图像处理：图像腐蚀与图像膨胀