云数据库 GaussDB-CREATE USER:示例

时间：2025-02-12 15:05:44

云数据库 GaussDB

示例

--创建用户jim，登录密码为********。gaussdb=# CREATE USER jim PASSWORD '********';--创建用户kim，登录密码为********。gaussdb=# CREATE USER kim IDENTIFIED BY '********';--创建用户tom,登录密码为********。gaussdb=# CREATE USER TOM PASSWORD '**********';--创建用户TOM，登录密码为********。gaussdb=# CREATE USER "TOM" PASSWORD '**********';--如果需要创建具有“创建数据库”权限的用户，则需要加CREATEDB关键字。gaussdb=# CREATE USER dim CREATEDB PASSWORD '********';--查看用户dim的权限。gaussdb=#  \du dim           List of roles Role name | Attributes | Member of -----------+------------+----------- dim       | Create DB  | {}（可以看到用户dim具有CREATEDB的权限）--修改用户jim的登录密码。gaussdb=# ALTER USER jim IDENTIFIED BY '**********' REPLACE '********';--为用户jim追加CREATEROLE权限。gaussdb=# ALTER USER jim CREATEROLE;--查看用户jim追加的CREATEROLE权限。gaussdb=# \du jim            List of roles Role name | Attributes  | Member of -----------+-------------+----------- jim       | Create role | {}--将enable_seqscan的值设置为on， 设置成功后，在下一会话中生效。gaussdb=# ALTER USER jim SET enable_seqscan TO on;--重置jim的enable_seqscan参数。gaussdb=# ALTER USER jim RESET enable_seqscan;--锁定jim账户。gaussdb=# ALTER USER jim ACCOUNT LOCK;--解锁jim账户。gaussdb=# ALTER USER jim ACCOUNT UNLOCK;--修改用户密码。gaussdb=# ALTER USER dim WITH PASSWORD '********';--修改用户名。gaussdb=# ALTER USER dim RENAME TO lisa;--创建具有OPRADMIN权限的用户user1和具有SYSADMIN权限的用户user2。gaussdb=# CREATE USER user1 WITH OPRADMIN PASSWORD '********';gaussdb=# CREATE USER user2 WITH SYSADMIN PASSWORD '********';--查看权限。gaussdb=# \du user1                    List of roles Role name |         Attributes          | Member of -----------+-----------------------------+----------- user1     |        Operatoradmin        | {}gaussdb=# \du user2                 List of roles Role name |       Attributes       | Member of -----------+------------------------+----------- user2     |       Sysadmin         | {}--创建具有CREATEDB权限的角色user3。gaussdb=# CREATE USER user3 CREATEDB PASSWORD '********';CREATE ROLE--查看权限。gaussdb=# \du user3                  List of roles Role name |       Attributes        | Member of -----------+-------------------------+----------- user3     |       Create DB         | {}--使用ADMIN子句创建新角色user4。gaussdb=# CREATE USER user4 WITH  CREATEDB  ADMIN user3  PASSWORD '********';CREATE ROLE--查看权限。gaussdb=# \du user3                  List of roles Role name |       Attributes        | Member of -----------+-------------------------+----------- user3     |       Create DB         | {user4}gaussdb=# \du user4                  List of roles Role name |       Attributes        | Member of -----------+-------------------------+----------- user4     |       Create DB         | {}--删除角色。gaussdb=# DROP USER user1;gaussdb=# DROP USER user2;gaussdb=# DROP USER user3;gaussdb=# DROP USER user4;--三权分立配置：设置参数enableSeparationOfDuty为on，则会开启三权分立配置，限制系统管理员的权限。不允许系统管理员创建或更改用户配置，这样可以有效控制系统管理员访问用户私有数据。--查看三权分立是否打开。gaussdb=# SHOW enableSeparationOfDuty; enableSeparationOfDuty ------------------------ off(1 row)--执行以下语句修改参数。gs_guc set -Z coordinator -Z datanode -N all -I all -c "enableSeparationOfDuty = on"--设置成功后，重启数据库生效。gs_om -t stop gs_om -t start--再次查看三权分立。gaussdb=# SHOW enableSeparationOfDuty; enableSeparationOfDuty ------------------------ on(1 row)--删除用户。gaussdb=# DROP USER kim CASCADE;gaussdb=# DROP USER jim CASCADE;gaussdb=# DROP USER lisa CASCADE;gaussdb=# DROP USER TOM CASCADE;gaussdb=# DROP USER "TOM" CASCADE;