Archives of the TeradataForum

Message Posted: Fri, 09 May 2008 @ 13:39:52 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi Pete:

You asked:

Although there are no parameters to views, what you could do is put the userid into a table for all users who are allowed to access the view. Then, in the view, you could do an EXISTS subquery using the reserved word "USER" to the table. Since you probably have this requirement for more than one view, you could put both the userid and the viewname into this table:

     ct authorize_table (viewname varchar(30), userid varchar(30))
        unique primary index(viewname, userid)

     sel * from viewname
     where exists (sel * from authorize_table
                     where viewname='viewname' and userid=user)

It does a single AMP/single row retrieve because of the unique PI and then duplicates it on all AMPS. It is followed by a join of this row to your view/table with a condition of (1=1). However, it is only one row and adds the control you indicate is needed in your view. Again, the viewname is only required if you have this same need for more than one view. Otherwise, only the userid is needed.

The alternative is to put the userids into an IN list, but if you have several views with this requirement, you would need to replace each of them every time a new user needed access. This can get really ugly really fast. With a table, it is an insert or a delete when a change is needed for a user.

Just a thought,

Michael Larkins
Certified Teradata Master
Certified Teradata SQL Instructor

	<Prev	Next>		<<First	<Prev	Next>	Last>>