DB2 10.5 for Linux, UNIX, and Windows

CREATE PERMISSION statement

The CREATE PERMISSION statement creates a row permission at the current server.

Invocation

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is implicitly or explicitly specified.

Authorization

The privileges held by the authorization ID of the statement must include SECADM authority. SECADM authority can create a row permission in any schema. Additional privileges are not needed to reference other objects in the permission definition. For example, the SELECT privilege is not needed to retrieve from a table, and the EXECUTE privilege is not needed to call a user-defined function.

Syntax


>>-CREATE--+------------+--PERMISSION--permission-name--ON--table-name-->
           '-OR REPLACE-'                                                

>--+--------------------------+--------------------------------->
   | .-AS-.                   |   
   '-+----+--correlation-name-'   

>--FOR ROWS WHERE--search-condition--ENFORCED FOR ALL ACCESS---->

   .-DISABLE-.   
>--+---------+-------------------------------------------------><
   '-ENABLE--'

Description

OR REPLACE

Specifies to replace the definition for the row permission if one exists at the current server. The existing definition is effectively dropped before the new definition is replaced in the catalog.

permission-name

Names the row permission. The name, including the implicit or explicit qualifier, must not identify a row permission or a column mask that already exists at the current server (SQLSTATE 42710).

table-name

Identifies the table on which the row permission is created. The name must identify a table that exists at the current server (SQLSTATE 42704). It must not identify a nickname, created or declared temporary table, view, synonym, typed table, or alias (SQLSTATE 42809). It must not identify a base table of a shadow table (SQLSTATE 428HZ). In releases before DB2® Version 10.5 Fix Pack 5, table-name must not identify a catalog table (SQLSTATE 42832).

correlation-name

Specifies a correlation name that can be used within case-expression to designate the table.

FOR ROWS WHERE

Indicates that a row permission is created. A row permission specifies a search condition under which rows of the table can be accessed.

search-condition

Specifies a condition that can be true or false for a row of the table. This follows the same rules used by the search condition in a WHERE clause of a subselect query. In addition, the search condition must not reference any of the following objects or elements (SQLSTATE 428HB):

A created global temporary table or a declared global temporary table.
A shadow table.
A nickname.
A table function.
A method.
A parameter marker (SQLSTATE 42601).
A user-defined function that is defined as not secure.
A function or expression (such as row change expression, sequence expression) that is non deterministic or has an external action
An XMLQUERY scalar function.
An XMLEXISTS predicate.
An OLAP specification.
A * or name.* in a SELECT clause.
A pseudocolumn.
An aggregate function without specifying the SELECT clause.
A view that includes any of the previously listed restrictions in its definition.

If search-condition references tables with currently activated row or column access control, access control from those tables are not cascaded. See "Notes" for details.

ENFORCED FOR ALL ACCESS

Specifies that the row permission applies to all references of the table. If row access control is activated for the table, when the table is referenced in a data manipulation statement, the database manager implicitly applies the row permission to control the access of the table. If the reference of the table is for a fetch operation such as SELECT, the application of the row permission determines what set of rows can be retrieved by the user who requested the fetch operation. If the reference of the table is for a data change operation such as INSERT, the application of the row permission determines whether all rows to be changed can be inserted or updated by the user who requested the data change operation.

ENABLE or DISABLE

Specifies that the row permission is to be enabled or disabled. The default is DISABLE.

DISABLE: Specifies that the row permission is to be disabled. If row access control is not currently activated for the table, the row permission will remain ineffective when row access control is activated for the table.
ENABLE: Specifies that the row permission is to be enabled for row access control. If row access control is not currently activated for the table, the row permission will become effective when row access control is activated for the table. If row access control is currently activated for the table, the row permission becomes effective immediately and all packages and dynamically cached statements that reference the table are invalidated.

See the ACTIVATE ROW ACCESS CONTROL clause in the ALTER TABLE statement for more information about how to activate row access control and how row permissions are applied.

Notes

Row permissions that are created before row access control is activated for a table: The CREATE PERMISSION statement is an independent statement that can be used to create a row permission before row access control is activated for a table. The only requirement is that the table and the columns exist before the permission is created. Multiple row permissions can be created for a table.

The definition of the row permission is stored in the database catalog. Dependency on the table for which the permission is being created and dependencies on other objects referenced in the definition are recorded. No package or dynamic cached statement is invalidated. A row permission can be created as enabled or disabled for row access control. An enabled row permission does not take effect until the ALTER TABLE statement with the ACTIVATE ROW ACCESS CONTROL clause is used to activate row access control for the table. A disabled row permission remains ineffective even when row access control is activated for the table. The ALTER PERMISSION statement can be used to alter between ENABLE and DISABLE.

After row access control is activated for a table, when the table is referenced in a data manipulation statement, all enabled row permissions that are defined for the table are implicitly applied by the database manager to control access to the table.

Creating row permissions before activating row access control for a table is the recommended sequence to avoid multiple invalidations of packages and dynamic cached statements that reference the table.
Row permissions that are created after row access control is activated for a table: An enabled row permission becomes effective as soon as it is committed. All the packages and dynamic cached statements that reference the table are invalidated. Thereafter, when the table is referenced in a data manipulation statement, all enabled row permissions are implicitly applied to the statement. Any disabled row permissions remain ineffective even when row access control is activated for the table.
No cascaded effect when row or column access control enforced tables are referenced in row permission definitions: A row permission definition might reference tables and columns that are currently enforced by row or column access control. Access control from those tables are ignored when the table for which the row permission is being created is referenced in a data manipulation statement.
Consideration for database limits: If the data manipulation statement already approaches some database limits in the statement, the more enabled row permissions and enabled column masks are created, the more likely they might affect some limits. This is because the enabled column mask and enabled row permission definitions are implicitly merged into the statement when the table is referenced in a data manipulation statement. See "SQL and XML Limits" for the limits of a statement.
Permissions that are enabled but in the invalid state: If a permission is enabled for row access control but its state is set to invalid, access to the table on which the permission is defined is blocked until this situation is resolved (SQLSTATE 560D0).

Example

The tellers in a bank can only access customers from their own branch. All tellers are members in role TELLER. The customer service representatives are allowed to access all customers of the bank. All customer service representatives are members in role CSR. A row permission is created accordingly for each group of personnel in the bank by a user with SECADM authority. After row level access control is activated for table CUSTOMER, in the SELECT statement the search conditions of both row permissions are merged into the statement and they are combined with the logical OR operator to control the set of rows accessible by each group.

CREATE PERMISSION TELLER_ROW_ACCESS ON CUSTOMER
 FOR ROWS WHERE VERIFY_ROLE_FOR_USER
 (SESSION_USER,'TELLER') = 1 AND
        BRANCH = (SELECT HOME_BRANCH FROM INTERNAL_INFO
             WHERE EMP_ID = SESSION_USER)
ENFORCED FOR ALL ACCESS
ENABLE;

CREATE PERMISSION CSR_ROW_ACCESS ON CUSTOMER
 FOR ROWS WHERE VERIFY_ROLE_FOR_USER(SESSION_USER,'CSR') = 1
 ENFORCED FOR ALL ACCESS
 ENABLE;