SELECT * - failing due to column access controls?

Database

SELECT * - failing due to column access controls?

Hello!

I have 'R' rights on <table> from two roles.  The first role gives me access to 41 of 57 columns.  The second role gives me access to the other 16 columns.  When I run "SELECT TOP 10 * FROM <table>" I get "3523: The user does not have SELECT access to <table>".  If I replace "*" by enumerating the 57 columns, I get 10 rows.

Why won't "SELECT *" work?  Or, what did my employer's DBAs choose that keeps "SELECT *" from working?

4 REPLIES
Teradata Employee

Re: SELECT * - failing due to column access controls?

As you discovered, "SELECT *" looks for an access rights row for 'All' columns. The "*" is not expanded to a list of column names until after the access rights check.

Re: SELECT * - failing due to column access controls?

Then how do I make an enhancement request that the expansion occurs prior to the access rights check?  Given that I'm sure my employer is not the only firm that has migrated from other RDBMS (Oracle, DB2, SQL Server) that don't behave this way, my low expectations on this "feature" changing are probably appropriate.

Today I was told that the workaround is to generate a view with the same name in my personal schema that has only the columns I can access.  Thankfully, we are provided with enough information that I can automate that....

Re: SELECT * - failing due to column access controls?

Then how do I make an enhancement request that the expansion is based on access rights?  Given that my employer surely is not the only firm that has migrated from other RDBMS (Oracle, DB2, SQL Server) that don't behave this way, my low expectations on this "feature" changing are probably appropriate.

Today I was told that the workaround is to generate a view with the same name in my personal schema that has only the columns I can access.  Thankfully, we are provided with enough information that I can automate that....




Teradata Employee

Re: SELECT * - failing due to column access controls?