Skip to main content
Question

Snowflake - SHOW GRANTS TO ROLE syntax


susmar
Contributor
Forum|alt.badge.img+6

I want to display a list of tables/views that a specific ROLE has access to. I can do this in SQL with the command SHOW GRANTS TO ROLE TESTROLE. However, putting this SHOW command directly into a Snowflake Reader WHERE Clause generates an error (syntax error line 1 at position 270 unexpected 'GRANTS'.) and returns nothing in a SQL Executor transformer.  

Is anyone please able to advise either what the correct syntax is or if there is another approach/transformer that should be used? Thanks.

 

3 replies

steveatsafe
Safer
Forum|alt.badge.img+12
  • Safer
  • August 21, 2024

The above works for me in 2024.1.  Are you certain you are connecting as the same user in FME that works in the SQL (Snowflake Web UI)?  

You’ll want to connect a Logger transformer to the Result port and then the first 20 records (by default) will get written to the log for review (or turn on Feature Caching).  If you want to use the Attributes, you’ll need to expose them and that is easily done by using the same working query ‘SHOW GRANTS TO ROLE MYROLE_RL

Certainly won’t work in the WHERE CLAUSE of the Reader though.

 

 

 


susmar
Contributor
Forum|alt.badge.img+6
  • Author
  • Contributor
  • August 21, 2024

Using the SQL Executor as above has now worked. What seemed to make the difference was adding a Creator transformer before the SQL Executor, as running that on its own generated no result. Thanks for the assistance @steveatsafe 


steveatsafe
Safer
Forum|alt.badge.img+12
  • Safer
  • August 28, 2024

Another option to try is the SQLCreator as this won’t need the Creator to run.  


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings