Is there any way to get the user (not the role) that created a view? I tried desc, show, grants on view but couldn't get the specific user
1 Answers
The INFORMATION_SCHEMA.QUERY_HISTORY* table-functions have a USER_NAME column, and you can filter on the QUERY_TEXT to find the view's CREATE-statement.
This should work unless the views were created with a variable. For example: CREATE OR REPLACE VIEW IDENTIIER($MY_VAR)... where MY_VAR is a variable containing the name of the view.
UPDATE
INFORMATION_SCHEMA.QUERY_HISTORY table-functions return results for the past 7 days. Also see the ACCOUNT_USAGE.QUERY_HISTORY table functions, which return results for the past 365 days, with a 45 minute lag.
UPDATE 2
You can also use conventions such as:
- Give each user their own schema, composed from their name or initials
- Ask users to put their name or initials in the view COMMENT
- Or ask users to prefix or suffix their views with their initials
If the views are created with a script or a stored procedure, then you can automate the above.
Lastly, while it's not a best practice, you can in fact create a separate role for each user. This however creates a lot of administrative overhead, especially as you scale, and is not recommended.
Related