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.
Permissions to access and interact with views based on Vantage Analytic Functions
- 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:
- 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.
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:
- 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.
- GRANT SELECT ON ALICE.assortedtext_input TO TOM WITH GRANT OPTION;
- GRANT SELECT ON ALICE.namefind_configure TO TOM WITH GRANT OPTION;