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.
Available in Coginiti Team and Coginiti Enterprise only.
Overview
A connection parameter has three parts:
| Part | Where it lives | Who sets it |
|---|---|---|
| The declaration and the domain filter that reads it | The semantic model, in SMDL | Whoever writes the model |
| The value | The connection or an Execution API request, as params.<name> | An administrator in a connection template, or your application |
| The access to the domain | Coginiti, in Admin Settings → Semantic Layer → Permissions | An 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.
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.
- Open Admin Settings → Semantic Layer → Permissions and select the layer.
- Grant
my_region_salesto the users and groups who use it. For the application setup, grant it to the application's account. - Do not grant them Full model or public.
- 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
- Open Admin Settings → Templates → Database Templates.
- 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.
- In Domain, select
my_region_sales. - In Advanced Properties, add the key
params.regionwith the valueEMEA. - In Assigned Consumers, add the users or groups who must see the EMEA rows.
- 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.
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.
-
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 setparams.region, because a request cannot change a key that a template sets. -
Create a personal access token for the application's account.
-
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.
-
Run a query against the domain:
SELECT MEASURE(unit_quantity) FROM my_region_sales.sales_detail -
Change the value of
params.regionto another region, such asAPAC, and run the query again. The total must change. -
Remove the value of
params.regionand 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 symptom | What it means |
|---|---|
connection parameter 'params.X' is not declared | The 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 number | The 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 ASCII | The 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 parameters | The 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 rows | The value does not match the data. Text is compared exactly, so emea does not match EMEA |
Related guides
- Connection parameters in the Semantic Model Reference: declarations, types, values and errors
- Manage semantic permissions: grant domains and withhold
public - Connect to Semantic Layer via JDBC: send
params.<name>from a JDBC client - Connection advanced properties in the API Reference: send
params.<name>with an Execution API request - Managing Connection Templates: create and assign templates
- Set up a Consumer: onboard the users who work through templates
- Set up semantic tenants: isolate customers. A connection parameter cannot choose the tenant