Script | Teradata Python Package - Script - Teradata Package for Python

Required permissions

• Teradata user needs permission to execute Script Table Operator and the default authentication.

  GRANT EXECUTE FUNCTION ON td_sysfnlib.script TO TDAPUSER;

  GRANT EXECUTE ON SYSUIF.DEFAULT_AUTH to TDAPUSER;

• Additional permissions required to run Script on the database.

  GRANT SELECT ON TDAPUSERDB TO TDAPUSER;

• Additional permissions required to install, remove and replace files.

  GRANT EXECUTE PROCEDURE ON SYSUIF.INSTALL_FILE TO TDAPUSER;

  GRANT EXECUTE PROCEDURE ON SYSUIF.REPLACE_FILE TO TFDAPUSER;

  GRANT EXECUTE PROCEDURE ON SYSUIF.REMOVE_FILE TO TDAPUSER;

• You must set SEARCHUIFDBPATH prior to accessing any installed scripts in the target database, so that the Linux system user 'tdatuser' can find the scripts and run them through the STO. If the scripts reside in 'TDAPUSERDB', then set search path in corresponding Python script or Python console as follows:

  get_connection().execute("SET SESSION SEARCHUIFDBPATH = TDAPUSERDB;")

• If the user ('me' in the example here) is using another database ('TDAPUSERDB' in the example here) as default database, the user must have the execute permissions on the Script function to the default database.

Here is the quick example.

1. Create the required users and databases as database administrator.

   -- Create a user 'sysdba' with default database as 'sysdba'.
   CREATE USER sysdba FROM DBC
   AS PERM = 14e9
   PASSWORD = sysdba
   DEFAULT DATABASE = sysdba
   NO BEFORE JOURNAL
   NO AFTER JOURNAL;
    
   -- Create a new database from the newly created user 'sysdba'.
   CREATE DATABASE TDAPUSERDB FROM sysdba
   AS PERM=1e9
   NO BEFORE JOURNAL
   NO AFTER JOURNAL;
    
   -- Create a user with default database as newly created database 'TDAPUSERDB'.
   CREATE USER me FROM sysdba
   AS PERM = 100e6
   PASSWORD = me
   DEFAULT DATABASE = TDAPUSERDB
   NO BEFORE JOURNAL
   NO AFTER JOURNAL;

2. Make sure the file 'mapper.py' is installed in the database 'TDAPUSERDB'. Refer to the Database Utility section for how to use install_file() to install file in Vantage. Ignore if the file is already present in Vantage.
3. The database administrator must provide the following permissions to the user 'me', if not already provided.

   -- Permissions to the user 'me'.
   GRANT EXECUTE FUNCTION ON td_sysfnlib.script TO me;
   GRANT EXECUTE ON SYSUIF.DEFAULT_AUTH to me;
    
    
   -- Additional permissions on the database 'TDAPUSERDB'.
   GRANT CREATE TABLE ON TDAPUSERDB TO me; -- Needed if the table and required permissions are not present.
   GRANT CREATE VIEW ON TDAPUSERDB TO me; -- Needed if the permissions for view creation is not present.
   GRANT SELECT ON TDAPUSERDB TO me;

4. Set the search path and run the Script function as the user 'me'.

   -- Set the search path.
   SET SESSION SEARCHUIFDBPATH = TDAPUSERDB;
    
    
   -- Create view on top of Script function.
   -- Note that view creation is successful.
   create view vw3 as SELECT * FROM Script(
    ON "TDAPUSERDB"."usecase5" AS "input"
    PARTITION BY ANY
    SCRIPT_COMMAND('Rscript ./TDAPUSERDB/mapper.R')
    delimiter(',')
    returns('oc1 VARCHAR(10), oc2 VARCHAR(10)')
   ) as sqlmr;
    *** View has been created.
    *** Total elapsed time was 1 second.
     
   -- Seeing the contents of the view is not possible as the user 'me' don't have execute permissions on TD_SYSFNLIB.SCRIPT and default auth i.e., SYSUIF.DEFAULT_AUTH.
   select * from vw3;
    *** Failure 3523 An owner referenced by user does not have EXECUTE FUNCTION
     WITH GRANT OPTION access to TD_SYSFNLIB.SCRIPT.
   

5. The user 'me' has to be given the following permissions, for the SELECT on view 'vw3' to work in step 4.

   GRANT EXECUTE FUNCTION ON td_sysfnlib.script TO TDAPUSERDB WITH GRANT OPTION;
   

   GRANT EXECUTE ON SYSUIF.DEFAULT_AUTH TO TDAPUSERDB WITH GRANT OPTION;