Question:

I am trying to implement view level database security. I have implemented a metadata filter to only load one schema into the metadata tree structure in AQB. This is working however, there is nothing preventing the user from re-writing the SQL Query in the editor and changing the schema name to something that is not shown in the tree structure. How can I prevent the unwanted schemas from being accessed through the query editor window?

The presence of unknown objects may be just typos, inaccurately referred objects, or intentionally accessed unwanted objects.

The easiest way to prevent loading queries with objects which don't exist in the Metadata Container to the Query Builder is to set the QueryBuilder.BehaviorOptions.ParsingErrorOnUnknownObjects property to True. You will get an exception on parsing such queries. Just make sure that you take a query to execute from the Query Builder, not directly from the SQL text editor.

Another way is to check SQL queries for the presence of non-existing objects and fields right after the query is parsed. Use the QueryBuilder.QueryStatistics.UsedDatabaseObjects and QueryBuilder.QueryStatistics.UsedDatabaseObjectFields collections for this purpose. Each item in these collections has reference to an item in the Metadata Container (StatisticsDatabaseObject.MetadataObject, StatisticsField.MetadataField). If these references aren't set (set to null), such objects do not exist in the Metadata Container. As Active Query Builder knows nothing about missing objects in this case, it can't distinguish these cases.

The last way is to load all objects into the Metadata Container, including the unwanted ones, and prevent browsing in the DatabaseSchemaTree by defining the custom tree structure using the MetadataStructure object. This way, you can distinguish typos that reference metadata items that don't exist (set to null) from intentionally added unwanted objects.