Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Fri, 06 Jan 2006 @ 11:28:11 GMT

  <Prev Next>   <<First <Prev

Subj:   Re: Row Level Security in Teradata??
From:   Ray, Edmond


I have implemented row level security at several Teradata sites.

It is not a feature of the database, like it is in other databases.

In Teradata you can create views to implement row level security.

Basically you create base table in one database. Then create a security table that contains the rules for who can see certain rows. For example user JoeSmith can see customer records from the state of Iowa. In this case the security table would contain userid and state code. If you wanted to grant access by role then the security table would contain roleID and state code.

Finally you create a database with views in it the replicate the table with a join to the security on state code and a where clause of:

WHERE userid = USER (NOTE: USER is an internal function of Teradata that returns the current username)

For the second example above the where clause would be:

WHERE roleid = ROLE (ROLE is another internal function that; you guessed it, returns the current rolename)

The create statement for the view is something like:

     REPLACE VIEW ViewData.Customers AS
     LOCK TABLE BaseData.Customers FOR ACCESS
     LOCK TABLE BaseData.SecurityTable FOR ACCESS
     SELECT CUST.* FROM BaseData.Customers CUST
     JOIN BaseData.SecurityTable SEC
       ON CUST.StateCd = SEC.StateCD
     WHERE UserID = USER;

You create join views for any table that needs to be secured and you grant users access to the view database and not the base table database. This way they can only access the data with the security in place. For tables that don't need to be secured the view is simply a SELECT * FROM tablename.

I have used this method and it works. There is a performance hit for the join to the security table but it should not contain many rows and the WHERE clause filter usually makes the spool table small enough to be replicated across all amps and performs well.

Ed Ray

  <Prev Next>   <<First <Prev
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 28 Jun 2020