Row Level Security View

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Enthusiast

Row Level Security View

Hi, Everyone, I have a problem about RLS implement. We have a experiment table and a experiment view. The users could only access the view, and the view can access the table. We try to add a RLS constrait that the users can only access the rows which belong to their project by the view. Question: Could the RLS Constraint still work through the view?

Accepted Solutions
Teradata Employee

Re: Row Level Security View

Is RLS (using RLS CONSTRAINT) enforced even through access is through a view that might appear to give full access? Yes. 

 

How can "row-level security" be implemented without CONSTRAINT feature?

Some sites have one or more "security tables" that map users to security "tags" of some sort. The views typically retrieve rows for the current user from the security table and join - perhaps based on hierarchy/dimension fields already present in the tables, or perhaps using some "tag" column(s) added to the data rows. For SELECT-only access, this is often sufficient.

 

1 ACCEPTED SOLUTION
8 REPLIES 8
Teradata Employee

Re: Row Level Security View

It's not entirely clear what you are asking.

If you define a security CONSTRAINT on the table, then it will be applied whether access is directly to the table or through a view.

A view can do additional filtering, of course, including some things that might be termed "row-level security" (but do not use the RLS CONSTRAINT mechanism).

Enthusiast

Re: Row Level Security View

Hi, Fred. Thank you fro you reply. ⇒Is it applied through a view? RLS are not supported on our system, I can not test it right now. Example: Table: Add a constraint colum. View:Replace view as select * from table. User: Create User with constraint. Privilege: Grant select on View to User, Grant Select on Table to View with Grant. My Question: The RLS is still worked? ⇒If not use RLS mechanism, How could the filtering worked? Example: Table: No change. Security Table...
Teradata Employee

Re: Row Level Security View

Is RLS (using RLS CONSTRAINT) enforced even through access is through a view that might appear to give full access? Yes. 

 

How can "row-level security" be implemented without CONSTRAINT feature?

Some sites have one or more "security tables" that map users to security "tags" of some sort. The views typically retrieve rows for the current user from the security table and join - perhaps based on hierarchy/dimension fields already present in the tables, or perhaps using some "tag" column(s) added to the data rows. For SELECT-only access, this is often sufficient.

 

Enthusiast

Re: Row Level Security View

Hi, Fred, Thank you for your reply. It is really helpful. I can understand that we can use view for select-only access instead of constraint. Can you explain "Some sites have one or more "security tables" that map users to security "tags" of some sort." with more details? Do you mean I can retrieve the rows for the current user as below? REPLACE VIEW XXXX AS SELECT * FROM BASED_TABLE INNER JOIN SECURITY_TABLE ON SECURITY_TAG_COLUMN WHERE SECURITY_TABLE.USERS ='SELECT CURRENT_USER'?
Teradata Employee

Re: Row Level Security View

That's the general idea, though the filter condition would just be something like SECURITY_TABLE.USERS = CURRENT_USER (no SELECT needed, and no quotes).

Highlighted
Enthusiast

Re: Row Level Security View

Thank you very much, the view works well. But another question about the RLS constraint. ① I think the constraint should not be changed as possible as we can, so it always named like the country name or security level. If I want to do the row level security , which is constrainted by the project code. (The project code maybe changed every month. ) RLS constraint is not worked well , isn't it ? ② When a new record is added, a default constraint value is also added to the table . DBA user (with override privilege) have to modify the constraint value Everytime ? Could I add a logic to the default constraint value In the UDF ? Thank you for your help.
Teradata Employee

Re: Row Level Security View

True, you would want security classification categories / user & row labels to change only rarely.

 

For an INSERT or UPDATE, the row label comes from the session; you don't modify the value in the UDF (just accept or reject the access). Typically it is a "trusted" load user rather than the DBA that will set the values.

Enthusiast

Re: Row Level Security View

Thank you for your reply. For an SELECT or DELETE , the row label comes from the DBA , isn't it .