In this blog post, we will cover the basics of creating a database, schema, and table in Snowflake. We will also show you how to apply some permissions to the objects that you create.
In this case, we will create a database related to all KODEY related data. The schema then groups the database objects, like a directory on your computer. In this case, the schema relates to everything on the KODEY.CO.UK domain:
USE ROLE SYSADMIN;
CREATE DATABASE KODEY;
CREATE SCHEMA KODEY_CO_UK;
Note: Snowflake has four default roles, which we will be using throughout our demo. You can always review these by executing SHOW ROLES:
- ACCOUNTADMIN: This role has full administrative privileges over the account.
- SECURITYADMIN: This role has administrative privileges over security objects, such as roles and users.
- SYSADMIN: This role has full administrative privileges over the system.
- PUBLIC: This role has basic object access privileges.
Now, let’s create a database table. This table will store some information related to our blog posts. The CREATE TABLE command is used to create a new table in Snowflake. The syntax for the CREATE TABLE command is as follows:
CREATE TABLE <table_name> (
<column_name> <data_type> [NOT NULL] [DEFAULT <default_value>] [CONSTRAINT <constraint_name> <constraint_definition>] [, ...]
) [CLUSTER BY (<expression>)] [COPY GRANTS]
The <table_name> is the name of the new table. The <column_name> is the name of the column. The <data_type> is the data type of the column. The NOT NULL keyword specifies that the column cannot be NULL. The DEFAULT <default_value> keyword specifies the default value for the column. The CONSTRAINT <constraint_name> <constraint_definition> clause specifies a constraint on the column. The CLUSTER BY (<expression>) clause specifies that the table should be clustered by the expression. The COPY GRANTS clause specifies that the grants on the table should be copied from the parent schema.
In the below, we are creating a simple table with the name KODEY and column names: ID, TITLE, SUBTITLE and DESCRIPTION.
USE DATABASE KODEY;
CREATE TABLE KODEY_CO_UK.ARTICLES (
ID NUMBER,
TITLE VARCHAR,
SUBTITLE VARCHAR,
DESCRIPTION TEXT
);
Now let’s insert some data into our tables.
INSERT INTO KODEY_CO_UK.ARTICLES VALUES
(3324, 'SNOWFLAKE ARCHITECTURE','A great subtitle' ,'A database and schema are two important concepts in Snowflake. A database is a logical grouping of schemas, and a schema is a logical grouping of database objects (tables, views, etc.)'),
(43536,'SNOWPLOW', 'Another great subtitle', 'Databases and schemas can help to organize data in a way that makes it easy to understand and manage.'),
(325523, 'SNOWFLAKE CHEAT SHEET','Yet another great subtitle','Databases and schemas can be used to control access to data.');
Now when we query our table, we can see our data! So let’s create a user role and assign permissions to it. In Snowflake, roles are used to control access to objects in the system. Roles can be granted to users, which gives the users the privileges associated with the role. The USAGE privilege allows a role to see and access a database or schema.
To create a role in Snowflake, you can use the following command: CREATE ROLE DATANALYST. Once you have created a role, you can grant the USAGE privilege on a database or schema to the role using the following command:
CREATE ROLE DATANALYST;
GRANT USAGE ON DATABASE KODEY TO ROLE DATANALYST;
GRANT USAGE ON SCHEMA KODEY.KODEY_CO_UK TO ROLE DATANALYST;
GRANT SELECT ON TABLE KODEY.KODEY_CO_UK.ARTICLES TO ROLE DATANALYST;
In Snowflake, users are used to identify and authenticate users who want to access the system. Users can be granted different privileges, such as the ability to create databases, schemas, and tables via the roles we’ve outlined above. The CREATE USER command has a number of optional parameters that you can use to configure the user. For example, you can use the DEFAULT_ROLE parameter to specify the default role that the user will be granted when they log in. You can also use the MUST_CHANGE_PASSWORD parameter to specify whether the user is required to change their password on their first login.
Once you have created a new user, you can grant them privileges using the GRANT command. For example, the following command grants the DATANALYST role to our user, BOB.
CREATE USER BOB PASSWORD='temp' DEFAULT_ROLE = DATANALYST DEFAULT_WAREHOUSE='COMPUTE_WH' MUST_CHANGE_PASSWORD=TRUE;
GRANT ROLE DATANALYST TO USER BOB;
You can grant the user access directly to a database / schema without using a role, it’s not best practice, though, for the below reasons:
- It can be difficult to manage permissions. If you give users direct permissions, you have to track all of the different permissions that each user has. This can be difficult to do, especially if you have a lot of users.
- It can be difficult to revoke permissions. If you need to revoke permissions from a user, you have to find all of the objects that the user has permissions to and revoke the permissions on each object. This can be time-consuming and error-prone.
Roles solve all of these problems. Roles allow you to group permissions together, so you only have to track one permission per role. Roles also make it easy to revoke permissions, because you only have to revoke the permissions from the role. Finally, roles help to protect your data, because they prevent users from having access to objects that they don’t need to access.
GRANT SELECT ON TABLE ARTICLES TO USER BOB;
In Snowflake, you can revoke access to objects by using the REVOKE command. The REVOKE command takes two arguments: the first argument is the privilege that you want to revoke, and the second argument is the object that you want to revoke the privilege from. It’s simply the opposite to our GRANT command.
REVOKE SELECT ON TABLE ARTICLES TO USER BOB;
REVOKE SELECT ON TABLE ARTICLES FROM ROLE DATANALYST;
Now you might want to also grant access on future schemas’ too. In Snowflake, a future grant is a grant that applies to objects that do not yet exist. This can be useful for granting access to new objects that will be created in the future, without having to grant access manually each time a new object is created. The GRANT USAGE ON FUTURE SCHEMAS command grants the USAGE privilege on all future schemas in a database to a role. This means that the role will be able to see and access all future schemas in the database, even if they do not yet exist. In our case, we will be granting our DATANALYST role access to any schemas that are created in the KODEY database in the future.
GRANT USAGE ON FUTURE SCHEMAS IN DATABASE KODEY TO ROLE DATANALYST;
Note that I have used Google Bard to help generate some of the standard text.
