Skip Headers
Oracle® OLAP DML Reference
11g Release 1 (11.1)

Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Go to next page
PDF · Mobi · ePub


The PERMIT command lets you grant or deny read-only and read/write access permission for workspace objects and for specific values of dimensions and dimensioned objects. You can also use PERMIT to grant or deny permission to maintain dimensions and to change permission for workspace objects.

In general, access permissions that you set for a dimension are meaningful not only for that dimension, but also for the objects dimensioned by that dimension as explained more fully in "How Dimension Permission Interacts with Permissions for Other Objects". Access permissions that you set for other objects are limited in scope to that particular object. Various conditions determine when permission changes take effect as discussed in "When Permission Changes Take Effect"


The PERMIT command assigns permission to the object most recently defined or considered. When the definition of the object is not the current one, first use a CONSIDER statement before issuing PERMIT commands for the object


PERMIT {READ|WRITE|MNT|PERMIT} [WHEN permission-condition...]



Depending on the permission conditions, grants or restricts permission to read all of the values of an object or only certain values in a dimension or dimensioned object.

Note that restricting read permission for a dimension also restricts maintain permission for that dimension. Also, when you grant read permission for an object, write permission is also allowed for the values you can read, unless you deny it with an explicit PERMIT WRITE statement.


Depending on the permission conditions, grants or restricts permission to modify any of the values of an object or only certain values of a dimensioned object.

Restricting write permission for an object does not necessarily mean that the object is totally unavailable. For example, when you have restricted write access to a calculation specification (that is, an aggmap object, program, model, or formula, you can still execute the object— you just cannot edit the contents of the calculation specification.

Oracle OLAP does not prevent you from granting write permission for values that you cannot read within a dimensioned object. When you have both a PERMIT READ and a PERMIT WRITE statement for a dimensioned object, and some of the values which satisfy the permission conditions for write do not fall within the subset of values which satisfy the permission conditions for read, then those values may be modified but not seen.


Write permission is not meaningful for dimensions— except to provide write access to objects dimensioned by the dimension. To grant or restrict the ability to add values to a dimension, issue PERMIT MNT statements for the dimension instead.

Depending on the permission conditions, grants or restricts permission to maintain a dimension.

Note that you cannot grant maintain permission to a dimension for which you have restricted read permission. Oracle OLAP automatically denies maintain permission for a dimension when there is restricted read permission for that dimension.


Depending on the permission conditions, grants or restricts permission to use the PERMIT command to change the read, write, maintain, or permit permission for the object.

Note that unless explicitly you explicitly deny permit permission for an object, permit permission is allowed for an object no matter what other permissions are set for that object.

WHEN permission-condition...

When you omit the WHEN clause and execute a PERMIT READ, PERMIT WRITE, or PERMIT MNT statement, Oracle OLAP restores full read, write, or maintain permission to the object.

When you include the WHEN clause, the conditions for granting read, write, maintain, or permit permission consist of one or more Boolean expressions depending on whether or not the object has dimensionality, the type of permission you are setting and, for read and write permissions, whether or not the object has dimensionality:

  • When you are specifying permit permission, maintain permission, or read or write permission that applies to an object without dimensionality or when you want the read or write permissions to apply to all the values of a dimensioned object, specify the permission condition as a Boolean expression that evaluates to a single value. (Not that if you do specify a dimensioned Boolean expression to specify permissions for an object without dimensionality or when you are specifying permit or maintain permission, then PERMIT uses the first value in status.)


    Avoid specifying a Boolean value as a permission condition (for example, YES or NO) for a PERMIT PERMIT statement. Instead specify the permission condition as a Boolean variable, a function that returns a Boolean result, or a Boolean value calculated by comparison operators. In this way, when permit permission has been denied, you can restore it by setting the value of the Boolean and executing a PERMITRESET statement. If you ever do lock up an object and are unable to modify its permission, you can specify permit permission for it in the workspace permission program for that workspace, then detach and reattach the workspace.
  • If you are specifying read or write permission that applies to individual dimension values or to slices of a dimensioned object, specify one permission condition for each dimension of interest. In this case, the WHEN clause has the following syntax and each dimensioned permission condition consists of a Boolean expression dimensioned by one of the dimensions of the object.

    WHEN dimensioned_permission_condition1
       [BY dimensioned_permission_condition2
    [UNION dimensioned_permission_condition2A]...

    Use the UNION phrase to efficiently access the union of several symmetric subcubes of a multidimensional variable.

    Note that if a Boolean expression for a dimensioned permission condition has any extra dimensions in addition to one of the object dimensions, PERMIT takes the first value in status to determine which column of Boolean values to use. The intersection of the YES values for each dimension (a logical AND of the conditions) is the subset of values within the object to which the permission applies. When any of the object dimensions are not represented by a dimensioned permission condition, then Oracle OLAP assumes YES for all those dimension values.


Important Considerations Before You Change the Permissions of an Object

Keep the following important considerations in mind before you change the permissions of an object:

When Permission Changes Take Effect

Within PERMIT_READ and PERMIT_WRITE programs, permissions ares only evaluated when you issue an explicit PERMIT or PERMITRESET statement and then reference the targeted object.


AW ATTACH automatically executes a PERMITRESET immediately after executing an analytic workspace permission program. This causes the workspace to be attached with all permission implemented

Once an analytic workspace is attached and any PERMIT_READ and PERMIT_WRITE programs have executed, permission changes specified by a PERMIT statement take effect:

When the only PERMIT Statement for an Object is a PERMIT WRITE Statement

When the only PERMIT statement for an object is a PERMIT WRITE, then read permission is provided by default for the object. The default read permission is provided independent of the value of the permission condition(s) for the PERMIT WRITE statement. This means that a PERMIT WRITE with a single-cell permission condition which evaluates to NO provides read-only access to an nondimensional object or to all the values of a dimensioned object.

When the only PERMIT statement for an object is a PERMIT WRITE with dimensioned permission conditions, it designates some values for read/write access and the remaining values for read-only access. See Example 10-74, "Variable Permission".

How Dimension Permission Interacts with Permissions for Other Objects

All dimensioned data is affected by the read permission on its dimensions. The dimension values that satisfy the read permission condition determine the default status for the dimension. The values of dimensioned objects that correspond to dimension values without read permission are inaccessible.

However, in order for write permission associated with a dimension to apply to other objects dimensioned by it, there must be at least one PERMIT statement associated with the dimensioned object.

Dimension permission interacts with permission for most of the objects dimensioned by it in the following ways:

However, this is not the case for relations and valuesets. When there is restricted write permission for a dimension of a relation or a valueset, it does not affect relations and valuesets dimensioned by that dimension.

Determining Permission

The permission associated with an object is provided, like an LD, when you describe it using a DESCRIBE statement. The only exception is when you are denied permit permission for the object. In this case, no permission is provided when you describe it.

Advantages of PERMIT Over LIMIT as a Tool for Scoping

As a tool for scoping within application programs, PERMIT has several advantages over the LIMIT command. To restrict the scope of a dimensioned object according to a Boolean expression, you have to use two LIMIT statements, a LIMIT and a LIMITKEEP. You only need one PERMIT statement to do the same thing. Moreover, application users cannot change the restricted scope set by PERMIT commands in application programs. Application users can easily change the scope set by LIMIT commands in application programs simply by executing more LIMIT commands.

Handling Permission Violations

You can use the PERMITERROR option to control the way Oracle OLAP handles attempted violations of the permission established by PERMIT commands for variables. The default value of PERMITERROR is YES, meaning that Oracle OLAP will signal an error when a user attempts to access a value for which permission is denied. When you set PERMITERROR to NO, Oracle OLAP simply denies access without signaling an error condition. This is useful when you want to do a report of a dimensioned variable for which you have partial permission without limiting the dimensions to the permitted values up front. With PERMITERROR set to NO, values for which you do not have read permission appear as NA values in the report.


Example 10-74 Variable Permission

For a variable sales dimensioned by month, product, and district, you might have three dimensioned permission conditions in the form of three variables as illustrated in the following report.

-----------------  ------------------  -----------------------
Jan95    NO        Tents     YES       Boston     NO
Feb95    YES       Canoes    YES       Atlanta    NO
Mar95    NO        Racquets   NO       Chicago    YES
...      ...       ...        ...      ...        ...

When the YES values shown in the preceding example are the only YES values in the permission conditions, the following PERMIT statement provides read/write access to sales data for tents and canoes sold in Chicago in Feb95. In the absence of a PERMIT READ statement for sales, Oracle OLAP provides read-only permission for all the other values of sales.

PERMIT WRITE WHEN district.bool BY prod.bool BY month.bool

You can restore full write permission with the following PERMIT statement.


When there is no restricted write permission for sales, the following PERMIT statement provides read/write access to sales data for tents and canoes sold in Chicago in Feb95, and it causes all other values of sales to be invisible.

PERMIT READ WHEN district.bool BY prod.bool BY month.bool

Example 10-75 Dimensioned Permission Condition

To restrict access to the product dimension you need a permission condition dimensioned by product. However, when the permission condition has a second dimension, say authority, PERMIT selects the BOOLEAN values that pertain to product based on the first value in status of authority. When you restrict read permission on the authority dimension to one value, PERMIT uses that value to determine the BOOLEAN values of the permission condition for product. The REPORT commands produce the output that follows them.

DEFINE prod_authority VARIABLE BOOLEAN <product authority>
" Assign values to the variable
REPORT prod_authority
AUTHORITY     Tents   Canoes   Racquets   Sportswear   Footwear
---------     -----   ------   --------   ----------   --------
Other            NO       NO        YES          YES        YES
Dba             YES      YES        YES          YES        YES

CONSIDER product
PERMIT READ WHEN prod_authority
RPEPORT product

CONSIDER authority
Report product

Example 10-76 User-Defined Boolean Function

In the following example, usercheck is a user-defined Boolean function that checks the current value of the variable thisuser against a list of user IDs. usercheck returns NO when the current value of thisuser is not in the list. The following PERMIT statement applied to the sales variable provides read-only access to all values of sales when usercheck returns NO. It provides read/write access to all values of sales when usercheck returns YES.

PERMIT WRITE WHEN usercheck(thisuser)

The following PERMIT statement, applied to the variable price, provides full access to all values of price when usercheck returns YES. When it returns NO, it denies all access to the price variable.

PERMIT READ WHEN usercheck(thisuser)

Example 10-77 Individual Cells

When you want to prevent access to one particular sales figure, say for racquets in Boston in March of 1997, you can create a Boolean variable and use it in a PERMIT statement as illustrated in the following statements.

DEFINE sales.bool VARIABLE BOOLEAN <month product district>
sales.bool = yes
LIMIT month TO 'Mar97'
LIMIT product TO 'Racquets'
LIMIT district TO 'Boston'
sales.bool = no

Example 10-78 Individual Dimension Values

The following PERMIT commands applied to the district dimension prevent access to all dimension values except Boston and Atlanta. They provide read/write access for all data related to Boston and read-only access for all data related to Atlanta. They also prevent anyone with a user ID not allowed by the function usercheck (see Example 10-76, "User-Defined Boolean Function") from modifying the permission for district.

PERMIT READ WHEN district EQ 'Boston' OR district EQ 'Atlanta'
PERMIT WRITE WHEN district EQ 'Boston'
PERMIT PERMIT WHEN usercheck(thisuser)