SQL Server

Modelo de integración

Extracción de metadatos

Para la extracción de metadata de un objeto se utilizan los métodos que ofrece el driver de JDBC mediante los cuales se accede a la definición de esquemas y tablas.

Extrae los siguientes atributos que deben llamarse igual en la tabla attribute_definition, campo name para que aparezcan en la plantilla.

• catalog con el valor de catalog en la base de datos

• schema con el valor de schema en la base de datos

• physicalName y name con el mismo valor, el nombre de la tabla

• path con la concatenación de los valores de catalog, schema and table

• infrastructure con el valor seleccionado

• technology con el valor seleccionado

• zone con el valor seleccionado

En caso de extraer el metadato para crear un dataset, también se extraerán los siguientes atributos relativos a los campos del recurso pedido para poder rellenar la información de su estructura:

• physicalName y name con el mismo valor, el nombre del campo

• defaultValue con el valor por defecto que se haya establecido al field

• fieldDataType con el tipo de dato asignado al field, si se ha establecido

• length con la longitud del campo, si se ha establecido.

• incrementalField

• position con el valor de la posición que ocupa el field

• precision con el valor de la precisión del campo, si se ha establecido

• nullable indicando si el field es anulable o no (valor booleano)

• pk indicando si el field es una clave primaria (valor boolean)

• description la descripción del dataset-field

Los atributos a crear en Anjana deben de tener los siguientes tipos:

El plugin es capaz de realizar la extracción de metadatos de los siguientes tipos de elementos:

Tabla de base de datos

Muestreo de datos

Utilizando el driver JDBC se ejecuta una query con límite de registros sobre los campos definidos en el dataset en la que, adicionalmente, se sustituyen los valores de los campos sensibles por asteriscos.

Aquellos campos que se modifiquen después de crear el objeto en Anjana (es decir, que estén definidos en el metadato pero no se hayan incorporado en la estructura física) aparecerán como no disponibles en el muestreo.

Creación de estructuras

El plugin permite crear las estructuras físicas siempre que el objeto sea gobernado. Cuando esto ocurra y se valide el workflow asociado se creará la estructura en el path indicado del dataset. Una vez creado no se modificará aunque se generen nuevas versiones del dataset a no ser que se especifique un nuevo path.

Gestión de accesos

El plugin permite gestionar el acceso a aquellas estructuras que se gobiernen. Mediante el uso de roles y asociar permisos de SELECT sobre las estructuras al rol.

Edición de objetos

El plugin permite gestionar la activación o desactivación de entidades no nativas, de modo que cuando una entidad no nativa se active se darán los permisos correspondientes en las tablas y cuando se desactive se eliminarán los permisos.

Versiones soportadas

Soporte desde SQL Server 2016 a SQL Server 2019.

Credenciales requeridas

Extracción de metadatos

Usuario o rol con permisos VIEW DEFINITION sobre las tablas o vistas de las que se quiera extraer el metadato.

También se puede aplicar sobre esquemas o base de datos directamente y aplicará a todo lo que contiene.

Muestreo de datos

Usuario o rol con permisos SELECT sobre las tablas o vistas que se quieran obtener un muestreo de datos.

También se puede aplicar sobre esquemas o base de datos directamente y aplicará a todo lo que contiene.

Creación de estructuras

Usuario con los siguientes permisos/roles necesarios sobre los catálogos, esquemas y tablas que se quieran gobernar.

CREATE TABLE

Los nombres utilizados para crear los recursos en SQL Server están sujetos a las restricciones impuestas por el mismo SQL Server para cada uno de ellos.

Gestión de accesos

Usuario con los siguientes permisos necesarios sobre los catálogos, esquemas y tablas que se quieran gobernar.

• CREATE ROLE

• ALTER ANY ROLE

• ALTER ANY LOGIN (necesario a nivel master)

• CONTROL (opcional si la propiedad del rol se cede a tercero)

• SELECT ON OBJECT

ALTER ROLE anjana_service_rol ADD MEMBER anjana_service_user;

Gestión de accesos mediante Azure AD

Si el gobierno activo se va a realizar con Azure AD hay que realizar los siguientes pasos:

Crear una APP en Azure AD para que actúe de service principal y generar un secret

Activar en Identity del servidor de SQL la opción “System assigned managed identity”

Permitir a los servicios de Azure a acceder al servidor SQL

Dar permiso a la identidad del servidor SQL a acceder al directorio de Azure AD

En SQL hay que darle permisos al service principal de la APP que usamos en el plugin de Azure AD

-- LOGIN
CREATE USER [<app-name>] FROM EXTERNAL PROVIDER;

-- ADD PERMISSIONS TO [<app-name>]
ALTER ROLE anjana_service_rol ADD MEMBER [<app-name>];
GO

Edición de objetos

Cuando en Anjana se active o desactive una entidad no nativa, el plugin dará o eliminará los permisos en las tablas correspondientes.

Usuario de conexión debe de tener los siguientes permisos en los catálogos, esquemas y tablas que se quieran gobernar.

• ALTER ANY ROLE

• SELECT ON OBJECT

• CONTROL (opcional si la propiedad del rol se cede a tercero)

Configuración

En la Configuración técnica y en Tot despliegue de plugins hay algunas directrices.

Existe un YAML de ejemplo para facilitar la configuración en Nexus / com / anjana / documentation (necesario login previo) con explicaciones de las propiedades y valores por defecto.

Este plugin puede conectarse a varias instancias de la misma tecnología, también se puede ver el YAML de ejemplo.

Consideraciones especiales:

Los parámetros de credenciales se dividen en dos bloques y no pueden estar ambos definidos a la vez:

• Url, user y password, son credenciales de conexión a la BD, se deben usar cuando se conecte contra un SQL Server.

• ServerName, databaseName, principalId y principalSecret son credenciales contra un Azure SQL Server.

Para cada conexión configurable Anjana interpreta la siguiente para saber como comunicarse con SQLServer:

“using-catalogs” y “using-schemas” determina el nivel desde el que se gobierna el SQL Server y cómo se interpretan los path de las estructuras que se quieren gobernar desde Anjana, si ambos están a false solo muestran el schema por defecto o el elegido en la url de conexión. (EX: using-catalogs a false y using-schemas a true indican que se quiere gobernar todos los esquemas que se tenga acceso y siempre dentro del mismo catálogo o base de datos)

“path-separator” va a indicar el separador utilizado por parte de Anjana para el path. (EX: Si es “/” la tabla empleados en el esquema de hr se espera que llegue desde anjana como hr/empleados). El plugin transforma el path en una estructura correcta para SQL Server por lo que, si no se especifica correctamente, se intentarán crear recursos erróneos produciéndose un error.

“sampleRows” indica el número de filas que se recuperan para la funcionalidad de sampleo de datos.

“imType” indica que tipo de gestor de identidades externo a SQLServer se usa, los posibles valores son:

• EID para EntraID (antiguo AzureAD)

• AD para Windows AD

• NONE para usar SQLServer puro, por defecto se usa este

“imDomain” se usa para indicar sobre que dominio del gestor de identidades se va a trabajar, no lleva valor por defecto y no es necesario usarlo si en “imType” coge valor NONE.

Al crear roles nuevos en SQL Server para asignar permisos sobre las tablas que se quiera gobernar con ese rol se usa “rolePrefix” para indicar el sufijo que se quiere sobre el nombre del rol. Si no se quiere que tenga un sufijo es necesario incluir la variable en el yml sin ponerle valor.

En el proceso de creación de un rol y sus permisos existen una serie de reintentos y espera entre los reintentos, para configurar dichos reintentos se usan “azureCountRetry” y “azureWaitRetry”

totplugin:
 sql:
   query-pattern:
     createRole: "CREATE ROLE {0}"
     existRole: "SELECT DATABASE_PRINCIPAL_ID({0})"
     grantSelect: "GRANT SELECT ON {0} TO {1}"
     deleteRole: "DROP ROLE {0}"
     revokeSelect: "REVOKE SELECT ON {0} FROM {1}"
     createFromExternalProvider: "CREATE USER [{0}] FROM EXTERNAL PROVIDER WITH DEFAULT_SCHEMA = [{1}]"
     deleteUser: "DROP USER IF EXISTS [{0}]"
     addMemberRole: "ALTER ROLE [{0}] ADD MEMBER {1}"
     dropMemberRole: "ALTER ROLE [{0}] DROP MEMBER {1}"

Las queries que se ejecutan durante el ciclo de gobierno se presentan arriba, con sus valores por defecto.

• createRole: Usada para crear el rol

• existRole: Comprobación de existencia de roles para control de errores

• grantSelect: Dar permisos de lectura sobre una tabla a un rol

• deleteRole: Borrar el rol

• revokeSelect: Revocar permisos de lectura sobre una tabla a un rol

• createFromExternalProvider: Creación de un usuario con el nombre del grupo en el AD asociado con la tabla que se gobierna, que será la identidad que tome al acceder.

• deleteUser: Borrado del usuario que corresponde al nombre del grupo en el AD asociado

• addMemberRole: Permite añadir el usuario que se ha creado al rol que se creó.

• dropMemberRole: Se elimina al usuario del rol. La operación inversa al addMemberRole.

Conexión cifrada

En el caso de querer conectarse al plugin utilizando cifrado será necesario añadir a la cadena de conexión las siguientes propiedades: encrypt=true;trustServerCertificate=true

totplugin:
  connection:
    - name: dev
      technology:
        url: jdbc:sqlserver://rdbservice:1433;database=<db>;encrypt=true;trustServerCertificate=true

Según la configuración del servidor podría ser necesario cambiar los valores de las propiedades mencionadas. Para más información consultar la documentación de Microsoft: Connection SQLServer

ImAri disponibles

• Azure

• Ldap

Por defecto el plugin considera que se conecta a un Azure AD y hace el enlace entre el usuario de Sql Server y Azure AD. Si se utilizara otro AD distinto hay que especificar la siguiente configuración:

totplugin:
 connection:
    - name: dev
      technology:
        sql:
          query-pattern:
     createFromExternalProvider: "CREATE USER [DOMAIN\{0}] FROM EXTERNAL PROVIDER WITH DEFAULT_SCHEMA = [{1}]"

Siendo DOMAIN el dominio del AD