Creating and Configuring a MySQL System of Record

Prerequisites

An existing MySQL instance accessible by SGNL
An existing MySQL User with privileges to SELECT from all desired Tables
Knowledge of the schema for the Database and any Tables you want to import

Permissions Required

SGNL firmly believes in the principle of least privilege, as such - only the access required to achieve your authorization use-cases should be granted.
The MySQL User requires privileges to SELECT from all desired Tables

Configuring MySQL

Assuming all prerequisites are filled, no further configuration is required on the MySQL instance.

Configuring SGNL

Login to the SGNL Console
From the left menu, select Identity Data Fabric
Click “Add System of Record” or “Add”.
The SGNL SoR Catalog will show up on the screen:
Click on “Custom SoR Template” which will open up the New System of Record screen.
- Note: We do not currently provide a MySQL template since there is no “standard” Database / Table schema.
Update the Display Name and Description as applicable. Optionally, upload an icon.
Choose the correct adapter that matches the System of Record Type.
Replace all fields that have the {{Input Required:}} placeholder with relevant information. Choose Basic as the Authentication Method. For MySQL, the following fields are required:
- Address: The address of your MySQL instance, e.g. 127.0.0.1:3306
- Username: The configured MySQL User username
- Password: The configured MySQL User password
- Advanced Settings -> Adapter Config: The intended Database name must be specified here. Table names are specified later when configuring Entities.

Example Minimal Adapter Config:

{
    "database": "example_table"
}

Click “Continue” to save your MySQL System of Record. You will be taken to the landing page for a new Custom SoR.
We can see there are no currently created Entities since we’re using a Custom SoR template. To import data, we’ll want to create an Entity for each Table. The External ID of the Entity MUST exactly match the name of the Table (including capitalization). Attributes can then be added for each column on the Table. Similar to the Entity, the External ID of each Attribute MUST exactly match the column on the Table.
- Note: Each Entity requires 1 Attribute to be marked as a Unique ID and SGNL currently requires this type be a String. If the type stored in the MySQL instance is not a String, SGNL will convert this to String during ingestion. Similarly, this will work for some other types that can be meaningly converted (e.g. a Boolean can be converted to a String or an Int, but a String of characters cannot be converted into a Float, etc).
Relationships can optionally be created between these Entities as desired.
Note that synchronization is disabled by default when a new System of Record is created. You can choose to enable synchronization on Entities individually. Hover over the entity to see the Enable Sync button, and click on it.
Repeat for all Entities you want to synchronize to SGNL. Finally, Enable synchronization for the System of Record.
After some time, SGNL should complete ingesting the data from your MySQL instance into the SGNL graph. The number of objects ingested per Entity are displayed on the SoR screen. You should then be able to construct policies based on your ingested data and make access evaluation calls to SGNL.
Once ingestion is complete and your data is in the SGNL graph, you can use Data Lens to explore the SGNL graph.

Synchronization Filters

In most cases, you will want to reduce the data coming from your MySQL instance to only the objects that you need to successfully evaluate your policies or take action within SGNL. Given that, it’s likely that you will want to make use of filters to reduce that data.

Unlike some other adapters, we do not allow directly specifying SQL WHERE clauses due to the risk of SQL injection. Instead, we provide a structured syntax that allows constructing complex filters that we convert to SQL dynamically in the adapter.

On the Adapter Config, a filter object can be set for each Entity that allows specifying a Condition.

A Condition is a JSON object that represents either:

A Leaf Condition (a single field comparison), or
A Composite Condition (a logical combination of other Conditions)

Leaf Condition

This is the basic building block. It represents one field comparison.

{
  "field": "status",
  "op": "=",
  "value": "active"
}

In the adapter, this is translated to the following SQL WHERE clause:

WHERE status = 'active'

Composite Condition

Composite Conditions use the and or or keys to combine multiple Conditions.

AND Example

{
  "and": [
    { "field": "age", "op": ">=", "value": 18 },
    { "field": "country", "op": "=", "value": "US" }
  ]
}

In the adapter, this is translated to the following SQL WHERE clause:

WHERE age >= 18 AND country = 'US'

OR Example

{
  "or": [
    { "field": "role", "op": "=", "value": "admin" },
    { "field": "role", "op": "=", "value": "manager" }
  ]
}

In the adapter, this is translated to the following SQL WHERE clause:

WHERE role = 'admin' OR role = 'manager'

Nested Conditions

You can combine and and or recursively to form complex logic:

{
  "and": [
    { "field": "active", "op": "=", "value": true },
    {
      "or": [
        { "field": "department", "op": "=", "value": "sales" },
        { "field": "department", "op": "=", "value": "support" }
      ]
    }
  ]
}

In the adapter, this is translated to the following SQL WHERE clause:

WHERE active = true AND (department = 'sales' OR department = 'support')

Supported Operators

Reference: https://dev.mysql.com/doc/refman/8.4/en/non-typed-operators.html

Supported?	SGNL Operator	MySQL Operator	Description	Notes
No		&	Bitwise AND
Yes	>	>	Greater than operator
No		>>	Right shift
Yes	>=	>=	Greater than or equal operator
Yes	<	<	Less than operator
Yes	!=	<>, !=	Not equal operator
No		<<	Left shift
Yes	<=	<=	Less than or equal operator
No		<=>	NULL-safe equal to operator
No		%, MOD	Modulo operator
No		*	Multiplication operator
No		+	Addition operator
No		-	Minus operator
No		-	Change the sign of the argument
No		->	Return value from JSON column after evaluating path; equivalent to JSON_EXTRACT().
No		->>	Return value from JSON column after evaluating path and unquoting the result; equivalent to JSON_UNQUOTE(JSON_EXTRACT()).
No		/	Division operator
No		:=	Assign a value
No		=	Assign a value (as part of a SET statement, or as part of the SET clause in an UPDATE statement)
Yes	=	=	Equal operator
No		^	Bitwise XOR
Yes	AND (Composite Condition)	AND, &&	Logical AND
No		BETWEEN ... AND ...	Whether a value is within a range of values
No		BINARY	Cast a string to a binary string	MySQL: Deprecated
No		CASE	Case operator
No		DIV	Integer division
No		EXISTS()	Whether the result of a query contains any rows
Yes	IN	IN()	Whether a value is within a set of values
Yes	=	IS	Test a value against a boolean	The `=` SGNL operator detects if we’re comparing a boolean, and changes to `IS` accordingly
Yes	!=	IS NOT	Test a value against a boolean	The `!=` SGNL operator detects if we’re comparing a boolean, and changes to `IS NOT` accordingly
No		IS NOT NULL	NOT NULL value test
No		IS NULL	NULL value test
No		LIKE	Simple pattern matching
No		MEMBER OF()	Returns true (1) if first operand matches any element of JSON array passed as second operand, otherwise returns false (0)
No		NOT, !	Negates value
No		NOT BETWEEN ... AND ...	Whether a value is not within a range of values
No		NOT EXISTS()	Whether the result of a query contains no rows
No		NOT IN()	Whether a value is not within a set of values
No		NOT LIKE	Negation of simple pattern matching
No		NOT REGEXP	Negation of REGEXP
Yes	OR (Composite Condition)	OR, \|\|	Logical OR
No		REGEXP	Whether string matches regular expression
No		RLIKE	Whether string matches regular expression
No		SOUNDS LIKE	Compare sounds
No		XOR	Logical XOR
No		\|	Bitwise OR
No		~	Bitwise inversion

Adapter Config Fields

database - (required) the MySQL Database to retrieve data from - all Tables (Entities) should be located in this Database
requestTimeoutSeconds - how long to wait for a request to the MySQL instance to complete before failing and retrying, default 10
localTimeZoneOffset - the default local timezone offset that should be used for parsing date-time
filters - the filters to apply to each of the MySQL Entities
- The Field Name (e.g. Users, Groups, Cases, etc) will be the ExternalId of each of the Entities in SGNL, available from the SoR -> Entities -> Entity Page (e.g. Users), and the externalId of each of the attributes that you want to filter

Sample Adapter Config:

{
    "database": "sgnl",
    "requestTimeoutSeconds": 10,
    "localTimeZoneOffset": 43200,
    "filters": {
      "Users": {
        "or": [
          {
            "and": [
              { "field": "age", "op": ">=", "value": 18 },
              { "field": "country", "op": "=", "value": "US" }
            ]
          },
          {
            "and": [
              { "field": "age", "op": "<", "value": 18 },
              { "field": "email_verified", "op": "=", "value": true }
            ]
          }
        ]
      },
      "Groups": {
        "field": "country_code",
        "op": "IN",
        "value": ["US", "CA", "MX"]
      }
    }
}