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.
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.
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
Another option to try is the SQLCreator as this won’t need the Creator to run.