By Deiby Gómez
Wondering how to quickly identify Snowflake ACCOUNTADMIN privileges and the users or roles that hold them? This article will show you how to easily identify them using Prismafy, a free tool that generates detailed reports in just a few minutes. Prismafy supports external authentication, usernames and passwords, and MFA, making it secure and user-friendly for your Snowflake environment.
You can read more about Prismafy in the documentation.
How to Use Prismafy to Identify Snowflake ACCOUNTADMIN Privileges
What is Prismafy? It’s an open-source tool developed in Python to analyze metadata for cloud-native data platforms. It provides charts and insights helping you detect and fix problems quickly.
How to Use Prismafy to Analyze a Specific Query
Three simple steps:
- Download Prismafy: https://github.com/prismafy/prismafy
- Run Prismafy, specifying only the “Security” Section:
python prismafy.py -d snowflake -t password -a abc.us-east-2.aws -w Prismafy_wh -u Prismafy_user -m 6 -r Prismafy_role –s E
NOTE: Section “E” is for Security.
- Open the “prismafy-reports/prismafy-<date>/prismafy_index.html” report.
In the “Users with highest privileges” report, you will find all the information about which users and roles received the ACCOUNTADMIN and SECURITYADMIN roles, along with the granting path to show how these roles were assigned over time. For example, in the following image, you can see that the “TERMINATOR-T1000” user received the ACCOUNTADMIN role directly on “2021-12-02” and indirectly on “2024-07-25” via some roles.
The “Security” section of Prismafy also includes the following reports:
History failed logins:
This report contains all session attempts in Snowflake, providing the date of failure and an error description. This is useful to detect if someone is trying to access your Snowflake account.
Last month new logins:
In this report you can quickly detect new users who accessed your Snowflake account for the first time in the last month. This is useful to detect intruders.
Last week new logins:
In this report you can quickly detect new users who accessed your Snowflake account for the first time in the last week. This is useful to detect intruders.
Last day new logins:
In this report you can quickly detect new users who accessed your Snowflake account for the first time in the last month. This is useful to detect intruders.
Less frequent logins:
This report is useful to detect infrequent users, which may exhibit suspicious behavior. In the middle of hundreds of thousands of sessions in your Snowflake account, it would be difficult to detect these infrequent intruders.
Recent changes on network rules:
This report will list all the network rules that recently have received changes.
Recent changes on password policies:
This report will list all the Password Policies that recently have received changes.
Recent changes on masking policies
This report will list all the Masking Policies that recently have received changes.
Recent changes on row access policies:
This report will list all the Row Access Policies that recently have received changes.
Users with recent password changes:
This report will list all the Users who have recently changed their password. This is important to detect a password that recently was stolen by an intruder. After an intrusion, the password was reverted to the original password to “hide” the intrusion.
Sessions by authentication method per month:
This report is important to understand which authentication method is the most used by your users, per month. We might assume that all the users are using External Authentication, for example, when actually there are still some users using User & Password.
Sessions by authentication method for last week
This report is very important to understand which authentication method is the most used by your users, per every day in the last week. Wrongly we would think that all the users are using External Authentication, for example, when actually there are still some users using User & Password.
For example in the following image, we can identify that on “08/12/2024” and “08/13/2024” there are few sessions using “OAuth”, while the rest of the day, the sessions were opened with an User & Password.
Last month top logins by users:
This report will highlight users with the highest number of sessions. This can help detect DoS attack intents.
For example, in the following image, the User “Samus” has two days with a very high number of sessions compared to the other users. Doesn’t seem suspicious? 🤨
Changes on IP used for logins:
Snowflake is mainly used for Data Lakes, Data Lakehouses, or Data Warehouses, and in these scenarios, we usually have task or jobs, that almost always open sessions towards Snowflake from the same IP. So, detecting those users whose IP addresses changes unexpectedly is important. This report will highlight those users with IP changes.
Changes on client driver used for logins
Similar to the previous report (Changes on IP used for logins), tasks and jobs running in snowflake usually don’t change the Driver used to open a session. If a job was scheduled to run from Python, it would be suspicious to see the same user being connected to Snowflake from the Web GUI. This report will highlight those users with changes on the Driver.
Next up in my Snowflake series: How to Analyze a Warehouse in Snowflake
Resources
- The Definitive Guide to Analyzing Queries in Snowflake
- How to Detect Query Profile Changes in Snowflake
- Snowflake-related blogs
- Snowflake Developer Resources
Deiby Gómez was the first Oracle ACE Director of Guatemala (2015-2023). He holds 35 technology certifications, including the highest certifications for Oracle Database (Oracle Certified Master, OCM) and 3 Advanced Snowflake Certifications (Architect, Administrator, and Data Engineer). An engineer with 2 master’s degrees and a future lawyer, Deiby has been a speaker at technology events in more than 12 countries, including Oracle Open World in San Francisco, USA. He is the technical reviewer of the book Oracle Database 12cR2 Multitenant – Oracle Press and co-author of the book Oracle Database 12cR2 Testing Tools and Techniques for Performance and Scalability – Oracle Press.
Ready to unlock the full potential of your data? Our experts are here to help. Send us a message and see how we can transform your data into actionable insights.
