高斯数据库HCNA之权限控制

一、用户权限控制

1、用户、角色、权限

1.1、权限

权限是什么
执行某些特定SQL语句的能力以及访问或维护某一特定对象的能力

权限的类型
系统权限
作用：用户可以执行一类特定的SQL语句
例如：用户A想要连接数据库，则用户A需要有CREATE SESSION系统权限
对象权限
作用：用户可以访问或维护某一特定对象
例如：用户A访问用户B的表C，则用户A需要有表C的SELECT对象权限

1.2、系统权限

系统权限是什么
用户进行系统操作的能力
用户可以执行一类特定的SQL语句

1.3、对象权限

对象权限是什么
对数据库对象执行各种操作的能力
用户可以访问或维护某一特定对象

1.4、角色

角色是什么
角色是一组命名的权限的集合，数据库使用角色进行权限组织及划分

意义
一个数据库可能有多个用户需要访问，为了方便管理，可以先将权限分组，并授予给角色，每一个权限组对应一个角色
对于不同权限级别的用户，可以将对应的角色授权用户，相当于批量授予用户需要的权限，而不需要逐个授予权限

注意
角色不属于任何用户
角色不能登录数据库和执行SQL语句操作
角色在系统中必须是唯一的

1.5、角色的创建

语法
CREATE ROLE
注意事项
执行该语句的用户需要有CREATE ROLE系统权限
角色名不能与数据库中已有的用户名和角色名重名，否则会提示错误信息

系统预置角色
DBA 系统管理员角色
RESOURCE 创建基础对象的角色
CONNECT 连接角色
STATISTICS 统计角色

创建角色的语法格式
CREATE ROLE role_name;
主要参数说明
role_name:创建的角色名
示例
创建角色teacher
CREATE ROLE teacher;

1.6、角色的删除

语法
DROP ROLE
注意事项
执行该语句的用户需要有DROP ANY ROLE系统权限，或者是该角色的创建者，或者被授予该角色并携带WITH ADMIN OPTION属性
如果角色不存在，会提示错误信息
角色被删除时，将该角色具有的权限从被授予该角色的用户或者其他角色上回收

删除角色的语法格式
DROP ROLE role_name;
主要参数说明
role_name:要删除的角色名
示例：
删除角色teacher
DROP ROLE teacher;

1.7、用户

数据库用户的主要用途
连接数据库
访问数据库对象和执行SQL语句

默认情况下，GaussDB 100的用户可以分为三类
系统管理员
安全管理员
普通用户

注意
在连接数据库时，必须使用一个已经存在的数据库用户
一个用户可以建立多个连接

1.8、用户的创建

语法: CREATE USER
注意事项：
执行该语句的用户需要有CREATE USER系统权限
用户名不能和数据库中已存在的用户名和角色名重复
创建用户时，需要指定用户名和密码
SYS用户和PUBLIC用户不允许创建，是系统预置用户

创建用户的常用语法格式
CREATE USER user_name IDENTIFIED BY password;
设置用户名时，需遵循如下规范
用户名不允许包含以下特殊字符
分号(;)、竖线(|)、反引号(``)、美元符($)、位运算(&)、大于号(>)、小于号(<)、双引号("")、单引号('')、感叹号(!)、空格和版权符号(©)
用双引号或者反引号括起来也不可以
若用户名包含以上禁止特殊字符以外的其他特殊字符时。必须用双引号("")或者反引号(``)括起来

设置用户名密码时，需遵循如下规范
密码长度必须大于等于8个字符，小于等于64个字符
若密码不以单引号括起来，则密码首字符必须是字母、#号或下划线
密码不能与用户名或者用户名的倒序相同(校验时，不区分大小写)
密码只能包含如下4中类型，并且至少选取其中3种类型的组合
  字母、小写字母、大写字母、空格或特殊字符(GaussDB 100支持的特殊字符列表，参考特殊字符表)
创建密码时，若密码中包含_#$以外的特殊字符或者空格，则密码必须用单引号括起来
若创建的密码中包含特殊字符$，通过zsql连接数据库时密码在单引号里面$不需要使用\转义字符转义，密码不在单引号里面$需要使用\转义字符转义，否则会登录失败

密码可用的特殊字符表

示例
创建用户smith，密码为database_123
create user smith identified by database_123;

1.9、用户的修改

语法
ALTER USER
注意事项
执行该语句的用户需要有ALTER USER系统权限
如果执行的用户不存在，会提示错误信息

应用场景
修改用户密码
手动锁定用户或给用户解锁
给用户指定配置档案
定义用户默认表空间

修改用户密码的语法格式
ALTER USER user_name IDENTIFIED BY new_password;
主要参数说明
user_name 要修改的用户名
new_password 新的用户密码

示例
将用户smith的密码修改为database_456
ALTER USER smith IDENTIFIED BY database_456;

1.10、用户的删除

语法
DROP USER
注意事项
执行该语句的使用需要有DROP USER系统权限
不能删除当前正在与数据库相连的用户
SYS用户和PUBLIC用户不允许删除，是系统预置用户
请谨慎使用删除用户操作，即使删除过程中断，已经删除的对象也无法恢复，建议先删除该用户创建的数据库对象，再删除用户

删除用户的语法格式
DROP user [ if exists ] user_name [ CASCADE ];
主要参数说明：
user_name：要删除的用户名
if exists：要删除的用户是否存在
不指定if exists选项时，如果要删除的用户不存在，会提示错误信息
指定if exists选项时，如果要删除的用户不存在，直接返回执行成功，用户存在则删除该用户

主要参数说明
CASCADE
不指定CASCADE选项时，如果要删除的用户中还有数据库对象没有删除，会提示错误信息
如果指定CASCADE选项，删除该用户时，会同步强制删除属于该用户的数据库对象

示例
删除用户smith，
drop user if exists smith cascade;

1.11、用户、角色和权限关系

用户、角色和权限的关系
用户可定义角色，角色为多个权限的集合
角色赋予用户或其他角色，则用户或其他角色拥有了此角色的所有权限
角色的权限可以继承

2、授权

2.1、授权

授权是什么
将权限或角色授予给用户或其他角色
授权的语法
GRANT

2.2、系统权限的授予

常用的语法格式
GRANT system_privilege_name TO grantee [ WITH ADMIN OPTION ];
主要参数说明
system_privilege_name：系统权限名
grantee：被授予的用户或角色
WITH ADMIN OPTION：被授予的用户或橘色可将所获得的系统权限再次授予其他用户或角色

如果是授予系统权限，执行该语句用户需要满足如下条件之一
已被授予该系统权限，并有WITH ADMIN OPTION属性
已被授予GRANT ANY PRIVILEGE系统权限

示例
向用户smith授予CREATE SESSION系统权限，smith还可将此权限授予给其他用户或角色
grant create session to smith with admin option;

2.3、对象权限的授予

常用语法格式：
GRANT object_privilege_name ON object_name TO grantee [ WITH GRANT OPTION ];
主要参数说明
object_privilege_name：对象权限么
object_name：对象名
grantee：被授予对象权限的用户或角色
WITH GRANT OPTION：被授予的用户可将获得的对象权限再次授予其它用户或角色

如果是授予对象权限，执行该语句用户需要满足如下条件之一
已被授权该对象权限，并有WITH GRANT OPTION属性
已被授予GRANT ANY OBJECT PRIVILEGE系统权限
是该对象的创建者

示例
向用户smith授予表table1的SELECT对象权限，smith还可将此对象权限授予给其他用户或角色
GRANT SELECT ON table1 TO smith WITH GRANT OPTION;

注意
系统权限、角色和对象权限的授予都要遵循最小化使用原则

2.4、角色的授予

常用的语法格式
GRANT role_name TO grantee [ WITH ADMIN OPTION ]

主要参数说明
role_name：角色名
grantee：被授予的用户或角色
WITH ADMIN OPTION：被授予的用户或角色可将所获得的角色再次授予其他用户或角色

如果是授予角色，执行该语句用户需要满足如下条件之一
已授予该角色，并有WITH ADMIN OPTION属性
已被授予GRANT ANY ROLE系统权限
是该角色的创建者
示例
向用户smith授予teacher角色，smith还可将此角色授予给其他用户或角色
GRANT teacher TO smith WITH ADMIN OPTION;

3、权限回收

3.1、权限回收

权限回收是什么
将权限或角色从授权者回收

权限回收的语句
REVOKE

3.2、角色回收

常用的语法格式
REVOKE role_name FROM revokee;
如果回收角色，执行REVOKE操作的用户需要满足如下条件之一
已被授予该角色，且授权时携带WITH ADMIN OPTION属性
已被授予GRANT ANY ROLE系统权限
是被回收角色的创建者

示例
回收用户smith的teacher角色
REVOKE teacher FROM smith;

3.3、系统权限的回收

常用的语法格式
REVOKE system_privilege_name FROM revokee;
如果回收系统权限，执行REVOKE操作的用户需要满足如下条件之一
已被授予要被收回的该系统权限，且授权时携带WITH ADMIN OPTION属性
已被授予GRANT ANY PRIVILEGE系统权限

示例
回收用户smith的CREATE SESSION系统权限
REVOKE CREATE SESSION FROM smith;

3.4、对象权限的回收

对象权限的回收1
常用的语法格式
REVOKE object_privilege_name ON object_name from revokee;
主要参数说明
object_privilege_name：对象权限名
object_name：对象名
revokee：被回收对象权限的用户或角色

对象权限的回收2
如果回收对象权限，执行REVOKE操作的用户需要满足如下条件之一
已被授权该对象权限，且授权时携带WITH GRANT OPTION属性
是回收该对象的创建者
示例：
回收用户smith在表table1上的SELECT对象权限
REVOKE SELECT on table1 from smith;
注意：
系统权限、角色和对象权限在不使用时需及时回收

3.5、用户、角色和权限的示例

用户、角色和权限的示例
创建用户smith，密码为database_123
create user smith identified by database_123;
创建角色manager
create role manager;
授予角色manager系统权限
grant create session,create user to manager;
授予角色manager对象权限
grant select,insert on staffs to manager;
将角色manager授予用户smith
grant manager to smith;