Archives of the TeradataForum

Message Posted: Tue, 22 Apr 2014 @ 19:43:36 GMT

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

Was making significant progress with using Arrays as Columns, when I hit a bit of a problem granting Privileges to 'outside' Users.

All access to a particular Table (which has a Column defined as an Array) is via a Stored Procedure and I'd expected that, once the OWNER of the Stored Procedure had the required access to the Array Type - assuming With Grant Option was provided - then External User could piggy-back on this Privilege, assuming they only had Execute Privilege to the Stored Procedure.

Indeed that's how we do just about every other Privilege in our system.

So, I've got this Type 'Email_Address_A01', which is a 1-D Array.

I've then got a Table, with one of the Columns defined as Type 'Email_Address_A01'

The Stored Procedure received new entries (and updates) and then applies them to that Table - via a Teradata Queue, as it happens - by a series of Stored Procedures.

I was then expecting the following statement to provide that bridge between the User (with Execute Privilege on the Stored Procedure) and the Procedure itself (which makes the Insert/Update to the Queue Table) :

     GRANT UDTUSAGE ON TYPE SYSUDTLIB.EMAIL_ADDRESS_A01
     TO DEVTERAWRITE_X WITH GRANT OPTION;

('DevTerawrite_X' in this case is the owner of the Stored Procedure).

The Privilege statement did not fail, but still we got a Privilege issue when Executing the Stored Procedure (Sql Code 3523).

We also granted UDTUSAGE as well (as the description is "Allows you to USE, create, alter, and drop UDTs as well as create new methods or drop and replace existing methods."), just in case that was the clinching part of the Privilege - it wasn't.

It still SEEMED as though direct Privilege against SYSUDTLIB was required for each User that would execute the Stored Procedure.

So, my last suggestion was to allocate it to the Group Name which sits above the Users :

     GRANT UDTUSAGE ON TYPE SYSUDTLIB.EMAIL_ADDRESS_A01 TO PRODEXTERNAL_USERS

(with Allness Flay set to 'Y') i.e. no With Grant Option required.

Any particular Users defined with PRODEXTERNAL_USERS would then hopefully get that same Privilege.

But ..... I was TOLD (not seen it for myself) that this would also mean that each User under ProdExternal_Users would then also obtain Create/Drop Table against those objects which reference the particular Array.

So ...

1. Does that make sense to anyone and are we right in our understanding ?

2. What does 'With Grant Option' then actually mean when applied to the Grant statement above ? (as it doesn't seem to ?work like a Proxy Privilege, as least in the way that it normally does)

3. What other options have we got ?

As usual, thank you for any shared experience or insight provided.

Regards

David Clough
Senior BI Database Designer
BI Competency Centre

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