Linked Schemas and Tables: /Documentation

Linked Schemas and Tables

Linked schemas allow you to access data from another folder, by linking to a schema in that folder. Linked schemas are useful for providing read access to selected data in another folder which is otherwise not allowed by that folder's overall permissions. Linked schemas provide a way to apply security settings at a finer granularity than at the level of whole folders.

Usage Scenario
Security Considerations
Create a Linked Schema
Metadata Filters
Handling Attachment Fields
Schema Template
Troubleshooting Linked Schemas

Usage Scenario

Linked schemas are especially useful when you want to reveal some data in a folder without granting access to the whole folder. For example, suppose you have the following data in a single folder A. Some data is intended to be private, some is intended for the general public, and some is intended for specific audiences:

Private data to be shown only to members of your team.
Client data and tailored views to be shown to individual clients.
Public data to be shown on a portal page.

You want to reveal each part of the data as is appropriate for each audience, but you don't want to give out access to folder A. To do this, you can create a linked schema in another folder B that exposes selected items in folder A. The linked schema may expose some or all of the tables and queries from the original schema. Furthermore, the linked tables and queries may be additionally filtered to create more refined views, tailored for specific audiences.

Security Considerations

The nature of a linked schema is to provide access to tables and queries in another folder which likely has different permissions than the current folder.

Permissions: A linked schema always grants the standard "Reader" role in the target folder to every user who can access the schema. Site Administrators who configure a linked schema must be extremely careful, since they are effectively bypassing folder and dataset security for the tables/queries that are linked. In addition, since the "Reader" role is granted in only the target folder itself, a linked schema can't be used to execute a "cross-container" query, a custom query that references other folders. If multiple containers must be accessed then multiple linked schemas will need to be configured or an alternative mechanism such as an ETL will need to be employed. Linked schemas also can't be used to access data that is protected with PHI annotations.

Lookup behavior: Lookups are removed from the source tables and queries when they are exposed in the linked schema. This prevents traversing a table in a linked schema beyond what has been explicitly allowed by the linked schema creator.

URLs: URLs are also removed from the source tables. The insert, update, and delete URLs are removed because the linked schema is considered read-only. The details URL and URL properties on columns are removed because the URL would rarely work in the linked schema container. If desired, the lookups and URLs can be added back in the linked schema metadata xml. To carry over any attachment field links in the source table, first copy the metadata that enables the links in the source table and paste it into the analogous field in the linked schema table. See below for an example.

Create a Linked Schema

A user must have the "Site Administrator" role to create a linked schema.

To create a linked schema in folder B that reveals data from folder A:

Navigate to folder B.
Select (Admin) > Go To Module > Query.
Click Schema Administration.
Under Linked Schemas, click New Linked Schema and specify the schema properties:

Schema Name: Provide a name for the new schema.
Source Container: Select the source folder that holds the originating schema (folder A).
Schema Template: Select a named schema template in a module. (Optional.)
Source Schema: Select the name of the originating schema in folder A.
Published Tables: To link/publish all of the tables and queries, make no selection. To link/publish a subset of tables, use checkboxes in the multi-select dropdown.
Metadata: Provide metadata filters for additional refinement. (Optional.)

Metadata Filters

You can add metadata xml that filters the data or modifies how it is displayed on the page.

In the following example, a filter is applied to the table Location such that a record is shown only when InUse is true.

<tables xmlns="http://labkey.org/data/xml" xmlns:cv="http://labkey.org/data/xml/queryCustomView">
  <filters name="public-filter">
    <cv:filter column="InUse" operator="eq" value="true"/>
  </filters>
  <table tableName="Location" tableDbType="NOT_IN_DB">
    <filters ref="public-filter"/>
  </table>
</tables>

Learn about the filtering types available:

XML Metadata Filtering Expressions: Note that some filter types (such as "equals-one-of") use different names here (in this case "in").
XML schema documentation.

Handling Attachment Fields

Attachment fields in the source table are not automatically carried over into the target schema, but you can activate attachment fields by providing a metadata override. For example, the XML below activates an attachment field called "AttachedDoc" in the list called "SourceList", which is in the domain/folder called "/labkey/SourceFolder". The URL must contain the listId (shown here = 1), the entityId, and the name of the file.

<tables xmlns="http://labkey.org/data/xml">
  <table tableName="SourceList" tableDbType="NOT_IN_DB">
    <columns>
      <column columnName="AttachedDoc">
        <url>/labkey/SourceFolder/list-download.view?listId=1&amp;entityId=${EntityId}&amp;name=${AttachedDoc}</url>
      </column>
    </columns>
  </table>
</tables>

The entityId is a hidden field that you can expose on the original list. To see it, open (Grid Views) > Customize Grid, then check the box for "Show Hidden Fields" and scroll to locate the checkbox for adding "Entity Id" to your grid. Click View Grid to see it. To include it in the URL carried to the target schema, use the syntax "${EntityId} as shown above.

For more information about metadata xml, see Query Metadata.

Schema Template

Default values can be saved as a "schema template" -- by overriding parts of the template, you can change:

the source schema (for example, while keeping the tables and metadata the same).
the metadata (for example, to set up different filters for each client).

Set up a template by placing .template.xml file in the resources/schemas directory of a module:

myModuleA/resources/schemas/ClientA.template.xml

The example .template.xml file below provides a default linked schema and a default filter xml for Client A:

ClientA.template.xml

<templateSchema xmlns="http://labkey.org/data/xml/externalSchema"
	xmlns:dat="http://labkey.org/data/xml"
        xmlns:cv="http://labkey.org/data/xml/queryCustomView"
        sourceSchemaName="assay.General.Custom Assay">
    <tables>
        <tableName>Data</tableName>
    </tables>
    <metadata>
        <dat:tables>
            <dat:filters name="client-filter">
                <cv:filter column="Client" operator="eq" value="A Client"/>
            </dat:filters>
            <dat:table tableName="Data" tableDbType="NOT_IN_DB">
                <dat:filters ref="client-filter"/>
            </dat:table>
        </dat:tables>
    </metadata>
</templateSchema>

To use the module, you must enable it in the source folder (folder A):

Go to the source folder and select (Admin) > Folder > Management.
Select the Folder Type tab.
Under Modules, check the box next to your module.
Click Update Folder.

You can override any of the template values by clicking Override template value for the appropriate region.

For example, you can create a schema for Client B by (1) creating a new linked schema based on the template for Client A and (2) overriding the metadata xml, as shown below:

Once you've clicked override, the link will change to read Revert to template value giving you that option.

Troubleshooting Linked Schemas

Linked schemas will be evaluated with the permissions of the user who originally created them. If that user is later deleted from the system, or their permissions change, the schema may no longer work correctly. Symptoms may be errors in queries, the disappearance of the linked schema from the schema browser (while it still appears on the schema administration page) and other issues.

There are two ways to fix this:

Recreate the linked schema with the same name as a validated user. An easy way to do this is to rename the 'broken' schema, then create a new one with the original name, using the 'broken' one as a guide.
Directly access the database and find the query.ExternalSchema table row for this specific linked schema. Replace the 'CreatedBy' user value from the deleted user to a validated user. Clear Caches and Refresh after making this change.

LabKey Support

LabKey Support