Archives of the TeradataForum
Message Posted: Fri, 06 Jan 2006 @ 11:28:11 GMT
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.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|