Skip Headers

Oracle® OLAP DML Reference
10g Release 1 (10.1)

Part Number B10339-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page
Previous
Go to next page
Next
View PDF

PERMIT

The PERMIT command lets you control access to analytic workspace objects. You can use PERMIT commands in Oracle OLAP security applications that specify workspace access rights for many users. You can also use PERMIT as a general scoping tool in other types of applications. Scoping restricts the view of workspace objects.

With the PERMIT command, you can 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.

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 command before issuing PERMIT commands for the object.


Note:

When using PERMIT, it is important that you not lock out the DBA user, which must have access to everything in the workspace at all times.

Syntax

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

Arguments

READ

Grants permission to read an object or values in a dimension or dimensioned object, depending on the permission conditions. You can specify read permission either with a single-cell permission condition or with dimensioned permission conditions.

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.

To completely deny access to an object, you can specify PERMIT READ with a single-cell permission condition that evaluates to NO. To restrict access to a subset of values in a dimension or dimensioned object, you can specify PERMIT READ with dimensioned permission conditions. To restore full access to an object, issue a PERMIT READ command with no WHEN clause or with a single-cell permission condition that evaluates to YES.

WRITE

Grants permission to modify an object or values of a dimensioned object, depending on the permission conditions. Write permission is not meaningful for dimensions, except to provide write access to objects dimensioned by the dimension. You can specify write permission either with a single-cell permission condition or with dimensioned permission conditions.

When you do not specify a PERMIT READ command in addition to the PERMIT WRITE, then read permission is provided by default for the object. In this case, when the object is dimensioned and write permission only applies to some of its values, the values with write permission are available for read/write access and the values without write permission are available for read-only access.

MNT

Grants permission to maintain a dimension. Maintain permission always applies to the entire dimension, and is based on a single-cell permission condition. Maintain permission is automatically denied when there is restricted read permission for the dimension, even when you specify maintain permission.

PERMIT

Grants permission to use the PERMIT command to change the read, write, maintain, or permit permission for the object. Permit permission always applies to the entire object, and is based on a single-cell permission condition. Whether or not there is read, write, or maintain permission for an object, permit permission is always allowed unless explicitly denied with a PERMIT PERMIT statement with a permission condition that evaluates to NO.

WHEN permission-condition...

The conditions for granting read, write, maintain, or permit permission consist of one or more Boolean expressions. When you omit the WHEN clause and execute a PERMIT READ, PERMIT WRITE, or PERMIT MNT statement, Oracle OLAP will restore full read, write, or maintain permission.

When permission applies to an object without dimensionality or to all the values of a dimensioned object, or when you are specifying permit or maintain permission, the permission condition consists of a single Boolean value. When you specify a dimensioned Boolean expression in this case, PERMIT uses the first value in status.

When permission applies to individual cells within a dimensioned object, the permission condition consists of a Boolean variable dimensioned by some or all of the dimensions of the object.

When read or write permission applies to dimension values or slices of a dimensioned object, the permission conditions consist of dimensioned Boolean expressions with the following format.

WHEN dimensioned_permission_condition1

   [BY dimensioned_permission_condition2

    BY dimensioned_permission_condition3...]

Each dimensioned permission condition consists of a Boolean expression dimensioned by one of the dimensions of the object. When a Boolean expression 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.

Notes


PERMIT Commands and Objects

You can apply up to four PERMIT commands to an object, one for read, write, maintain, and permit permission. PERMIT commands must exist within the same workspace as the objects for which they control permission.


Resetting Permission

When you want to keep the existing PERMIT commands for an object, but you want Oracle OLAP to recalculate the permission conditions associated with them, issue a PERMITRESET command. The new permission conditions will be evaluated upon next reference to the object. See "Reevaluating Single-Cell Permission Conditions" and "Permission and the OBJ Function".


Changing Permission

Provided you have permit permission for an object, you can change its permission by issuing new PERMIT commands for it. The new permission will be evaluated upon next reference to the object. See "Permission and the OBJ Function".


Reevaluating Single-Cell Permission Conditions

When you are targeting any object but a dimension for permission, and the permission condition consists of a single Boolean variable, any changes to that variable affect the permission immediately. You do not need to execute a PERMITRESET in this case.


Permission and the OBJ Function

In general, Oracle OLAP evaluates permission upon next reference to the object. However, the OBJ function is an exception to this rule. The OBJ function provides information about a workspace object that you specify. Since OBJ does not load the object into memory, it does not reflect any changes to the object permission since the last time it was loaded. When you want OBJ to provide information based on new permission criteria, execute a LOAD command before the OBJ.


Workspace Permission Programs

You can specify values for the variables of permission conditions in the workspace permission programs, PERMIT_READ and PERMIT_WRITE. These programs are user-defined functions which cause the AW ATTACH command to either attach or not attach the workspace, depending on the return value of the program.

When a user attaches the workspace RO (read-only), Oracle OLAP runs PERMIT_READ, if it exists. When a user attaches the workspace RW (read/write), Oracle OLAP runs PERMIT_WRITE, if it exists.

When you specify a password with the AW command, it is passed as an argument to the workspace permission program. The workspace permission programs run before AUTOGO. Permission specified in the workspace permission programs only pertains to objects in the workspace being attached.


Workspace Permission Programs: Evaluating Permission

Within the workspace permission programs, permission is not evaluated upon first reference to an object, as it is in every other context. Permission is only evaluated within a workspace permission program when you issue an explicit PERMIT or PERMITRESET command and then reference the targeted object. AW ATTACH executes a PERMITRESET immediately after executing a workspace permission program. This causes the workspace to be attached with all permission implemented.


Workspace Permission Programs: In More Than One Workspace

When you have workspace permission programs defined in workspaces that are currently attached, Oracle OLAP executes the one in the workspace that you are attaching. However, when you have workspace permission programs in more than one currently attached workspace, you need to take special care when you edit them or use them in any other way, to ensure that you access the appropriate version.


Read/Write Permission

When the only PERMIT command 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 undimensioned object or to all the values of a dimensioned object. When the only PERMIT command 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 19-29, "Variable Permission".


Write But Not Read

Oracle OLAP does not prevent you from establishing 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.


Default Status

The dimension values that satisfy the permission condition for PERMIT READ constitute the default status for the dimension. When Oracle OLAP loops over the dimension, it only includes those values with read permission. For example, a LIMIT ALL statement provides only those values. A reference to integer position means the position within the set of values with read permission. The same principle also applies to QDRs, LAG and LEAD references, and UNRAVEL.


Note:

Dimensions with an INTEGER data type have values identified by their numeric position. PERMIT renumbers INTEGER dimensions to keep the normal sequence of integers (1, 2, 3, ...). When this behavior is not desirable, you should use a text or time-period data type.

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


Dimension Permission

Write permission is only meaningful for dimensions in providing write access to objects dimensioned by the dimension. In order for write permission associated with a dimension to apply to objects dimensioned by it, there must be at least one PERMIT command associated with the dimensioned object. When you want a dimensioned object to inherit write permission from its dimensions but you do not want it to have permission of its own, which could interact with the dimension permission, you can simply use a PERMIT READ with a single-cell permission condition that evaluates to YES. Dimension permission interacts with permission for objects dimensioned by it in the following ways:


Assigning Access Permissions to a Concat Dimension

Use the PERMIT command to grant or deny access to dimension values. Access restrictions that you apply to the concat dimension are added to any restrictions that already exist on the component dimensions.


Relations, Valuesets, and Worksheets

You can specify permission based on a single-cell permission condition for relations, valuesets, and worksheets. When there is restricted write permission for a dimension of a relation, it does not affect the relation. Restricted write permission on the dimension from which a valueset derives does not affect permission on the valueset.


Programs, Models, and Formulas

You can specify read and write permission for programs, models, and formulas with a single-cell permission condition. When you have read/write permission for a program, model, or formula, you can both edit and run it. When you have read-only permission, you can run it but not change it.


Change Permission Authority

You should avoid specifying a PERMIT PERMIT statement with a Boolean value as a permission condition (for example, YES or NO). 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 command. When you 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. For more information on workspace permission programs, see "Workspace Permission Programs" .


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.


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 LIMIT KEEP. You only need one PERMIT command 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.


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.


Permissions and Concat Dimensions

You can use the PERMIT command to assign permissions to a concat dimension. Any access restrictions on a concat dimension are in addition to the restrictions on its component dimensions. To have access to a value of the concat dimension, you must have permission to access the value in the concat itself and in all the components that contain the value.


Permissions and Dimension Surrogates

You cannot use the PERMIT command on a dimension surrogate. The access permissions of a dimension apply to all dimension surrogates defined for that dimension.

Examples

Example 19-29 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.

MONTH.BOOL<MONTH>  PROD.BOOL<PRODUCT>  DISTRICT.BOOL<DISTRICT>
-----------------  ------------------  -----------------------
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 command 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 command.

PERMIT WRITE

When there is no restricted write permission for sales, the following PERMIT command 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 19-30 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 authority DIMENSION TEXT
MAINTAIN authority ADD OTHER DBA
DEFINE prod_authority VARIABLE BOOLEAN <product authority>
...
" Assign values to the variable
...
REPORT prod_authority
 
              -----------------PROD_AUTHORITY------------------
              --------------------PRODUCT----------------------
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
PERMITERROR = NO
RPEPORT product
 
PRODUCT
-------------
Racquets
Sportswear
Footwear

CONSIDER authority
PERMIT READ WHEN AUTHORITY EQ 'dba'
PERMITRESET
Report product
 
PRODUCT
-------------
Tents
Canoes
Racquets
Sportswear
Footwear

Example 19-31 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 command 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 command, 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 19-32 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 command 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
CONSIDER sales
PERMIT READ WHEN sales.bool

Example 19-33 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 19-31, "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)