Skip to main content

Restrict domain rows with connection parameters

A connection parameter lets one domain return different rows to different users. The domain's filter reads a value, such as a region, and the value comes from the connection. This guide shows how to declare the parameter, restrict access to the domain, and set the value for each user: in a connection template, or from your own application.

info

Available in Coginiti Team and Coginiti Enterprise only.

Overview​

A connection parameter has three parts:

PartWhere it livesWho sets it
The declaration and the domain filter that reads itThe semantic model, in SMDLWhoever writes the model
The valueThe connection or an Execution API request, as params.<name>An administrator in a connection template, or your application
The access to the domainCoginiti, in Admin Settings → Semantic Layer → PermissionsAn administrator

Use a connection parameter when many users need the same domain, each with a different value. A domain template writes the value in the model, so it needs one domain for each region. A connection parameter lets one domain serve every region.

Who can change the value​

The semantic layer uses the value that the connection sends. It does not check who chose the value. The filter restricts a user only when that user cannot send a different value, which is true in two setups:

  • An application that you trust opens the connections with its own Coginiti account and sets the value for each of its users. The users never get the credentials of that account.
  • An administrator fixes the value in a connection template, and assigns the template to users who cannot create their own connection to the layer, such as Consumers.
warning

A user who can open their own connection to the layer, in Coginiti or from a JDBC client with their own credentials, can send any value and read the rows of every region. Do not use a connection parameter to restrict such a user. Instead, use a domain template with one domain for each region and grant each domain separately, or use row-level security in the warehouse.

Administrators can query public or choose any value, so a connection parameter does not restrict them. When an administrator queries the domain, its filter still applies.

Prerequisites​

  • Coginiti Team or Enterprise, and an account with the Admin role
  • A project with a semantic layer, published to the Project Hub. See Share a Project With Semantic Layer
  • A column whose values decide what each user can see, such as a region column

The examples use the sales_detail, sales_header and countries entities from the Semantic Model Reference, where sales_detail reaches countries through sales_header, and countries.region holds values such as EMEA and APAC.

Step 1: Declare the parameter and read it in a filter​

Declare the parameter at the top level of any .smdl file in the layer. Then read it in a domain filter as ${connection.params.<name>}:

connection_parameter "region" {
type = "text"
}

domain "my_region_sales" {
label = "My Region Sales"

entity "sales_detail" {}
entity "sales_header" { include = [] }
entity "countries" { include = [] }

filter "own_region" {
expr = "countries.region = ${connection.params.region}"
}
}

A connection that sends params.region with the value EMEA reads only the EMEA rows from my_region_sales.

The type is text, number or bool, and the value that a connection sends must fit it. For the rules on names, types and values, see Connection parameters in the Semantic Model Reference.

Publish the updated project to the Project Hub, as in Step 1 of Share a Project With Semantic Layer.

Step 2: Restrict access to the domain​

The public schema applies no domain filter. A user who can query public reads the rows of every region, so give users access to my_region_sales only.

  1. Open Admin Settings → Semantic Layer → Permissions and select the layer.
  2. Grant my_region_sales to the users and groups who use it. For the application setup, grant it to the application's account.
  3. Do not grant them Full model or public.
  4. After you grant access, set Access mode to Granted explicitly.

Manage semantic permissions describes each step on this screen.

Step 3: Set the value​

Option A: Fix the value in a connection template​

  1. Open Admin Settings → Templates → Database Templates.
  2. Create a template with the Semantic Layer platform, or open an existing one. Set the Project and the Semantic Layer as in Step 4 of Share a Project With Semantic Layer.
  3. In Domain, select my_region_sales.
  4. In Advanced Properties, add the key params.region with the value EMEA.
  5. In Assigned Consumers, add the users or groups who must see the EMEA rows.
  6. Click Save.

Every user who is assigned the template gets the same value. For each other region, create another template. A template for each regional group is a common setup.

warning

Set every parameter that the domain's filters read in the template. A user who is assigned the template cannot change a parameter that the template sets, but can add a parameter that the template does not set, with a request to the Execution API.

Assign the template only to users who cannot create their own connection to the layer. A Consumer does not manage connections; see Set up a Consumer, which also explains why a Consumer's template needs Share Sensitive Data. A user with the User or Admin role can create a semantic connection and send any value.

Option B: Set the value from your application​

Your application signs in its own users. For each user, it opens a connection with the application's Coginiti account and sets params.region to the value that belongs to that user:

Properties props = new Properties();
props.setProperty("user", "sales-portal");
props.setProperty("password", System.getenv("COGINITI_PASSWORD"));
props.setProperty("project", "@Project Hub/retail/analytics");
props.setProperty("semantic-layer", "sales_domain");
props.setProperty("domain", "my_region_sales");
props.setProperty("params.region", user.getRegion());

try (Connection conn = DriverManager.getConnection("jdbc:coginiti://coginiti.company.com:443/", props)) {
ResultSet rows = conn.createStatement().executeQuery(
"SELECT MEASURE(unit_quantity) FROM sales_detail");
}

In this example, sales-portal is the application's account, and user.getRegion() stands for the region that your application stores for the signed-in user. The application chooses the value. The user never types it.

A value in a Properties object is sent as it is, so do not URI-encode it. A connection keeps its value for its whole life. If your application uses a connection pool, keep one pool for each value, or open a connection for each user. A statement prepared on a connection with one value is refused on a connection with another value; see Troubleshooting. For other ways to connect, see Connect to Semantic Layer via JDBC.

Option C: Send the value with each API request​

If your application calls the Execution API instead of a JDBC driver, it sends the value in connection.advanced_properties of each request. The setup is the same as in Option B: the application uses its own Coginiti account, and its users never get the credentials of that account.

  1. Sign in to Coginiti with the application's account, and create a connection with the Semantic Layer platform. Set the Project and the Semantic Layer, and in Domain, select my_region_sales. If the application uses a connection template instead, the template must not set params.region, because a request cannot change a key that a template sets.

  2. Create a personal access token for the application's account.

  3. For each user, send the value that belongs to that user:

    curl -X POST 'https://coginiti.company.com/api/v1/exec/sql' \
    --header "Authorization: Bearer $COGINITI_TOKEN" \
    --header 'Content-Type: application/json' \
    --data-raw '{
    "sql": "SELECT MEASURE(unit_quantity) FROM sales_detail",
    "connection": {
    "name": "Sales Portal",
    "advanced_properties": {
    "params.region": "EMEA"
    }
    }
    }'

In this example, Sales Portal is the connection that the application's account owns. The value applies to that request only, and Coginiti does not save it. Thus one connection can serve all the users. Keep the token on the server of your application, because a person who has the token can send any value.

For the rules on keys and values, and for the errors, see Connection advanced properties in the API Reference.

Verify the setup​

Check the filter with a semantic connection of your own. Give it the same Domain and the same params.region in Advanced Properties as the template. The filter applies to administrators too; only the grants do not.

  1. Run a query against the domain:

    SELECT MEASURE(unit_quantity) FROM my_region_sales.sales_detail
  2. Change the value of params.region to another region, such as APAC, and run the query again. The total must change.

  3. Remove the value of params.region and click Test. The connection is refused:

    domain 'my_region_sales' needs connection parameter 'params.region'

If the total does not change in step 2, make sure that the filter reads ${connection.params.region}, and that the query reads my_region_sales, not public.

Then check the grants. Sign in with a non-administrator test account that has access to my_region_sales only, and open a connection to the layer. The Domain dropdown must offer my_region_sales and must not offer public. See Step 4 of Manage semantic permissions.

Troubleshooting​

Message or symptomWhat it means
connection parameter 'params.X' is not declaredThe connection sends a key that the layer does not declare. Check the spelling of the key in Advanced Properties, or declare the parameter in the model. Clicking Test on the connection or template also reports it
connection parameter 'params.X' must be a numberThe value does not fit the declared type. A number takes digits with an optional - and decimal part. A bool takes true or false
connection parameter 'params.X' is not printable ASCIIThe value has a character such as é or a tab. Values can contain printable ASCII characters only
domain 'X' needs connection parameter 'params.Y'The connection sends no value for a parameter that the domain's filter reads. An empty value, or a value of spaces only, counts as no value
this statement handle belongs to different connection parametersThe statement was prepared with one value of the parameter and run with another value. Prepare the statement again on the connection that runs it. If your application uses a connection pool, keep one pool for each value
The query returns no rowsThe value does not match the data. Text is compared exactly, so emea does not match EMEA