Allowed Schemas

Allowed Schemas secure your external data connections by implementing access restrictions to a specific set of schemas. By defining which schemas are accessible, you can effectively control access to sensitive data.

Usages

To illustrate this feature, let's consider an example using the SQL sample database known as AdventureWorks. Within this database exist various schemas such as Person, HumanResources, Sales, and more. By leveraging the Allowed Schemas feature, you can restrict the connection to only grant access to the Person and HumanResources schemas. Consequently, users utilizing this connection will be unable to access other schemas, such as Sales. This security measure not only enhances data protection but also streamlines the exploration of table contents, as irrelevant tables are excluded from view.

Showing Table from a Restricted Schemas

If there is a table that is in not in one of the allowed schemas, but users still need access to it.Then simply by creating a table alias mappings to the restricted table, this will allow users of the connection to access the restricted table.

Microsoft SQL Server

This section pertains to Microsoft SQL Server connections that do not explicitly specify database (also known as a catalog). In this case, when the visual assist button is clicked to display all available schemas, it will present schemas from all databases that are available to the user. By selecting only a desired set of schemas, users can avoid seeing all schemas. This is particularly crucial when performing joins, as joins can only be executed within a single connection. Using a connection with no default database allows users to perform joins between different databases through a single connection, since joins must always be performed on a single database connection.

To illustrate, consider the following practical example: users can easily create joins between tables in [ProductionDB].[HumanResources] and [TestDB].[HumanResources].

Remarks

Despite the connection being locked to a predefined set of schemas, administrators of a connection retain the ability to introduce a table alias mapping to a table within a restricted schema. By doing so, the associated table can now be utilized within the connection.