MySQL用户管理与PostgreSQL用户管理的区别说明
一.MySQL用户管理
【例1.1】使用root用户登录到本地mysql服务器的test库中
mysql-uroot-p-hlocalhosttest
【例1.2】使用root用户登录到本地mysql服务器的test库中,执行一条查询语句
mysql-uroot-p-hlocalhosttest-e"DESCperson;"
【例1.3】使用CREATEUSER创建一个用户,用户名是jeffrey,密码是mypass,主机名是localhost
CREATEUSER'jeffrey'@'localhost'IDENTIFIEDBY'mypass';
【例1.4】使用GRANT语句创建一个新的用户testUser,密码为testpwd。用户testUser对所有的数据有查询和更新权限,并授于对所有数据表的SELECT和UPDATE权限
GRANTSELECT,UPDATEON*.*TO'testUser'@'localhost'IDENTIFIEDBY'testpwd';/*创建账户并授予权限*/ SELECTHost,User,Select_priv,Update_priv,FROMmysql.userwhereuser='testUser';/*查看账户权限信息*/
【例1.5】使用INSERT创建一个新账户,其用户名称为customer1,主机名称为localhost,密码为customer1:
INSERTINTOuser(Host,User,Password)VALUES('localhost','customer1',PASSWORD('customer1'));
【例1.6】使用DROPUSER删除用户'jeffrey'@‘localhost'
DROPUSER'jeffrey'@'localhost';
【例1.7】使用DELETE删除用户'customer1'@‘localhost'
DELETEFROMmysql.userWHEREhost='localhost'anduser='customer1';
【例1.8】使用mysqladmin将root用户的密码修改为“rootpwd”
mysqladmin-uroot-ppassword"123456"
【例1.9】使用UPDATE语句将root用户的密码修改为“rootpwd2”:
UPDATEmysql.usersetPassword=password("rootpwd2") WHEREUser="root"andHost="localhost";
【例1.10】使用SET语句将root用户的密码修改为“rootpwd3”:
SETPASSWORD=password("rootpwd3");
【例1.11】使用SET语句将testUser用户的密码修改为“newpwd”:
SETPASSWORDFOR'testUser'@'localhost'=password("newpwd");
【例1.12】使用UPDATE语句将testUser用户的密码修改为“newpwd2”:
UPDATEmysql.usersetPassword=PASSWORD("newpwd2") WHEREUser="testUser"andHost="localhost";
【例1.13】使用GRANT语句将testUser用户的密码修改为“newpwd3”:
sql
【例1.14】testUser用户使用SET语句将自身的密码修改为“newpwd4”:
SETPASSWORD=PASSWORD("newpwd4");
【例1.15】使用GRANT语句创建一个新的用户grantUser,密码为“grantpwd”。用户grantUser对所有的数据有查询、插入权限,并授于GRANT权限。GRANT语句及其执行结果如下:
MySQL>GRANTSELECT,INSERTON*.*TO'grantUser'@'localhost'IDENTIFIEDBY'grantpwd'WITHGRANTOPTION; QueryOK,0rowsaffected(0.03sec)
结果显示执行成功,使用SELECT语句查询用户testUser2的权限:
MySQL>SELECTHost,User,Select_priv,Insert_priv,Grant_privFROMmysql.userwhereuser='grantUser'; +-----------+------------+-------------+--------------+-------------+ |Host|User|Select_priv|Insert_priv|Grant_priv| +-----------+------------+-------------+--------------+--------------+ |localhost|testUser2|Y|Y|Y| +-----------+------------+-------------+--------------+--------------+ 1rowinset(0.00sec)
【例1.16】使用REVOKE语句取消用户testUser的更新权限。REVOKE语句及其执行结果如下:
MySQL>REVOKEUPDATEON*.*FROM'testUser'@'localhost'; QueryOK,0rowsaffected(0.00sec)
执行结果显示执行成功,使用SELECT语句查询用户test的权限:
MySQL>SELECTHost,User,Select_priv,Update_priv,Grant_privFROMMySQL.userwhereuser='testUser';
【例1.17】使用SHOWGRANTS语句查询用户testUser的权限信息。SHOWGRANTS语句及其执行结果如下:
MySQL>SHOWGRANTSFOR'testUser'@'localhost';
案例操作过程
打开MySQL客户端工具,输入登录命令,登录MySQL。
C:\>mysql-uroot-p Enterpassword:**
输入正确密码,按回车,出现欢迎信息表示登录成功。
选择mysql数据库为当前数据库。
MySQL>usemysql; Databasechanged
出现Databasechanged信息表明切换数据库成功。
创建新账户,用户名称为newAdmin,密码为pw1,允许其从本地主机访问MySQL。
使用GRANT语句创建新账户,创建过程如下:
MySQL>GRANTSELECT,UPDATE(id,name,age) ->ONtest_db.person_old ->TO'newAdmin'@'localhost'IDENTIFIEDBY'pw1' ->WITHMAX_CONNECTIONS_PER_HOUR30; SELECThost,user,select_priv,update_privFROMuserWHEREuser='newAdmin'; SELECThost,db,user,table_name,table_priv,column_priv FROMtables_privWHEREuser='newAdmin'; SELECThost,db,user,table_name,column_name,column_priv FROMcolumns_privWHEREuser='newAdmin';
3条SQL语句的查询结果分别如下:
MySQL>SELECThost,user,select_priv,update_privFROMuserWHEREuser='newAdmin'; MySQL>SELECThost,db,user,table_name,table_priv,column_priv ->FROMtables_privWHEREuser='newAdmin'; MySQL>SELECThost,db,user,table_name,column_name,column_priv ->FROMcolumns_privWHEREuser='newAdmin';
使用SHOWGRANTS语句查看newAdmin的权限信息。
查看newAdmin账户的权限信息,输入语句如下:
SHOWGRANTSFOR'newAdmin'@'localhost';
使用newAdmin用户登录MySQL。
退出当前登录,使用EXIT命令,语句如下:
MySQL>exit Bye
使用newAdmin账户登录MySQL,语句如下:
C:\>MySQL-unewAdmin-p Enterpassword:***
输入密码正确后,出现“mysql>”提示符,登录成功。
使用newAdmin用户查看test_db数据库中person_dd表中的数据。
newAdmin用户被授予test数据库中person表中3个字段上的查询权限,因此可以执行SELECT语句查看这几个字段的值,执行过程如下:
MySQL>SELECT*FROMtest_db.person_ddLIMIT5;
使用newAdmin用户向person_dd表中插入一条新记录,查看语句执行结果。
插入新记录,输入语句如下:
INSERTINTOtest_db.person_old(name,age,info)VALUES('gaga',30);
执行结果如下:
ERROR1142(42000):INSERTcommanddeniedtouser'newAdmin'@'localhost'fortable'person'
可以看到,语句不能执行,错误信息表明newAdmin用户不能对person表进行插入操作。因此,用户不可以执行没有被授权的操作语句。
退出当前登录,使用root用户重新登录,收回newAdmin账户的权限。
输入退出命令:exit
重新以root用户登录MySQL,并选择mysql数据库为当前数据库。
输入语句收回newAdmin账户的权限,执行过程如下:
REVOKESELECT,UPDATEONtest.personFROM'newAdmin'@'localhost';
执行结果如下:
MySQL>REVOKESELECT,UPDATEONtest.personFROM'newAdmin'@'localhost'; QueryOK,0rowsaffected(0.00sec)
删除newAdmin的账户信息。
删除指定账户,可以使用DROPUSER语句,输入如下:
DROPUSER'newAdmin'@'localhost';
二.PostgreSQL用户管理
2.1组角色管理
【例2.1】创建一个名称为post2的角色,SQL代码如下:
CREATEROLEpost2;
【例2.2】查看系统中的角色,SQL代码如下:
SELECTrolnameFROMpg_roles;
【例2.3】修改角色post1的名称为post3。SQL语句如下:
ALTERROLEpost1RENAMETOpost3;
【例2.4】删除角色post3。SQL语句如下:
DROPROLEpost3;
2.2角色的各种权限
1、登录
【例2.5】创建角色post4,此角色具有登录权限。SQL语句如下:
CREATEROLEpost4LOGIN;
2超级用户
【例2.6】创建角色post5,此角色具有超级用户权限。SQL语句如下:
CREATEROLEpost5SUPERUSER;
3、创建数据库
【例2.7】创建角色post6,此角色具有创建数据库权限。SQL语句如下:
CREATEROLEpost6CREATEDB;
4、创建角色
角色要想创建角色,必须明确给出该权限(除了超级用户以外)。一旦角色具有CREATEROLE权限,即可更改和删除其他角色,还可以给其它角色赋予或者撤销成员关系。当然,如果想对超级用户进行操作,仅有此权限还不够,必须拥有SUPERUSER权限。
创建具有创建角色权限的角色,SQL语法如下:
CREATEROLEnameCREATEROLE;
【例2.8】创建角色post7,此角色具有创建数据库权限。SQL语句如下:
CREATEROLEpost7CREATEROLE;
5、口令
在客户认证方法要求与数据库建立连接时,需要口令权限。常见的认证方法包括password、md5和crypt。
创建具有口令权限的角色,SQL语法如下:
CREATEROLEname
口令认证方法具体口令
【例2.9】创建角色post8,此角色具有口令权限。SQL语句如下:
CREATEROLEpost8PASSWORD'123456';
2.3账户管理
1、创建用户
【例2.10】创建用户名称为postgre02,并具有创建数据库和创建角色的权限,同时登陆密码为“123456789”。SQL语句如下:
CREATEUSERpostgre02PASSWORD'123456789' CREATEDBCREATEROLE;
2、删除用户
【例2.11】使用DROPUSER删除账户“postgre02”,SQL语句如下:
DROPUSERpostgre02;
3、修改用户密码
【例2.12】将账户“postgre01”的密码修改为“123123”,SQL语句如下:
ALTERUSERpostgre01PASSWORD'123123';
2.4组角色和用户角色管理
1、对组角色授权
【例2.13】给“post1”角色添加创建数据表和创建角色的权限,SQL语句如下:
ALTERROLEpost1CREATEDBCREATEROLE;
2、对用户授权
【例2.14】给“postgre01”用户添加创建数据表和创建角色的权限,SQL语句如下:
ALTERUSERpostgre01CREATEDBCREATEROLE;
3、收回组角色权限
【例2.15】将“post1”角色的创建数据表和创建角色权限收回,SQL语句如下:
ALTERROLEpost1NOCREATEDBNOCREATEROLE;
4、收回用户权限
【例2.16】将“postgre01”用户的创建数据表和创建角色权限收回,SQL语句如下:
ALTERUSERpostgre01NOCREATEDBNOCREATEROLE;
2.5数据库权限管理
1、修改数据库的拥有者
【例2.17】将“mytest”数据库的所有者修改为post1。SQL语句如下:
ALTERDATABASEmytestOWNERTOpost2;
2、增加用户的数据表权限
【例2.18】其中ppo1是一个现有的数据表,postgres是一个现有的用户,允许postgres更新ppo1数据表,用下面的命令更新表的权限:
GRANTUPDATEONppo1TOpostgres;
如果将上面的语句修改为:
GRANTUPDATEONppo1TOPUBLIC;
则表示将数据表ppo1的更新权限赋予系统中的所有角色。
如果将上面的语句修改为:
GRANTALLONppo1TOpostgres;
则表示把适用于该对象的所有权限都赋予用户postgres。
以上为个人经验,希望能给大家一个参考,也希望大家多多支持毛票票。如有错误或未考虑完全的地方,望不吝赐教。