数据库必知必会系列：数据库权限管理与访问控制

作者：禅与计算机程序设计艺术

1.简介

数据库权限管理与访问控制（Database Access Control），即管理者能够针对特定的用户、组或其他实体，分配有限或完全权限来控制对数据库资源的访问权限，确保数据的安全、完整和可用。权限管理通过限制数据访问、授权用户执行特定操作、审核数据库活动等方式来保证数据的安全性。因此，数据库权限管理可以帮助企业实现合规性、合法性、可控性。 数据库权限管理包括以下关键要素：

• 用户管理：创建、删除、修改数据库用户账号，并对用户赋予相应的权限，管理用户访问数据库的身份认证；
• 角色管理：创建、删除、修改数据库角色，并将用户加入到角色中，定义角色的权限集；
• 数据访问权限控制：设定数据访问策略，控制用户在数据库内的数据读、写和查询权限；
• 事务级安全机制：提升数据库系统的可靠性、稳定性和安全性，通过数据库的事务处理和数据备份功能来保证数据的完整性、一致性和安全性；
• 智能审计与风险防护：集成到数据库系统中，实时监测数据库活动，发现异常行为并进行违规检测和预警；
• 配置中心：通过配置中心实现权限管理的自动化，简化管理和部署工作，加快系统整体运行速度； 目前，数据库权限管理工具已经非常完善了，如Oracle Database Audit Vault，MySQL Grant Wizard等，但各个厂商的工具之间也存在差异。本文以Oracle 数据库中的Oracle Database Vault 为例，阐述数据库权限管理的原理和方法。

  2.基本概念术语说明

  2.1 权限

  数据库权限主要分为如下四种类型：
• SELECT：允许用户查询数据库对象（表、视图、存储过程、函数）中的数据；
• INSERT：允许用户向数据库对象中插入记录；
• UPDATE：允许用户更新数据库对象的现有记录；
• DELETE：允许用户从数据库对象中删除记录；
• CREATE：允许用户在数据库对象上创建新对象；
• ALTER：允许用户更改数据库对象的结构、名称或其他元数据信息；
• DROP：允许用户删除数据库对象；
• GRANT OPTION：允许被授予权限的用户同时获得自己权限的同意；
• REFERENCES：允许用户创建外键约束；
• INDEX：允许用户创建索引；
• EXECUTE：允许用户执行存储过程、函数；
• READ WRITE：赋予用户对数据库对象的所有权限；
• READ ONLY：只读模式，赋予用户查看和查询数据库对象但不能进行任何变更。

  2.2 实体

  实体是指用于标识数据库对象、用户或组的命名空间。实体可以是表空间、用户名或角色名。

  2.3 访问控制列表（ACL）

  访问控制列表（ACL）是一个允许或拒绝特定实体访问特定对象上的特定权限的集合。一个ACL由多个条目（ACEs）组成，每个条目定义了一个实体的特权（如SELECT）以及该实体所属的角色或组，以及它们被授予的访问级别（如READ ONLY）。每条ACL都有一个唯一的顺序号，决定它的优先级，最高的条目优先级最高。

  2.4 管理员

  管理员是具有所有权限的特权用户。数据库管理员是管理数据库及其组件的专门用户，负责管理整个数据库，包括物理服务器、网络、存储设备、系统和应用程序等，而不仅仅是数据库。一般情况下，数据库管理员由DBA（数据库管理员）来执行。

  2.5 用户

  用户是连接到数据库并尝试存取数据的一方，他们具有数据库中定义的权限。用户通常由个人或者组织的成员组成，拥有登录数据库的用户名和密码。用户可以通过不同的身份验证方式（如密码验证、基于令牌的验证或Kerberos）来认证自己的身份。

  2.6 角色

  角色是具有相同权限集的用户集合，它使得多个用户具有统一的权限集。角色不需要分配具体的权限，而是分配给它所包含的用户。角色是权限的集合，它允许管理者指定用户具有的各种权限，而不是单独设置。角色还可以是多层次的嵌套关系，而嵌套的子角色继承父级角色的权限。例如，一个父角色可以包含两个子角色，第一个子角色拥有部分权限，第二个子角色拥有另一些权限，但只能访问某些指定的表。

  2.7 临时对象

  临时对象是在执行事务期间使用的对象，其生命周期比数据表短，并在事务结束后自动删除。临时对象可以是表、视图、游标或序列。临时对象可以帮助解决事务性问题，比如UPDATE语句失败导致的错误回滚，因为这个对象只在当前事务有效。临时对象也可以在逻辑上与实际数据相对应。

  2.8 对象路径

  对象路径是表示数据库对象的字符串，其中包含数据库名、对象类型、对象名称。例如，HR.EMPLOYEES代表HR数据库中的employees表。

  2.9 源码

  源码是用来定义对象属性和数据类型的数据库对象，由数据库开发人员编写并编译后生成。每种语言都有自己的源码格式，例如SQL Developer、PL/SQL Developer或DBMS_METADATA.CREATE_SOURCE命令。

  2.10 DDL

  DDL（数据定义语言）是用来定义数据库对象、关系型数据库结构、过程、触发器、函数、视图、包和序列的语言。它包括CREATE、ALTER、DROP、TRUNCATE、COMMENT等子句，用来创建或改变对象的结构，或删除对象。

  2.11 DML

  DML（数据操纵语言）是用来检索、插入、删除或更新数据库中的数据的语言。它包括SELECT、INSERT、DELETE、UPDATE和MERGE等子句。

  3.核心算法原理和具体操作步骤

  3.1 创建数据库角色

  首先，创建一个普通用户。然后，以sysdba身份登陆，使用下面的CREATE ROLE命令创建一个新的数据库角色：

  CREATE ROLE role_name;

  接着，授予用户权限，可以使用GRANT命令，如：

  GRANT create table to user_or_role; --允许用户创建新表格
  GRANT select on mytable to user_or_role; --允许用户读取mytable表格中的数据
  GRANT execute on stored_procedure to user_or_role; --允许用户执行stored_procedure存储过程

  也可以一次性授予用户全部权限：

  GRANT all privileges to user_or_role; --赋予用户全部权限

  3.2 修改数据库角色

  如果需要修改已有的数据库角色，可以使用RENAME TO命令：

  RENAME role_name TO new_role_name;

  如果要添加或删除某个角色的权限，则可以使用GRANT或REVOKE命令。例如：

  GRANT insert ON employees TO hradmin; --允许hradmin用户写入employees表
  GRANT delete ON employees WHERE dept = 'Marketing' TO marketingstaff; --允许marketingstaff用户删除dept列为"Marketing"的行

  3.3 删除数据库角色

  如果需要删除某个数据库角色，可以使用下面的DROP ROLE命令：

  DROP ROLE role_name;

  如果要永久删除角色和所有关联的ACL，那么就需要使用CASCADE关键字：

  DROP ROLE role_name CASCADE;

  如果只是删除角色下的某个ACL，可以使用REVOKE ALL PRIVILEGES FROM 命令：

  REVOKE ALL PRIVILEGES FROM role_name;

  3.4 设置用户密码

  如果想设置用户的密码，可以使用如下SET PASSWORD命令：

  ALTER USER user_name IDENTIFIED BY password;

  如果想让用户用其它口令登录数据库，可以使用如下ALTER AUTHORIZATION命令：

  ALTER USER user_name IDENTIFIED WITH auth_method SET identified_value;

  3.5 检查用户的权限

  如果需要检查某个用户是否有某项权限，可以使用如下CHECK PRIVILEGE命令：

  CHECK PRIVILEGE FOR user_name AT object_path;

  如果object_path为空，默认检查该用户对数据库中任意对象是否有权限。否则，只检查该用户是否有权限在指定的对象上做特定操作。

  3.6 查看数据库角色的信息

  如果需要查看数据库角色的相关信息，可以使用如下SHOW ROLE和DESCRIBE ROLE命令：

  SHOW ROLES LIKE pattern; --搜索数据库角色的名字，支持模糊查询
  DESCRIBE ROLE role_name; --显示数据库角色的详细信息

  3.7 管理用户账号

  创建用户账号并给予相应的权限，可以使用如下CREATE USER和GRANT命令：

  CREATE USER user_name [identified BY {password | EXTERNAL}]
        [DEFAULT TABLESPACE tablespace]
        [TEMPORARY | TEMPTABLESPACE tablespace];
  GRANT {permission|ALL PRIVILEGES} ON database_objects TO user_name [WITH ADMIN OPTION];

  例如，创建一个名为user1的用户并设置密码： ，赋予权限：

  CREATE USER user1 IDENTIFIED BY mypassword;
  GRANT SELECT ON HR.* TO user1;
  GRANT CONNECT, RESOURCE TO user1;

  如果要删除某个用户账号，可以使用如下DROP USER命令：

  DROP USER user_name;

  如果需要禁止或开启某个用户的登录，可以使用如下ALTER USER命令：

  ALTER USER user_name ACCOUNT LOCK | UNLOCK;

  3.8 管理用户组

  创建用户组并添加用户，可以使用如下CREATE GROUP和ADD MEMBER命令：

  CREATE GROUP group_name;
  ADD MEMBER user_name TO group_name;

  删除用户组，可以使用如下DROP GROUP命令：

  DROP GROUP group_name;

  3.9 使用自定义权限模板管理数据库

  如果需要自定义权限模板，可以使用如下CREATE PRIVILEGE TEMPLATE和GRANT ON TEMPLATE命令：

  CREATE PRIVILEGE TEMPLATE template_name AUTHORIZATION admin_role AS
    GRANT {permission|ALL PRIVILEGES} ON database_objects 
    TO role [,...] [WITH GRANT OPTION];
  GRANT ON OBJECT privilege_template_name@dblink TO role [,...];

  例如，创建一个自定义权限模板：

  CREATE PRIVILEGE TEMPLATE my_default_template AUTHORIZATION sa_role AS
    GRANT SELECT, UPDATE, INSERT, DELETE ON tables TO PUBLIC WITH GRANT OPTION;

  给予用户使用自定义权限模板的权限：

  GRANT ON OBJECT my_default_template@database TO user1;

  4.具体代码实例和解释说明

  4.1 创建数据库角色

  以oracle数据库为例，首先，创建一个普通用户：

  CREATE USER manager IDENTIFIED BY mypassword;

  然后，创建一个新的数据库角色：

  CREATE ROLE staff;

  授予用户权限：

  GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, 
    DROP ANY OBJECT, DBA TO staff;

  将manager用户添加到staff角色中：

  GRANT staff TO manager;

  最终效果：manager用户既拥有staff角色的权限，又属于staff角色。

  4.2 修改数据库角色

  修改staff角色的名称，使用RENAME TO命令：

  RENAME staff TO salespeople;

  再次查看数据库角色信息：

  DESCRIBE ROLE salespeople;

  4.3 删除数据库角色

  如果要永久删除salespeople角色和所有关联的ACL，可以使用如下DROP ROLE命令：

  DROP ROLE salespeople CASCADE;

如果只是删除角色下的某个ACL，可以使用REVOKE ALL PRIVILEGES FROM 命令：

REVOKE ALL PRIVILEGES FROM salespeople;

注意：永久删除角色前务必先查看是否有正在使用的用户和角色，或者有计划禁止所有权限的角色登录。

4.4 设置用户密码

ALTER USER user_name IDENTIFIED BY password;

示例：

ALTER USER joe IDENTIFIED BY MYPASSWORD;

4.5 检查用户权限

CHECK PRIVILEGE FOR joe AT DATABASE;

如果检查结果为FALSE，则说明joe无任何权限。如果为TRUE，则说明joe至少具备某个权限。

注意：此命令不会检查所有角色或所有系统权限，只会检查给定用户和数据库上已有对象的权限。

4.6 查看角色信息

--显示角色信息
SHOW ROLES; 

--根据pattern搜索角色信息
SHOW ROLES LIKE'sal%'; 

--显示角色的描述信息
DESCRIBE ROLE role_name; 

4.7 创建用户

CREATE USER user_name [identified BY {password | EXTERNAL}]
        [DEFAULT TABLESPACE tablespace]
        [TEMPORARY | TEMPTABLESPACE tablespace];

示例：

CREATE USER johndoe IDENTIFIED BY MYPASSWORD DEFAULT TABLESPACE users;

4.8 添加到用户组

--创建用户组
CREATE GROUP developers;

--添加成员
ADD MEMBER johndoe TO developers;

--添加多种成员
ADD MEMBER jane DOE TO developers;
ADD MEMBER bill BILL TO developers;

4.9 从用户组中删除用户

--删除John Doe成员
REMOVE MEMBER John Doe FROM developers;

--删除用户组
DROP GROUP developers;

4.10 在用户组中禁用登录

ALTER GROUP developers DISABLE;

4.11 启用用户登录

ALTER GROUP developers ENABLE;