8 Security

Oracle OLAP secures your data using the standard security mechanisms of Oracle Database.

This chapter contains the following topics:

Security of Multidimensional Data in Oracle Database

Your company's data is a valuable asset. The information must be secure, private, and protected. Analytic data is particularly vulnerable because it is highly organized, easy to navigate, and summarized into meaningful units of measurement.

When you use Oracle OLAP, your data is stored in the database. It has the security benefits of Oracle Database, which leads the industry in security. You do not need to expose the data by transferring it to a standalone database. You do not need to administer security on a separate system. And you do not need to compromise your data by storing it in a less secure environment than Oracle Database.

Security Management

Because you have just one system to administer, you do not have to replicate basic security tasks such as these:

  • Creating user accounts

  • Creating and administering rules for password protection

  • Securing network connections

  • Detecting and eliminating security vulnerabilities

  • Safeguarding the system from intruders

The cornerstone of data security is the administration of user accounts and roles. Users open a connection with Oracle Database with a user name and password, and they have access to both dimensional and relational objects in the same session.

Types of Security

Users by default have no access rights to an analytic workspace or any other data type in another user's schema. The owner or an administrator must grant them, or a role to which they belong, any access privileges.

Oracle OLAP provides two types of security: Object security and data security.

  • Object security provides access to dimensional objects. You must set object security before other users can access them. Object security is implemented using SQL GRANT and REVOKE.

  • Data security provides fine-grained control of the data on a cellular level. This type of security is optional. You must define data security policies only when you want to restrict access to specific areas of a cube. Data security is implemented using the XML DB security of Oracle Database.

You can administer both data security and object security in Analytic Workspace Manager. For object security, you also have the option of using SQL GRANT and REVOKE.

About the Privileges

Using both object security and data security, you can grant and revoke the following privileges:

  • Alter: Change the definition of a cube or dimension. Users need this privilege to create and modify a dimensional model.

  • Delete: Remove old dimension members. Users need this privilege to refresh a dimension.

  • Insert: Add new dimension members. Users need this privilege to refresh a dimension.

  • Select: Query the cube or dimension. Users need this privilege to query a view of the cube or dimension or to use the CUBE_TABLE function. CUBE_TABLE is a SQL function that returns the values of a dimensional object.

  • Update: Change the data values of a cube or the name of a dimension member. Users need this privilege to refresh a dimension or cube.

Users exercise these privileges either using Analytic Workspace Manager to create and administer dimensional objects, or by using SQL to query them. They do not issue commands such as SQL INSERT and UPDATE directly on the cubes and dimensions.

Layered Security

For dimensional objects, you can manage security at these levels:

  • Dimension member

  • Dimension

  • Cube

  • Analytic workspace

  • View

  • Materialized view

The privileges are layered so that, for example, a user with SELECT data security on Software products must also have SELECT object security on the PRODUCT dimension and the Global analytic workspace. Users also need SELECT privileges on the views of the dimensional objects.

You administer security on views and materialized views for dimensional objects the same way as for any other views and materialized views in the database.

Setting Object Security

You can use either SQL or Analytic Workspace Manager to set object security. The results are identical.

Using SQL to Set Object Security

You can set and revoke object privileges on dimensional objects using the SQL GRANT and REVOKE commands.

Setting Object Security on an Analytic Workspace

Object privileges on an analytic workspace simply open the container. You must grant object privileges on the cubes and dimensions for users to be able to access them. The table name is the same as the analytic workspace name, with the addition of an AW$ prefix.

The following command enables Scott to attach the Global analytic workspace, AW$GLOBAL, to a session:

GRANT SELECT ON aw$global TO scott;

Setting Object Security on Dimensions

You can grant privileges on individual dimensions to enable users to query the dimension members and attributes. For users to query a cube, they must have privileges on every dimension of the cube.

The privileges apply to the entire dimension. However, you can set fine-grained access on a dimension to restrict the privileges, as described in "Creating Data Security Policies on Dimensions and Cubes".

Example 8-1 shows the SQL commands that enable Scott to query the Product dimension. They give Scott SELECT privileges on the Product dimension, on the Global analytic workspace, and on the Product view.

Example 8-1 Privileges to Query the Product Dimension

GRANT SELECT ON product TO scott;
GRANT SELECT ON aw$global TO scott;
GRANT SELECT ON product_view TO scott;

Setting Object Security on Cubes

Privileges on cubes enable users to access business measures and perform analysis. You must also grant privileges on each of the dimensions of the cube.

The privileges apply to the entire cube. However, you can create a data security policy on the cube or on its dimensions to restrict the privileges, as described in "Creating Data Security Policies on Dimensions and Cubes".

Example 8-2 shows the SQL commands that enable Scott to query the Units cube. They give Scott SELECT privileges on the Global analytic workspace, the cube, and all of its dimensions. Scott also gets privileges on the dimension views so that he can query the dimension attributes for formatted reports.

Example 8-2 Privileges to Query the Units Cube

/* Grant privileges on the analytic workspace */
GRANT SELECT ON global.aw$global TO scott;

/* Grant privileges on the cube */
GRANT SELECT ON global.units_cube TO scott;

/* Grant privileges on the dimensions */
GRANT SELECT ON global.channel TO scott;
GRANT SELECT ON global.customer TO scott;
GRANT SELECT ON global.product TO scott;
GRANT SELECT ON global.time TO scott;

/* Grant privileges on the cube, dimension, and hierarchy views */
GRANT SELECT ON global.units_cube_view TO scott;
GRANT SELECT ON global.channel_view TO scott;
GRANT SELECT ON global.channel_primary_view TO scott;
GRANT SELECT ON global.customer_view TO scott;
GRANT SELECT ON global.customer_shipments_view TO scott;
GRANT SELECT ON global.customer_segments_view TO scott;
GRANT SELECT ON global.product_view TO scott;
GRANT SELECT ON global.product_primary_view TO scott;
GRANT SELECT ON global.time_view TO scott;
GRANT SELECT ON global.time_calendar_view TO scott;
GRANT SELECT ON global.time_fiscal_view TO scott;

Example 8-3 shows the SQL commands that give SCOTT the privileges to query the relational tables for the detail level data and to use query rewrite to obtain summary data from the Units cube.

Example 8-3 Privileges to Use Cube Materialized Views for Query Rewrite

/* Grant privileges on materialized views using query rewrite */
GRANT GLOBAL QUERY REWRITE TO scott;

/* Grant privileges on the relational source tables */
GRANT SELECT ON global.channel_dim TO scott;
GRANT SELECT ON global.customer_dim TO scott;
GRANT SELECT ON global.product_dim TO scott;
GRANT SELECT ON global.time_dim TO scott;
GRANT SELECT ON global.units_fact TO scott;

/* Grant privileges on the analytic workspace */
GRANT SELECT ON global.aw$global TO scott;

/* Grant privileges on the cube */
GRANT SELECT ON global.units_cube TO scott;

/* Grant privileges on the dimensions */
GRANT SELECT ON global.channel TO scott;
GRANT SELECT ON global.customer TO scott;
GRANT SELECT ON global.product TO scott;
GRANT SELECT ON global.time TO scott;

Example 8-4 shows the SQL commands that give SCOTT the privileges to modify and update all dimensional objects in GLOBAL using Analytic Workspace Manager.

Note:

The GRANT ALL commands encompass more privileges than those discussed in this chapter. Be sure to review the list of privileges before using GRANT ALL.

Example 8-4 Privileges to Modify and Refresh GLOBAL

/* Grant privilege to use Analytic Workspace Manager */
GRANT OLAP_USER TO scott;

/* Grant privileges on the analytic workspace */
GRANT ALL ON global.aw$global TO scott;

/* Grant privileges on the cubes */
GRANT ALL ON global.units_cube TO scott;
GRANT ALL ON global.price_cost_cube TO scott;

/* Grant privileges on the dimensions */
GRANT ALL ON global.channel TO scott;
GRANT ALL ON global.customer TO scott;
GRANT ALL ON global.product TO scott;
GRANT ALL ON global.time TO scott;

Using Analytic Workspace Manager to Set Object Security

Analytic Workspace Manager provides a graphical interface for setting object security. It also displays the SQL commands, so that you can cut-and-paste them into a script.

Setting Object Security on an Analytic Workspace

Take these steps to set object security on an analytic workspace in Analytic Workspace Manager:

  1. In the navigation tree, right-click the analytic workspace and select Set Analytic Workspace Object Security.

    The Set Analytic Workspace Object Security dialog box is displayed.

  2. Complete the dialog box, then click OK.

    Click Help for specific information about the choices.

  3. Grant privileges on one or more cubes and their dimensions.

    Privileges on the analytic workspace do not automatically extend to the cubes and dimensions contained in the analytic workspace.

Figure 8-1 shows the SELECT privilege on GLOBAL granted to PUBLIC.

Figure 8-1 Setting Object Security on GLOBAL

Set Analytic Workspace Object Security dialog box
Description of "Figure 8-1 Setting Object Security on GLOBAL"

Setting Object Security on Dimensions

Take these steps to set object security on dimensions in Analytic Workspace Manager:

  1. In the navigation tree, right-click any dimension and select Set Dimension Object Security.

    The Set Dimension Object Security dialog box is displayed.

  2. Complete the dialog box, then click OK.

    You can set privileges on all of the dimensions simultaneously. Click Help for specific information about the choices.

  3. Grant privileges on the analytic workspace and one or more cubes. Use SQL to grant privileges on the views.

Figure 8-2 shows the SELECT privilege on all dimensions granted to PUBLIC.

Figure 8-2 Setting Object Security on Dimensions

Set Dimension Object Security dialog box
Description of "Figure 8-2 Setting Object Security on Dimensions"

Setting Object Security on Cubes

Take these steps to set object security on cubes in Analytic Workspace Manager:

  1. In the navigation tree, right-click any cube and select Set Cube Object Security.

    The Set Cube Object Security dialog box is displayed.

  2. Complete the dialog box, then click OK.

    You can set privileges on all of the cubes simultaneously. Click Help for specific information about the choices.

  3. Grant privileges on the cube's dimensions and the analytic workspace. Use SQL to grant privileges on the views.

Creating Data Security Policies on Dimensions and Cubes

Data security policies enable you to grant users and roles privileges on a selection of dimension members. For example, you might restrict district sales managers to the data for just their own districts instead of all geographic areas. You can create a data security policy on dimensions, cubes, or both:

  • When you create a data security policy on a dimension, the policy extends to all cubes with that dimension. You do not need to re-create the policy for each cube.

  • When you create a data security policy on a cube, you select the members for each dimension of the cube. The policy only applies to that cube.

  • When you create data security policies on both dimensions and cubes, users have privileges on the most narrowly defined portion of the data, where the policies overlap.

Granting Data Privileges

You can apply a policy to one or more users, roles, and data security roles. A data security role is a group of users and database roles that you can manage in Analytic Workspace Manager just for use in security policies. You create data security roles and policies in Analytic Workspace Manager.

As soon as you create a data security policy, all other users are automatically denied access. Analytic Workspace Manager creates a default policy that grants all privileges to the owner. Otherwise, the owner is denied access also.

Note:

Do not delete the default policy. It grants you the privileges to access your own data.

Selecting Data By Criteria

When defining a data security policy, you can select specific dimension members or those that meet certain criteria based on the dimension hierarchy. By using criteria instead of hard-coding specific dimension members, the selection remains valid after a data refresh. You do not need to modify the selection after adding members. For example, a security policy that grants SELECT privileges to all Hardware products remains valid when old products are rolled off and new products are added to the PRODUCT dimension.

Note:

You must have the OLAP_XS_ADMIN role to manage data security policies in Analytic Workspace Manager.

To create a data security policy in Analytic Workspace Manager: 

  1. Expand the folder for a dimension or a cube.

  2. Right-click Data Security and choose Create Data Security Policy.

    The Create Data Security Policy dialog box is displayed.

  3. On the General tab, type a descriptive name in the Data Security Policy Name field.

  4. Click Add Users or Roles.

    The Add Users or Roles dialog box is displayed.

  5. Select the users, roles, and OLAP data security roles to use this policy. Then click OK to close the dialog box.

    The selected users and roles are now listed in the table on the General tab.

  6. Select the permissions you want to grant to each user or role.

  7. On the Member Selection tab, select the dimension members or conditions. For cubes, set the scope for each dimension.

  8. Click OK to save the data security policy.

    The data security policy appears in the navigation tree in the Data Security folder for the dimension.

  9. Grant these users and roles object privileges on the dimension or cube, and on the analytic workspace.

    See Also:

Figure 8-3 shows the Member Selection tab of the data security policy for PRODUCT. Users who have privileges on the PRODUCT dimension based on this policy have access to all Hardware products. They do not have access to Software products or Total Product.

Figure 8-3 Restricting Product to Hardware and Descendants

Create Data Security Policy dialog box
Description of "Figure 8-3 Restricting Product to Hardware and Descendants"

See Also:

Analytic Workspace Manager Help for information about creating data security roles.