Teradata – Secure Zones

Secure Zones

This feature supports grouping of users, databases, and database objects into Secure Zones. Business Value This feature provides the following business value: Improves total cost of ownership for systems where data from multiple applications must be completely segregated. Addresses cloud/multi-tenancy security requirements for privileged user access and data segregation. Addresses regulatory compliance requirements to separate administration duties from access control. Provides companies that are multi-national the ability to prevent data from crossing boundaries (geographical, national, divisional, etc.). Technical Overview Background information Secure Zones supports grouping of user/database hierarchies into secure zones and restriction of user access to database objects within a secure zone Consolidation of multiple tenants into one instance of a database system Isolation of tenants from each other as if they were running on physically segregated databases Users within a tenancy have no access or visibility to objects within other tenancies Secure Zones addresses regulatory compliance requirements to separate administration duties from access control. Data within a zone is secure from being accessed by the database or zone administrators. Tenant Zones are managed by a system-level Zone Administrator Administration of objects within a tenancy is done by tenant DBAs as required. When to Use This Feature There are two use cases for this feature. In particular, it addresses multi-national companies and multi-tenancy configurations, such as Cloud. (See example use cases later in this document) Requirements This feature must be enabled by a Teradata Customer Service representative. Limitations The following limitations apply: COPY operations on zoned objects are not supported. Nested zones are not supported. Installation & Configuration This feature must be installed and configured by a Teradata Customer Service representative. Usage To add secure zones there are new SQL Data Dictionary Language (DDL) additions, new SQL Data Control Languages (DCL) and other Data Dictionary changes. New DDLs: CREATE ZONE, ALTER ZONE, DROP ZONE New DCLs: GRANT ZONE, REVOKE ZONE Other changes: Extensions to existing CREATE USER, MODIFY USER syntaxes (See example use case 1 for more details) New system tables: DBC.Zones, DBC.ZoneGuests (added in Teradata Database 15.0) Feature Elements Zone A ‘zone’ is a secure area which restricts unauthorized outside users from accessing data and objects within the zone. A zone can be created by a database administrator who has the ‘Create Zone’ privilege. Zone Creator Zone creator creates a zone and assigns a user or a database as the zone root. Zone creator will be prevented from accessing the objects or data within the zone he creates. Rules concerning a zone creator are as follows: Only the zone creator can add a root and primary DBA to the zone. Only the zone creator can delink the root and primary DBA from the zone. Zone creator may grant zone access to users or roles outside the zone. Note: Zone grants do not allow grantees to access database objects within the zone. The zone’s primary DBA must also grant Discretionary Access Control (DAC) privileges to zone guests before access is permitted. Zone creator is also responsible for revoking zone access to a zone. Zone creator must be a user having ‘Create Zone’ and ‘Drop Zone’ privileges and additionally have all privileges on the user which will become a root of the zone. Zone creator cannot be dropped until the zone is dropped. Zone Root Zone root is a database or a user on which a zone is created. A zone creator creates a zone and associates a database or a user as its root. If zone root is a user, it will behave as primary DBA for the zone else (database as root) the zone creator will assign a primary DBA to the zone. The database or user being assigned as a zone root must be empty, i.e., when a user/database is made a zone root, it cannot have any objects/users/databases/roles/profiles associated with it. The root database or user is created with the existing ‘Create Database’ or ‘Create User’ DDL. Zone Primary DBA A zone primary DBA acts as the initial database administrator within the zone. The zone primary DBA is created or assigned by the zone creator. The primary DBA may create zone users/databases, objects, or appoint certain zone users as secondary DBAs by granting them zone-specific administrative privileges such as CREATE USER, CREATE DATABASE, DROP USER, and DROP DATABASE. Zone Member and Zone objects A zone member is a user/database created by a zone DBA, under the hierarchy of zone root. Zone members are created using the existing ‘CREATE USER/DATABASE’ syntax. Any object such as table, triggers, macro etc. created inside a zone is termed as zone object. Zone objects will be created, modified, or dropped using existing syntaxes. Objects not qualified by database names (such as roles, profiles, constraints etc.) are only accessible inside the zone where they are created. Zone Guest A zone guest is a role or user outside the zone who may be granted privileges to create/access objects in the zone where he is a guest. To make an external LDAP user as zone guest, an external role may be granted a zone and privileges to zone objects. If an external user logs on with that role, he will be able to access zone objects he has privileges on. Rules concerning a zone guest: Only zone members can grant privileges on database objects within the zone to zone guests. Zone members cannot grant privileges to the zone guest with “WITH GRANT OPTION”. A zone can have more than one zone guest. A user/role can be a guest of more than one zone. Zone guest with required privileges can create users, databases, and TVM objects inside the zone. Zone guest can create views, triggers, macros etc. on the zoned objects in their perm space. Zone guest doesn’t have the privilege to add another guest to a zone. A zone member cannot be a zone guest in another zone [when nested zones are supported in future, a zone guest may either be a non-zone user or a member of the outer zone]. Example use cases Case 1: Multi-national Company Company with multiple subsidiaries where access to subsidiary data must be restricted to users of the subsidiary or citizens of a specific country: Users within a subsidiary have no access or visibility to objects within other subsidiaries. Corporate-level users may have access to objects across any or all subsidiaries. Administration of objects within a subsidiary may be done by either corporate or subsidiary DBAs as required. Subsidiary zones are managed by a system-level zone administrator. The following steps illustrate the basic usage of the feature as applied to a Multi-national use case. SET DBSControl Internal Field 348 SecureZonesPurchased = T Run DIPSZ and DIPSZVIEWS scripts. DBC does the following: Create zone admin user. CREATE USER Zone_Admin AS PERM=xxx, PASSWORD=xxx; 1.        Grant CREATE/DROP ZONE to zone admin. GRANT ZONE to Zone_Admin; Note: Needs CREATE ZONE privilege to create zone. 1.        If DBC is zone admin, skip this step #2. DBC or DBA creates non-zone corporate-level users: 5.    CREATE USER User_CFO AS PERM=xxx, PASSWORD=xxx; CREATE USER User_CEO AS PERM=xxx, PASSWORD=xxx; Zone admin does the following: Create root users before creating zones. o   CREATE USER Root_Canada AS PERM=xxx, PASSWORD=xxx; CREATE USER Root_France AS PERM=xxx, PASSWORD=xxx; 1.        Create zones for Canadian and French subsidiaries. o   CREATE ZONE Subsid_Canada ROOT Root_Canada; CREATE ZONE Subsid_France ROOT Root_France; 1.        Root user space becomes the zone space. Root user becomes the primary DBA. Grants EXEC privilege on DBC.DBAUserAdmin macro to root users. GRANT EXEC ON DBC.DBAUserAdmin TO Root_Canada, Root_France WITH GRANT OPTION; Note: Execute on DBC.DBAUserAdmin is needed to assign/drop DBA attribute. Root user (primary DBA) creates secondary DBAs: CREATE USER DBA_User1 AS DBA, PERM=xxx, PASSWORD=xxx; [Note: DBA users may be created to restrict database administrators’ access to data. DBA users cannot perform DMLs (INS/SEL/UPD/DEL) on tables they create in another user’s (including children’s) space. They can access data only in tables they create within their own user space. To remove DML restrictions on a user: MODIFY USER DBA_User1 AS NOT DBA;] Primary and secondary DBAs create zone users, roles, profiles using existing DDL syntaxes. Zone users, roles, and profiles are zone-specific, i.e., they can access database objects or be assigned to users within the same zone only. Zone users create database objects (tables, views, etc.) with existing DDL syntaxes. Database objects created automatically become zoned objects, accessible only to users within the same zone, and to zone guests granted access. Set up zone guest access if necessary. Zone guests need 2 grants before they can access zoned data: Zone admin must grant zone to give guest status to a user. GRANT ZONE Subsid_Canada TO User_CFO; 1.        Primary DBA must grant Discretionary Access Control (DAC) privilege to give zone guest access to zoned objects. GRANT SELECT ON Root_Canada.AcctsTbl TO User_CFO; 1.        Note: Only non-zone users can be zone guests. If a corporate-level user needs to see all rows in DBC tables without zone restrictions, grant the user ZONE OVERRIDE privilege: GRANT ZONE OVERRIDE TO User_CEO; Only DBC is allowed to do the above grant. Note: Without this privilege, User_CEO can only access DBC table rows for his/her zone (null zone, i.e., non-zone user). With this privilege, User_CEO can access DBC table rows for all zones, i.e., Subsid_Canada, Subsid_France, and HQ (non-zone). Case 2: Multi-tenancy Consolidation of multiple tenants onto one instance of a database system while isolating the tenants from each other as if they were running on physically segregated databases. Users within a tenancy have no access or visibility to objects within other tenancies. Users within a tenancy cannot grant rights on any objects within the tenancy to other users or roles within the system. Administration of objects within a tenancy is done by tenant DBAs as required. Tenant zones are managed by Teradata. Data Dictionary changes New ZoneId column added to some DBC tables (added in Teradata Database 15.00) AccLogRuleTbl AccLogTbl ConstraintValues Dbase DBQLExplainTbl DBQLogTbl DBQLObjTbl DBQLParamTbl DBQLSummaryTbl DBQLStepTbl DBQLXMLLockTbl DBQLUtilityTbl EventLog Profiles Roles SessionTbl SecConstraints Secure Zones data dictionary views: Secure Zone constrained Views (all <view_name>_SZ views). Access is constrained by zone. When a non-zone user or a zone user does a select from a view, internally the view name is changed to <view-name>_SZ and information is returned from the constrained view. For example, if a zone user or a non-zone user does a SELECT from the QryLogXMLV view, they get information from the zone constrained view, QryLogXMLV_SZ. There are over 90 views that are affected by secure zones. Also, there are a few views specific to Secure Zones. They are; DBC.ZonesV[X] DBC.ZoneGuestsV[X] Considerations Database DBC archive includes DBC.Zones and DBC.ZoneGuests. Zoned data can be restored on another system as long as database DBC is already restored. Think of this as cloning a system for testing or other purposes. The Zone DBA is responsible for archiving their zone’s data. DBC archive will only include non-zone data. COPY is not supported in the first release. User access to data dictionary should only be done via DBC views. Direct access to DBC table by users should be discourages because data will not be constrained by zone. Sites that use Secure Zones must revoke SELECT privileges granted directly on DBC tables and grant access to DBC tables through views only. This prevents users from accessing information not related to their zones. Support Not supported / Incompatibility Moving zoned data from one system to another is not supported in Teradata Database 15.10. However, you can create (clone) a system. (See Considerations section). Potential Problems & Workaround SELECTs from DBC tables are internally filtered by user’s zone. Unconditional SELECTs may return subset of rows, not all rows. If rows appear missing in query result (e.g. in DBQL, access logging, AR table, etc.), it could be due to filtering. DBC or user granted Zone Override privilege can access all rows if query result needs to be verified. Query returns: *** Failure 3706 Syntax error: Secure Zones Security operations are not supported on this system. Site has not paid for feature, Internal flag 348 not enabled. Error Messages: 9861 – Cannot assign the user ‘<name>’ as zone root that has default database|role|profile. 9862 – ‘<name>’ is already part of another zone. 9863 – Cannot drop zone ‘<name>’ with root still associated. 9864 – A root must be defined for the zone ‘<name>’. 9865 – Root for zone ‘<name>’ already exists. 9866 – Cannot drop zone ‘<name>’ with a guest still associated. 9867 – <name> does not have access to <name>. 9868 – Zone creator ‘<name>’ cannot be a guest|root|administrator of the zone. 9869 – Zone ‘<name>’ does not exist. 9870 – Role ‘<name>’ cannot be a zone root. 9871 – Zone guest cannot be assigned as zone root. 9872 – Cannot delink root ‘<name>’ from zone ‘<name>’ with primaryDBA still associated. 9877 – Cannot drop ‘<name>’. User is still linked to the zone. 9873 – Ownership of ‘<name>’ can only be transferred within the zone. 9874 – Only the zone creator is allowed to alter|grant the zone ‘<name>. 9875 – Primary administrator is not assigned to the zone ‘<name>’. 9876 – The user or role {cannot be}|{is not} a zone guest. 9878 – Guest role cannot be a granted to zone creator directly or through nested role. 9879 – Concurrent change conflict on zone — try again. 9914 – Current user’s zoneid does not match zoneid of object being logged. 9918 – Zoned users cannot be defined as trusted users. 9927 – User ‘<name>’ and profile ‘<name>’ does not belong to the same zone. 9928 – A Zone with the name ‘<name>’ exists. 9929 – Recipient ‘<name>’ and role ‘<name>’ does not belong to the same zone. 9917 – Cannot specify a user or object in a different zone. 9919 – Constraint ‘<name>’ belongs to another zone. 9923 – A zoned object cannot be granted WITH GRANT|ALL OPTION to a non-zoned object. 9924 – Too many zones listed in GRANT/REVOKE statement. 9925 – A zone guest cannot be connected to zone creator directly or through nested role. 9926 – {Nested Zone}|{Zone-level logging of all users and all objects} is currently not supported. 9931 – Role(s) cannot be revoked from primary DBA of the zone. 9932 – A creator|guest cannot be given to a zone user. 9933 – CTControl privilege cannot be given to zoned user. 9934 – {Non-zone}|Zone user <name> cannot grant or revoke system level privileges to|from {non-zone}}zone user <name>. 9936 – <name> does not have access to zone of <name>. 9937 – Update all related dictionary entries because user has been moved into a zone. 9943 – Cannot revoke privileges on root or primary DBA from primary DBA.

Thanks,

Pankaj Chahar

+91-8802350184

Pankajchahar052@gmail.com