Here is a simple example of how to maintain Essbase security in a maxl statement:
spool on to Security.log;
login admin password on localhost;
set timestamp on;
/*-------------------------------------------------------------------------------------------------------------*/
/* READ ACCESS TO WEST AND ITS DESCENDANTS ONLY */
/*-------------------------------------------------------------------------------------------------------------*/
create or replace filter Sample.Basic.'Read_WestMarket_Filter' READ on '@IDESCENDANTS("West")';
/*-------------------------------------------------------------------------------------------------------------*/
/* CREATE GROUP(S) */
/*-------------------------------------------------------------------------------------------------------------*/
create or replace group 'Read_WestMarket_Group';
/*-------------------------------------------------------------------------------------------------------------*/
/* GRANT FILTER ACCESS TO GROUP(S) */
/*-------------------------------------------------------------------------------------------------------------*/
grant filter TestApp.TestDb.'Read_WestMarket_Filter' to 'Read_WestMarket_Group';
/*-------------------------------------------------------------------------------------------------------------*/
/* ADD USERS TO GROUP(S) */
/*-------------------------------------------------------------------------------------------------------------*/
alter user testuser1 add to group 'Read_WestMarket_Group';
logout;
spool off;
exit;
Create all of your filters in one section, then create all of your groups, then grant the filters to the groups and finally put the users in those groups.
Here are some examples of other types of filters you may need to create:
/*-------------------------------------------------------------------------------------------------------------*/
/* WRITE ACCESS TO BUDGET */
/*-------------------------------------------------------------------------------------------------------------*/
create or replace filter Sample.Basic.'FilterName' WRITE on '"Budget"';
/*-------------------------------------------------------------------------------------------------------------*/
/* WRITE ACCESS TO BUDGET & LEVEL 0 MARKET */
/*-------------------------------------------------------------------------------------------------------------*/
create or replace filter Sample.Basic.'FilterName' WRITE on '"Budget", @LEVMBRS("Market",0)';
/*-------------------------------------------------------------------------------------------------------------*/
/* WRITE ACCESS TO BUDGET & LEVEL 0 EAST */
/*-------------------------------------------------------------------------------------------------------------*/
create or replace filter Sample.Basic.'FilterName' WRITE on '"Budget", @RELATIVE("EAST",0)';
The @RELATIVE function allows you to take any member within the hierarchy and get an associated level of that member. So we are able get all Level 0 members of East without having to take all Markets
/*-------------------------------------------------------------------------------------------------------------*/
/* WRITE ACCESS TO BUDGET & LEVEL 0 EAST (except NY) */
/*-------------------------------------------------------------------------------------------------------------*/
create or replace filter Sample.Basic.'FilterName' WRITE on '"Budget", @REMOVE(@RELATIVE("EAST",0),@LIST("New York")';
If you manually created each filter in Essbase Administration Services, you would put a READ statement on line 1 and a WRITE statement on Line 2. Well, with MAXL, simply seperate the lines with commas and put the actual filter in single quotes, like this:
/*-------------------------------------------------------------------------------------------------------------*/
/* WRITE ACCESS TO BUDGET & READ ACCESS TO OTHER VERSIONS */
/*-------------------------------------------------------------------------------------------------------------*/
create or replace filter Sample.Basic.'FilterName' WRITE on '"Budget"', READ on '@IDESCENDANTS("Scenario")';
Let's say you have data in the Sample.Basic Essbase cube, but don't want a given set of users to have access to it, you have two options: You can give them no access to that member or you can actually remove that member from the users profile (meaning, if they do a member selection, the will not even see that the member exists in the cube)
/*-------------------------------------------------------------------------------------------------------------*/
/* NO ACCESS TO BUDGET & READ ACCESS TO OTHER VERSIONS */
/*-------------------------------------------------------------------------------------------------------------*/
create or replace filter Sample.Basic.'FilterName' NONE on '"Budget"', READ on '@IDESCENDANTS("Scenario")';
or
/*-------------------------------------------------------------------------------------------------------------*/
/* NO ACCESS TO BUDGET & READ ACCESS TO OTHER VERSIONS */
/*-------------------------------------------------------------------------------------------------------------*/
create or replace filter Sample.Basic.'FilterName' NONE on '"Budget", @REMOVE(@IDESCENDANTS("Scenario",0),@LIST("Budget")';
The two examples above will give the end user a NOACCESS when doing a retrieval. The below example will give the user a "Unknown Member" Error message when doing a retrival since you are removing that member from the database for that user (even though the member is still there):
/*-------------------------------------------------------------------------------------------------------------*/
/* NO ACCESS TO BUDGET & READ ACCESS TO OTHER VERSIONS */
/*-------------------------------------------------------------------------------------------------------------*/
create or replace filter Sample.Basic.'FilterName' READ on '@IDESCENDANTS("Scenario")', META_READ on '@REMOVE(@IDESCENDANTS("Scenario",0),@LIST("Budget"));