Secure Zones Implementation Example | Teradata Vantage - Example Scenario - Advanced SQL Engine - Teradata Database

Suppose that you want to create two zones for an automotive company to use, one for the sales staff and one for the service personnel.

First, you need to create a zone creator. The DBC user, for example, can create a user named Dana and grant that user the privileges that a zone creator needs, as follows:

CREATE USER dana ;
GRANT CREATE ZONE, DROP ZONE TO dana WITH GRANT OPTION ;

Dana can now create two databases on which to create the zones, as follows:

CREATE DATABASE jcl_sales FROM dana AS PERM=100000 ;
CREATE DATABASE jcl_service FROM dana AS PERM=100000 ;

Now Dana creates the two zones. The first command creates the zone and assigns the ROOT directly. The second command creates the zone and then uses an ALTER statement to assign the ROOT.

CREATE ZONE jcl_sales_zone ROOT jcl_sales ;
CREATE ZONE jcl_service_zone ;
ALTER ZONE jcl_service_zone ADD ROOT jcl_service ;

Dana then creates a primary DBA for each zone, as follows:

CREATE USER jcl_sales_dba FROM jcl_sales
AS
  PASSWORD=jcl_sales_dba
  PERM = 0 ;
CREATE USER jcl_service_dba FROM jcl_service
AS
  PASSWORD=jcl_service_dba
  PERM = 0 ;

Now the zone DBAs can create zone users and grant rights to them, as follows:

CREATE USER jcl_sales_bob FROM jcl_sales
AS
  PASSWORD=jcl_sales_bob
  PERM = 0 ;
GRANT ALL ON jcl_sales TO jcl_sales_bob WITH GRANT OPTION ;
CREATE USER jcl_service_bill FROM jcl_service
AS
  PASSWORD=jcl_service_bill
  PERM = 0 ;
GRANT ALL ON jcl_service TO jcl_service_bill WITH GRANT OPTION ;

The new users can create tables in their respective zones, as follows:

CREATE TABLE jcl_sales.Auto_Inv (Vin_no INT, Auto_Desc Varchar(25));
CREATE TABLE jcl_service.Part_Inv (Part_no INT, Part_Desc Varchar(25)) ;

The zone creator can create users to be made as zone guests and grant them access to the zones, as follows:

CREATE USER jcl_sales_guest FROM dana
AS
  PASSWORD=jcl_sales_guest
  PERM = 0 ;
GRANT ZONE jcl_sales_zone TO jcl_sales_guest ;
CREATE USER jcl_service_guest FROM dana
AS
  PASSWORD=jcl_service_guest
  PERM = 0 ;
GRANT ZONE jcl_service_zone TO jcl_service_guest ;

(Or the zone creator could use GRANT ZONE to make existing users into zone guests.)

A zone user, in this case the zone DBA, grants the zone guests the rights needed to access objects in the zone, as follows:

GRANT SELECT ON jcl_sales.Auto_Inv TO jcl_sales_guest ;
GRANT SELECT ON jcl_service.Part_Inv TO jcl_service_guest ;