A Snowflake Administrator created a role ROLE_MANAGED_ACCESS and a schema SCHEMA_MANAGED_ACCESS as follows:USE ROLE SECURITYADMIN;CREATE...

Snowflake ADA-C01 Question Answer

A Snowflake Administrator created a role ROLE_MANAGED_ACCESS and a schema SCHEMA_MANAGED_ACCESS as follows:

USE ROLE SECURITYADMIN;

CREATE ROLE ROLE_MANAGED_ACCESS;

GRANT ROLE ROLE_MANAGED_ACCESS TO ROLE SYSADMIN;

GRANT USAGE ON WAREHOUSE COMPUTE_WH TO ROLE ROLE_MANAGED_ACCESS;

GRANT ALL privileges ON DATABASE WORK TO ROLE ROLE_MANAGED_ACCESS;

USE ROLE ROLE_MANAGED_ACCESS;

CREATE SCHEMA SCHEMA_MANAGED_ACCESS WITH MANAGED ACCESS;

USE ROLE SECURITYADMIN;

GRANT SELECT, INSERT ON FUTURE TABLES IN SCHEMA SCHEMA MANAGED ACCESS to ROLE_MANAGED_ACCESS;

The Administrator now wants to disable the managed access on the schema.

How can this be accomplished?

ALTER SCHEMA SCHEMA MANAGED ACCESS DISABLE MANAGED ACCESS;

USE ROLE ROLE MANAGED_ACCESS;

DROP SCHEMA WORK. SCHEMA_MANAGED_ACCESS;

CREATE SCHEMA SCHEMA_MANAGED_ACCESS;

Then recreate all needed objects.

REVOKE SELECT, INSERT ON FUTURE TABLES IN SCHEMA SCHEMA_MANAGED_ACCESS FROM

ROLE_MANAGED_ACCESS;

ALTER SCHEMA SCHEMA MANAGED ACCESS DISABLE MANAGED ACCESS;

USE ROLE ROLE_MANAGED_ACCESS;

DROP SCHEMA WORK. SCHEMA MANAGED_ACCESS;

CREATE SCHEMA SCHEMA_MANAGED_ACCESS WITHOUT MANAGED ACCESS;

Then recreate all needed objects.

ADA-C01 PDF/Engine

Get 60% Discount on All Products, Use Coupon: "8w52ceb345"