Skip Headers
Previous
Previous
 
Next
Next

Setting Object Security

You can use either SQL or the SQL Developer user interface to set object security. The results are identical:

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.

Example: Privileges to Query the Product Dimension 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.

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.

Example: Privileges to Query the Units Cube 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.

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: Privileges to Modify and Refresh GLOBAL shows the SQL commands that give SCOTT the privileges to modify and update all dimensional objects in GLOBAL.


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.

Privileges to Modify and Refresh GLOBAL

/* 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;