查询报表：只读用户配置

数据库中的只读用户仅具有读取信息和执行查询报表的权限。此配置可确保安全查询，并阻止访问 application 数据库中的敏感数据。

所需角色：组织管理员、SDAdmin、具有 创建查询报表 权限的用户

配置只读用户需要具备 SQL 专业知识。

在 Postgres 中配置只读用户。
在 Microsoft SQL 数据库中配置只读用户。
排查查询报表失败问题

在 Postgres 中配置只读用户

以具有创建角色/用户权限的用户身份连接到 application 数据库。

在脚本中，请将 <> 内的值替换为您所需的内容。

创建一个具有登录凭据的用户。

CREATE USER <username> WITH PASSWORD '<password>';

向该用户授予所需权限：
- 数据库

GRANT CONNECT ON DATABASE <servicedesk_database> TO <username>;

架构

GRANT USAGE ON SCHEMA public TO <username>;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO <username>;

若要限制对某些表的访问，请撤销 SELECT 访问权限。请参阅脚本。

--示例查询

REVOKE SELECT ON <tableName> TABLE FROM <username>;

REVOKE SELECT ON aaapassword TABLE FROM readonlyuser;

若要限制 postgres 函数，请使用数据控制语言 (DCL) 命令从 public 角色中移除 execute 权限。
使用此脚本创建一个 revoke 函数。创建 revoke 函数后，运行以下查询以移除 public 用户的 execute 权限。

SELECT sdp_revoke_execute_on_functions('public');

运行以下查询，从只读用户中撤销函数的 execute 权限。

SELECT sdp_revoke_execute_on_functions('<只读用户名>');

由于 revoke 权限只能针对 public 角色提供，因此您可以为用户逐个恢复 public 角色权限。

若要为其他用户恢复 revoke 权限，请为除 public 和只读用户之外的所有用户授予 execute 权限。使用此脚本创建 grant 函数。

执行以下查询以获取用户列表。

SELECT usename FROM pg_catalog.pg_user;

截图中提到的用户仅供参考。

运行以下查询，将 public 函数权限授予其他用户。请将 <username> 替换为 public 或只读用户以外的用户。

SELECT sdp_grant_execute_on_functions('<username>');

若要验证限制是否已生效，请以新创建的只读用户连接数据库，并确认受限查询会被拒绝。

SELECT pg_sleep(10);

默认情况下，某些单词和表会被限制查询。若要获取这些关键字，请使用以下脚本：

SELECT Distinct(table_name) as "Name" FROM information_schema.tables WHERE lower(table_name) NOT IN (SELECT lower(table_name) from TableDetails) ORDER BY table_name;\

在 Microsoft SQL 数据库中配置只读用户

在 Microsoft SQL 中，当数据库通过 Windows 身份验证链接到 application 服务器时，无法配置只读用户。

以具有 CREATE USER 或 LOGIN 角色的用户身份连接到 Microsoft SQL 数据库。
连接到带有 application 数据库的查询控制台，并创建一个具有登录凭据的用户。

CREATE LOGIN <username> WITH PASSWORD '<password>';

CREATE USER <username> FOR LOGIN <username>;

要实现所需的只读权限，请使用以下命令。
除 SELECT 外，限制所有其他命令。

DENY INSERT, UPDATE, DELETE ON SCHEMA :: [dbo] TO <userName>;

以下命令将撤销对所有表的 SELECT 权限。

REVOKE SELECT ON SCHEMA :: [dbo] FROM <userName>;

Microsoft SQL 受限表访问

若要仅提供对特定表的访问权限，请执行此处的脚本。

忽略有关未找到表或对象的问题。

若要验证是否已实现限制，请以新创建的用户身份登录数据库，运行以下查询，并确保无法查询受限表。\

--示例查询

SELECT * FROM aaapassword;

如果查询结果显示 SQL 表限制消息，则表示表限制已生效。
- 如果表限制未生效，请执行以下命令后再次检查。有关受限表列表，请参阅此处。

REVOKE SELECT ON [SchemaName].[TableName] FROM [UserName];

--或

DENY SELECT ON [SchemaName].[TableName] FROM [UserName];

检查是否允许查询所有其他表。

--示例查询

SELECT * FROM reportmoduleconfiguration;

检查是否返回了表中的行。如果没有，请执行此脚本，然后再次检查。

若要限制 SQL 函数，请执行以下查询。

DENY EXECUTE ON <FunctionName> FROM <Read-Only User Name>;

对于类似 xp_cmdshel 的函数，请执行以下命令

DENY EXECUTE ON xp_cmdshell FROM rouser

默认情况下，某些表和词语无法查询。使用以下查询获取这些词语。

SELECT * FROM ( SELECT DISTINCT(name) as "Name" FROM sys.objects WHERE type_desc = 'SYSTEM_TABLE' OR type_desc = 'INTERNAL_TABLE' OR type_desc = 'USER_TABLE' OR type_desc = 'VIEW' UNION SELECT DISTINCT(name) FROM sys.tables UNION SELECT DISTINCT(name) FROM sysobjects WHERE sysobjects.xtype = 'U' OR sysobjects.xtype = 'S' UNION SELECT DISTINCT(name) FROM sys.system_views UNION SELECT DISTINCT(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES ) AS t WHERE t.Name NOT IN (SELECT TableDetails.TABLE_NAME FROM TableDetails );

获取密码的加密密钥

在命令提示符中转到 [ServiceDesk Plus Home]\bin。
执行 encrypt.bat 文件。
输入只读用户密码并按 Enter。
复制命令提示符中显示的密码加密密钥，并将其存储在安全位置。
转到 {SDP_Home}/ServiceDesk/conf。
打开 database_params.conf 文件。
在相应标签中配置用户名。例如，

rodatasource.username=<username>

获取加密后的密码密钥，并在相应标签中进行配置。例如，

rodatasource.password=<password>

更新数据库标志

创建用户后，将 application 连接到数据库并执行以下查询。这将允许只读用户创建安全查询报表，而不会从受限表中获取数据：UPDATE ReportModuleConfiguration SET PARAMVALUE = 'true' WHERE CATEGORY LIKE 'ROUser' AND PARAMETER LIKE 'Use_ROUser'。
重启 application 以使更改生效。

排查查询报表失败问题

1. 捆绑版 Postgres 中的备份失败。

如果因权限问题导致备份失败，请使用此脚本为 application 数据库用户（sdpadmin）恢复必要的权限。

2. 在 Microsoft SQL 数据库中，查询报表失败并显示错误消息“Restricted table(s) found in query.”

确保查询未访问受限表。
重命名任何与受限关键字匹配的列别名。
修改查询中包含受限关键字的任何值。
按需更改或修改查询中存在的任何关键字。