How to Configure Row-Level Security (RLS) in Snowflake and Integrate with Spotfire

This article provides recommended practices for implementing user- or team-based Row-Level Security (RLS) in Snowflake while integrating with Spotfire. It also includes important notes regarding Snowflake stored procedure limitations within Spotfire Information Services.

1. Implement Row Access Policies in Snowflake

Define Row Access Policies in Snowflake to enforce data-level restrictions dynamically for Spotfire users. When Spotfire connects through SSO or user impersonation, Snowflake automatically filters data based on the logged-in user.

CREATE OR REPLACE ROW ACCESS POLICY rls_policy AS
  (department STRING) RETURNS BOOLEAN -
    EXISTS (
      SELECT 1
      FROM security_map
      WHERE user_name = CURRENT_USER()
        AND department = security_map.department
    );

ALTER TABLE assay_results
  ADD ROW ACCESS POLICY rls_policy ON (department);

2. Centralized Security Mapping Table

Create a mapping table to define which users or teams have access to specific data segments (e.g., department, region, or project).

CREATE TABLE security_map (
  user_name STRING,
  department STRING
);

This simplifies RLS maintenance since updates only require modifying the mapping table instead of multiple Spotfire information links.

3. Filtering and Joins within Spotfire

If users are end users (not Snowflake accounts), or if additional flexibility is needed, you can bring the data into Spotfire and filter it locally:

• Load both the main dataset and the user/team mapping table from Snowflake.
• Apply inner joins or left joins in Spotfire based on matching columns like Department or Project_ID.
• Display only the data subset relevant to each user or group.

4. On-Demand Data Tables with Property Controls

Use On-Demand Data Tables to dynamically fetch data subsets based on Spotfire property controls (parameters).

SELECT * FROM assay_results
WHERE department = '${DepartmentParameter}'

This approach improves performance and ensures users only load the data they need.

5. Combine Approaches for Scalability

For best results, combine Snowflake Row Access Policies with Spotfire-level joins and On-Demand filtering. This hybrid design ensures security, scalability, and minimal maintenance of multiple Information Links.

⚠️ Important Note on Snowflake Stored Procedures

As per the TIBCO KB article Error when creating procedure elements for Snowflake stored procedures in TIBCO Spotfire Information Services:

• Spotfire Information Services does not support Snowflake stored procedures or user-defined functions as procedure elements.
• Stored procedures typically return scalar values or non-tabular results, which are incompatible with Spotfire’s data source requirements.
• Users may encounter errors such as:
  • Error retrieving metadata: Invalid column index
  • The previously selected source procedure was taken from a data source...

Recommendation: Avoid encapsulating filter logic in Snowflake stored procedures. Instead, implement row-level filters via Snowflake views, Row Access Policies, or Spotfire joins and On-Demand queries.

✅ Summary of Best Practices