User Permissions in Teradata Vantage | Teradata R Package - 17.00 - User Permissions in Vantage - Teradata R Package

Teradata® R Package User Guide

prodname
Teradata R Package
vrm_release
17.00
created_date
November 2020
category
User Guide
featnum
B700-4005-090K

Permissions to operate and interact with Vantage

To operate and interact with Vantage with tdplyr, the user that is specified in the Testing Connection to Vantage with Teradata SQL Driver for R procedure must have a series of permissions granted. Otherwise, executing tdplyr functions can result in errors on the R client that stem from inadequate database user permissions.

A database user must be granted in advance the following permissions by the Vantage database administrator:

  • GRANT EXECUTE FUNCTION ON SYSLIB TO user ;
  • GRANT CONNECT THROUGH proxyuser TO PERMANENT user WITHOUT ROLE;
  • GRANT SELECT ON TD_SERVER_DB.coprocessor TO user ;
  • GRANT INSERT ON TD_SERVER_DB.coprocessor TO user ;
  • GRANT EXECUTE FUNCTION ON TD_SERVER_DB.coprocessor TO user ;
  • GRANT CREATE SERVER ON TD_SERVER_DB TO user ;
  • GRANT EXECUTE FUNCTION ON TD_SYSFNLIB.QGEXECUTEFOREIGNQUERY TO user ;
  • GRANT EXECUTE FUNCTION ON TD_SYSFNLIB.QGINITIATOREXPORT TO user ;
  • GRANT EXECUTE FUNCTION ON TD_SYSFNLIB.QGINITIATORIMPORT TO user ;
  • GRANT EXECUTE FUNCTION ON TD_SYSFNLIB.QGREMOTEEXPORT TO user ;
  • GRANT EXECUTE FUNCTION ON TD_SYSFNLIB.QGREMOTEIMPORT TO user ;
  • GRANT CTCONTROL ON user TO proxy_user .
The proxyuser is a suitable database proxy user for the analytic functions as determined by the database administrator.

Permissions to access and interact with views based on Vantage Analytic Functions

tdplyr requires that the user has certain permissions on the user's default database or the initial default database specified using the database argument, or the temporary database when specified using temp.database. These permissions allow the user to:
  • Create tables to save results of Vantage analytic functions.
  • Create views in the background for objects of class tbl_teradata based on a query.

It is expected that the user has the required permissions to create these objects in the database that will be used.

For views based on Vantage Analytic Functions, additional permissions may be required, which can be granted using: GRANT EXECUTE FUNCTION ON SYSLIB ... WITH GRANT OPTION.

Example 1:

In this example, a user named "ALICE" connects to a non-default database named "TOM" to run the td_named_entity_finder_mle analytic function.
  • Creates the connection.
    > con <- DBI::dbConnect(teradatasql::TeradataDriver(), host="vantage_host", user="alice", password="password", database = "TOM") 
    > td_set_context(con) 
    > con <- td_get_context()$connection 
  • Load example data.
    > loadExampleData("namedentityfinder_example", "assortedtext_input", "namefind_configure") 
    Loading: TOM.assortedtext_input .... 
    Loading: TOM.namefind_configure ....
  • Create object(s) of class "tbl_teradata".
    > assortedtext_input <- tbl(con, "assortedtext_input") 
    > namefind_configure <- tbl(con, "namefind_configure") 
  • Find entities using a configuration table containing model items.
    > td_namedentity_finder_out <- td_namedentity_finder_mle(newdata = assortedtext_input, configure.table.data = namefind_configure, text.column = "content", model = "all", accumulate = c("id", "source") )
  • Get the entity column from the result of td_namedentity_finder_out.
    > output <- td_namedentity_finder_out$result %>% select(entity) 
    Error: In .ta.describe.columns.wrapper(e$con.cached, e$from.cached): [tdplyr - (TDR_E1001)] Error: [tdplyr - (TDR_E1014)] Failed to retrieve column info for: help column "TOM"."r__t__view_1598267189564970".*; Error in obtainRows(res, FALSE, params): [Version 17.0.0.2] [Session 7978] [Teradata Database] [Error 3523] An owner referenced by user does not have EXECUTE FUNCTION WITH GRANT OPTION access to SYSLIB.NamedEntityFinder. 
In order for this to work, the database "TOM" needs a certain permission, which can be granted using: GRANT EXECUTE FUNCTION ON SYSLIB TO TOM WITH GRANT OPTION.

To access the views created, additional permissions may be required depending on which database is used and which object the view being created is based on. The permission can be granted using: GRANT SELECT ... WITH GRANT OPTION.

Example 2:

In this example, a user named "ALICE" connects to a non-default database named "TOM" to run the td_named_entity_finder_mle analytic function.
  • Connect to the default database to load the example data.
    > con <- td_create_context(host = "vantage_host", uid = "alice", pwd = "password", dType = "native")
  • Load example data.
    > loadExampleData("namedentityfinder_example", "assortedtext_input", "namefind_configure")
    Loading:  ALICE.assortedtext_input ....
    Loading:  ALICE.namefind_configure ....
  • Reconnect to make sure all writes here on happen to the database specified using "temp.database".
    > td_remove_context()
    > con <- td_create_context(host = "vantage_host", uid = "alice", pwd = "password", dType = "native", temp.database = "TOM")
  • Create object(s) of class "tbl_teradata" - from the user's default database.
    > assortedtext_input <- tbl(con, "assortedtext_input")
    > namefind_configure <- tbl(con, "namefind_configure")
  • Find entities using a configuration table containing model items.
    > td_namedentity_finder_out <- td_namedentity_finder_mle(newdata = assortedtext_input, configure.table.data = namefind_configure, text.column = "content", model = "all", accumulate = c("id", "source"))
  • Get the entity column from the result of td_namedentity_finder_out.
    > output <- td_namedentity_finder_out$result %>% select(entity)
    Error: In .ta.describe.columns.wrapper(e$con.cached, e$from.cached):
    [tdplyr - (TDR_E1001)] Error: [tdplyr - (TDR_E1014)] Failed to retrieve column info for: help column "TOM"."r__t__view_1598268821563227".*;
    Error in obtainRows(res, FALSE, params): [Version 17.0.0.2] [Session 7981] [Teradata Database] [Error 3523] An owner referenced by user does not have SELECT WITH GRANT OPTION access to alice.assortedtext_input.
In order for this to work, the following permission must be granted:
  • GRANT SELECT ON ALICE.assortedtext_input TO TOM WITH GRANT OPTION;
  • GRANT SELECT ON ALICE.namefind_configure TO TOM WITH GRANT OPTION;