Authorization of Db2 Big SQL objects

Authorization of Db2® Big SQL objects is controlled at several layers. The Db2 Big SQL permissions are maintained within the Db2 Big SQL catalog tables.

Level 1: Controlling access with authorization in the distributed file system (DFS)

The Db2 Big SQL server runs as the bigsql user and accesses tables and data as the bigsql user. In this context, the bigsql user is the database administrator.

At the file system level, you must have Linux®-style privileges to access the directories and files that contain tables and the data that is contained in those tables.

When you create a schema in Db2 Big SQL, a directory for the schema is created in two locations in the DFS: /warehouse/tablespace/managed/hive/ and /warehouse/tablespace/external/hive/. Only transactional tables ('transactional'='true') are placed in the /warehouse/tablespace/managed/hive directory. If a table is not transactional ('transactional'='false'), the default location is /warehouse/tablespace/external/hive/ unless the LOCATION clause specifies otherwise. For more information about transactional tables, see Transactional tables in Db2 Big SQL.

The bigsql user performs operations on the DFS on behalf of the connected user, and in most instances, it is only the bigsql user who requires access to the DFS location. The following statements require that the connected user also has access to the DFS:
  • CREATE [EXTERNAL] HADOOP TABLE with the LOCATION clause.
  • ALTER TABLE [PARTITION] SET LOCATION.
  • LOAD HADOOP USING FILE.
  • DROP TABLE, with the following requirements:
    • The table is a MANAGED table, which means the DROP removes its data.
    • The table has a LOCATION that is outside of the /warehouse/tablespace/managed/ directory.
    • A server configuration property is set that enables the use of the LOCATION clause without the EXTERNAL keyword.

To determine the permissions that Db2 Big SQL users require on the DFS, it is important to understand how these permissions are managed on CDP. For more information, see Authorizing Apache Hive Access.

Level 2: Authorization with the GRANT command

A privilege is a permission to perform an action or a task. If you have the correct authorization, you can create objects, and you can access those objects. In addition, as the owner and creator of the object, you can use the GRANT command to give a privilege to other users.

The security of GRANT and REVOKE depends on the Db2 Big SQL server security, as described in Level 1: Controlling access with authorization in the distributed file system (DFS).

You use GRANT and REVOKE commands to give or remove certain privileges to users. If you do not have database administrative authority, you cannot load data into a table. If you created the table, you automatically have the proper authority. If you are the database administrator, you can grant authority to other users. In the following example, an administrator is granting a user that is named user1 access to a table, SALES:
GRANT INSERT, SELECT, DELETE ON myschema.SALES TO USER user1
In the following example, an administrator is granting a group that is named SALESTM read access to a table, EMPLOYEE:
GRANT SELECT ON EMPLOYEE TO GROUP SALESTM

Level 3: Authorization at the row and column level

Row and column access (RCAC) provides an additional layer of security to the privileges. It controls access to a table at the row level, column level, or both. The row permission rule is in the form of an SQL search condition that describes the set of rows to which a user has access. The column mask rule is an SQL CASE expression that describes the column values that a user is permitted to see and under what conditions.

You do not need to change your application to take advantage of RCAC. This is an important security advantage in using Db2 Big SQL because the security administrator controls the access, not the SQL application. But, you should be aware that queries do not see the whole picture in terms of the data in the table unless granted specific permission to do so.

For a more detailed discussion of RCAC see Row and column access control (RCAC) overview.

Level 4: Controlling access by using VIEWS or STORED PROCEDURES

You can also control access by using VIEWS or STORED PROCEDURES. You can set privileges on the VIEW objects or on the STORED PROCEDURES to control access. With VIEWS and STORED PROCEDURE, you can allow restricted access to data to which the user normally does not have access.