第五课
5.1 学习openGauss创建用户、修改用户属性、更改用户权限和删除用户
👉openGauss SQL学习参考资料
https://opengauss.org/zh/docs/2.1.0/docs/Developerguide/SQL%E8%AF%AD%E6%B3%95.html
学习目标
学习openGauss创建用户、修改用户属性、更改用户权限和删除用户
课程学习
用户是用来登录数据库的，通过对用户赋予不同的权限，可以方便地管理用户对数据
库的访问及操作
连接openGauss
#第一次进入等待15秒
#数据库启动中...
su - omm
gsql -r
1.创建用户
–以下两种设置密码方法等效
CREATE USER jim PASSWORD 'abcd@123';
CREATE USER kim IDENTIFIED BY 'abcd@456';
–用户dim具有创建数据库权限
CREATE USER dim CREATEDB PASSWORD 'abcd@789';
–查看用户
\du
2.修改用户属性
–修改密码
ALTER USER jim IDENTIFIED BY 'Abcd@123' REPLACE 'abcd@123';
–为用户jim增加CREATEROLE权限
ALTER USER jim CREATEROLE;
–查看用户
\du
3.授权
–将用户jim的权限授权给用户kim
GRANT jim to kim;
–将sysadmin权限授权给用户dim
GRANT ALL PRIVILEGES TO dim;
–重命名用户dim
alter user dim rename to tim;
–查看用户
\du
4.回收权限
–撤消kim的权限
REVOKE jim FROM kim;
–撤消用户dim的sysadmin权限
revoke all privilege from tim;
–查看用户
\du
5.删除用户
drop user tim;
drop user jim;
drop user kim;
课程作业
1.创建用户user1、user2和user3，user1具有CREATEROLE权限，user2具有CREATEDB权限，要求使用两种不同的方法设置密码
CREATE USER user1 CREATEROLE PASSWORD 'abcd@123';
CREATE USER user2 CREATEDB IDENTIFIED BY 'abcd@456';
CREATE USER user3 PASSWORD 'abcd@123';
2.修改用户user1的密码
ALTER USER user1 IDENTIFIED BY 'News@123' REPLACE 'abcd@123';
3.重命名用户user2
alter user user2 rename to user20;
4.将用户user1的权限授权给用户user3，再回收用户user3的权限
grant user1 to user3;
revoke user1 from user3;
\du+
5.删除所有创建用户
过程中使用\du或\du+查看用户信息
drop user user1;
drop user user20;
drop user user3;
执行过程：
omm=# CREATE USER user1 CREATEROLE PASSWORD 'abcd@123';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# CREATE USER user2 CREATEDB IDENTIFIED BY 'abcd@456';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# CREATE USER user3 PASSWORD 'abcd@123';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# ALTER USER user1 IDENTIFIED BY 'New@123' REPLACE 'abcd@123';
ERROR: Password must contain at least 8 characters.
omm=# ALTER USER user1 IDENTIFIED BY 'News@123' REPLACE 'abcd@123';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
ALTER ROLE
omm=# alter user user2 rename to user20;
NOTICE: MD5 password cleared because of role rename
omm=# ALTER ROLE
omm=# grant user1 to user3;
GRANT ROLE
omm=# revoke user1 from user3;
REVOKE ROLE
omm=# \du+
List of roles
Role name | Attributes
| Member of | Description
-----------+-----------------------------------------------------------------------------------------------
-------------------+-----------+-------------
gaussdb | Sysadmin
| {} |
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin,
Policyadmin, UseFT | {} |
user1 | Create role
| {} |
user20 | Create DB
| {} |
user3 |
| {} |
omm=# drop user user1;
DROP ROLE
omm=# drop user user20;
DROP ROLE
omm=# drop user user3;
DROP ROLE
omm=#
