Menu Request Demo

Multiple Databases and Teradata Grants

Date:
30 January 2009
Author:
Jason Laws

In the simplest kind of data warehouse environment, the WhereScape RED metadata and all data warehouse tables exist in a single database or user.  A Teradata user is preferred (over a database) as the metadata includes some procedures that use dynamic SQL.  Procedures containing dynamic SQL may only be compiled under a user in Teradata.

For the simplest kind of environment, a user called dssdemo can be created and granted sufficient privileges to get started with WhereScape RED using this SQL:

CREATE USER dssdemo FROM dbc AS
PERMANENT = 100000000, SPOOL = 100000000, TEMPORARY = 100000000
,PASSWORD = wsl;
GRANT ALL ON dssdemo TO dssdemo WITH GRANT OPTION;
GRANT SELECT ON DBC.TVM TO dssdemo;
GRANT SELECT ON DBC.Dbase TO dssdemo;
GRANT SELECT ON DBC.ErrorMsgs TO dssdemo WITH GRANT OPTION;

The grants in this example may be considered excessive, but are simple to achieve.  The worst case scenario results in the user being dropped by another user they’ve on-granted some privilege to.

In a more complex example, a Database called Test_Grants owns the development data warehouse.  There are two developers, called John and Mary.  Test_Grants has three users:
- john
- mary
- metadata

The users john and mary are used by John and Mary to log into Teradata using WhereScape RED.  The user metadata owns the WhereScape RED metadata and all compile stored procedures generated by WhereScape RED.  The WhereScape metadata is installed and upgraded whilst signed in as metadata.

John has four databases for storing tables and views:
- john_load
- john_stage
- john_model
- john_view

Similarly, Mary has four databases for storing tables and views:
- mary_load
- mary_stage
- mary_model
- mary_view

Here is the database/user structure:

Once created, the metadata user has the following grants on itself:

These are insufficient for WhereScape RED and need to be changed to:

Note: a tick indicates a privilege has been granted, a green circle indicates a privilege has been granted WITH GRANT OPTION.

Each developer and the metadata user also require the following grants:

1. GRANT SELECT ON DBC.TVM
2. GRANT SELECT ON DBC.Dbase

The metadata user also required the following grant:
3. GRANT SELECT ON DBC.ErrorMsgs WITH GRANT OPTION

The first two grants are required by WhereScape RED to compare physical tables and metadata definitions when Validate against database is used.  In all cases, WhereScape RED includes LOCK ROW FOR ACCESS in SELECTs from these two tables.

The third grant is given to all users by default without the WITH GRANT OPTION.  WhereScape RED generated stored procedures use the DBC view ErrorMsgs to look up error message details.  In order for the executer of stored procedures be able to query DBC.ErrorMsgs, the metadata user must be able to on-grant select on DBC.ErrorMsgs.

As mentioned above, metadata installation and upgrade (validation) must be done as the metadata user.  For the ODBC connection DemoTDAT (in this case also with a TDPID of DemoTDAT), the following information is entered to install the WhereScape RED metadata:

The developers (John and Mary) also need some grants.  These examples work thru the details for the john user.  Everything also needs to be repeated for other developers.

The john user needs to be able to:
1. Read, write and delete data in the WhereScape RED metadata tables (in the metadata user).

2. Create, replace and drop stored procedures in the metadata user.
3. Create and drop tables in his four development databases.
4. Read, write and delete data in his four development databases (directly and via stored procedures compiled in the user metadata).

Grants to enable “Read, write and delete data in the WhereScape RED metadata tables” and "create, replace and drop stored procedures in the metadata user" (grants from metadata to john):

Grants to enable john to create and drop tables in his four development databases and to directly read, write and delete data in his four development databases:

Grants to enable john to read, write and delete data in his four development databases via stored procedures compiled in the user metadata:

Grants to enable john to create and drop views in the john_view development database and to directly read, write and delete data in his model database (john_model):

 

Note: It may be advisable to use database roles rather than direct grants.

Once these privileges have been added, sign into WhereScape RED as john, with the Meta Database set to metadata:

If required, create projects for each developer:

Then ensure each table or view is created in the correct database:

 

This approach can be expanded to the most complex scenarios, including multiple staging databases, etc.

CATEGORIES:

Comments 0

Leave a Comment

No comments.