- MariaDB数据库操作
- MariaDB表操作
- MariaDB表结构修改
- MariaDB增删改查操作
- MariaDB子句
- MariaDB聚合函数
- MariaDB连接
- MariaDB操作符
- MariaDB正则与条件
- MariaDB约束
- MariaDB导出与导入
- MariaDB权限与权限管理
查看用户
select user(or *) from mysql.user;
创建用户
通过create
命令创建用户:
create user 'newuser'@'localhost' identified by '123456';
通过grant
命令创建用户:
grant all privileges on *.* to username@localhost identified by 'password';
删除用户
使用drop
命令来删除用户:
drop user user_name1,user_name2...
通过操作user
表来删除用户:
delete from user where user=username and host='localhost';
修改用户名
RENAME USER 'olduser'@'127.0.0.1' TO 'newuser'@'localhost';
查看用户权限
show grants for 'root'@'localhost';
添加用户权限
grant all privileges on *.* to test@localhost identified by 'test' with grant option;
or
grant select,insert,update,delete,create,drop,alter privileges on 'test'.'*' to 'test1'@'*' identified by 'test';
with grant option
:该参数表示为用户添加grant
权限。
收回用户权限
revoke select,delete,delete,insert on mysql.user from testname@localhost;
or
REVOKE ALL ON *.* FROM 'test'@'localhost' with grant option;
通过修改表来创建用户、修改密码或者修改权限后,必须要执行如下命令:
flush privileges;
常用的用户权限:
ALTER | 更改数据表或索引的定义 |
CREATE | 创建数据库或数据表 |
DELETE | 删除数据库中现有的数据行 |
DROP | 删除数据库、数据表或其他对象 |
INSERT | 往数据表中插入新数据行 |
SELECT | 检索数据表里的数据行 |
UPDATE | 修改数据行 |
处理忘记密码的情况
# net stop mysql
# mysqld_safe --skip-grant-tables --skip-networking &
# mysql
mysql> update mysql.user set password=password("123456") where user='root' and host='localhost';
mysql> flush privileges;
mysql> select user,host,password from mysql.user where user='root' and host='localhost';
+------+-----------+-------------------------------------------+
| user | host | password |
+------+-----------+-------------------------------------------+
| root | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+------+-----------+-------------------------------------------+
1 row in set
mysql> exit
[root@xuexi mysql]# net stop mysql
[root@xuexi mysql]# net start mysql
[root@xuexi mysql]# mysql -uroot -p123456
mysql> \q