Read-Only User has exclusive permissions to read information in the database, modify select data, and execute secure query reports to improve the security level of the application.

You can check the security level of the application under Global Settings or Admin.

Role Required: SDOrgAdmin/SDAdmin; Users with Create Query Report permissions.

Create Read-Only User

Refer to the following pointers to understand how to create a user depending on the database used: 

• Postgres Database: Execute the following queries to create a user in the database.

CREATE USER <username> WITH LOGIN PASSWORD <password>;
GRANT CONNECT ON DATABASE <databaseName> TO <username>;
GRANT USAGE ON SCHEMA public TO <username>;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO <username>;

• Grant relevant permissions for all tables.

• Revoke access for tables that contain private or irrelevant data using the following query: REVOKE SELECT ON <tableName> FROM <username>. For example: REVOKE SELECT ON passwordtable FROM rouser; 

• MSSQL Database: Execute the following query to create a user in the database.

CREATE LOGIN <username> WITH PASSWORD <password>;
CREATE USER <username> FOR LOGIN <username>;

• Execute query to block user from executing commands and revoke user's access to all tables in database. Access is granted only for specified tables.

DENY INSERT, UPDATE, DELETE ON SCHEMA :: [dbo] TO <userName>;
REVOKE SELECT ON SCHEMA :: [dbo] FROM <userName>;
declare commands cursor for
SELECT 'GRANT SELECT ON [dbo].' + QUOTENAME(t.TABLE_NAME) + ' TO <userName>;'
FROM TableDetails t
WHERE t.TABLE_NAME NOT LIKE 'AaaAccHttpSession' AND t.TABLE_NAME NOT LIKE 'AaaPassword' AND t.TABLE_NAME NOT LIKE 'RememberMeDetails' AND t.TABLE_NAME NOT LIKE 'ADSTFAUserEnrollment' AND t.TABLE_NAME NOT LIKE 'CustomFunctionDetails' AND t.TABLE_NAME NOT LIKE 'AdminAuditHistoryJson' AND t.TABLE_NAME NOT LIKE 'MobileAuthKey' AND t.TABLE_NAME NOT LIKE 'COMMONPASSWORD' AND t.TABLE_NAME NOT LIKE 'PasswordInfo' AND t.TABLE_NAME NOT LIKE 'PasswordResetLink' AND t.TABLE_NAME NOT LIKE 'BackupSchedule' AND t.TABLE_NAME NOT LIKE 'DBCredentialsAudit' AND t.TABLE_NAME NOT LIKE 'ChatJson' AND t.TABLE_NAME NOT LIKE 'ThrottleExceedingHistory' AND t.TABLE_NAME NOT LIKE 'UserAdditionalFields_multiselect' AND t.TABLE_NAME NOT LIKE 'UserAdditionalFields_history' AND t.TABLE_NAME NOT LIKE 'UserAdditionalFields_historydiff' AND t.TABLE_NAME NOT LIKE 'CustomPickListValues' AND t.TABLE_NAME NOT LIKE 'CustomModuleInstance' AND t.TABLE_NAME NOT LIKE 'CustomModuleInstanceImages' AND t.TABLE_NAME NOT LIKE 'CustomModuleDescription' AND t.TABLE_NAME NOT LIKE 'CustomModuleHistory' AND t.TABLE_NAME NOT LIKE 'CustomModuleHistoryDiff' AND t.TABLE_NAME NOT LIKE 'CM_Tasks' AND t.TABLE_NAME NOT LIKE 'CM_Comments' AND t.TABLE_NAME NOT LIKE 'CM_Attachments' AND t.TABLE_NAME NOT LIKE 'Custom_001' AND t.TABLE_NAME NOT LIKE 'Custom_MultiSelect_001';

declare @cmd varchar(max)

open commands
fetch next from commands into @cmd
while @@FETCH_STATUS=0
begin
exec(@cmd)
fetch next from commands into @cmd
end

close commands
deallocate commands

• Obtain the encrypted key of the password.

• Go to [SupportCenter Plus Home]\bin in the command prompt.

• Execute the file encrypt.bat.

• Type the Read-Only User password and click Enter.

• Copy the password encryption key displayed in the command prompt and store it in a secure location.

• Go to {SCP_Home}/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.

Restore SupportCenter Plus

While restoring SupportCenter Plus from backup data,

• If the database setup is not changed, update the database flag after restoring the data.

• If the database or application setup is modified, create a Read-Only User again as mentioned above.
  

Restrict Execution of Database Functions

For Postgrest Database:

Administrative functions that interfere with or slow down generation of query reports must be restricted.

To restrict postgres functions, remove the Execute permission from the public role using the Data Control Language (DCL) commands.

• Download the following file:

• Copy and execute the query present the file.

• The functions that interfere with query reports will be listed in the query result.

• Copy the query result to the following query to revoke the Execute permissions for public users:

REVOKE EXECUTE ON FUNCTION <insert query result> FROM public;

• Copy the query result to the following query to revoke the Execute permissions for Read-Only user:

REVOKE EXECUTE ON FUNCTION <insert query result> FROM <Read-Only User name>;

• Copy the query result to the following query to grant Execute permission to all users except for public users and Read-Only User:

GRANT EXECUTE ON FUNCTION <insert query result> TO <users except Read-Only User and public>;

For MSSQL Database:

• Fetch the functions to be restricted manually.

• Revoke the Execute permission for public user and Read-Only User. Use the following query to revoke permission for Read-Only User:

DENY EXECUTE ON [dbo].<FunctionName> TO <Read-Only User Name>;

• Grant Execute permissions to users manually except for public users and Read-Only User.
  

Administrative functions that interfere with query reports must be restricted for public user and Read-only user every time after the build version of SupportCenter Plus is updated.  

Tables to be restricted for the Read-Only User:  

• AaaAccHttpSession

• AaaPassword

• RememberMeDetails

• CustomFunctionDetails

• AdminAuditHistoryJson

• MobileAuthKey

• CommonPassword

• PasswordInfo

• PasswordResetLink

• BackupSchedule

• CustomModuleInstance

• CustomModuleDescription

• CustomModuleHistory

• CustomModuleHistoryDiff

• CM_Tasks

• CM_Comments

• CM_Attachments

• Custom_001

• Custom_MultiSelect_001

Query to Fetch Internal Tables

By default, internal tables are restricted from being viewed by users to avoid exposing sensitive data.

To view internal tables, administrators can execute the following queries.

For MSSQL Database:

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

For Postgres Database:

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;