0
0
During this post we will discuss how the OWNERSHIP of Object behaves when Custom Role Renames to New Name in your account. Recently Business has asked us to explore the impact on the underlying Objects if the existing ROLE Name gets change with new Name. As per the current structure of application there are some Custom Roles available in the account. With the use of Custom Role, User has created multiple objects and hence owns the OWNERSHIP on these objects.
As per the current scenario, some Hierarchical structure changes are going on inside the organization. To be more business aligned, Customer wants to change the name of existing role with functional role that implies the Business domain. At first glance, changing the Role name to new name is very simpler and straight forward process. But before implementing the process we want to ensure that this will not break any role and object relationship. So we have below few questions to proceed ahead with our changes:
- Will the new Role owns the OWNERSHIP Privileges on the existing objects govern by old role.
- Should we explicitly align the objects to the new role.
- What would happen to the default role assigned to USER.
- How the Masking policies will work with new Role name.
Technical Implementation:
I tried to replicate a similar scenario in this post and we will find the answers of above questions.
We have SUPER_ROLE in our account and has governed the following objects:
Database (XEROX_DB),Schema, Tables, Stages, File Formats and Pipes.
Below is the masking policy:
CREATE OR REPLACE MASKING POLICY EMPLOYEE_PII_PHONE
AS (VAL NUMBER) RETURNS NUMBER ->
CASE
WHEN CURRENT_ROLE() IN
('SUPER_ROLE') THEN VAL
ELSE '000000000'
END;
ALTER TABLE EMPLOYEE MODIFY COLUMN PHONE SET MASKING POLICY EMPLOYEE_PII_PHONE;
Rename the SUPER_ROLE to XEROX_ROLE.
ALTER ROLE SUPER_ROLE RENAME TO XEROX_ROLE;
Now verify the ownership on the objects. Does it still points to the older role or change to the new Role.
As we can see the OWNERSHIP change automatically. Why so, Because OWNERSHIP by roles has stronger association with the object under-the-hood of Snowflake and hence changes are done automatically.
Default Role and Masking:
Now next concern, what happens to the default role assigned to the User.
As per the below screenshot it confirms that User still ties to the older Role which is no longer exists. So running the SQL with older Roles does not allow and need to select the XEROX_ROLE.
Login to the ACCOUNTADMIN and set the Default Role like below:
ALTER USER SUPER_USER SET DEFAULT_ROLE = XEROX_ROLE;
Final Question comes, what happens to the Masking Policies. Does the new Role able to see the Masked data as per the Masking Rule.
As per the below screenshot it clears that XEROX_ROLE not able to see the actual content of column. Masking Policies still points to the older Role.
So it is required to update the Masking policy definition with new Role.
ALTER MASKING POLICY EMPLOYEE_PII_PHONE SET BODY ->
CASE
WHEN CURRENT_ROLE() IN
('XEROX_ROLE') THEN VAL
ELSE '000000000'
END
