GaussDB 100: 用户权限以及修改 SYS 用户的缺省口令

在完成 GaussDB 的安装之后，应该及时修改缺省的用户口令，避免发生安全问题。

缺省的系统存在两个帐号，SYS 是系统的 SYSDBA 用户，PUBLIC 是一个特殊都公用用户对象。

SQL> select username from dba_users;
USERNAME
------------------------------------------------------
SYS
PUBLIC

SYS 用户的缺省口令是： Changeme_123

[root@enmodb1 ~]# su - eygle
Last login: Wed Dec 4 22:41:05 CST 2019 on pts/1
[eygle@enmodb1 ~]$ zsql SYS/Changeme_123@127.0.0.1:1888

Warning: SSL connection to server without CA certificate is insecure. Continue anyway? (y/n):y
connected.

SQL> select * from v$version;

VERSION

GaussDB_100_1.0.0.B019 Release 0b7bb43
ZENGINE
0b7bb43

3 rows fetched.

SQL> ALTER USER SYS IDENTIFIED BY ENmotech_100 REPLACE Changeme_123;

Succeed.

SQL> connect SYS/ENmotech_100@127.0.0.1:1888

Warning: SSL connection to server without CA certificate is insecure. Continue anyway? (y/n):y
connected.

SQL>

这样就完成了数据库创建之后的第一个安全工作。

GaussDB 缺省有 4 个角色：

SQL> select role from dba_roles;

ROLE

----------------------------------------------------------------

DBA

RESOURCE

CONNECT

STATISTICS

4 rows fetched.

权限视图同 Oracle 一样是 DBA_SYS_PRIVS :

SQL> select grantee,privilege from dba_sys_privs;

GRANTEE PRIVILEGE

---------------------------------------------------------------- ----------------------------

SYS ALTER ANY INDEX

SYS ALTER ANY MATERIALIZED VIEW

SYS ALTER ANY PROCEDURE

SYS ALTER ANY ROLE

SYS ALTER ANY SEQUENCE

SYS ALTER ANY TABLE

SYS ALTER ANY TRIGGER

SYS ALTER DATABASE

SYS ALTER PROFILE

SYS ALTER SESSION

SYS ALTER SYSTEM

SYS ALTER TABLESPACE

SYS ALTER USER

SYS CREATE ANY INDEX

SYS CREATE ANY MATERIALIZED VIEW

SYS CREATE ANY PROCEDURE

SYS CREATE ANY SEQUENCE

SYS CREATE ANY SYNONYM

SYS CREATE ANY TABLE

SYS CREATE ANY TRIGGER

SYS CREATE ANY VIEW

SYS CREATE DATABASE

SYS CREATE MATERIALIZED VIEW

SYS CREATE NODE

SYS CREATE PROCEDURE

SYS CREATE PROFILE

SYS CREATE PUBLIC SYNONYM

SYS CREATE ROLE

SYS CREATE SEQUENCE

SYS CREATE SESSION

SYS CREATE SYNONYM

SYS CREATE TABLE

SYS CREATE TABLESPACE

SYS CREATE TRIGGER

SYS CREATE USER

SYS CREATE VIEW

SYS CREATE DISTRIBUTE RULE

SYS DROP ANY INDEX

SYS DROP ANY MATERIALIZED VIEW

SYS DROP ANY PROCEDURE

SYS DROP ANY ROLE

SYS DROP ANY SEQUENCE

SYS DROP ANY SYNONYM

SYS DROP ANY TABLE

SYS DROP ANY TRIGGER

SYS DROP ANY VIEW

SYS DROP PROFILE

SYS DROP PUBLIC SYNONYM

SYS DROP TABLESPACE

SYS DROP USER

SYS FLASHBACK ANY TABLE

SYS FLASHBACK ARCHIVE ADMINISTER

SYS GLOBAL QUERY REWRITE

SYS GRANT ANY OBJECT PRIVILEGE

SYS GRANT ANY PRIVILEGE

SYS GRANT ANY ROLE

SYS LOCK ANY TABLE

SYS MANAGE TABLESPACE

SYS ON COMMIT REFRESH

SYS PURGE DBA_RECYCLEBIN

SYS READ ANY TABLE

SYS SELECT ANY SEQUENCE

SYS SELECT ANY TABLE

SYS UNLIMITED TABLESPACE

SYS UNDER ANY VIEW

SYS COMMENT ANY TABLE

SYS UPDATE ANY TABLE

SYS INSERT ANY TABLE

SYS DELETE ANY TABLE

SYS EXECUTE ANY PROCEDURE

SYS SYSBACKUP

SYS SYSDBA

SYS SYSOPER

SYS ANALYZE ANY

SYS DROP NODE

SYS ALTER NODE

DBA ALTER ANY INDEX

DBA ALTER ANY MATERIALIZED VIEW

DBA ALTER ANY PROCEDURE

DBA ALTER ANY ROLE

DBA ALTER ANY SEQUENCE

DBA ALTER ANY TABLE

DBA ALTER ANY TRIGGER

DBA ALTER DATABASE

DBA ALTER PROFILE

DBA ALTER SESSION

DBA ALTER SYSTEM

DBA ALTER TABLESPACE

DBA ALTER USER

DBA CREATE ANY INDEX

DBA CREATE ANY MATERIALIZED VIEW

DBA CREATE ANY PROCEDURE

DBA CREATE ANY SEQUENCE

DBA CREATE ANY SYNONYM

DBA CREATE ANY TABLE

DBA CREATE ANY TRIGGER

DBA CREATE ANY VIEW

DBA CREATE DATABASE

DBA CREATE MATERIALIZED VIEW

DBA CREATE NODE

DBA CREATE PROCEDURE

DBA CREATE PROFILE

DBA CREATE PUBLIC SYNONYM

DBA CREATE ROLE

DBA CREATE SEQUENCE

DBA CREATE SESSION

DBA CREATE SYNONYM

DBA CREATE TABLE

DBA CREATE TABLESPACE

DBA CREATE TRIGGER

DBA CREATE USER

DBA CREATE VIEW

DBA CREATE DISTRIBUTE RULE

DBA DROP ANY INDEX

DBA DROP ANY MATERIALIZED VIEW

DBA DROP ANY PROCEDURE

DBA DROP ANY ROLE

DBA DROP ANY SEQUENCE

DBA DROP ANY SYNONYM

DBA DROP ANY TABLE

DBA DROP ANY TRIGGER

DBA DROP ANY VIEW

DBA DROP PROFILE

DBA DROP PUBLIC SYNONYM

DBA DROP TABLESPACE

DBA DROP USER

DBA FLASHBACK ANY TABLE

DBA FLASHBACK ARCHIVE ADMINISTER

DBA GLOBAL QUERY REWRITE

DBA GRANT ANY OBJECT PRIVILEGE

DBA GRANT ANY PRIVILEGE

DBA GRANT ANY ROLE

DBA LOCK ANY TABLE

DBA MANAGE TABLESPACE

DBA ON COMMIT REFRESH

DBA PURGE DBA_RECYCLEBIN

DBA READ ANY TABLE

DBA SELECT ANY SEQUENCE

DBA SELECT ANY TABLE

DBA UNLIMITED TABLESPACE

DBA UNDER ANY VIEW

DBA COMMENT ANY TABLE

DBA UPDATE ANY TABLE

DBA INSERT ANY TABLE

DBA DELETE ANY TABLE

DBA EXECUTE ANY PROCEDURE

DBA SYSBACKUP

DBA SYSDBA

DBA SYSOPER

DBA ANALYZE ANY

DBA DROP NODE

DBA ALTER NODE

RESOURCE CREATE PROCEDURE

RESOURCE CREATE SEQUENCE

RESOURCE CREATE TABLE

RESOURCE CREATE TRIGGER

CONNECT CREATE SESSION

157 rows fetched.

供参考。