主页 / dba / 问题 / 344695
Accepted
folow
Asked: 2025-01-14 18:55:01 +0800 CST

如何在模式上分配 SELECT 但如何在模式中的特定表上分配 DENY？

  • 772

在 Linux 上的 Db2 v11.5 上，我有几个数据库，其中包含 MYSCHEMA 模式中的数百个表。

要求

a) 我需要为 MYSCHEMA 模式中的所有表分配 SELECT 权限，但不包括用户不得访问的特定表 MYSCHEMA.NO_ACCESS。

b) 如果在 MYSCHEMA 模式中创建了新表，我需要手动在新表上添加 GRANT SELECT。我希望在创建新表时避免任何类型的安全管理。

c) 当我们从审计公司进行审计时，需要简单地提供一些用户拥有和不拥有哪些访问权限的信息。拥有数百个授权很难向用户表明他们确实只拥有他们需要的访问权限。

当前解决方案

目前，我们授予除表 MYCHEMA.NO_ACCESS 之外的所有表的选择权限，如下所示：

GRANT SELECT ON TABLE MYSCHEMA.TAB1 TO GROUP GROUP1
GRANT SELECT ON TABLE MYSCHEMA.TAB2 TO GROUP GROUP1
...
GRANT SELECT ON TABLE MYSCHEMA.TAB5000 TO GROUP GROUP1 
(make sure there is no MYSCHEMA.NO_ACCESS on grant list)

上述方法有局限性，当在 MYSCHEMA 模式中创建新表时，我需要在新表上设置 GRANT SELECT。

另外，在审计时由于有大量的权限，很难证明用户确实只拥有他们应该拥有的权限。

如果可能的话，采取新方法

Db2 v11.5 对模式具有 GRANT SELECTIN 权限。

有没有类似的东西：

GRANT SELECTIN ON SCHEMA MYSCHEMA TO GROUP GRUP1
DENY SELECT ON TABLE MYSCHEMA.NO_ACCESS TO GROUP GROUP1

拒绝比授予更重要。

或者还有其他方法吗？

permissions

2 个回答

  1. Best Answer

    您可以在此表上创建行权限。

    您必须根据引发异常的标准函数创建一个安全函数：

    CREATE OR REPLACE FUNCTION RAISE_ERROR_MY(P_SQLSTATE CHAR(5), P_MSG VARCHAR(70))
RETURNS INT
CONTAINS SQL
DETERMINISTIC
NO EXTERNAL ACTION
SECURED
RETURN RAISE_ERROR(P_SQLSTATE, P_MSG)::INT

    使用此功能：

    CREATE PERMISSION DENY_ALL_ON_NO_ACCESS_TO_GROUP1 ON MYSCHEMA.NO_ACCESS
FOR ROWS WHERE
CASE 
  WHEN VERIFY_GROUP_FOR_USER(SESSION_USER, 'GROUP1') = 0 THEN 1
  ELSE RAISE_ERROR_MY('75000', 'Not allowed')
END = 1
ENFORCED FOR ALL ACCESS
ENABLE;

ALTER TABLE MYSCHEMA.NO_ACCESS ACTIVATE ROW ACCESS CONTROL;

    如果当前用户是 的成员GROUP1，那么 DB2 会在尝试访问此表时引发 SQLCODE = -438 和 SQLSTATE = '75000' 异常。表达式计算结果为 TRUE，允许访问非 成员的相应表GROUP1。
    在这种“错误”的访问尝试中，您不会得到标准 SQLCODE = -551，但我认为这并不重要……

    更新
    此外，您甚至可以让 DB2 在这种访问中引发 SQLCODE = -551 的异常。
    您需要SYSIBMINTERNAL.SQLEML_RAISE_ERROR为此使用未记录的例程。
    将其包装到标量函数中：

    CREATE OR REPLACE FUNCTION RAISE_ERROR_MY(P_SQLCODE INT, P_MSG VARCHAR(4000))
RETURNS INT
CONTAINS SQL
DETERMINISTIC
NO EXTERNAL ACTION
SECURED
BEGIN --ATOMIC
  CALL SYSIBMINTERNAL.SQLEML_RAISE_ERROR(P_SQLCODE, P_MSG);
  RETURN 0;
END

    并在行权限表达式中使用它，如下所示：

    RAISE_ERROR_MY(-551, USER || x'ff' || 'ANY ACCESS' || x'ff' || 'MYSCHEMA.NO_ACCESS')

    然后，您会收到标准 SQLCODE/SQLSTATE 的异常。
    请注意，您必须使用编译函数RAISE_ERROR_MY（不带 ATOMIC 子句）。无论如何，它都会CASE在行权限表达式中内部调用，否则会引发意外异常。

    • 2

  2. 您当然是对的，只有被授予的权限才可以撤销。虽然这不是您问题的确切答案，但您可以使用类似这样的解决方法（我添加了撤销和处理程序，用于处理用户没有选择身份验证的情况）：

    CREATE OR REPLACE PROCEDURE GRANT_ALL_BUT(s VARCHAR(128), t VARCHAR(128), u VARCHAR(128))
LANGUAGE SQL
DYNAMIC RESULT SETS 0
BEGIN
    DECLARE CONTINUE HANDLER FOR SQLSTATE '42504' BEGIN END;
    FOR v AS c1 CURSOR FOR SELECT TABSCHEMA, TABNAME FROM SYSCAT.TABLES WHERE TABSCHEMA = s AND TABNAME <> t
    DO
        EXECUTE IMMEDIATE 'GRANT SELECT ON TABLE ' || v.TABSCHEMA || '.' || v.TABNAME || ' TO USER ' || u;
    END FOR;
    EXECUTE IMMEDIATE 'REVOKE SELECT ON TABLE ' || s || '.' || t || ' FROM USER ' || u;
END @

db2 "create table tmp.t1 (x int)"
db2 "create table tmp.t2 (x int)"

db2 "call GRANT_ALL_BUT('TMP','T1','DB2FENC1')"

    作为 db2fenc1：

    db2 "select * from tmp.t2"

X          
-----------

  0 record(s) selected.

db2 "select * from tmp.t1"
SQL0551N  The statement failed because the authorization ID does not have the 
required authorization or privilege to perform the operation.  
Authorization ID: "DB2FENC1".  Operation: "SELECT". Object: "TMP.T1".  SQLSTATE=42501

    我现在明白那是一个群组，而不是一个用户，但这留给读者作为练习；-)

    如果这不合适，您可能需要查看行访问控制，用户（在我的情况下是 db2fenc1）可以从表中选择，但看不到任何行。您可以在行和列访问控制 (RCAC) 概述中找到更多信息

    这只是一个愚蠢的例子：

    DROP TABLE TMP.t1;
DROP TABLE TMP.t2;

CREATE TABLE tmp.t1 (x int);
CREATE TABLE tmp.t2 (x int);

insert into tmp.t1 values (1),(2);
insert into tmp.t2 values (2),(3);

grant selectin on schema tmp to user DB2FENC1;

CREATE PERMISSION ROW_ACCESS ON tmp.t1
FOR ROWS WHERE (SESSION_USER <> 'DB2FENC1')
enforced for all access
enable;

ALTER TABLE tmp.t1 ACTIVATE ROW ACCESS CONTROL;

    db2fenc1 看到的内容

    [db2fenc1@db2host ~]$ db2 "select * from tmp.t2"

X          
-----------
          2
          3

  2 record(s) selected.

[db2fenc1@db2host ~]$ db2 "select * from tmp.t1"

X          
-----------

  0 record(s) selected.
    • 1

相关问题