Teradata Secure Zones – Another great Post by David Wellman!
Teradata Secure Zones
I’ve had a couple of conversations recently with different customers about the “Secure Zones” optional feature added in Teradata 15.10 and based on those there is a fair amount of misunderstanding about what this feature offers and does.
This article is aimed at providing some clear information for anyone considering the use of this feature.
Before I cover that, let me describe the basic set up of Secure Zones on a Teradata system and what they offer that is different from previously available.
A “Secure Zone” on a Teradata system typically contains multiple databases and users under a single database or user (referred to as the ‘root’ of that zone). The users defined in a zone can only access data in tables/views that are also set in that zone.
Where a Teradata system has multiple “secure zones”, perhaps named ‘UK’ and ‘EU,’ then users in the ‘UK’ zone cannot see data in the ‘EU’ zone and vice-versa. All of this could be achieved quite simply using standard Teradata Access Rights.
The difference that “Secure Zones” provides is that users in the ’UK’ zone don’t know that the ‘EU’ zone objects even exist – even if they run a query such as:
WHERE databasename <> ‘DBC’
ORDER BY 1,2;
In a “Secure Zones” environment this query will not return any objects defined in the ‘EU’ zone – the ’UK’ user doesn’t even know the ‘EU’ zone or its objects exist.
The above ‘works’ because when the query is sent to the Teradata system, the SQL is effectively changed to use view DBC.TablesV_SZ (and not DBC.TablesV as coded). It is this view which uses the new table DBC.Zones to filter the returned data.
There are two things to note about this mechanism:
- This change to the SQL only happens for non-DBC users. If user DBC runs the above query, then the SQL is not modified, and rows are returned from all zones.
- This switch to the SQL only happens if it references the views (e.g. DBC.TablesV). If the SQL references the underlying tables (e.g. DBC.Dbase, DBC.TVM, etc.) then no changes to the SQL are made, and rows for objects in all zones will be returned – regardless of which zone the user running this SQL is defined in.
Possible Use Case
A commonly quoted use case for the “Secure Zones” feature is where a multi-national organization needs to separate data for its different country organizations. This separation may be for regulatory requirements.
In this scenario, there may be a separate secure zone for each country containing databases, tables, and users, etc. The data and users within each zone are specific to a country.
Assume that there is an ‘Orders’ table in each zone, possibly with the same definition. The ‘Orders’ table in each zone is a different physical table – because they are in different databases.
Therefore if there is a requirement to produce a ‘corporate level’ Orders report spanning multiple countries (i.e. zones), then the SQL has to reference multiple tables. Yes, that element may be hidden inside a view, but the underlying processing still requires access to multiple tables. Whether those tables are joined, or UNION’d together is a different question and relies on the business requirement. With either solution, this is likely to provide slower performance compared to all of the data being in a single physical table.
A similar segregation of the data (from different countries) can be achieved without using “Secure Zones,” different approaches have advantages and disadvantages.
- Use different databases and user hierarchies for each country: This is the closest to the “Secure Zones” feature in that the data is separated into different physical tables, Access Rights can be granted such that only the appropriate users have access to the data and corporate level reporting requires access to multiple tables. The major difference with this approach is that a ‘UK’ user who runs the query shown above will see that the ‘EU’ objects exist.
- Row-Level Security: With this method, there is only one, corporate-wide Orders table (as an example) which contains the data for all countries. Additional ‘row level constraint’ column(s) are added to the table definition, which causes the correct data to be returned to each user based on their access constraints (i.e. country). Corporate level reporting now only has to access a single table.
- Country Specific Views: Historically in Teradata, this has been a very common solution to this type of requirement. The ‘Orders’ data is stored in a single physical table, and a series of ‘country specific’ views are defined, each one with an appropriate WHERE clause such that only data for a particular country is returned. A user is given access to the consistent view of their country.
- Security Table: Alternatively a single view is used which joins to a ‘security table’ that identifies which country the user should see data for.
Whether any of these alternate strategies provide sufficient data security for an organization is a separate question and only one that the organization itself can answer.
The data in a “Secure Zone” table is not encrypted; it is stored just like any other data in the Teradata DBMS. If you need your data encrypted, then you have to look at some of the Teradata partner offerings to achieve that.
The “Secure Zones” feature is not expected to enhance the performance of either ETL or query processing. In fact, as stated above, it is possible for some processing (i.e. corporate level reporting) to suffer in this environment.
These rules are unique to the zone of the user (assuming it is not DBC) executing the BEGIN / REPLACE QUERY LOGGING command.
Because all zones are on the same physical Teradata platform, they are all subject to the same set of rules. (Personally, I think this is one area where future versions of the Teradata DBMS software will change).
In my opinion (and it is just that) the “Secure Zones” feature is aimed at an organization that wants to offer a ‘cloud bureaux service’ to customers so that those customers can take advantage of the Teradata features without having to invest in their system. Such customers will end up sharing a physical Teradata system with other completely independent organizations without them being aware of it.
Please don’t get me wrong; the above requirement is not ‘bad’ it is just (in my opinion) very specific, and I don’t think many organizations will have a real need for it.