Click here to Skip to main content
15,867,921 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am using the query band so the user can connect through the proxy user to the data pool in Teradata. Proxy users have full access to the table. How can I force the external users to see the selective columns?




What I have tried:

<pre>SET QUERRY_BAND='PROXYUSER=TheAdmin; PROXYROLE=RoleOne;' FOR TRANSACTION;
SELECT Employee_Id, First_name, Last_Name FROM Project.myTest;


This query work but the problem is if the external user use

SELECT * FROM Project.myTest;


It returns the whole table. I want that user should not be able to see all the columns.

I don't want to use the view as I have a big DB and there I have to create so many views in each table. Is there any way to do that? Will be great help and thanks in advance.
Posted
Updated 2-Mar-22 4:40am
Comments
Maciej Los 21-Feb-22 11:18am    
I'm afraid it's impossible. You can grant access to the entire table, but you can't disallow access to the single column.
Andre Oosthuizen 22-Feb-22 3:25am    
I don't know Teradata and it's limitations at all but is there a way you can limit the column names and not allow (*) SELECT ALL maybe?

1 solution

Views are designed to do what exactly you want so use them.

You only need to create one view per table with the columns that you want exposed. You can then control access to the views rather than the tables.
For further details see Teradata Online Documentation | Quick access to technical manuals[^]

You can automate the creation of the views by using something like this to get a list of the tables
SQL
SELECT  TableName,
FROM    DBC.TablesV
WHERE   TableKind = 'T'
and     DatabaseName = 'yourDatabaseName'
ORDER BY    TableName;
and then for each of those something like
SQL
SELECT ColumnName 
FROM dbc.columnsV
WHERE DatabaseName = 'yourDatabaseName' and
TableName = 'table_name_from_above';
Generate the script for all columns and then just delete the ones you want to omit from the view - you could use any scripting language for that - even Excel!
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900