Query Report: Read-Only User Configuration

A read-only user in a database has exclusive permissions to read information and execute query reports. This configuration ensures secure querying and blocks access to sensitive data within the application database.

Role Required: Organization Admin, SDAdmin, Users with Create Query Report permission

SQL expertise is required to configure read-only users.

Contents

Configure a Read-Only User in Postgres.
Configure a Read-Only User in the Microsoft SQL database .
Troubleshoot Query Report Failures

Configure a Read-Only User in Postgres

Connect to the application database as a user with permission to create a role/user.

In the scripts, replace values within <> to your preference.

Create a user with login credentials.

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

Grant the necessary privileges to the user:
- Database

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

Schema

GRANT USAGE ON SCHEMA public TO <username>;

Tables

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

To restrict access to certain tables, revoke SELECT access. Refer to the script .

--Sample Query

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

REVOKE SELECT ON aaapassword TABLE FROM readonlyuser;

To restrict postgres functions, remove the execute permission from the public role using Data Control Language (DCL) commands.
Use this script to create a revoke function. After a revoke function is created, run the following query to remove execute permission for public users.

SELECT sdp_revoke_execute_on_functions('public');

Run the following query to revoke execute permissions for functions from a read-only user.

SELECT sdp_revoke_execute_on_functions('<read-only user name>');

Since revoke privileges can only be provided for public roles, you can restore public role privileges for users individually.

To restore revoke privileges for other users, provide execute permissions to all users except public and read-only users. Use this script to create a grant function.

Execute the following query to fetch users.

SELECT usename FROM pg_catalog.pg_user;

Users mentioned in the screenshot are only for reference.

Run the following query to grant public function privileges to other users. Replace <username> with users other than public or read-only users.

SELECT sdp_grant_execute_on_functions('<username>');

To verify if the restriction has been met, connect to the database as the newly created read-only user and ensure restricted queries are denied.

SELECT pg_sleep(10);

By default, certain words and tables are restricted to query. To fetch those keywords, use the following script:

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;

Configure a Read-Only User in Microsoft SQL database

In Microsoft SQL, you cannot configure a read-only user when the database is linked to the application server using Windows authentication.

Connect to the Microsoft SQL database as a user having the CREATE USER or LOGIN role.
Connect to the query console with the application database, and create a user with login credentials.

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

CREATE USER <username> FOR LOGIN <username>;

To achieve the required read-only privileges, use the following commands.
Restrict all other commands except SELECT.

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

The following command revokes the SELECT privileges for all tables.

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

Microsoft SQL Restricted Table Access

To provide access to only specific tables, execute the script from here.

Ignore issues regarding a table or object not being found.

To verify if the restriction has been met, log in to the database as the newly created user, run the following queries, and ensure the restricted tables are not queried.

--Sample Query

SELECT * FROM aaapassword;

If the query result shows a SQL table restriction message, the restriction of tables is achieved.
- If restriction of tables is not achieved. Follow these commands and check again. Refer here for the restricted table list.

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

--or

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

Check whether all other tables are allowed to query.

--Sample Query

SELECT * FROM reportmoduleconfiguration;

Check whether the rows of the table are returned. If not, execute this script and check again.

To restrict SQL functions, execute the following query.

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

For function like xp_cmdshel, execute the following

DENY EXECUTE ON xp_cmdshell FROM rouser

By default, some tables and words cannot be queried. Fetch those words using the following query.

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 );

Obtain Encrypted Key of the Password

Go to [ServiceDesk Plus Home]bin in the command prompt.
Execute the encrypt.bat file.
Type the Read-Only User password and press Enter.
Copy the password encryption key displayed in the command prompt and store it in a secure location.
Go to {SDP_Home}/ServiceDesk/conf.
Open the database_params.conf file.
Configure the username in the relevant tag. For example,

rodatasource.username=<username>

Fetch the encrypted password key and configure it in the relevant tag. For example,

rodatasource.password=<password>

Update Database Flag

After the user is created, connect the application to the database and execute the following query. This will allow the Read-Only User to create secure query reports that do not fetch data from the restricted tables: UPDATE ReportModuleConfiguration SET PARAMVALUE = 'true' WHERE CATEGORY LIKE 'ROUser' AND PARAMETER LIKE 'Use_ROUser'.
Restart the application for the changes to take effect.

Troubleshoot Query Report Failures

1. Backup failures in bundled Postgres.

If a backup failure occurs due to permission issues, use this script to restore necessary permissions for the application database users (sdpadmin).

2. In Microsoft SQL database, query report fails with the error message "Restricted table(s) found in query."

Ensure that the query is not accessing restricted tables.
Rename any column aliases that match restricted keywords.
Modify any values in the query that contain restricted keywords.
Change or modify any keywords present in the query as required.