Teradata Secure Zones
I’ve had a few conversations recently with different customers about the “Secure Zones” optional feature added in Teradata 15.10 based on those. There is a fair amount of misunderstanding about what this feature offers and does.
This article aims to provide some clear information for anyone considering using this feature.
Before I cover that, let me describe the basic setup of Secure Zones on a Teradata system and what they offer 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 set in that zone.
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 entirely 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 view DBC.TablesV_SZ (and not DBC.TablesV as coded). It is this view that 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 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.), 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, each country may have a separate secure zone containing databases, tables, users, etc. The data and users within each zone are specific to a country.
Assume an ‘Orders’ table exists in each zone, possibly with the exact definition. The ‘Orders’ table in each zone is a different physical table – because they are in other databases.
Therefore if there is a requirement to produce a ‘corporate-level’ Orders report spanning multiple countries (i.e., zones), the SQL has to reference various tables. Yes, that element may be hidden inside a view, but the underlying processing still requires access to various tables. Whether those tables are joined or UNION’d together is a different question and relies on the business requirement. Either solution will likely provide slower performance than all of the data in a single physical table.
Similar data segregation (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 can access the data, and corporate-level reporting requires access to multiple tables. The significant 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, only one corporate-wide Orders table (as an example) 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 widespread solution to this type of requirement. The ‘Orders’ data is stored in a single physical table. A series of ‘country-specific’ views are defined, each with an appropriate WHERE clause such that only data for a particular country is returned. A user is given access to a consistent view of their country.
- Security Table: Alternatively, a single view joins a ‘security table’ that identifies which country the user should see data.
Whether any alternate strategies provide sufficient data security for an organization is a separate question and the only one the organization can answer.
The data in a “Secure Zone” table is not encrypted; it is stored like any other data in the Teradata DBMS. If you need your data encrypted, look at some Teradata partner offerings to achieve that.
The “Secure Zones” feature is not expected to enhance ETL or query processing performance. 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 user’s zone (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. (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 customers a ‘cloud bureaux service’ 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 independent organizations without being aware.
Please don’t get me wrong; the above requirement is not ‘bad’. It is just (in my opinion) particular, and I don’t think many organizations will have a real need for it.
Thanks for the article but i have one question :
Can we have two different zoneid for the same queryid.
Thanks, David for this interesting article on secure zones