通过对比mongo和mysql对于账户的操作来帮助大家更快的熟悉两种数据库相关命令。
基础增删改查
| 命令 | mysql | mongo |
|---|---|---|
| 创建用户 | create user 'user'@'%' indentified by 'pwd'; | db.createUser({user:'user',pwd:'pwd',roles:[{}]}) |
| 查看用户 | show grants for 'user'@'%'; | 1、db.runCommand({usersInfo:'user'}) 2、db.getUser("siber") |
| 授权用户 | grant ... on ... to 'user'@'%'; | db.grantRolesToUser() |
| 删除用户 | drop user 'user'@'%' ; | db.dropUser('user') |
系统账户表
mysql 中使用 mysql.user 表维护账户用户名密码及权限。操作可以通过对该表的增删改查来实现对账户、密码、权限的变更（不建议）。修改完之后需要执行flush命令来使变更生效。
mysql> desc mysql.user;
+------------------------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Password | char(41) | NO | | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) unsigned | NO | | 0 | |
| plugin | char(64) | YES | | | |
| authentication_string | text | YES | | NULL | |
| password_expired | enum('N','Y') | NO | | N | |
+------------------------+-----------------------------------+------+-----+---------+-------+
43 rows in set (0.02 sec)
flush privileges;
mongo中使用
查看当前连接的账户信息
mysql使用\s命令可以查看当前的连接信息，包括连接使用的用户是谁：”Current User“
mysql> \s
--------------
/usr/local/mysql/bin/mysql Ver 14.14 Distrib 5.7.26, for macos10.14 (x86_64) using EditLine wrapper
Connection id: 1
Current database:
Current user: root@127.0.0.1
SSL: Not in use
Current pager: less
Using outfile: ''
Using delimiter: ;
Server version: 5.6.16-log Source distribution
Protocol version: 10
Connection: TCP/IP
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
TCP port: 3306
Uptime: 38 days 22 hours 48 min 53 sec
mongo使用runCommand命令进行查看。该命令不仅可以查看到用户是谁，还直接显示了该用户的权限。
mgset-4884813:PRIMARY> db.runCommand({connectionStatus : 1})
{
"authInfo" : {
"authenticatedUsers" : [
{
"user" : "user",
"db" : "demo"
}
],
"authenticatedUserRoles" : [
{
"role" : "readWrite",
"db" : "demo"
}
]
},
"ok" : 1
}
修改密码
mysql修改密码：
set password for user = 'newpassword';
mongo修改密码：
use db; // 切换到指定到db中
db.changeUserPassword('user','newpassword');
授予权限
mysql授权：
// 授予用户所有查询权限
grant select on *.* to 'user'@'%';
mongo授权
// 授予用户指定DB的读写权限
db.grantRolesToUser("user",[{role:"readWrite",db:"test"}])
回收权限
mysql回收权限
// 回收用户的全部查询权限
revoke select on *.* from 'user'@'%';
mongo回收权限
use db;
db.revokeRolesFromUser( "user",[{role: "read", db: "dynamo"}])
实例开启、关闭权限认证
权限详细说明
| 操作 | mysql | mongo |
|---|---|---|
| 创建超级用户 | 。。。 | 。。 |
| 授予全局权限 | 。。。 | 。。。 |
| 授予指定库权限 | 。。。 | 。。。 |
| 授予指定表、集合权限 | 。。。 | 。。。 |
