SnowSQL Installation and Configuration

This document provides a step-by-step guide on how to install, configure, and use SnowSQL on macOS, along with assigning role-based database and schema privileges to a user.

Installing SnowSQL on macOS using Homebrew

Install SnowSQL with Homebrew: Run the following command to install SnowSQL using Homebrew:
```
brew install --cask snowflake-snowsql
```
Update the PATH variable in ~/.zshrc: Add the following line to your ~/.zshrc file to include SnowSQL in your PATH:
```
alias snowsql="/Applications/SnowSQL.app/Contents/MacOS/snowsql"
```
If you're using a different shell, update the appropriate configuration file (e.g., ~/.bashrc or ~/.bash_profile for Bash).
Refresh the shell configuration: Run the following command to reload the updated configuration file:
```
source ~/.zshrc
```
Verify the SnowSQL installation: Run the following command to check the installed version of SnowSQL:
```
snowsql -v
```
If SnowSQL was installed successfully, you should see its version number in the output.

Configuring SnowSQL

Create the SnowSQL configuration directory: Run the following command to create a .snowsql directory in your home folder:
```
mkdir ~/.snowsql
```

Create and edit the SnowSQL configuration file: Create a new file named config in the ~/.snowsql directory and add the following content, replacing the username and password placeholders with your Snowflake account information:

[options]
log_file = /path/to/your/preferred/directory/snowsql_rt.log

accountname = wib71536.prod3.us-west-2
region = us-west-2
username = myusername
password = mypassword
dbname = REVELIO_DB
schemaname = REVELIO_CUSTOM
warehousename = COMPUTE_WH
rolename = USERADMIN

Set file permissions: Run the following command to set the appropriate permissions for the SnowSQL configuration file:
```
chmod 700 ~/.snowsql/config
```

Connecting and Using SnowSQL

Connect to Snowflake: Run the following command in your terminal to connect to your Snowflake instance using the SnowSQL configuration you've set up:
```
snowsql
```
Run queries: Once connected, you can run SQL queries directly in the terminal. For example, to select all records from a table called my_table, run:
```
SELECT * FROM my_table;
```
Autocomplete and suggestions: SnowSQL provides autocomplete functionality to help you write queries faster. As you type, suggestions will appear in a dropdown menu. Use the arrow keys to navigate through the suggestions and press Enter to select one.
Additional commands and exit: Type ! in the SnowSQL prompt to access a list of additional commands, such as !history to view your query history or !help for more information. To exit SnowSQL, type !exit or !quit.

Assigning a Role and Database Schema Privileges to a User

As an administrator, you can assign role-based privileges to a user by running the following SQL commands in SnowSQL:

Switch to the ACCOUNTADMIN role: Run the following command to use the ACCOUNTADMIN role:
```
USE ROLE ACCOUNTADMIN;
```
Grant a role to a user: Run the following command to grant the USERADMIN role to a specific user:
```
GRANT ROLE USERADMIN TO USER KJALBA;
```

Grant USAGE privileges on a database and schema: Run the following commands to grant USAGE privileges to the USERADMIN role for the REVELIO_DB database and REVELIO_CUSTOM schema:

GRANT USAGE ON DATABASE REVELIO_DB TO ROLE USERADMIN;
GRANT USAGE ON SCHEMA REVELIO_DB.REVELIO_CUSTOM TO ROLE USERADMIN;
GRANT USAGE ON ALL SCHEMAS IN DATABASE REVELIO_DB TO ROLE USERADMIN;

Grant SELECT privileges on tables in a schema: Run the following commands to grant SELECT privileges to the USERADMIN role for all current and future tables in the REVELIO_DB.REVELIO_CUSTOM schema:

GRANT SELECT ON ALL TABLES IN SCHEMA REVELIO_DB.REVELIO_CUSTOM TO ROLE USERADMIN;
GRANT SELECT ON FUTURE TABLES IN SCHEMA REVELIO_DB.REVELIO_CUSTOM TO ROLE USERADMIN;

This completes the steps for granting SELECT privileges to the USERADMIN role for all current and future tables in the REVELIO_DB.REVELIO_CUSTOM schema.