如何跟踪架构中特定对象上所有授权的历史记录？

Question

我每周创建一个表( ALTERYX覆盖)，并为角色分配SELECT授权。奇怪的是，在过去的6个月里，业务用户已经打过电话( 2-3次)，说SELECT access丢失了。如何跟踪该对象上的特定授权的历史，以便我能够了解哪个进程以及何时被丢弃。

顺便说一句，我知道如何跟踪一个授权，它清楚地显示在创建表之后，该对象上的SELECT是可用的(只是最近一次)

EDP_DB.INFORMATION_SCHEMA.OBJECT_PRIVILEGES创建的

选择设保人、受让人、OBJECT_CATALOG、OBJECT_SCHEMA、OBJECT_NAME、OBJECT_TYPE、PRIVILEGE_TYPE、IS_GRANTABLE

Answer 1

您可以查询QUERY_HISTORY视图，找出是什么改变了该角色在该表上的特权。您必须对关键字(如表名、动词类型(alter、revoke)和角色名称)使用自由文本匹配。你可以这样做：

select  * 
from    "SNOWFLAKE"."ACCOUNT_USAGE"."QUERY_HISTORY"
where   START_TIME > current_date - 180
    and  (QUERY_TEXT ilike '%revoke%' or QUERY_TEXT ilike '%alter%')
    and  QUERY_TEXT ilike '%table <TABLE_NAME>%'
;

这将在过去180天内进行搜索。根据帐户上运行的语句数量，可能需要一段时间才能完成。如果您知道最近发生了这种情况，您可以更改日期范围。

还检查正在删除和创建的表或表上的“创建或替换”语句。

还可以使用QUERY_TYPE列对特定类型进行筛选，如ALTER、GRANT、CREATE等，并筛选出其他类型。

Answer 2

一个简单的支点，显示被priv类型打破的角色:-)

select
*
from
(
    select
        object_name,
        PRIVILEGE_TYPE,
        listagg(distinct grantee, ',') grantee,
        count(distinct grantee) grantees
    from
        SKYLAKE_DEV_DB.INFORMATION_SCHEMA.OBJECT_PRIVILEGES
    group by
        1,
        2
) a pivot (
    sum(grantees) for PRIVILEGE_TYPE in (
        'CREATE FUNCTION',
        'UPDATE',
        'CREATE EXTERNAL TABLE',
        'CREATE MASKING POLICY',
        'TRUNCATE',
        'CREATE TEMPORARY TABLE',
        'INSERT',
        'CREATE SCHEMA',
        'CREATE PIPE',
        'SELECT',
        'CREATE FILE FORMAT',
        'OWNERSHIP',
        'REBUILD',
        'ADD SEARCH OPTIMIZATION',
        'DELETE',
        'MODIFY',
        'CREATE STREAM',
        'CREATE SEQUENCE',
        'CREATE TABLE',
        'CREATE STAGE',
        'CREATE MATERIALIZED VIEW',
        'REFERENCES',
        'CREATE TASK',
        'USAGE',
        'MONITOR',
        'CREATE VIEW',
        'CREATE PROCEDURE'
    )
)