0_Demo_Environment_Setup.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "likely-brush",
   "metadata": {},
   "source": [
    "<header>\n",
    "   <p  style='font-size:36px;font-family:Arial; color:#F0F0F0; background-color: #00233c; padding-left: 20pt; padding-top: 20pt;padding-bottom: 10pt; padding-right: 20pt;'>\n",
    "       VantageCloud Lake Environment Automation\n",
    "  <br>\n",
    "       <img id=\"teradata-logo\" src=\"images/TeradataLogo.png\" alt=\"Teradata\" style=\"width: 125px; height: auto; margin-top: 20pt;\">\n",
    "    </p>\n",
    "</header>\n",
    "<hr>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "biblical-outreach",
   "metadata": {},
   "source": [
    "<br>\n",
    "\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Utilize common workflow engines to automate the deployment of enterprise systems configuration.</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Teradata VantageCloud Lake</b> has been designed from the ground-up with self-service as a foundational architectural design. Customers may start out with leveraging the Console UI for setup and configuration tasks, but will quickly move towards automation.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Apache Aiflow</b> is one of the most common workflow automation engines, and provides a wide array of capabilities for creating batch, recurrring, and on-demand automated workflows.  Airflow can leverage the open APIs available in VantageCloud Lake, using python, SQL, or REST interfaces.</p>\n",
    "\n",
    "<img src='images/Apache_Airflow.png' width = '800'>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "norwegian-things",
   "metadata": {},
   "source": [
    "<hr>\n",
    "\n",
    "<b style = 'font-size:28px;font-family:Arial;color:#00233C'>Summary Overview</b>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>This demonstration utilizes a set of Python functions to automate a \"enterprise looking\" deployment of VantageCloud Lake, including key features such as:</p>\n",
    "\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li><b>SYSDBA Role</b>.  A DBA responsible for overal Systems Administration</li>\n",
    "    <li><b>Compute Group Admin</b>.  A \"Departmental\" Administrator responsible for creating and assigning Compute Cluster Resources</li>\n",
    "    <li><b>User Hierarchy</b>.  Creation of users and groups of users into resource admins/support users, and business users/analysts.</li>\n",
    "    <li><b>Database Hierarchy</b>.  Databases and objects with organized parent/child relationships</li>\n",
    "    <li><b>Compute Resource Standards</b>.  Naming conventions for Compute Groups, Profiles, and Access Roles</li>\n",
    "    <li><b>Common Role-Based permissions</b>.  Admins, Read-Only, Read/Write, etc.</li>\n",
    "    </ul>\n",
    "    \n",
    "<hr>\n",
    "\n",
    "<b style = 'font-size:28px;font-family:Arial;color:#00233C'>Demonstration Overview</b>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial';color:#00233C>This demonstration consists of several assets that work in combination to illustrate environment automation:</p>\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li><b>This Notebook</b>.  This notebook consists of the overview and introduction, as well as executable code for automating the deployment of of Lake resources.  Each cell is idempotent, so can be run and rerun to illustrate how to use SQL APIs for each high leel step.</li>\n",
    "    <li><b>vars.json</b>.  A variable file which contains environment and hierarchy specifics; including the IP address and DBC credentials for logging into the deployed VCLE Account.  There is a great deal of additional information which controls the deployment; such as the number and types of clusters, users, databases, etc.</li>\n",
    "    <li><b>Demo_Setup_Airflow_Python.py</b>.  The Apache Airflow python file.  This uses the Python Operators to execute the below code in an Airflow DAG with some amount of dependency chaining in the DAG.  Note, the vars.json file should be uploaded as \"Variables\" in Airflow after editing for the correct environment/hierarchy.</li>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "boolean-shoot",
   "metadata": {},
   "source": [
    "<hr>\n",
    "\n",
    "<b style = 'font-size:24px;font-family:Arial;color:#00233C'>Database Hierarchy</b>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The scripts combined with vars.json, will create the following structure in any new VCLE system:</p>\n",
    "\n",
    "\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>DBC\n",
    "        <ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "            <li>SYSDBA\n",
    "                <ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "                    <li>Repositories\n",
    "                        <ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "                            <li>PubAuth Public Authorization Object</li>\n",
    "                            <li>demo\n",
    "                                <ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "                                    <li>Customer_EBS</li>\n",
    "                                    <li>Accounts_Mapping_EBS</li>\n",
    "                                </ul></li>\n",
    "                            <li>demo_ofs\n",
    "                                <ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "                                    <li>Customer_EBS</li>\n",
    "                                    <li>Accounts_Mapping_EBS</li>\n",
    "                            </ul></li>\n",
    "                        </ul>\n",
    "                        <li>Users\n",
    "                            <ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "                                <li>SupportUsers\n",
    "                                    <ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "                                        <li>cgadmin  /* Compute Group admin */</li>\n",
    "                                    </ul>\n",
    "                                    <li>BusinessUsers\n",
    "                                        <ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "                                            <li>data_engineer</li>\n",
    "                                            <li>data_scientist</li>\n",
    "                                        </ul></li>\n",
    "                            </ul></li>\n",
    "                </ul></li>\n",
    "        </ul></li>\n",
    "    </ul>\n",
    "\n",
    "<b style = 'font-size:24px;font-family:Arial;color:#00233C'>Naming Standards</b>\n",
    "   \n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'>Compute Group (CG) naming standard</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>CG_<i>BusGrpName_type</i></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>where <i>BusGrpName</i>  Business Group (if more than 1 CG, reflect in name)</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>    <i>type</i>        Compute Group type (STD/ANL) (ie: Standard/Analytic)</p>\n",
    "      \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'> eg: CG_BusGrpA_STD<br>\n",
    "eg: CG_BusGrpA_ANL</p>\n",
    "\n",
    "\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'>Compute Group Role (CR) naming standard</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>CR_<i>BusGrpName_type</i> <br>\n",
    "eg: CR_BusGrpA_STD<br>\n",
    "eg: CR_BusGrpA_ANL</p>\n",
    "\n",
    "\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'>Compute Group Profile (CP) naming standard</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>CP_<i>BusGrpName_type_n_size</i><br>\n",
    "    where <i>BusGrpName</i>  Business Group (if more than 1 CG, reflect in name)<br>\n",
    "    <i>type</i>       Compute Group type (STD/ANL)<br>\n",
    "    <i>n</i>           Compute Profile idendifier (n=1-9)<br>\n",
    "    <i>size</i>        Compute Cluster t-shirt size (TNY/SML/MED/LRG/1XL/2XL)<br>\n",
    "\n",
    "eg: CP_BusGrpA_STD_1_SML<br>\n",
    "eg: CP_BusGrpA_STD_2_MED<br>\n",
    "eg: CP_BusGrpA_ANL_1_SML</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "waiting-christianity",
   "metadata": {},
   "source": [
    "<hr>\n",
    "\n",
    "<b style = 'font-size:24px;font-family:Arial;color:#00233C'>Environment for Demo</b>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>vars.json</b> is structured as follows for the demonstration:</p>\n",
    "\n",
    "```json\n",
    "{\n",
    "    \"environment\":{\"host\":\"54.69.11.56\"},      \n",
    "    \"hierarchy\":{\n",
    "        \"DBC\":{\"username\":\"DBC\",\"password\":\"demo_dbc\"},\n",
    "        \"SYSDBA\":{\"username\":\"SYSDBA\", \"password\":\"password\"},\n",
    "        \"users\":{\"support_users\":[{\"username\":\"cgadmin\", \"password\":\"password\"}],\n",
    "                \"business_users\":[{\"username\":\"data_engineer\", \n",
    "                                   \"password\":\"password\", \n",
    "                                   \"role\":\"CR_BusGrpA_STD\",\n",
    "                                   \"compute_group\":\"CG_BUSGRPA_STD\"}, \n",
    "                               {\"username\":\"data_scientist\", \n",
    "                                \"password\":\"password\", \n",
    "                                \"role\":\"CR_BusGrpA_ANL\", \n",
    "                                \"compute_group\":\"CG_BUSGRPA_ANL\"}]},\n",
    "        \"compute_groups\":[{\"name\":\"CG_BusGrpA_STD\", \"type\":\"STANDARD\", \"profiles\":\n",
    "                           [{\"name\":\"CP_BusGrpA_STD_1_SML\",\"type\":\"STANDARD\", \"size\":\"TD_COMPUTE_SMALL\", \"min\":\"1\", \"max\":\"1\"}]}, \n",
    "                          {\"name\":\"CG_BusGrpA_ANL\", \"type\":\"ANALYTIC\", \"profiles\":\n",
    "                           [{\"name\":\"CP_BusGrpA_ANL_1_SML\",\"type\":\"ANALYTIC\",\"size\":\"TD_COMPUTE_SMALL\", \"min\":\"1\", \"max\":\"1\"}]}],\n",
    "        \"compute_group_roles\":[{\"name\":\"CR_BusGrpA_STD\", \"type\":\"STANDARD\"}, \n",
    "                               {\"name\":\"CR_BusGrpA_ANL\", \"type\":\"ANALYTIC\"}],\n",
    "        \"repositories\":{\"name\":\"Repositories\", \n",
    "                        \"perm\":\"5e11\",\n",
    "                       \"databases\":[{\"name\":\"demo\", \"perm\":\"2e11\", \"default_storage\":\"TD_NDSSTORAGE\"}, \n",
    "                                   {\"name\":\"demo_ofs\", \"perm\":\"2e11\", \"default_storage\":\"TD_OFSSTORAGE\"}]}}\n",
    "\n",
    "}\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "medium-jaguar",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Import the minimal number of packages\n",
    "\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "\n",
    "import json\n",
    "import pandas as pd\n",
    "import teradatasql\n",
    "\n",
    "from teradataml import *"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "annoying-whale",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# load vars json\n",
    "with open('vars.json', 'r') as f:\n",
    "    session_vars = json.load(f)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "infinite-barrel",
   "metadata": {},
   "source": [
    "\n",
    "## Section 1: Environment Setup ## \n",
    "### steps overview ###\n",
    "\n",
    "01) Create TD admin super user (sysdba) and grant privileges   [as dbc]\n",
    "02) Create User hierachy & Compute Group admin (cgadmin)       [as sysdba]\n",
    "03) Grant Compute Group admin (cgadmin) privileges             [as dbc]\n",
    "04) Create Environment database hierachy                       [as sysdba]\n",
    "05) Create user profiles                                       [as sysdba]\n",
    "06) Create Roles                                               [as sysdba]\n",
    "07) Grant privileges to Roles                                  [as sysdba]\n",
    "08) Create Compute Groups / Compute Profiles                   [as cgadmin]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "later-encounter",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# /* -------------------------------------------------------- */\n",
    "# -- Perform this as DBC.\n",
    "# -- Create Super User (SYSDBA). Parent of hierarchy.\n",
    "# /* -------------------------------------------------------- */\n",
    "\n",
    "dbc_name = session_vars['hierarchy']['DBC']['username']\n",
    "dbc_pwd = session_vars['hierarchy']['DBC']['password']\n",
    "name = session_vars['hierarchy']['SYSDBA']['username']\n",
    "pwd = session_vars['hierarchy']['SYSDBA']['password']\n",
    "perm = session_vars['hierarchy']['SYSDBA']['perm']\n",
    "spool = session_vars['hierarchy']['SYSDBA']['spool']\n",
    "\n",
    "\n",
    "with teradatasql.connect(host = session_vars['environment']['host'], \n",
    "                     user = dbc_name, \n",
    "                     password = dbc_pwd) as con:\n",
    "    cur = con.cursor()\n",
    "    \n",
    "    qry = f'''\n",
    "        CREATE USER {name} FROM DBC\n",
    "          AS PERM  = {perm}          /* 800GB */\n",
    "            ,SPOOL = {spool}          /* 800GB */\n",
    "            ,PASSWORD = \"{pwd}\"\n",
    "            ,ACCOUNT = ('$M')\n",
    "            ,TIME ZONE = '00:00'   /* UDT */\n",
    "            ,DEFAULT ROLE = ALL  ;\n",
    "        '''\n",
    "    try:\n",
    "        cur.execute(qry)\n",
    "    except Exception as e:\n",
    "        # check if user exists\n",
    "        if str(e.args).find(\"Error 5612\") >= 1:\n",
    "            pass\n",
    "        else:\n",
    "            raise\n",
    "            \n",
    "    \n",
    "    queries = [f'''\n",
    "        --Grant All privileges on SYSDBA to itself.\n",
    "        GRANT all on \"{name}\" to \"{name}\" WITH GRANT OPTION;\n",
    "        ''', \n",
    "              f'''\n",
    "        --Grant system-level privileges to SYSDBA.\n",
    "        GRANT EXECUTE, SELECT, INSERT, UPDATE, DELETE, STATISTICS, DUMP, \n",
    "        RESTORE, CHECKPOINT, SHOW, EXECUTE PROCEDURE, ALTER PROCEDURE, \n",
    "        EXECUTE FUNCTION, ALTER FUNCTION, ALTER EXTERNAL PROCEDURE, \n",
    "        CREATE OWNER PROCEDURE, CREATE TABLE, CREATE VIEW, CREATE MACRO, \n",
    "        CREATE DATABASE, CREATE USER, CREATE TRIGGER, CREATE PROCEDURE, \n",
    "        CREATE FUNCTION, CREATE EXTERNAL PROCEDURE, CREATE AUTHORIZATION, \n",
    "        DROP TABLE, DROP VIEW, DROP MACRO, DROP DATABASE, DROP USER,\n",
    "        DROP TRIGGER, DROP PROCEDURE, DROP FUNCTION, DROP AUTHORIZATION ON \"{name}\" TO \"{name}\" WITH GRANT OPTION;\n",
    "        ''', \n",
    "              f'''GRANT EXECUTE, SELECT, STATISTICS, SHOW on DBC to \"{name}\" WITH GRANT OPTION;''', \n",
    "              f'''GRANT UDTTYPE, UDTMETHOD on SYSUDTLIB to \"{name}\" WITH GRANT OPTION;''', \n",
    "              f'''GRANT CTCONTROL ON \"{name}\" TO \"{name}\" WITH GRANT OPTION;''',\n",
    "              f'''GRANT SELECT, INSERT, UPDATE, DELETE on \"Sys_Calendar\" to \"{name}\" WITH GRANT OPTION;''',\n",
    "              f'''GRANT SELECT, EXECUTE FUNCTION, EXECUTE PROCEDURE on TD_SYSFNLIB to \"{name}\" WITH GRANT OPTION;''',\n",
    "              f'''GRANT SELECT, EXECUTE FUNCTION, EXECUTE PROCEDURE on TD_MLDB to \"{name}\" WITH GRANT OPTION;''',\n",
    "              f'''GRANT SELECT, EXECUTE FUNCTION, EXECUTE PROCEDURE on syslib to \"{name}\" WITH GRANT OPTION;''', \n",
    "              f'''GRANT EXECUTE FUNCTION on TD_SYSFNLIB.READ_NOS to \"{name}\" WITH GRANT OPTION;''', \n",
    "              f'''GRANT CREATE DATASET SCHEMA on SYSUDTLIB to \"{name}\" WITH GRANT OPTION;''', \n",
    "              f'''GRANT SELECT on TD_METRIC_SVC to \"{name}\" WITH GRANT OPTION;''',\n",
    "              f'''GRANT SELECT, EXECUTE FUNCTION, EXECUTE PROCEDURE on SYSSPATIAL to {name} WITH GRANT OPTION;''', \n",
    "              #f'''GRANT ALL ON retail_sample_data to \"{name}\" WITH GRANT OPTION;''', \n",
    "              f'''GRANT SELECT, CREATE SERVER ON TD_SERVER_DB TO \"{name}\" WITH GRANT OPTION;''',\n",
    "              f'''GRANT CREATE PROFILE, CREATE ROLE TO \"{name}\" WITH GRANT OPTION;''',\n",
    "              f'''GRANT CREATE TABLE, DROP TABLE ON TD_SERVER_DB TO \"{name}\" WITH GRANT OPTION;''',\n",
    "              f'''\n",
    "        /* Grant SYSDBA the privileges to grant others to System Roles, except TD_COMPUTE_CLUSTER_ADMIN. */\n",
    "        /* This is to lockdown who may administer Computer Groups.                                        */ \n",
    "        GRANT  TD_USER_ADMIN\n",
    "              ,TD_DATA_SCIENTIST\n",
    "              ,TD_FLOW_COMPOSER\n",
    "              ,TD_CURRENT_QUERIES_ADMIN\n",
    "              ,TD_CURRENT_QUERIES_VIEW\n",
    "              ,TD_QUERY_HISTORY_VIEW\n",
    "              ,DS_ADMIN\n",
    "              ,DS_CITIZEN\n",
    "              ,DS_PROVIDER\n",
    "              ,TD_CONF_REPORTS_USER\n",
    "              ,TD_SYS_CONF_REPORTS_USER\n",
    "              ,TD_COMPUTE_CLUSTER_ADMIN\n",
    "            to {name} WITH ADMIN OPTION;\n",
    "        ''', \n",
    "              f'''GRANT LOGON ON ALL TO {name} WITH NULL PASSWORD;''']\n",
    "    for qry in queries:\n",
    "        cur.execute(qry)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "exotic-history",
   "metadata": {},
   "outputs": [],
   "source": [
    "#/* -------------------------------------------------------- */\n",
    "#-- Perform this as SYSDBA.\n",
    "#-- Create User hierachy\n",
    "#/* -------------------------------------------------------- */\n",
    "\n",
    "name = session_vars['hierarchy']['SYSDBA']['username']\n",
    "pwd = session_vars['hierarchy']['SYSDBA']['password']\n",
    "\n",
    "user_perm = session_vars['hierarchy']['users']['perm']\n",
    "user_spool = session_vars['hierarchy']['users']['spool']\n",
    "\n",
    "# calculate the amount of perm for each of\n",
    "# the business users (.75) and support users (.25) hierarchy:\n",
    "\n",
    "bus_perm = str(int(float(user_perm)*.75))\n",
    "sup_perm = str(int(float(user_perm)*.25))\n",
    "\n",
    "\n",
    "with teradatasql.connect(host = session_vars['environment']['host'], \n",
    "                     user = name, \n",
    "                     password = pwd) as con:\n",
    "    cur = con.cursor()\n",
    "    \n",
    "    qry = f'''\n",
    "        CREATE USER \"Users\" FROM SYSDBA\n",
    "          AS PERM  = {user_perm}       /*   30GB */\n",
    "            ,SPOOL = {user_spool}      /* 100GB */\n",
    "            ,PASSWORD = \"{pwd}\"\n",
    "            ,ACCOUNT = ('$M')\n",
    "            ,TIME ZONE='00:00'\n",
    "            ,DEFAULT ROLE=ALL;\n",
    "            '''\n",
    "    try:\n",
    "        cur.execute(qry)\n",
    "    except Exception as e:\n",
    "        # check if user exists\n",
    "        if str(e.args).find(\"Error 5612\") >= 1:\n",
    "            pass\n",
    "        else:\n",
    "            raise\n",
    "    \n",
    "    qry = f'''\n",
    "    CREATE USER \"BusinessUsers\" FROM Users\n",
    "      AS PERM  = {bus_perm}       /*   20GB */\n",
    "        ,SPOOL = {user_spool}      /* 100GB */\n",
    "        ,PASSWORD = \"{pwd}\"\n",
    "        ,ACCOUNT = ('$M')\n",
    "        ,TIME ZONE = '00:00'\n",
    "        ,DEFAULT ROLE = ALL ;\n",
    "    '''\n",
    "    try:\n",
    "        cur.execute(qry)\n",
    "    except Exception as e:\n",
    "        # check if user exists\n",
    "        if str(e.args).find(\"Error 5612\") >= 1:\n",
    "            pass\n",
    "        else:\n",
    "            raise\n",
    "    \n",
    "    qry = f'''\n",
    "    CREATE USER \"SupportUsers\" FROM Users\n",
    "      AS PERM =  {sup_perm}      /*   10GB */\n",
    "        ,SPOOL = {user_spool}      /* 100GB */\n",
    "        ,PASSWORD = \"{pwd}\"\n",
    "        ,ACCOUNT = ('$M')\n",
    "        ,TIME ZONE = '00:00'\n",
    "        ,DEFAULT ROLE = ALL;\n",
    "        '''\n",
    "    try:\n",
    "        cur.execute(qry)\n",
    "    except Exception as e:\n",
    "        # check if user exists\n",
    "        if str(e.args).find(\"Error 5612\") >= 1:\n",
    "            pass\n",
    "        else:\n",
    "            raise\n",
    "    \n",
    "    # create support users from array of support_users\n",
    "    for u in session_vars['hierarchy']['users']['support_users']:\n",
    "        qry = f'''\n",
    "        /* Create Compute Group administrator */\n",
    "        CREATE USER {u['username']} FROM SupportUsers\n",
    "          AS PERM = 0       \n",
    "            ,SPOOL = {user_spool}       /* 10GB */\n",
    "            ,ACCOUNT = ('$M')\n",
    "            ,DEFAULT ROLE = ALL\n",
    "            ,PASSWORD = \"{u['password']}\" ;\n",
    "        '''\n",
    "        try:\n",
    "            cur.execute(qry)\n",
    "        except Exception as e:\n",
    "            # check if user exists\n",
    "            if str(e.args).find(\"Error 5612\") >= 1:\n",
    "                pass\n",
    "            else:\n",
    "                raise\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "fallen-contest",
   "metadata": {},
   "outputs": [],
   "source": [
    "#/* -------------------------------------------------------- */\n",
    "#-- Perform this as *** DBC ***.\n",
    "#-- Grant privileges to Compute Group administrator. \n",
    "#-- NB: To lockdown who may create Compute Clusters, there is\n",
    "#--     intentionally only one CG admin.\n",
    "#/* -------------------------------------------------------- */\n",
    "\n",
    "dbc_name = session_vars['hierarchy']['DBC']['username']\n",
    "dbc_pwd = session_vars['hierarchy']['DBC']['password']\n",
    "\n",
    "\n",
    "with teradatasql.connect(host = session_vars['environment']['host'], \n",
    "                     user = dbc_name, \n",
    "                     password = dbc_pwd) as con:\n",
    "    cur = con.cursor()\n",
    "    \n",
    "    # iterate over all support users\n",
    "    for u in session_vars['hierarchy']['users']['support_users']:\n",
    "        \n",
    "        queries = [f'''\n",
    "        -- Compute Group super user ONLY.\n",
    "        GRANT TD_USER_ADMIN, TD_COMPUTE_CLUSTER_ADMIN, TD_FLOW_COMPOSER, TD_CURRENT_QUERIES_ADMIN, DS_ADMIN TO {u['username']};\n",
    "        ''', \n",
    "                  f'''GRANT SELECT ON DBC TO {u['username']};''', \n",
    "                  f'''GRANT DROP COMPUTE PROFILE to \"{u['username']}\" WITH GRANT OPTION;''',\n",
    "                  f'''GRANT CREATE COMPUTE PROFILE to \"{u['username']}\" WITH GRANT OPTION;''', \n",
    "                  f'''grant CREATE COMPUTE GROUP to \"{u['username']}\" WITH GRANT OPTION;''',\n",
    "                  f'''grant DROP COMPUTE GROUP to \"{u['username']}\" WITH GRANT OPTION;''', \n",
    "                  f'''GRANT LOGON ON ALL TO {u['username']} WITH NULL PASSWORD;''']\n",
    "        for qry in queries:\n",
    "            cur.execute(qry)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "legendary-patent",
   "metadata": {},
   "outputs": [],
   "source": [
    "# /* -------------------------------------------------------- */\n",
    "# -- Perform this as SYSDBA.\n",
    "# -- Create Environment database hierachy\n",
    "# -- Root database contains a PubAuth Authorization object for\n",
    "# -- Anonymous NOS access\n",
    "# /* -------------------------------------------------------- */\n",
    "\n",
    "\n",
    "name = session_vars['hierarchy']['SYSDBA']['username']\n",
    "pwd = session_vars['hierarchy']['SYSDBA']['password']\n",
    "\n",
    "with teradatasql.connect(host = session_vars['environment']['host'], \n",
    "                     user = name, \n",
    "                     password = pwd) as con:\n",
    "    cur = con.cursor()\n",
    "    \n",
    "    # Create root database\n",
    "    qry = f'''\n",
    "    CREATE DATABASE {session_vars['hierarchy']['repositories']['name']} from {name} \n",
    "      AS PERM = {session_vars['hierarchy']['repositories']['perm']} ;\n",
    "      '''\n",
    "    try:\n",
    "        cur.execute(qry)\n",
    "    except Exception as e:\n",
    "        # check if user exists\n",
    "        if str(e.args).find(\"Error 5612\") >= 1:\n",
    "            pass\n",
    "        else:\n",
    "            raise\n",
    "    \n",
    "    # Create root Public Auth object with NULL username and password\n",
    "    qry = f'''REPLACE AUTHORIZATION {session_vars['hierarchy']['repositories']['name']}.PubAuth\n",
    "            user '' password '';'''\n",
    "    cur.execute(qry)\n",
    "    \n",
    "    # Create root IAM Auth object[s] for S3 access\n",
    "    if len(session_vars['hierarchy']['repositories']['iam_authorizations']) > 0:\n",
    "        for a in session_vars['hierarchy']['repositories']['iam_authorizations']:\n",
    "            qry = f'''REPLACE AUTHORIZATION {session_vars['hierarchy']['repositories']['name']}.{a['name']}\n",
    "                    USING\n",
    "                    AUTHSERVICETYPE 'ASSUME_ROLE'\n",
    "                    ROLENAME '{a['arn']}'\n",
    "                    EXTERNALID '{a['externalid']}';\n",
    "                    ;'''\n",
    "            cur.execute(qry)\n",
    "    \n",
    "    # Create child databases\n",
    "    for d in session_vars['hierarchy']['repositories']['databases']:\n",
    "        qry = f'''\n",
    "        CREATE DATABASE {d['name']} from Repositories  AS\n",
    "          DEFAULT STORAGE = {d['default_storage']} OVERRIDE ON ERROR,\n",
    "          PERM = {d['perm']};\n",
    "        '''\n",
    "        try:\n",
    "            cur.execute(qry)\n",
    "        except Exception as e:\n",
    "            # check if user exists\n",
    "            if str(e.args).find(\"Error 5612\") >= 1:\n",
    "                pass\n",
    "            # check if object exists - \"perm space\"\n",
    "            elif str(e.args).find(\"Error 3541\") >= 1:\n",
    "                pass\n",
    "            else:\n",
    "                raise\n",
    "        \n",
    "        qry = f'''GRANT Select on DBC to {d['name']} with grant option;'''\n",
    "        cur.execute(qry)\n",
    "        \n",
    "        qry = f'''GRANT Select on Sys_Calendar to {d['name']} with grant option;'''\n",
    "        cur.execute(qry)\n",
    "        \n",
    "        qry = f'''GRANT Select on TD_METRIC_SVC to {d['name']} with grant option;'''\n",
    "        cur.execute(qry)\n",
    "        \n",
    "        qry = f'''GRANT EXECUTE on retail_sample_data.demo_Auth_NOS to \"{d['name']}\" WITH GRANT OPTION;'''\n",
    "        #cur.execute(qry)\n",
    "        \n",
    "        # Grant execute to the PubAuth object\n",
    "        qry = f'''GRANT EXECUTE on {session_vars['hierarchy']['repositories']['name']}.PubAuth to \"{d['name']}\" WITH GRANT OPTION;'''\n",
    "        cur.execute(qry)\n",
    "        \n",
    "        # Grant execute to the iam role(s)\n",
    "        if len(session_vars['hierarchy']['repositories']['iam_authorizations']) > 0:\n",
    "            for a in session_vars['hierarchy']['repositories']['iam_authorizations']:\n",
    "                qry = f'''GRANT EXECUTE on {session_vars['hierarchy']['repositories']['name']}.{a['name']} to \"{d['name']}\" WITH GRANT OPTION;'''\n",
    "                cur.execute(qry)\n",
    "        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "employed-insurance",
   "metadata": {},
   "outputs": [],
   "source": [
    "# /* -------------------------------------------------------- */\n",
    "# -- Perform this as SYSDBA.\n",
    "# -- Create Profiles\n",
    "# -- Note that applying a generic Profile to a user that doesn't\n",
    "# -- Have a default compute group will not allow the user session\n",
    "# -- to be automatically set on login to the correct default compute group\n",
    "# /* -------------------------------------------------------- */\n",
    "\n",
    "name = session_vars['hierarchy']['SYSDBA']['username']\n",
    "pwd = session_vars['hierarchy']['SYSDBA']['password']\n",
    "\n",
    "user_spool = session_vars['hierarchy']['users']['spool']\n",
    "\n",
    "with teradatasql.connect(host = session_vars['environment']['host'], \n",
    "                     user = name, \n",
    "                     password = pwd) as con:\n",
    "    cur = con.cursor()\n",
    "    \n",
    "    qry = f'''\n",
    "        CREATE PROFILE \"P_BusGrpA\" AS\n",
    "        SPOOL={user_spool}      /* 100GB */\n",
    "        TEMPORARY=1E8   /* 100MB */\n",
    "        ACCOUNT=NULL\n",
    "        DEFAULT DATABASE=NULL ;\n",
    "        '''\n",
    "    try:\n",
    "        cur.execute(qry)\n",
    "    except Exception as e:\n",
    "        # check if user exists\n",
    "        if str(e.args).find(\"Error 5652\") >= 1:\n",
    "            pass\n",
    "        else:\n",
    "            raise\n",
    "    \n",
    "    qry = f'''\n",
    "        CREATE PROFILE \"P_SupportUsers\" AS\n",
    "        SPOOL={user_spool}      /* 100GB */\n",
    "        TEMPORARY=1E8   /* 100MB */\n",
    "        ACCOUNT=NULL\n",
    "        DEFAULT DATABASE=NULL ;\n",
    "        '''\n",
    "    try:\n",
    "        cur.execute(qry)\n",
    "    except Exception as e:\n",
    "        # check if user exists\n",
    "        if str(e.args).find(\"Error 5652\") >= 1:\n",
    "            pass\n",
    "        else:\n",
    "            raise"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "comprehensive-ordinary",
   "metadata": {},
   "outputs": [],
   "source": [
    "# /* -------------------------------------------------------- */\n",
    "# -- Perform this as SYSDBA.\n",
    "# -- Create Roles for Compute Groups admin, General TD access, NOS, etc.\n",
    "# /* -------------------------------------------------------- */\n",
    "\n",
    "name = session_vars['hierarchy']['SYSDBA']['username']\n",
    "pwd = session_vars['hierarchy']['SYSDBA']['password']\n",
    "\n",
    "with teradatasql.connect(host = session_vars['environment']['host'], \n",
    "                     user = name, \n",
    "                     password = pwd) as con:\n",
    "    cur = con.cursor()\n",
    "    \n",
    "    for d in session_vars['hierarchy']['compute_group_roles']:\n",
    "        \n",
    "        qry = f'''create role {d['name']};'''\n",
    "        try:\n",
    "            cur.execute(qry)\n",
    "        except Exception as e:\n",
    "            # check if user exists\n",
    "            if str(e.args).find(\"Error 5612\") >= 1:\n",
    "                pass\n",
    "            else:\n",
    "                raise\n",
    "    \n",
    "    queries = ['''\n",
    "    -- Create a Teradata general admin role \n",
    "    create role R_TD_Admin; ''', \n",
    "               '''\n",
    "    -- Create Roles for general TD access rights \n",
    "    create role R_TD_General;''', \n",
    "              '''\n",
    "    -- Create Roles for NOS access rights \n",
    "    create role R_NOS;''', \n",
    "              '''\n",
    "    -- Create ANALYTICS Role\n",
    "    create role R_Analytics_1;''', \n",
    "              '''\n",
    "    -- Create Demo Role (Read only)\n",
    "    create role R_Demo_R;''', \n",
    "              '''\n",
    "    -- Create Demo Role (Read/Write)\n",
    "    create role R_Demo_RW;''']\n",
    "    \n",
    "    for qry in queries:\n",
    "        try:\n",
    "            cur.execute(qry)\n",
    "        except Exception as e:\n",
    "            # check if user exists\n",
    "            if str(e.args).find(\"Error 5612\") >= 1:\n",
    "                pass\n",
    "            else:\n",
    "                raise"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "single-harvard",
   "metadata": {},
   "outputs": [],
   "source": [
    "# /* -------------------------------------------------------- */\n",
    "# -- Perform this as SYSDBA.\n",
    "# -- Grant privileges to Roles.\n",
    "# /* -------------------------------------------------------- */\n",
    "\n",
    "\n",
    "name = session_vars['hierarchy']['SYSDBA']['username']\n",
    "pwd = session_vars['hierarchy']['SYSDBA']['password']\n",
    "\n",
    "with teradatasql.connect(host = session_vars['environment']['host'], \n",
    "                     user = name, \n",
    "                     password = pwd) as con:\n",
    "    cur = con.cursor()\n",
    "    \n",
    "    queries = ['''\n",
    "    /* R_TD_Admin */\n",
    "    GRANT EXECUTE, SELECT, STATISTICS, SHOW ON \"DBC\" to R_TD_Admin;''', \n",
    "              '''GRANT  TD_CURRENT_QUERIES_ADMIN to R_TD_Admin;''', \n",
    "              '''\n",
    "    /* R_TD_General */\n",
    "    GRANT SELECT ON TD_METRIC_SVC to R_TD_General;''', \n",
    "              '''GRANT TD_CURRENT_QUERIES_VIEW to R_TD_General;''', \n",
    "              '''GRANT TD_QUERY_HISTORY_VIEW   to R_TD_General;''', \n",
    "              '''GRANT EXECUTE FUNCTION ON TD_SYSFNLIB to R_TD_General;''',\n",
    "              '''GRANT EXECUTE FUNCTION ON TD_MLDB to R_TD_General;''', \n",
    "              '''GRANT SELECT ON DBC to R_TD_General;''', \n",
    "              '''GRANT SELECT, EXECUTE FUNCTION, EXECUTE PROCEDURE ON syslib to R_TD_General;''',\n",
    "              '''GRANT SELECT, EXECUTE FUNCTION, EXECUTE PROCEDURE ON sysspatial to R_TD_General;''',\n",
    "              '''GRANT CREATE SERVER ON TD_SERVER_DB TO R_TD_General;''', \n",
    "              '''GRANT SELECT ON TD_SERVER_DB TO R_TD_General;''', \n",
    "              '''GRANT Execute Function, Show ON SPECIFIC FUNCTION \"TD_SYSFNLIB\".\"SCRIPT\" TO R_TD_General;''',\n",
    "              '''\n",
    "    /* R_NOS */\n",
    "    GRANT EXECUTE FUNCTION ON TD_SYSFNLIB.READ_NOS  to R_NOS;''', \n",
    "              '''GRANT EXECUTE FUNCTION ON TD_SYSFNLIB.WRITE_NOS to R_NOS;''', \n",
    "              '''GRANT CREATE DATASET SCHEMA on SYSUDTLIB to R_NOS;''', \n",
    "              '''\n",
    "    /* R_Analytics_1 */\n",
    "    GRANT TD_DATA_SCIENTIST TO R_Analytics_1;''',\n",
    "               \n",
    "              '''/* R_Demo_R */\n",
    "                GRANT Select  on demo to R_Demo_R ; ''', \n",
    "               \n",
    "               '''/* R_Demo_R */\n",
    "                GRANT Select  on demo_ofs to R_Demo_R ;''',\n",
    "               \n",
    "              '''\n",
    "    /* R_Demo_RW */\n",
    "    GRANT Create Table, Drop Table, Insert, Update, Delete, Select,\n",
    "      Create AUTHORIZATION on demo to R_Demo_RW;''',\n",
    "               '''\n",
    "    /* R_Demo_RW */\n",
    "    GRANT Create Table, Drop Table, Insert, Update, Delete, Select,\n",
    "      Create AUTHORIZATION on demo_ofs to R_Demo_RW;''',\n",
    "    '''GRANT Create View, Drop View, Select on demo to R_Demo_RW;''',\n",
    "    '''GRANT Create View, Drop View, Select on demo_ofs to R_Demo_RW;''', \n",
    "    '''GRANT CREATE TABLE, DROP TABLE ON TD_SERVER_DB TO R_Demo_RW;''']\n",
    "    \n",
    "    for qry in queries:\n",
    "        try:\n",
    "            cur.execute(qry)\n",
    "        except Exception as e:\n",
    "            # check if user exists\n",
    "            if str(e.args).find(\"Error 5612\") >= 1:\n",
    "                pass\n",
    "            else:\n",
    "                raise\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "cheap-attribute",
   "metadata": {},
   "outputs": [],
   "source": [
    "# /* -------------------------------------------------------- */\n",
    "# -- Perform this as cgadmin\n",
    "# -- Create Compute Groups / Compute Profiles\n",
    "# /* -------------------------------------------------------- */\n",
    "\n",
    "# /* Learnings: \n",
    "# --  1) Some of these statements do not run under ODBC or .NET (only JDBC) \n",
    "# --  2) If you create a Compute Profile \"INITIALLY_SUSPENDED\", it goes through its\n",
    "# --     states of \"Deploying\", \"Hibernating\", \"Hibernated\". This takes about 10 mins.\n",
    "# --     Once it's in the \"Hibernated\" (suspend) state, currently we are not able to\n",
    "# --     \"Resume\" it. Therefore, start/stop the CG by Create/Drop the Compute Profile.\n",
    "# */\n",
    "\n",
    "name = session_vars['hierarchy']['users']['support_users'][0]['username']\n",
    "pwd = session_vars['hierarchy']['users']['support_users'][0]['password']\n",
    "\n",
    "with teradatasql.connect(host = session_vars['environment']['host'], \n",
    "                     user = name, \n",
    "                     password = pwd) as con:\n",
    "    cur = con.cursor()\n",
    "    \n",
    "    # iterate through the groups and profiles\n",
    "    for cg in session_vars['hierarchy']['compute_groups']:\n",
    "        \n",
    "        qry = f'''CREATE COMPUTE GROUP {cg['name']} USING QUERY_STRATEGY('{cg['type']}');'''\n",
    "        try:\n",
    "            cur.execute(qry)\n",
    "        except Exception as e:\n",
    "            # check if group exists\n",
    "            if str(e.args).find(\"Error 4822\") >= 1:\n",
    "                pass\n",
    "            else:\n",
    "                raise\n",
    "        \n",
    "        # iterate over each child profile in the group\n",
    "        for cp in cg['profiles']:\n",
    "            \n",
    "            qry = f'''\n",
    "            /* This profile is configured to be available from 9am to 9pm EDT. */\n",
    "            CREATE COMPUTE PROFILE {cp['name']} IN COMPUTE GROUP {cg['name']}\n",
    "            ,INSTANCE = {cp['size']}\n",
    "            ,INSTANCE TYPE = {cp['type']}   \n",
    "             USING\n",
    "                 MIN_COMPUTE_COUNT ({cp['min']})\n",
    "                 MAX_COMPUTE_COUNT ({cp['max']})\n",
    "                 SCALING_POLICY ('STANDARD')\n",
    "                 START_TIME ('0 13 * * MON-FRI')\n",
    "                 END_TIME ('0 01 * * MON-SAT')\n",
    "                 COOLDOWN_PERIOD (30) ;\n",
    "            '''\n",
    "            try:\n",
    "                cur.execute(qry)\n",
    "            except Exception as e:\n",
    "                # check if profile exists\n",
    "                if str(e.args).find(\"Error 4826\") >= 1:\n",
    "                    pass\n",
    "                else:\n",
    "                    raise\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "hearing-basis",
   "metadata": {},
   "source": [
    "# Section 2 - User Creation #\n",
    "#####################################################################\n",
    "## End-User creation/privileges instructions\n",
    "#####################################################################"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "complicated-validation",
   "metadata": {},
   "outputs": [],
   "source": [
    "# /* -------------------------------------------------------- */\n",
    "# -- Perform this as SYSDBA.\n",
    "# -- Create end-users\n",
    "# -- Recommendation: Create new users under \"BusinessUsers\"\n",
    "# /* -------------------------------------------------------- */\n",
    "\n",
    "name = session_vars['hierarchy']['SYSDBA']['username']\n",
    "pwd = session_vars['hierarchy']['SYSDBA']['password']\n",
    "user_perm = session_vars['hierarchy']['users']['perm']\n",
    "user_spool = session_vars['hierarchy']['users']['spool']\n",
    "\n",
    "# split up perm space based on the number of users:\n",
    "# business users are .75 of total user perm\n",
    "# divide this by the number of users in business users\n",
    "\n",
    "ind_user_perm = str(int((float(user_perm)*.75) / len(session_vars['hierarchy']['users']['business_users'])))\n",
    "\n",
    "\n",
    "\n",
    "with teradatasql.connect(host = session_vars['environment']['host'], \n",
    "                     user = name, \n",
    "                     password = pwd) as con:\n",
    "    cur = con.cursor()\n",
    "    \n",
    "    for u in session_vars['hierarchy']['users']['business_users']:\n",
    "        \n",
    "        qry = f'''\n",
    "        CREATE USER {u['username']} FROM BusinessUsers\n",
    "         AS PERM =  {ind_user_perm} \n",
    "           ,Spool = {user_spool}    /* Spool is defined here vs. in the Profile */\n",
    "           ,Temporary = 1E8 \n",
    "           --,Profile = \"P_BusGrpA\"\n",
    "           ,PASSWORD = \"{u['password']}\"\n",
    "           ,ACCOUNT = ('$M')\n",
    "           ,TIME ZONE = '00:00'\n",
    "           ,DEFAULT ROLE = ALL\n",
    "           ,COMPUTE GROUP = {u['compute_group']};\n",
    "        '''\n",
    "        try:\n",
    "            cur.execute(qry)\n",
    "        except Exception as e:\n",
    "            # check if user exists\n",
    "            if str(e.args).find(\"Error 5612\") >= 1:\n",
    "                pass\n",
    "            else:\n",
    "                raise\n",
    "        \n",
    "        qry = f'''GRANT LOGON ON ALL TO {u['username']} WITH NULL PASSWORD;'''\n",
    "        cur.execute(qry)\n",
    "        \n",
    "        qry = f'''GRANT TD_DATA_SCIENTIST TO {u['username']};'''\n",
    "        cur.execute(qry)\n",
    "        \n",
    "        qry = f'''GRANT TD_FLOW_COMPOSER TO {u['username']};'''\n",
    "        cur.execute(qry)\n",
    "        \n",
    "        qry = f'''GRANT TD_CONF_REPORTS_USER TO {u['username']};'''\n",
    "        cur.execute(qry)\n",
    "        \n",
    "        qry = f'''GRANT TD_SYS_CONF_REPORTS_USER TO {u['username']};'''\n",
    "        cur.execute(qry)\n",
    "        \n",
    "        qry = f'''GRANT DS_ADMIN TO {u['username']};'''\n",
    "        cur.execute(qry)\n",
    "        \n",
    "        qry = f'''GRANT TD_COMPUTE_CLUSTER_ADMIN TO {u['username']};'''\n",
    "        cur.execute(qry)\n",
    "                \n",
    "        qry = f'''GRANT EXECUTE PROCEDURE ON {u['username']} TO {u['username']};'''\n",
    "        cur.execute(qry)\n",
    "\n",
    "        qry = f'''GRANT CREATE PROCEDURE ON {u['username']} TO {u['username']};'''\n",
    "        cur.execute(qry)\n",
    "        \n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "statutory-winning",
   "metadata": {},
   "outputs": [],
   "source": [
    "# /* -------------------------------------------------------- */\n",
    "# -- Perform this as CGADMIN.\n",
    "# -- Grant COMPUTE GROUP to every user\n",
    "# -- Grant COMPUTE GROUP to each CG Role\n",
    "# /* -------------------------------------------------------- */\n",
    "\n",
    "name = session_vars['hierarchy']['users']['support_users'][0]['username']\n",
    "pwd = session_vars['hierarchy']['users']['support_users'][0]['password']\n",
    "\n",
    "with teradatasql.connect(host = session_vars['environment']['host'], \n",
    "                     user = name, \n",
    "                     password = pwd) as con:\n",
    "    cur = con.cursor()\n",
    "    \n",
    "    for u in session_vars['hierarchy']['users']['business_users']:\n",
    "        \n",
    "        qry = f'''GRANT COMPUTE GROUP {u['compute_group']} TO {u['username']}'''\n",
    "        cur.execute(qry)\n",
    "    \n",
    "    for d in session_vars['hierarchy']['compute_group_roles']:\n",
    "        \n",
    "        qry = f'''GRANT COMPUTE GROUP {d['compute_group']} TO {d['name']};'''\n",
    "        cur.execute(qry)\n",
    "    \n",
    "    for c in session_vars['hierarchy']['compute_groups']:\n",
    "        qry = f'''GRANT COMPUTE GROUP {c['name']} TO BUSINESSUSERS;'''\n",
    "        cur.execute(qry)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "radical-glass",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "# /* -------------------------------------------------------- */\n",
    "# -- Perform this as SYSDBA.\n",
    "# -- Grant Roles to User (including access to Compute Groups)\n",
    "# /* -------------------------------------------------------- */\n",
    "\n",
    "# --##############################################################################\n",
    "# -- Refer to the environment setup file (VCLE_environment_setup_vXX) for \n",
    "# -- details on privileges assigned to each Role. \n",
    "# -- This is an overview.\n",
    "# --\n",
    "# -- ROLENAME                RIGHTS\n",
    "# --                         \n",
    "# -- R_TD_Admin              Access to DBC and TD_CURRENT_QUERIES_ADMIN\n",
    "# -- R_TD_General            Access to MSS, TD_SYSFNLIB, SYSLIB, Select on DBC\n",
    "# -- R_NOS                   Access to TD_SYSFNLIB.READ_NOS, CREATE DATASET SCHEMA on SYSUDTLIB\n",
    "# -- R_Analytics_1           TD_DATA_SCIENTIST\n",
    "# -- R_Demo_R              Read (only) on Demo databases\n",
    "# -- R_Demo_RW             Read/Write  on Demo databases\t\n",
    "\t\t\t\t   \n",
    "# -- CR_BusGrpA_STD  Non-admin privileges to use Standard Compute Group CG_BusGrpA_STD\n",
    "# -- CR_BusGrpA_ANL  Non-admin privileges to use Analytic Compute Group CG_BusGrpA_ANL\n",
    "# --##############################################################################\n",
    "\n",
    "name = session_vars['hierarchy']['SYSDBA']['username']\n",
    "pwd = session_vars['hierarchy']['SYSDBA']['password']\n",
    "\n",
    "with teradatasql.connect(host = session_vars['environment']['host'], \n",
    "                     user = name, \n",
    "                     password = pwd) as con:\n",
    "    cur = con.cursor()\n",
    "    \n",
    "    for u in session_vars['hierarchy']['users']['business_users']:\n",
    "        \n",
    "        queries = [f'''\n",
    "        -- Grant Non-Admin user to the Compute Group (CG) Roles\n",
    "        -- NB: User cannot use the CG until it is \"SET\" as their default CG. \n",
    "        GRANT {u['role']} to {u['username']};''',\n",
    "                  f'''\n",
    "        -- Grant other Roles to user\n",
    "        GRANT  R_TD_General   to {u['username']} ;\n",
    "        ''', \n",
    "                  f'''GRANT  R_Analytics_1 to {u['username']};''', \n",
    "                  f'''GRANT R_NOS to {u['username']};''', \n",
    "                  f'''GRANT R_Demo_RW to {u['username']} ;''', \n",
    "                  #f'''\n",
    "       # --special grant to get access to the NOS AUTH in retail_sample_data\n",
    "       # GRANT ALL ON retail_sample_data TO {u['username']} WITH GRANT OPTION;''', \n",
    "        #          f'''grant execute on retail_sample_data.demo_Auth_NOS to \"{u['username']}\" WITH GRANT OPTION;''',\n",
    "                   f'''\n",
    "                   --special grant to get access to the root PubAuth\n",
    "                   GRANT EXECUTE on {session_vars['hierarchy']['repositories']['name']}.PubAuth to \"{u['username']}\" WITH GRANT OPTION;''',\n",
    "                   f'''\n",
    "                   --special grant to WITH GRANT OPTION to demo database\n",
    "                   GRANT SELECT on demo to \"{u['username']}\" WITH GRANT OPTION;''',\n",
    "                   f'''\n",
    "                   --special grant to WITH GRANT OPTION to demo_ofs database\n",
    "                   GRANT SELECT on demo_ofs to \"{u['username']}\" WITH GRANT OPTION;''',\n",
    "                  f'''\n",
    "        -- Set default Compute Group to existing user.\n",
    "        -- NB: This may also be done by the user via \"SET SESSION COMPUTE GROUP <CG_Name>;\"\n",
    "        MODIFY user {u['username']} as COMPUTE GROUP = {u['compute_group']} ;''']\n",
    "        \n",
    "        for qry in queries:\n",
    "            cur.execute(qry)\n",
    "            \n",
    "        # Grant execute to the iam role(s)\n",
    "        if len(session_vars['hierarchy']['repositories']['iam_authorizations']) > 0:\n",
    "            for a in session_vars['hierarchy']['repositories']['iam_authorizations']:\n",
    "                qry = f'''GRANT EXECUTE on {session_vars['hierarchy']['repositories']['name']}.{a['name']} to \"{u['username']}\" WITH GRANT OPTION;'''\n",
    "                cur.execute(qry)\n",
    "        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "genetic-thumb",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fewer-internet",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.8"
  },
  "toc-showcode": false,
  "toc-showmarkdowntxt": false
 },
 "nbformat": 4,
 "nbformat_minor": 5
}