Home > Securing the Data in Dimens... > Setting Object Security
![]() Previous |
![]() Next |
You can use either SQL or the SQL Developer user interface to set object security. The results are identical:
Using the SQL Developer user interface, right-click the object in the Connections navigator, and select Privileges, then Grant. Complete the Perform Grant Action dialog box.
Using SQL, issue GRANT
or REVOKE
commands.
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;
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 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: TheGRANT 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;