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

Teradata® Package for R User Guide

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Teradata Package for R
Release Number
17.20
Published
March 2024
ft:locale
en-US
ft:lastEdition
2024-04-09
dita:mapPath
efv1707506846369.ditamap
dita:ditavalPath
ayr1485454803741.ditaval
dita:id
nqx1518630623256
Product Category
Teradata Vantage

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 database administrator:

  • GRANT EXECUTE FUNCTION ON VAL 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 VAL ... 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 VAL.NamedEntityFinder. 
In order for this to work, the database "TOM" needs a certain permission, which can be granted using: GRANT EXECUTE FUNCTION ON VAL 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 objects 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;