Informe de consulta: Configuración de usuario de solo lectura

Un usuario de solo lectura en una base de datos tiene permisos exclusivos para leer información y ejecutar informes de consulta. Esta configuración garantiza consultas seguras y bloquea el acceso a datos confidenciales dentro de la base de datos de la aplicación.

Rol requerido: Administrador de la organización, SDAdmin, usuarios con el permiso de Crear informe de consulta

Se requiere experiencia en SQL para configurar usuarios de solo lectura.

Contenido

Configurar un usuario de solo lectura en Postgres.
Configurar un usuario de solo lectura en la base de datos Microsoft SQL .
Solucionar fallas de informes de consulta

Configurar un usuario de solo lectura en Postgres

Conéctese a la base de datos de la aplicación como un usuario con permiso para crear un rol/usuario.

En los scripts, reemplace los valores dentro de <> según su preferencia.

Cree un usuario con credenciales de inicio de sesión.

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

Otorgue los privilegios necesarios al usuario:
- Base de datos

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

Esquema

GRANT USAGE ON SCHEMA public TO <username>;

Tablas

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

Para restringir el acceso a ciertas tablas, revoque el acceso SELECT. Consulte el script .

--Consulta de ejemplo

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

REVOKE SELECT ON aaapassword TABLE FROM readonlyuser;

Para restringir funciones de postgres, elimine el permiso de ejecución del rol público mediante comandos de Lenguaje de Control de Datos (DCL).
Use este script para crear una función de revoke. Después de crear una función de revoke, ejecute la siguiente consulta para eliminar el permiso de ejecución para los usuarios públicos.

SELECT sdp_revoke_execute_on_functions('public');

Ejecute la siguiente consulta para revocar los permisos de execute para funciones de un usuario de solo lectura.

SELECT sdp_revoke_execute_on_functions('<nombre del usuario de solo lectura>');

Como los privilegios de revoke solo se pueden proporcionar para roles públicos, puede restaurar los privilegios del rol público para los usuarios de forma individual.

Para restaurar los privilegios de revoke para otros usuarios, otorgue permisos de execute a todos los usuarios excepto a los usuarios públicos y de solo lectura. Use este script para crear una función grant.

Ejecute la siguiente consulta para obtener los usuarios.

SELECT usename FROM pg_catalog.pg_user;

Los usuarios mencionados en la captura de pantalla son solo de referencia.

Ejecute la siguiente consulta para otorgar privilegios de función pública a otros usuarios. Reemplace <username> por usuarios distintos de los usuarios públicos o de solo lectura.

SELECT sdp_grant_execute_on_functions('<username>');

Para verificar si la restricción se ha aplicado, conéctese a la base de datos como el usuario de solo lectura recién creado y asegúrese de que las consultas restringidas sean denegadas.

SELECT pg_sleep(10);

De forma predeterminada, ciertas palabras y tablas están restringidas para consulta. Para obtener esas palabras clave, use el siguiente 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;\

Configurar un usuario de solo lectura en la base de datos Microsoft SQL

En Microsoft SQL, no puede configurar un usuario de solo lectura cuando la base de datos está vinculada al servidor de aplicaciones mediante autenticación de Windows.

Conéctese a la base de datos Microsoft SQL como un usuario que tenga el rol CREATE USER o LOGIN.
Conéctese a la consola de consultas con la base de datos de la aplicación y cree un usuario con credenciales de inicio de sesión.

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

CREATE USER <username> FOR LOGIN <username>;

Para lograr los privilegios requeridos de solo lectura, use los siguientes comandos.
Restrinja todos los demás comandos excepto SELECT.

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

El siguiente comando revoca los privilegios de SELECT para todas las tablas.

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

Acceso restringido a tablas en Microsoft SQL

Para proporcionar acceso solo a tablas específicas, ejecute el script desde aquí.

Ignore los problemas relacionados con que no se encuentre una tabla u objeto.

Para verificar si la restricción se ha aplicado, inicie sesión en la base de datos como el usuario recién creado, ejecute las siguientes consultas y asegúrese de que las tablas restringidas no se consulten.\

--Consulta de ejemplo

SELECT * FROM aaapassword;

Si el resultado de la consulta muestra un mensaje de restricción de tabla SQL, la restricción de tablas se ha logrado.
- Si no se logra la restricción de tablas, siga estos comandos y vuelva a comprobar. Consulte aquí para la lista de tablas restringidas.

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

--o

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

Verifique si todas las demás tablas están permitidas para consulta.

--Consulta de ejemplo

SELECT * FROM reportmoduleconfiguration;

Verifique si se devuelven las filas de la tabla. Si no es así, ejecute este script y vuelva a comprobar.

Para restringir funciones SQL, ejecute la siguiente consulta.

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

Para una función como xp_cmdshel, ejecute lo siguiente

DENY EXECUTE ON xp_cmdshell FROM rouser

De forma predeterminada, algunas tablas y palabras no se pueden consultar. Obtenga esas palabras usando la siguiente consulta.

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

Obtener la clave cifrada de la contraseña

Vaya a [ServiceDesk Plus Home]\bin en el símbolo del sistema.
Ejecute el archivo encrypt.bat.
Escriba la contraseña del usuario de solo lectura y presione Enter.
Copie la clave de cifrado de la contraseña que se muestra en el símbolo del sistema y guárdela en una ubicación segura.
Vaya a {SDP_Home}/ServiceDesk/conf.
Abra el archivo database_params.conf .
Configure el nombre de usuario en la etiqueta correspondiente. Por ejemplo,

rodatasource.username=<username>

Obtenga la clave de la contraseña cifrada y configúrela en la etiqueta correspondiente. Por ejemplo,

rodatasource.password=<password>

Actualizar indicador de base de datos

Después de crear el usuario, conecte la aplicación a la base de datos y ejecute la siguiente consulta. Esto permitirá que el usuario de solo lectura cree informes de consulta seguros que no obtengan datos de las tablas restringidas: UPDATE ReportModuleConfiguration SET PARAMVALUE = 'true' WHERE CATEGORY LIKE 'ROUser' AND PARAMETER LIKE 'Use_ROUser'.
Reinicie la aplicación para que los cambios surtan efecto.

Solucionar fallas de informes de consulta

1. Fallas de respaldo en Postgres integrado.

Si se produce una falla de respaldo debido a problemas de permisos, use este script para restaurar los permisos necesarios para los usuarios de la base de datos de la aplicación (sdpadmin).

2. En la base de datos Microsoft SQL, el informe de consulta falla con el mensaje de error "Restricted table(s) found in query."

Asegúrese de que la consulta no esté accediendo a tablas restringidas.
Cambie el nombre de cualquier alias de columna que coincida con palabras clave restringidas.
Modifique cualquier valor en la consulta que contenga palabras clave restringidas.
Cambie o modifique cualquier palabra clave presente en la consulta según sea necesario.

Informe de consulta: Configuración de usuario de solo lectura

Configurar un usuario de solo lectura en Postgres

Configurar un usuario de solo lectura en la base de datos Microsoft SQL

Acceso restringido a tablas en Microsoft SQL

Obtener la clave cifrada de la contraseña

Actualizar indicador de base de datos

Solucionar fallas de informes de consulta

Con la confianza de las mejores organizaciones del mundo