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.
- 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).
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.