Grant Access on multiple tables

Database
New Member

Grant Access on multiple tables

Is it possible to do this in Teradata 14.10 without procedurally going through every table I've created and executing a grant statement?

 

I have a script that will run and produce all the grant statements automatically, but I still have to copy all of them and run them. I'm hoping there's a way to cut out the middle step.

 

 

The query to auto-generate grant statements, in case anyone wants it for their own use.

 

SELECT 'grant select,insert,update,delete,'

||(CASE WHEN tablekind = 't' THEN 'DROP TABLE' WHEN tablekind = 'V' THEN 'DROP VIEW' END)||' on '||TRIM(databasename)||'.'||TRIM(TABLENAME)||' to [user] with grant option;'

FROM dbc.tables

WHERE creatorname = (Select User)

ORDER BY databasename, TABLENAME;

1 REPLY
rjg
Supporter

Re: Grant Access on multiple tables

Ru_Hickson,

 

You could use BTEQ,

Export the results of your SQL to a file then use .run in the same script to run the file.

 

Rglass