- 显示当前登录用户 select user();
- 修改当前/其它用户密码
set password=password(‘123456’) –修改当前用户的密码
set password for user=password(‘123456’) 其实是修改 用户名为user 主机地址为% 的用户的秘密
set password for ‘user’@’localhost’ = password(‘123456’)
update mysql.user set password=password(‘123456′) where User=’user’
- 创建用户 （不授权）
CREATE USER aaa –创建不带密码的用户aaa 主机位 %
CREATE USER bbb@localhost –创建不带密码的用户 bbb 主机位localhost
CREATE USER ccc IDENTIFIED BY ‘123’ –创建密码为 123 主机为 % 的 用户ccc
create user ‘ddd’@’localhost’ indentified by ‘123’
–单引号也是可以加的
create user eee@localhost indentified by ‘123’
还可以通过 insert 新增用户
- 删除用户
drop user aaa 删除用户 aaa 主机为%的
drop user aaa@localhost
如果不指定主机 默认删除 %的用户
也可以用 delete 删除用户
- 修改用户名
RENAME USER bbb@localhost TO admin@localhost
下面是错误的
RENAME USER bbb@localhost TO ‘admin’@’%’
也可以通过update修改
- grant 语法
GRANT priv_type [(column_list)] [, priv_type [(column_list)]] …
ON [object_type] {tbl_name | * | *.* | db_name.*}
TO user [IDENTIFIED BY [PASSWORD] ‘password’]
[, user [IDENTIFIED BY [PASSWORD] ‘password’]] …
[REQUIRE
NONE |
[{SSL| X509}]
[CIPHER ‘cipher’ [AND]]
[ISSUER ‘issuer’ [AND]]
[SUBJECT ‘subject’]]
[WITH with_option [with_option] …]object_type =
TABLE
| FUNCTION
| PROCEDUREwith_option =
GRANT OPTION
| MAX_QUERIES_PER_HOUR count
| MAX_UPDATES_PER_HOUR count
| MAX_CONNECTIONS_PER_HOUR count
| MAX_USER_CONNECTIONS count
GRANT ALL PRIVILEGES ON test.* TO ‘ccc’@’%’ IDENTIFIED BY ‘456’
如果用户存在则修改密码，如果不存在则新增用户
flash privileges 刷新权限
创建视图 授权
CREATE VIEW aa AS SELECT id 商品ID ,title 商品标题 FROM dbtable;
GRANT SELECT ON dbname.dbtable TO ‘ccc’@’%’;
DROP VIEW aa;
CREATE VIEW aa AS SELECT id 商品ID,title 商品标题 FROM dbtable ORDER BY id ASC LIMIT 10
- 取消权限
REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] …
ON [object_type] {tbl_name | * | *.* | db_name.*}
FROM user [, user] …REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] …
REVOKE ALL PRIVILEGES ON dbname.* FROM ‘ccc’@’%’;
未经允许不得转载：开心乐窝-乐在其中 » Mysql 用户权限管理
