4.3 Sentry Authorization in Oracle Big Data SQL

In addition to supporting authorization for HDFS file access, Oracle Big Data SQL supports Sentry policies,which authorize access to Hive metadata. Sentry enables fine-grained control over user access, down to the column level.

Note: Sentry is not supported for Big Data SQL installations on Oracle Database 12.1 systems.

4.3.1 Sentry and Multi-User Authorization

Oracle Big Data SQL utilizes Sentry policies whenever Sentry is enabled on the Hadoop cluster. Support for Sentry in Oracle Big Data SQL is most effective when used in conjunction with the Multi-User Authorization system. Multi-User Authorization enables Sentry authorization based on the actual identity of the currently connected user.

If Mulit-User Authorization is not enabled, then the oracle proxy user is used for authorization for all queries. Sentry must be properly configured with rules for the oracle proxy user.

See Also:

• The Multi-User Authorization Model, the previous section in this chapter.
• DBMS_BDSQL PL/SQL Package describes SYS.BDSQL_USER_MAP and the procedures for managing the table.
• Jaguar Configuration Parameter and Command Reference in Oracle Big Data SQL Installation Guide shows how to use Jaguar parameters to configure security features in Oracle Big Data SQL. The impersonation_enabled parameter enables or disables multi-user authorization (which is based on Apache's Hadoop Secure Impersonation).

4.3.2 Groups, Users, and Role-Based Access Control in Sentry

Oracle Big Data SQL does not directly control access to Hive tables. It respects the access controls defined by the Hive database administrator. For Sentry, these controls are role-based. A given user's access rights are defined by their group memberships and the roles assigned to those groups.

The administrator uses tools such as the HiverServer2 Beeline utility to assign privileges (such as SELECT and INSERT) to Hive tables and their columns. The administrator also creates Sentry roles, assigns roles to different user groups, and grants privileges to those roles. The Hadoop user operating in the Hadoop environment inherits all of the privileges from each role that is assigned to their group or groups.

For example, to provide access to salary data, an administrator may create a role for this purpose and then grant SELECT privileges to the role:

CREATE ROLE auditfixedcosts;
GRANT SELECT ON TABLE salary TO ROLE auditfixedcosts;

Grants may also be given to the role auditfixedcosts for access to data on other servers or in other databases, tables, or columns.

The administrator assigns the auditfixedcosts role to a group:

GRANT ROLE fixedcosts TO GROUP finance;

Members of the finance group then have SELECT access to all data in the salary table, any other SELECT access granted to the auditfixedcosts role, as well as access inherited from any other roles granted to the finance group.

See Also:

• Hive SQL Syntax for Use with Sentry on Cloudera's web site provides information on how to configure Sentry permissions.
• See the Apache Foundation documentation at https://sentry.apache.org for more details.

4.3.3 How Oracle Big Data SQL Uses Sentry

In Oracle Big Data SQL, Sentry provides a way to grant or withhold the SELECT privilege for individual Oracle Database users who attempt to query Oracle external tables over Hive tables.

As a result, Sentry policies do not need to be replicated to the Oracle external tables (i.e. using GRANT), which simplifies administration.

Sentry can be used to control the SELECT privilege at these levels of scope:

• Server (cluster node)
• Hive database
• Specific columns within a Hive table

Big Data SQL does not support DML operations. Therefore, only the SELECT privilege applies to Big Data SQL queries. The Sentry privileges ALL, OWNER, CREATE, INSERT, and REFRESH are not relevant to Big Data SQL access.

How Big Data SQL Uses Sentry to Determine Access at Query Execution

Big Data SQL first determines the corresponding cluster user using multi-user authentication. If no multi-user authentication is enabled it uses the oracle user. When a user executes a query against an Oracle external table over a Hive table, Big Data SQL checks for role-based Sentry privileges granted to the Hadoop user and applies them against the Oracle external table that is created over the current Hive table. It then compares these with the privileges required to execute the query. If the privileges granted to the user do not fulfill the requirements of the query, then an exception is raised.

4.3.4 Oracle Big Data SQL Privilege-Related Exceptions for Sentry

Oracle Big Data SQL raises one of two exceptions when a user's Sentry privileges are not sufficient to execute a query.

The exceptions and the errors returned by as follows.

• Table-level privilege exception:

  "User <the user> does not have SELECT privileges on oracle table <external table name> 
  for Server=<server name>->Db=<Hive db name>->Table=<table name>”

• Column-level privilege exception:

  "User <the user>  does not have SELECT privileges on oracle table <external table name> 
  column <column name> for Server=<server name>->Db=<Hive db name>->Table=<table name>->Field=<field name>”