1.创建用户
–以下两种设置密码方法等效
CREATE USER jim PASSWORD ‘abcd@123’;
CREATE USER kim IDENTIFIED BY ‘abcd@456’;
omm=# create user jim password ‘abcd@123’
omm-# ;
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# create user kim identified by ‘abcd@456’;
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
–用户dim具有创建数据库权限
CREATE USER dim CREATEDB PASSWORD ‘abcd@789’;
omm=# create user dim createdb password ‘abcd@789’;
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
–查看用户
\du
omm=# \du
List of roles
Role name | Attributes
| Member of
-----------±------------------------------------------------------------------------------------
-----------------------------±----------
dim | Create DB
| {}
gaussdb | Sysadmin
| {}
jim |
| {}
kim |
| {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Opera
toradmin, Policyadmin, UseFT | {}
2.修改用户属性
–修改密码
ALTER USER jim IDENTIFIED BY ‘Abcd@123’;
omm=# alter user jim identified by ‘Abcd@123’;
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
ALTER ROLE
–为用户jim增加CREATEROLE权限
ALTER USER jim CREATEROLE;
omm=# alter user jim createrole;
ALTER ROLE
–查看用户
\du
omm=# \du
List of roles
Role name | Attributes
| Member of
-----------±------------------------------------------------------------------------------------
-----------------------------±----------
dim | Create DB
| {}
gaussdb | Sysadmin
| {}
jim | Create role
| {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Opera
toradmin, Policyadmin, UseFT | {}
kim |
| {}
3.授权
–将用户jim的权限授权给用户kim
GRANT jim to kim;
omm=# grant jim to kim;
GRANT ROLE
–将sysadmin权限授权给用户dim
GRANT ALL PRIVILEGES TO dim;
omm=# grant all privileges to dim;
ALTER ROLE
–重命名用户dim
alter user dim rename to tim;
–查看用户
\du
omm=# \du List of roles
Role name | Attributes
| Member of
-----------±------------------------------------------------------------------------------------
-----------------------------±----------
dim | Create DB, Sysadmin
| {}
gaussdb | Sysadmin
| {}
jim | Create role
| {}
kim | Sysadmin
| {jim}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Opera
toradmin, Policyadmin, UseFT | {}
4.回收权限
–撤消kim的权限
REVOKE jim FROM kim;
omm=# revoke jim from kim;
REVOKE ROLE
–撤消用户dim的sysadmin权限
revoke all privilege from tim;
omm=# revoke jim from kim;
REVOKE ROLE
–查看用户
\du
omm=# \du
List of roles
Role name | Attributes
| Member of
-----------±------------------------------------------------------------------------------------
-----------------------------±----------
dim | Create DB
| {}
gaussdb | Sysadmin
| {}
kim | Sysadmin
| {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Opera
jim | Create role
| {}
toradmin, Policyadmin, UseFT | {}
5.删除用户
drop user tim;
drop user jim;
drop user kim;
omm=# drop user jim;
DROP ROLE
omm=# drop user dim;
DROP ROLE
omm=# drop user kim;
DROP ROLE
课程作业
1.创建用户user1、user2和user3，user1具有CREATEROLE权限，user2具有CREATEDB权限，要求使用两种不同的方法设置密码
omm=# create user user1 password ‘abcd.1234’;
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# create user user2 identified by ‘abcd.1234’;
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# create user user3 password ‘abcd.1234’;
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# \du
List of roles
Role name | Attributes
| Member of
-----------±------------------------------------------------------------------------------------
-----------------------------±----------
gaussdb | Sysadmin
| {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Opera
toradmin, Policyadmin, UseFT | {}
user1 |
| {}
user2 |
| {}
| {}
user3 |
2.修改用户user1的密码
omm=# alter user user1 password ‘abcd.12345’;
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
ALTER ROLE
3.重命名用户user2
omm=# alter user user2 rename to user20;
NOTICE: MD5 password cleared because of role rename
ALTER ROLE
4.将用户user1的权限授权给用户user3，再回收用户user3的权限
omm=# grant user1 to user3;
GRANT ROLE
omm=# rovoke user1 from user3;
ERROR: syntax error at or near “rovoke”
LINE 1: rovoke user1 from user3;
^
omm=# revoke user1 from user3;
REVOKE ROLE
omm=# grant all privilege to user3;
ALTER ROLE
omm=# \du
List of roles
Role name | Attributes
user3 | Sysadmin
| {}
| Member of
-----------±------------------------------------------------------------------------------------
-----------------------------±----------
gaussdb | Sysadmin
| {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Opera
toradmin, Policyadmin, UseFT | {}
user1 | Create role
| {}
user20 | Create DB
| {}
5.删除所有创建用户
omm=# drop user user1;
DROP ROLE
omm=# drop user user20;
DROP ROLE
omm=# drop user user3;
DROP ROLE
omm=# \du
-----------±------------------------------------------------------------------------------------
-----------------------------±----------
gaussdb | Sysadmin
| {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Opera
toradmin, Policyadmin, UseFT | {}
List of roles
Role name | Attributes
| Member of
omm=#
