Deal of The Day! Hurry Up, Grab the Special Discount - Save 25% - Ends In 00:00:00 Coupon code: SAVE25
Welcome to Pass4Success

- Free Preparation Discussions
Mail Us support@pass4success.com
Location US
Mob_nav_barsMENU

Snowflake ARA-R01 Exam - Topic 3 Question 31 Discussion

An Architect needs to grant a group of ORDER_ADMIN users the ability to clean old data in an ORDERS table (deleting all records older than 5 years), without granting any privileges on the table. The group's manager (ORDER_MANAGER) has full DELETE privileges on the table.How can the ORDER_ADMIN role be enabled to perform this data cleanup, without needing the DELETE privilege held by the ORDER_MANAGER role?
C) Create a stored procedure that runs with owner's rights, including the appropriate '> 5 years' business logic, and grant USAGE on this procedure to ORDER_ADMIN. The ORDER_MANAGER role owns the procedure.
A) Create a stored procedure that runs with caller's rights, including the appropriate '> 5 years' business logic, and grant USAGE on this procedure to ORDER_ADMIN. The ORDER_MANAGER role owns the procedure.
B) Create a stored procedure that can be run using both caller's and owner's rights (allowing the user to specify which rights are used during execution), and grant USAGE on this procedure to ORDER_ADMIN. The ORDER_MANAGER role owns the procedure.
D) This scenario would actually not be possible in Snowflake -- any user performing a DELETE on a table requires the DELETE privilege to be granted to the role they are using.

Snowflake ARA-R01 Exam - Topic 3 Question 31 Discussion

Actual exam question for Snowflake's ARA-R01 exam
Question #: 31
Topic #: 3
[All ARA-R01 Questions]

An Architect needs to grant a group of ORDER_ADMIN users the ability to clean old data in an ORDERS table (deleting all records older than 5 years), without granting any privileges on the table. The group's manager (ORDER_MANAGER) has full DELETE privileges on the table.

How can the ORDER_ADMIN role be enabled to perform this data cleanup, without needing the DELETE privilege held by the ORDER_MANAGER role?

Show Suggested Answer Hide Answer
Suggested Answer: C

This is the correct answer because it allows the ORDER_ADMIN role to perform the data cleanup without needing the DELETE privilege on the ORDERS table. A stored procedure is a feature that allows scheduling and executing SQL statements or stored procedures in Snowflake. A stored procedure can run with either the caller's rights or the owner's rights. A caller's rights stored procedure runs with the privileges of the role that called the stored procedure, while an owner's rights stored procedure runs with the privileges of the role that created the stored procedure. By creating a stored procedure that runs with owner's rights, the ORDER_MANAGER role can delegate the specific task of deleting old data to the ORDER_ADMIN role, without granting the ORDER_ADMIN role more general privileges on the ORDERS table. The stored procedure must include the appropriate business logic to delete only the records older than 5 years, and the ORDER_MANAGER role must grant the USAGE privilege on the stored procedure to the ORDER_ADMIN role.The ORDER_ADMIN role can then execute the stored procedure to perform the data cleanup12.


Snowflake Documentation: Stored Procedures

Snowflake Documentation: Understanding Caller's Rights and Owner's Rights Stored Procedures

by Renay at Apr 04, 2025, 01:38 AM

Limited Time Offer

25%

Off

Get Premium ARA-R01 Questions as Interactive Web-Based Practice Test or PDF

Contribute your Thoughts:

0/2000 characters
Submit Cancel
Annette
7 months ago
C makes sense, but I’m not convinced it’ll work as expected.
upvoted 0 times
...
Shala
7 months ago
I think D is correct, can't delete without privileges!
upvoted 0 times
...
Elroy
7 months ago
Option C seems like the best choice here.
upvoted 0 times
...
Belen
7 months ago
Wait, are you sure this can be done without DELETE rights?
upvoted 0 times
...
Daren
8 months ago
A is interesting, but I prefer B for flexibility.
upvoted 0 times
...
Crista
8 months ago
I'm leaning towards option A, but I worry that running with caller's rights might not give ORDER_ADMIN the necessary permissions to delete records.
upvoted 0 times
...
Fletcher
8 months ago
I feel like we practiced a similar question where we had to grant access without direct privileges, but I can't recall the exact details.
upvoted 0 times
...
Mertie
8 months ago
I think option C sounds familiar because it mentions running with owner's rights, which might be necessary to perform the DELETE operation.
upvoted 0 times
...
Daren
8 months ago
I remember we discussed stored procedures in class, but I'm not sure if they should run with caller's rights or owner's rights for this scenario.
upvoted 0 times
...
Krystina
9 months ago
Hmm, Option D is interesting. If it's truly not possible to achieve this in Snowflake, I'll need to make a note of that and explain my reasoning in the exam. I'll double-check the documentation to be sure.
upvoted 0 times
...
Lai
9 months ago
I think Option B might be the way to go. Allowing the user to specify the rights they want to use during execution could be a good solution. I'll need to make sure I understand how that would work in practice.
upvoted 0 times
...
Emerson
9 months ago
I'm a bit confused by the wording of the question. Does the ORDER_MANAGER role really need to own the stored procedure, or can the Architect create it and grant the necessary permissions? I'll need to review the details more closely.
upvoted 0 times
...
Tequila
9 months ago
Okay, let's see. I think the key here is to find a way to give the ORDER_ADMIN users the ability to clean up the old data without directly granting them the DELETE privilege. Option A sounds promising, but I'll need to double-check the details.
upvoted 0 times
...
Pamella
9 months ago
Hmm, this is a tricky one. I'll need to think carefully about the different options and how they might work in the exam setting.
upvoted 0 times
...
Nancey
1 year ago
Wait, is the ORDER_MANAGER role allowed to own the procedure in the first place? Seems like a potential security risk. I'd go with option B to be on the safe side.
upvoted 0 times
...
Emiko
1 year ago
Haha, D is a classic 'this is not possible' answer. At least the question is giving us a real-world scenario to work with.
upvoted 0 times
Donte
1 year ago
Haha, D is a classic 'this is not possible' answer. At least the question is giving us a real-world scenario to work with.
upvoted 0 times
...
Nada
1 year ago
B) Create a stored procedure that can be run using both caller's and owner's rights (allowing the user to specify which rights are used during execution), and grant USAGE on this procedure to ORDER_ADMIN. The ORDER_MANAGER role owns the procedure.
upvoted 0 times
...
Ressie
1 year ago
A) Create a stored procedure that runs with caller's rights, including the appropriate '> 5 years' business logic, and grant USAGE on this procedure to ORDER_ADMIN. The ORDER_MANAGER role owns the procedure.
upvoted 0 times
...
...
Shelba
1 year ago
Ah, I see. Option A looks like it would work too, but having the ORDER_MANAGER own the procedure is a bit sketchy. I don't know, I'm torn between B and C.
upvoted 0 times
Junita
1 year ago
Yeah, I see your point. Option B does provide more flexibility. Let's go with that.
upvoted 0 times
...
Kimberlie
1 year ago
I agree, but having ORDER_MANAGER own the procedure does seem a bit risky. Option B gives more control over rights during execution.
upvoted 0 times
...
Sherell
1 year ago
I think Option A is the best choice. It allows ORDER_ADMIN to clean old data without needing DELETE privilege.
upvoted 0 times
...
...
Louisa
1 year ago
I think option C is the way to go. Keeping the business logic in the stored procedure and running it with the owner's rights is a cleaner approach.
upvoted 0 times
Arletta
1 year ago
True, but having the business logic centralized in the stored procedure, as in option C, can also help maintain consistency.
upvoted 0 times
...
Reyes
1 year ago
That's a good point. Having that flexibility could be useful in certain situations.
upvoted 0 times
...
German
1 year ago
But wouldn't it be better to have the flexibility of choosing between caller's and owner's rights, like in option B?
upvoted 0 times
...
Lillian
1 year ago
I agree, option C seems like the most secure way to handle this.
upvoted 0 times
...
Effie
1 year ago
True, it's important to consider both security and organization when granting permissions.
upvoted 0 times
...
Carisa
1 year ago
That's a good point, but I think having the business logic in the stored procedure itself, like in option C, is more organized.
upvoted 0 times
...
Naomi
1 year ago
But wouldn't it be better to have the flexibility of choosing rights during execution, like in option B?
upvoted 0 times
...
Selene
1 year ago
I agree, option C seems like the most secure way to handle this.
upvoted 0 times
...
...
Rose
1 year ago
I'm not sure about option A. Maybe we should consider option B as well, since it allows the user to specify rights during execution.
upvoted 0 times
...
Apolonia
1 year ago
I agree with Nikita. Option A seems like the most secure and efficient solution for this scenario.
upvoted 0 times
...
Oretha
1 year ago
Hmm, option B seems like the best choice here. Giving the ORDER_ADMIN users the ability to choose which rights to use during execution is a pretty neat solution.
upvoted 0 times
Santos
1 year ago
I see your point. It's important to consider the level of control given to ORDER_ADMIN.
upvoted 0 times
...
Alease
1 year ago
True, but with option B, ORDER_ADMIN can have more control over the execution rights.
upvoted 0 times
...
My
1 year ago
But wouldn't option A also work since it grants USAGE on the procedure to ORDER_ADMIN?
upvoted 0 times
...
Tuyet
1 year ago
I agree, option B sounds flexible and secure.
upvoted 0 times
...
Maryann
1 year ago
I see your point. Having the ability to choose rights during execution can be beneficial in this case.
upvoted 0 times
...
Amina
1 year ago
True, option A could work as well, but option B gives more control to the ORDER_ADMIN users.
upvoted 0 times
...
Kayleigh
1 year ago
But wouldn't option A also work since it grants USAGE on the procedure to ORDER_ADMIN?
upvoted 0 times
...
Michel
1 year ago
I agree, option B sounds like a flexible solution for this scenario.
upvoted 0 times
...
...
Nikita
1 year ago
I think option A is the best choice. It allows ORDER_ADMIN to clean old data without needing DELETE privilege.
upvoted 0 times
...

Save Cancel