Snowflake roles are not able to see newly created Privileged database?

user_7095639 0
Viewed 67

I created a new Database (DB_COMMON) using ACCOUNTADMIN role and I grant ALL PRIVILEGES to other roles but I am not able to see newly created Database (DB_COMMON) using those roles. Am I missing something? Please guide.

Here is the complete code:

USE DATABASE ACCOUNTADMIN;

-- DATABASE CREATION
CREATE DATABASE IF NOT EXISTS DB_COMMON;


-- PERMISSION TO ALL THE FUTURE SCHEMAS
GRANT ALL PRIVILEGES ON FUTURE SCHEMAS IN DATABASE DB_COMMON TO ROLE DEVADMIN;
GRANT ALL PRIVILEGES ON FUTURE SCHEMAS IN DATABASE DB_COMMON TO ROLE QAADMIN;
GRANT ALL PRIVILEGES ON FUTURE SCHEMAS IN DATABASE DB_COMMON TO ROLE UATADMIN;
GRANT ALL PRIVILEGES ON FUTURE SCHEMAS IN DATABASE DB_COMMON TO ROLE PRODADMIN;


-- PERMISSION TO ALL THE FUTURE TABLES
GRANT ALL PRIVILEGES ON FUTURE TABLES IN DATABASE DB_COMMON TO ROLE DEVADMIN;
GRANT ALL PRIVILEGES ON FUTURE TABLES IN DATABASE DB_COMMON TO ROLE QAADMIN;
GRANT ALL PRIVILEGES ON FUTURE TABLES IN DATABASE DB_COMMON TO ROLE UATADMIN;
GRANT ALL PRIVILEGES ON FUTURE TABLES IN DATABASE DB_COMMON TO ROLE PRODADMIN;


-- CREATION OF SCHEMA
USE DATABASE DB_COMMON;
CREATE SCHEMA IF NOT EXISTS COMMON;

After these commands, if I switch to these roles (DEVADMIN, QAADMIN, etc) I am not able to see DB_COMMON Database. Am I missing something? Please guide.

snowflake-cloud-data-platform roles database-schema database
1 Answers

It is not recommeded to use ACCOUNTADMIN role as owner of user-defined databases:

Avoid Using the ACCOUNTADMIN Role to Create Objects:

The ACCOUNTADMIN role is intended for performing initial setup tasks in the system and managing account-level objects and tasks on a day-to-day basis. As such, it should not be used to create objects in your account, unless you absolutely need these objects to have the highest level of secure access. If you create objects with the ACCOUNTADMIN role and you want users to have access to these objects, you must explicitly grant privileges on the objects to the roles for these users.

Instead, we recommend creating a hierarchy of roles aligned with business functions in your organization and ultimately assigning these roles to the SYSADMIN role. For more information, see Aligning Object Access with Business Functions in this topic.

Second the USAGE permission on the database needs to be granted.

GRANT USAGE ON DATABASE ... TO ROLE ...;

Database Privileges:

USAGE

Enables using a database, including returning the database details in the SHOW DATABASES command output. Additional privileges are required to view or take actions on objects in a database.

user_50708790
Related