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;