All Forums Database
jmwildenthal 3 posts Joined 04/11
24 Jan 2014
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?

Fred 1096 posts Joined 08/04
28 Jan 2014

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.

jmwildenthal 3 posts Joined 04/11
29 Jan 2014

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....

jmwildenthal 3 posts Joined 04/11
29 Jan 2014

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....