Securing a data model

----

(% class="wikigeneratedid" id="HPrE9ambule" %)

This guide describes the methods for securing the information contained in data cubes. This is generally referred to as Row Level Security. We also refer to this as 'personalisation' of cubes and flows.

7

8

(% class="wikigeneratedid" %)

9

It presents an overview of classic personalisation methods with the use of user variables, and then introduces a more powerful and flexible approach:** Live Security**, dynamic customisation based on scripts.

(% class="box" %)

(((

💡 See [[Live Security: examples of use>>doc:.Live Security \: exemples.WebHome]] for detailed usage examples.

14

)))

15

16

= Security via personalisation =

17

18

Row-level security can be achieved by personalising cubes or flows using user variables **(${user.<variablename>}**).

19

20

== Personalisation at data model (cube) level ==

21

22

Line-level security is resolved at data cube generation via the use of user variables **(${user.<namevariable>}**) in the definition of the data source and data model. For example in SQL: SELECT * FROM UneTable WHERE country='${user.country}' AND service in '${user.services}'.

23

24

In this example, each user has a //country// variable equal to their country, and a //services// variable which is a comma-separated list of department names (Marketing,Sales,Logistics) to which the user has access rights.

25

26

Thus, for each user, DigDash generates a different cube (one for each combination of variable values) containing only the data corresponding to each user's "personalisation profile". If several users have the same values for these variables, they are said to have the same profile, so they share the same cube.

27

28

This mechanism is very simple to set up, and generally makes it possible to segment the volume of data, and therefore the memory load on the server during consultation. Users rarely all connect to the server at the same time, so the server will not need to store all the cubes in memory at all times.

29

30

Another advantage is that, depending on the type of data source, the security rule can be very advanced. Our simple example of filtering on two columns, //countries// and //services//, can take into account other criteria, come from other tables (that we don't want to include in the cube), etc.

31

32

On the other hand, this mechanism multiplies the number of cubes to be generated and therefore the number of queries to the data source.

33

34

Finally, the security rule can generate data redundancy between different cubes, representing the common part of the data that several users can see, even if they don't share exactly the same profile. This common part is duplicated in several cubes.

35

36

* **Advantages **: Strong security, easy to implement, great flexibility (the complexity of security depends on the capabilities of the data source).

37

* **Disadvantages **: More queries to generate cubes, risk of data redundancy in several cubes.

38

39

[[image:Picture1.png||alt="DM personalisation" data-xwiki-image-style-alignment="center" height="319" width="363"]]

40

41

== Personalisation at flow level (chart/table) ==

42

43

Another more dynamic approach is to use these user variables in the value of filters on each chart using the cube. To take the previous example, the cube takes its data from an SQL source: SELECT * FROM ATable. It is unique (not personalised) for all users, and therefore large. In all the flows using this cube, you can add a filter on the dimension Country ( rule //"equals to"// ${user.country}) and on the dimension Department ( rule //"equals to" //rule ${user.services}). Each time a flow is displayed, the filter will be applied to the data cube so that only the rows to which the user has access are retained. Of course, browsing these dimensions must be forbidden to the user so that they are restricted to this perimeter.

44

45

The advantage is that there is only one cube to generate, and therefore only one request to the data source.

46

47

On the other hand, as the filtering is controlled by the flows themselves when the dashboards are consulted, there is a risk of allowing users to access data that does not concern them. This can happen either because of a design flaw in the dashboard pages (e.g. the prohibition on navigation on a dimension has been omitted), or through manipulation (by an expert user) of the display requests sent to the server.

48

49

Finally, another disadvantage is the reduced flexibility of the security rule. In this approach, it depends directly on what has been stored in the cube and relies solely on dimension filtering.

50

51

* **Advantage **: A single request for the generation of a single cube.

52

* **Disadvantages **: tedious to implement (pre-configure filtering on all flows), low security, limited security rules.

53

54

[[image:Picture2c.png||alt="Picture2b.png" data-xwiki-image-style-alignment="center" height="337" width="341"]]

55

56

== Comparison of the 2 types of personalisation ==

57

58

Here is a table comparing these two personalisation approaches:

59

60

| |**Cube personalisation**|**{{glossaryReference glossaryId="Glossary" entryId="Flux"}}Flow{{/glossaryReference}} personalisation**

61

|**Security**|High|Low

62

|**Generation of cubes**|(((

63

X cubes generated (1 per "personalisation profile")

~=> X queries

)))|(((

1 cube generated

~=> 1 single query

)))

|**Implementation**|Simple|Tedious

72

|**Flexibility**|Depends on the data source (e.g. SQL)|Filtering on cube data only

73

74

= Security via Live Security{{id name="live_security"/}} =

== Concept ==

The Live Security approach aims to retain only the advantages of the two personalisation mechanisms described above: strong security, a low number of queries required to generate the cube, and implementation that is as simple as possible while retaining a sufficiently high level of flexibility.

79

80

It allows security rules to be added at the level of the data model itself, without multiplying the number of cubes generated and without having to manage security at row level in the flows.

81

82

Security is expressed through simple filtering rules set up using a wizard to cover the most common requirements for restricting browsing to members of one or more dimensions, depending on a user's variables.

83

84

For advanced needs, you can use a Javascript script that will be executed each time the cube is queried on the incoming selection. In this case, the selection can be transformed. This can range from simple filtering similar to the "{{glossaryReference glossaryId="Glossary" entryId="Flux"}}Flow{{/glossaryReference}} personalisation" approach to more complex transformation based on the user's profile. For example, changing an exploration level, deleting an axis, one or more measures, etc.

85

86

Some examples of advanced selection transformations (advanced examples of Live Security are detailed in our knowledge base):

87

88

* Filtering one dimension //or //another according to the value of a user variable.

89

* Creating "OR" filter rules between dimensions.

90

* Query another cube to extract a dynamic security perimeter, depending on the current selection...

91

92

On the security side, this transformation, whether simple or advanced, is applied before the selection is processed on the server side and only depends on the user's profile. There is no way for a user, even an expert, to alter this process in the same way as for flow personalisation.

93

94

* **Advantages **: A single request for the generation of a single cube, strong security, great flexibility (potentially total transformation of the flow selection).

95

* **Disadvantages **: Potentially complicated implementation (Javascript) for complex requirements. But there is an interface that helps users to create simple Live Security filters, without Javascript.

96

97

[[image:Picture3.png||alt="Live security" data-xwiki-image-style-alignment="center" height="427" width="759"]]

98

99

100

Capture en basse résolution

101

102

103

== Comparison of security methods ==

104

105

Here is a table comparing this new approach with the two previous personalisation approaches:

106

107

| |**Cube personnalisation**|**{{glossaryReference glossaryId="Glossary" entryId="Flux"}}Flow{{/glossaryReference}} personalisation**|**"Live Security"**

108

|**Security**|Strong|Low|High

109

|**Generation of cubes**|(((

110

X cubes generated (1 per "personalisation profile")

~=> X queries

)))|(((

1 cube generated

~=> 1 single request

)))|(((

1 cube generated

~=> 1 single query

)))

|**How to set it up**|Simple|Tedious (per {{glossaryReference glossaryId="Glossary" entryId="Flux"}}Flow{{/glossaryReference}})|Assistant / Script

123

|**Flexibility**|Depending on the data source (e.g. SQL)|Filtering on cube data only|Selection transformation

== Implementation ==

We will now look at how to implement the Live Security mechanism using a standard dimension filtering example.

128

129

The example is based on a data source containing at least two columns //Area //(level country) and //department// and value columns (measures). Each user is assigned to a country and one or more department and will therefore only see the figures relating to them. Some users may have the right to see all countries and/or all services.

=== Requirements ===

__Users__

Each user has two user variables in LDAP: //country// and //department//, which define the user's security perimeter. Each of the two variables can be empty, which means that the user can view all countries and/or departments. The variable //department// can be a list of service names separated by commas (no spaces after the commas).

136

137

For example, we have the following 2 users:

* User U1 :

** country =//'fr'//

** department = 'Marketing,Sales'

142

* User U2 :

143

** country = 'de'

144

** department = (empty string)

__Data model__

The data model is based on an SQL source on which the following query is executed:

149

150

151

SELECT country, department, val1, val2 FROM ATable

152

153

154

Note that there is no WHERE clause in this query, as the aim is to generate a single cube containing all the data for all the users.

155

156

The data model therefore contains two dimensions and two measures.

157

158

(% class="box warningmessage" %)

159

(((

160

⚠ **Important**

161

//The user variables **${user.country}**, **${user.department}** must not be used in this context, either in the data source (WHERE clause), in a calculated measurement formula or anywhere in the data model. Otherwise this would force the system to use cube-based personalisation, which we don't want in this approach.//

162

)))

163

164

=== Creation of the Live Security function ===

165

166

Live Security is activated in the **Advanced configuration **screen of the data model, on the **Advanced** tab. Here we describe the two ways of developing a Live Security function, either via our assistant dedicated to simple functions, or via the creation of a Javascript function.

167

168

[[image:Advanced_tab_EN.png||alt="Advanced tab"]]

169

170

(% class="wikigeneratedid" id="HCrE9ationavecl27assistant" %)

171

**Creating a function using the wizard**

172

173

1. To create a simple Live Security function without Javascript, you can click on the **Create (assistant)...** button next to the **Transformation selection function** drop-down list.

174

1. Enter a name for the function.

175

1. Choose the dimension **Country** for the first filter rule. This rule has no hierarchy or level, so leave these two fields empty.

176

1. Enter **${user.country}** in the **Selected members** field.

177

1. Add a new rule by clicking on the **+** icon

178

1. Select the dimension **Department **on the second filter rule. This rule has no hierarchy or level, so leave these two fields empty.

179

1. Enter **${user.department}** in the selected members field.

180

1. As we have defined that the user variable //department //can have multiple values separated by commas, a comma (**,**) must be entered in the value separator field.

181

1. Click **OK **to create the new Live Security function.

182

183

[[image:Function_assistant_EN.png||alt="Function assistant"]]

184

185

(% class="wikigeneratedid" id="HCrE9ationd27unscript" %)

186

**Creating a function based on a script**

187

188

1. To create an advanced Live Security function based on a JavaScript script, click the **Edit...** button next to the **Transformation selection function** drop-down list.

189

1. Choose **Shared function **in the Function **type**.

190

1. Add a new pre-defined function by clicking on the **+** button in the Function Manager toolbar.

191

1. Enter a name for the function.

192

193

You can now enter a script to transform the selections made on the cube corresponding to this data model. This is described in the following paragraph.

194

195

The script to be written is the body of a JavaScript function which takes a "selection" object as its parameter. This object represents the description of the result we want to obtain from the cube ("flattening" operation). This complex object defines the desired axes, dimensions, filters, measurements and other parameters specific to each type of flow (chart, table, etc.). What interests us in this example are the dimension filter parameters. The aim is to transform this selection to include two 'forced' filters, one on the user's country and one on their departments, if the user has defined (non-empty) variables for country and/or departments.

196

197

Let's start by retrieving the value of the user's variable //country//using the function **getUserAttribute('<variableName>')** :

198

199

200

var country = getUserAttribute('country');

201

202

203

Then, if the country variable is defined and not empty, we apply a filter to the selection object:

...

if (country != null && country != '')

{

var tabCountry = [country]; //creation of a table containing the country

213

214

var dim = selection.dm.getDimensionById('Country');

215

216

var filt = new FilterSelection(dim, -1, -1, [], tabCountry);

217

218

selection.setFilter(filt);

}

Explanation: The filter is constructed using the instruction **new FilterSelection(Dimension, hierarchyIndex, levelIndex, [ ], arrayMembers)**:

224

225

* **Dimension**: The dimension object can be retrieved directly from the data model accessible via **selection.dm**, using the **getDimensionById(dimId)** function.

226

* **hierarchyIndex** and **levelIndex**: Then, in this example, we filter the root members of the dimension directly, there is no hierarchy or level to specify (...-1, -1...).

227

* **[ ] **(empty array): Used internally, do not modify.

228

* **arrayMembers**: Finally, a filter on a dimension can have several members selected, so an array of members must be passed to it (here **tabCountry**). For the country, as the user only has one, the array contains only one element.

229

230

Then we apply this filter to the selection using **selection.setFilter(...)**.

231

232

Finally, we do the same thing for the variable //department//__,__ __which represents a list of departments__, which we treat specifically using the Javascript **split**() function. This splits a character string into an array of character strings according to a separator character (the comma in our case):

...

var department = getUserAttribute('department');

238

239

if (department != null && department != '')

{

var tabDepartment = department.split(',');

244

245

var dim = selection.dm.getDimensionById('Department');

246

247

var filt = new FilterSelection(dim, -1, -1, [], tabDepartment);

248

249

selection.setFilter(filt);

}

=== Use ===

All that remains is to create a flow based on this data model and place it on a dashboard page.

257

258

If you don't hide the dimensions //country// and //department// in the dashboard (or in the flow) it will still be possible to filter on one of these dimensions. However, the filter will be overwritten by the Live Security script if the user has a non-empty value for his variable //country// or //department//. In the end, filtering on these dimensions in the dashboard is only useful for users who have the right to see everything, i.e. those who have an empty value in their variable //country// and/or //department//.

== API reference ==

There are a few recommended methods for manipulating a selection, presented below.

263

264

* Retrieving user information

265

266

267

(String) getUserAttribute (attr)

268

269

270

Description : Returns the LDAP **attr** attribute of the user. See the [[User attributes >>doc:.User_attributes.WebHome]]page for a list of default user attributes.

271

It is also possible to retrieve the value of user parameters. See the paragraph [[Adding user parameters>>doc:Digdash.deployment.configuration.administration.WebHome||anchor="Parametres_utilisateurs"]] paragraph for more information.

Example:

var userName = getUserAttribute('displayName');

277

278

279

* **Retrieving user session information**

280

281

282

(String) getSessionAttribute (attr)

283

284

285

Description: Returns the user's Session **attr** attribute for the current session.

286

287

(% class="box infomessage" %)

288

(((

289

The use of session variables is covered on the page [[**Session Variables**>>doc:Digdash.user_guide.tutorials.session_variables_tutorial.WebHome]].

)))

Example:

var scenario = getSessionAttribute('Scenario');

296

297

298

* **Retrieving information from the current selection or data model**

299

300

301

(Number) getDDVar(variable)

302

303

304

Description: Returns the value of the corresponding **model variable** (DDVar). These variables are defined in the data model and correspond to a widget in the dashboard page.

305

306

(% class="box warningmessage" %)

307

(((

308

//Warning: do not confuse the DDVar with the user variable (LDAP attribute).//

)))

Example:

var rateEuroDollar = getDDVar('rateEuroDollar');

315

316

(Dimension) selection.dm.getDimensionById(dimId)

317

318

319

Description: Returns a Javascript object corresponding to the **dimId** identifier dimension. Returns **null** if this dimension is not in the current model. This object is needed for other functions, for example to build a filter.

Example:

var dimCountry = selection.dm.getDimensionById('Country');

325

326

327

* **Selection transformations**

328

329

__FilterSelection function__

330

331

332

(Filter) new FilterSelection(dim, hierarchy, level, [ ], ValuesTab)

333

// dim : dimension to be filtered

334

// hierarchy : hierarchy index (or -1 if no hierarchy)

335

// level : hierarchy level (or -1 for root)

336

// [] : internal array — always empty, do not touch!

337

// ValuesTab : List of values/members to be filtered

338

339

340

Description: Returns a JavaScript object corresponding to the desired filter on the dimension in parameter, in a given hierarchy and level (**-1** if no hierarchy/level) and with the specified members. This object is needed for other functions, for example to apply the filter to the selection.

341

342

(% class="box infomessage" %)

(((

💡 **Hierarchies**

In this function, you need to use indexes for hierarchies and levels. This corresponds to the position of the hierarchy in the dimension's hierarchies, as well as its level.

347

For hierarchies, the positions are as follows:

0

1

2

...

For levels, the positions are as follows:

-1 (root level)

0

1

...

For example, if in my time dimension I have three hierarchies: "Date", "Month Year", "Week Year" then the index of "Date" is 0, "Month Year" is 1 and "Week Year" is 2.

358

For the levels of Date: root -1, "day" 0, "month" 1 and "year" 2.

)))

Example :

var filter = new FilterSelection(dim, -1, -1, [], new Array('fr','it','de'));

365

366

void selection.setFilter(filter)

367

368

369

Description: Applies a filter to the current selection. Overwrites the existing filter on this dimension if there was one.

Example: Description

selection.setFilter(filter);

375

376

void setDDVar(variable, value)

377

378

379

Description: Modifies the value of a model variable (DDVar) for the current selection. The value of the variable is not modified persistently, only for this selection.

380

381

(% class="box warningmessage" %)

382

(((

383

⚠ //Warning: do not confuse the DDVar with the user variable (LDAP attribute).//

)))

Example:

setDDVar('rateEuroDollar', 1.06);

390

391

392

__FilterSelectionMatch function__

393

394

395

new FilterSelectionMatch (dim, hierarchy, level, values, operators, matchMode)

396

// dim : dimension to be filtered

397

// hierarchy : hierarchy index (or -1 if no hierarchy)

398

// level : hierarchy level (or -1 for root)

399

// values : table of values to be filtered

400

// operators : table of operators applied to each value

401

// matchMode : 0 = all rules must be true (AND), 1 = at least one of the rules must be true (OR)

Description: Allows you to filter a dimension using one or more conditions, specifying which operator to use for each value (equal, begins with, contains, etc.).

406

//values// and //operators// are parallel arrays: each value has its own operator.

407

It is also possible to choose whether the conditions should be combined using AND or OR.

408

409

The following operators are available:

410

411

|=(% style="width: 330px;" %)JavaScript code|=(% style="width: 260px;" %)Value|=(% style="width: 902px;" %)Meaning

412

|(% style="width:330px" %)OP_ISNOTNULL|(% style="width:260px" %)0|(% style="width:902px" %)Is non-null

413

|(% style="width:330px" %)OP_ISNULL|(% style="width:260px" %)1|(% style="width:902px" %)Is null

414

|(% style="width:330px" %)OP_EQUAL|(% style="width:260px" %)2|(% style="width:902px" %)Equals

415

|(% style="width:330px" %)OP_CONTAIN|(% style="width:260px" %)3|(% style="width:902px" %)Contains

416

|(% style="width:330px" %)OP_NOTCONTAIN|(% style="width:260px" %)4|(% style="width:902px" %)Does not contain

417

|(% style="width:330px" %)OP_NOTEQUAL|(% style="width:260px" %)5|(% style="width:902px" %)Different

418

|(% style="width:330px" %)OP_MATCHREGEXP|(% style="width:260px" %)6|(% style="width:902px" %)Matches the regular expression

419

|(% style="width:330px" %)OP_CONTAINWORD|(% style="width:260px" %)7|(% style="width:902px" %)Contains the word

420

|(% style="width:330px" %)OP_NOTCONTAINWORD|(% style="width:260px" %)8|(% style="width:902px" %)Does not contain the word

421

|(% style="width:330px" %)OP_SUP|(% style="width:260px" %)9|(% style="width:902px" %)Greater than

422

|(% style="width:330px" %)OP_INF|(% style="width:260px" %)10|(% style="width:902px" %)Lower

423

|(% style="width:330px" %)OP_SUPEQUAL|(% style="width:260px" %)11|(% style="width:902px" %)Greater than or equal to

424

|(% style="width:330px" %)OP_INFEQUAL|(% style="width:260px" %)12|(% style="width:902px" %)Less than or equal to

425

|(% style="width:330px" %)OP_STARTSWITH|(% style="width:260px" %)13|(% style="width:902px" %)Starts with

426

|(% style="width:330px" %)OP_ENDSWITH|(% style="width:260px" %)14|(% style="width:902px" %)Ends with

427

|(% style="width:330px" %)OP_ISIN|(% style="width:260px" %)15|(% style="width:902px" %)Is in

428

|(% style="width:330px" %)OP_ISNOTIN|(% style="width:260px" %)16|(% style="width:902px" %)Not in

429

430

__Example with 1 single filter :__

431

432

433

new FilterSelectionMatch(dim, hierarchy, level, ['admin'], [13], 0);

In this case :

* Value: ['admin']

* Operator: [13] → OP_STARTSWITH → login must begin with admin

440

* matchMode: 0 → only one rule, so AND or OR changes nothing.

441

442

__Example with 2 filters:__

443

444

445

new FilterSelectionMatch(dim, hierarchy, level, ['admin', 'John'], [13, 2], 1);

In this case:

* We combine 2 rules:

451

** login starts with admin (13 = STARTSWITH)

452

** login equal to John (2 = EQUAL)

453

* matchMode: 1 → at least one of the rules must be true (OR logic).

454

455

= To find out more... =

456

457

Wiki source code of Sécuriser un modèle de données

Sommaire