Best practices, news, tips and tricks - learn about Oracle's R Technologies for Oracle Database and Big Data

  • FAQ
    July 8, 2013

Accessing Data from Multiple Schemas using Oracle R Enterprise

The most common Oracle R Enterprise configuration is to connect directly to a database schema that contains tables you wish to analyze. However, users may occasionally need to access tables that exist in other schemas. Oracle R Enterprise allows several options when accessing tables from another schema is desired. Database tables and views are currently supported, and these form the basis for our recommendations.

Named Schema Access

If you have SELECT TABLE or SELECT ANY TABLE privilege on tables in another schema, you can access these tables after connecting to the database with your own schema credentials. The function ore.sync synchronizes database table and view metadata with the R client environment. For example, by setting schema to “user2”, user1 will see all of user2's tables on which user1 has been granted access:

R> library(ORE)
R> ore.connect(user="user1", sid="sid", host="hostname", password="password")
R> ore.sync(schema = "user2", table="myTable")

R> ore.attach(schema = "user2")
R> ore.ls()

[1] "myTable"

Here, we combine the schema and table arguments to look at a specific table, but this can be omitted to
access all tables available in schema "user2" at once:

R> ore.sync(schema = "user2")
R> ore.ls()
[1] "myTable" "anotherTable
Accessing a materialized table typically offers the best query performance for operations such as joins, however in
other cases, such as
calculating simple summaries, the performance advantage may be negligible.

Create Views in Local Schema

Another option is to map views in your own schema to the tables or views in the another schema. You can restrict users to the view instead of the underlying table, thereby enhancing security, and also include in the view only those columns needed. For example, if a user exports the contents of a carefully defined view, they will see only the table columns selected by the view - no unselected columns, unique identifiers or table keys. Views also simplify the user experience by exposing only those database tables the user can or should access. The only catch when using views is that you must update those views if the underlying tables or views change.

Provided you've been granted CREATE VIEW privilege and SELECT TABLE access, use the ore.exec function to execute the SQL statement that will create the view from the R client

R> library(ORE)
R> ore.connect(user="user1", sid="sid", host="hostname", password="password")
R> ore.exec("create view myView as select * from user2.myTable")
R> ore.sync(table = "myView")
[1] "myView"

The code above assumes you already have privileges to access the table or view. If you do not, log in as sysdba or to the schema of interest in invoke:

SQL> grant select on MYTABLE to user1;

Oracle Wallet

Password credentials for connecting to databases can be stored in a client-side Oracle Wallet, a container used to encrypt authentication credentials. The contents of the wallet are not readable, eliminating the need to expose schema credentials when connecting to the database.  Security risks are reduced because such passwords are not exposed in clear text. Oracle R Enterprise 1.3 and later is integrated with Oracle Wallet, providing a secure way for R scripts to avoid storing passwords in the script. For detailed information about creating wallets, see Oracle Database Advanced Security Administrator's GuideSteps for using Oracle Wallet with Oracle R Enterprise are provided in the Oracle R Enterprise Installation and Administration Guide.

If you have a creative technique for accessing data across schemas or other platforms, please recommend it in the blog comments, along with any opinions you have on these approaches.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.