Michael Ellerbeck

Once again I am fascinated by the Hollywood Movie Poster, would be fun to recreate in Qlik

http://krisztinaszucs.com/

Filed under: Uncategorized | Leave a comment »

Section Definition  –  Section (access | application)

With the section statement, it is possible to define whether the subsequent LOAD and SELECT statements should be considered as data or as a definition of the access rights.

The access rights section is specified by ‘Section Access’

If nothing is specified, section application is assumed.  (i.e. a normal qlikview is in section application mode)

Quick Definition of Section Access

The logic for reduction is that the reduction values are applied as selections, and the possible data after the selection is the reduced data provided to the user.

Common Errors

Most Common Error:  not UPPERing ALL the fields.

HIC spells it out

 All fields in Section Access must be upper case. Hence, the reducing field must be in upper case also in the data. Use the Upper() function and name the fields in upper case.

ADMIN rights are only relevant for local documents! Documents opened on a Server are always accessed with USER rights.

Tip: Comment out the Section access to see how it is joining

Tip from Steve Dark – Use % on the field name so you can hide the prefix

Note i.

A * character in the OMIT means all listed OMIT fields will be unavailable

Note ii.

A * character in the ‘Reducing Field’ also only allows access to all listed fields

As noted by Steve Dark

The biggest gotcha with Section Access is the use of * for all values.  A user being admin does not actually grant them rights to any data, a * has to be used also.  The * however doesn’t give access to all rows, only all of the rows that are explicitly listed in Section Access.  If all values appear in the Section Access (ie. someone has explicit rights to each value) then all is good, if not you will need to add some dummy rows to the Section Access so each value is listed.

So one way of granting true * access is by using a SQL union ALL to the reducefield for the QVSERVICE account. The service account needs ADMIN access if you want to be able to reload it anyways 🙂

SECTION ACCESS;

AUTH:
LOAD "ACCESS",
 "NTNAME",
 "REDUCEFIELD";
SQL SELECT [ACCESS]
 ,[NTNAME]
 ,[REDUCEFIELD]
FROM [Test].[dbo].[SecurityTbl]
UNION ALL
select 'ADMIN', 'DOMAIN\QVSERVICE', REDUCEFIELD
from [Test].[dbo].[Fields];

OR you could join it in Qlikview

SECTION ACCESS;

AUTH:
LOAD "ACCESS",
 "NTNAME",
 "REDUCEFIELD";
SQL SELECT [ACCESS]
 ,[NTNAME]
 ,[REDUCEFIELD]
FROM [Test].[dbo].[SecurityTbl]

Load Distinct
'ADMIN' as ACCESS,
'DOMAIN\QVSERVICE' as NTNAME,
REDUCEFIELD
 Resident
 TheFields;

Note iii. Section access on the Desktop versus the Access Point (QVS)

From Peter Cammaert

• ADMIN access in the Desktop: in QlikView Desktop, SA entries with ACCESS=ADMIN will always be able to get in, whatever the link values, data reduction results, strict exclusion setting, etc. This is a behavior that is Desktop-only. Probably configured this way to allow developers to debug…document security.
• Strict exclusion: in the Access Point, everybody is a USER, even the IDs that have ACCESS=ADMIN. Since most ADMINS have an empty link field (for example the service account responsible for reloading the document should best be configured with an empty link field.)  Strict exclusion will always deny them access because there is no data left after reduction.

My Findings:

If you want to use strict exclusion with the Access Point, you need to have the service account that reloads it as an ADMIN in section access. If you want to be able to use a * you need to create dummy records for every value in your reduction field.

Interestingly enough, even if you grant NTFS permissions to view a qlikview on the access point, it still checks section access to decide whether it is viewable to the user or not. (Whether the user or group is in section access) They can still get accessed denied if they have a reduction that isn’t part of your reduction fields (or null).

References

Of course HIC wrote a primer on section access 🙂 I love all of his white papers!

This is another good article on more complex authorization

Section Access: Strict Exclusion https://community.qlik.com/docs/DOC-8562

https://community.qlik.com/docs/DOC-1853

https://community.qlik.com/docs/DOC-8633

https://community.qlik.com/blogs/qlikviewdesignblog/2014/06/09/methods-for-data-reduction

https://community.qlik.com/blogs/qlikviewdesignblog/2014/06/02/data-reduction-using-multiple-fields

Filed under: Uncategorized | Leave a comment »

DBA friend sent me this one.

http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm

–To search all columns of all tables in Pubs database for the keyword “Computer”
EXEC SearchAllTables ‘Computer’
GO

Here is the complete stored procedure code:

CREATE PROC SearchAllTables
(
	@SearchStr nvarchar(100)
)
AS
BEGIN

	-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
	-- Purpose: To search all columns of all tables for a given search string
	-- Written by: Narayana Vyas Kondreddi
	-- Site: http://vyaskn.tripod.com
	-- Tested on: SQL Server 7.0 and SQL Server 2000
	-- Date modified: 28th July 2002 22:50 GMT


	CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

	SET NOCOUNT ON

	DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
	SET  @TableName = ''
	SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

	WHILE @TableName IS NOT NULL
	BEGIN
		SET @ColumnName = ''
		SET @TableName = 
		(
			SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
			FROM 	INFORMATION_SCHEMA.TABLES
			WHERE 		TABLE_TYPE = 'BASE TABLE'
				AND	QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
				AND	OBJECTPROPERTY(
						OBJECT_ID(
							QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
							 ), 'IsMSShipped'
						       ) = 0
		)

		WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
		BEGIN
			SET @ColumnName =
			(
				SELECT MIN(QUOTENAME(COLUMN_NAME))
				FROM 	INFORMATION_SCHEMA.COLUMNS
				WHERE 		TABLE_SCHEMA	= PARSENAME(@TableName, 2)
					AND	TABLE_NAME	= PARSENAME(@TableName, 1)
					AND	DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
					AND	QUOTENAME(COLUMN_NAME) > @ColumnName
			)
	
			IF @ColumnName IS NOT NULL
			BEGIN
				INSERT INTO #Results
				EXEC
				(
					'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
					FROM ' + @TableName + ' (NOLOCK) ' +
					' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
				)
			END
		END	
	END

	SELECT ColumnName, ColumnValue FROM #Results
END

Filed under: Uncategorized | Leave a comment »

I got sort of stumped on a recent interview by a question about section access. I had read about the concept but hadn’t ever sat down and played with it. Time to rectify!

First things first, edit the script and Hit Control Q Q, yay magic data.

Goto file and then create hidden script, set a password

Goto Insert, section access, Inline, and then Basic User Access

Let’s keep it simple to start. Apparently section access likes everything in uppercase 🙂

ACCESS USERID PASSWORD

ADMIN ADMIN ADMIN123

USER DIM1 DIM1

USER DIM2 DIM2

USER DIM3 DIM3

Reload it and save, now when you close it and come back in it should prompt you for a password.

Ok, lets make it a little more elaborate.

So let’s use section access to limit who can see Dim1, Dim2, and Dim3

This is a crude way of accomplishing it, add an OMIT header and then Cartesian out the fields that they shouldn’t see

Section Access;
LOAD * INLINE [
ACCESS, USERID, PASSWORD, OMIT
ADMIN, ADMIN, ADMIN123
USER, DIM1, DIM1, DIM2
USER, DIM1, DIM1, DIM3
USER, DIM2, DIM2, DIM1
USER, DIM2, DIM2, DIM3
USER, DIM3, DIM3, DIM1
USER, DIM3, DIM3, DIM2
];
Section Application;

A little more elegant approach is

Section Access;

LOAD * INLINE [
ACCESS, USERID, PASSWORD, OMITGROUP
ADMIN, ADMIN, ADMIN123
USER, DIM1, DIM1, DIM1
USER, DIM2, DIM2, DIM2
USER, DIM3, DIM3, DIM1
];

LOAD * INLINE [
OMITGROUP, OMIT
DIM1,DIM2
DIM1,DIM3
DIM2,DIM1
DIM2,DIM3
DIM3,DIM1
DIM3,DIM2
];

Section Application;

Next time I’ll dive even a little deeper

Filed under: Uncategorized | Leave a comment »

https://community.qlik.com/docs/DOC-14232

Filed under: Uncategorized | Leave a comment »

To actually download it, as stated here https://help.qlik.com/en-US/governance-dashboard/2.0/Content/Getting-Started-with-the-Governance-Dashboard.htm

The QlikView Governance Dashboard is obtained from www.qlik.com. Log on with a registered customer or partner account and select Support. On the Qlik Support page, select Customer Downloads and then select the QlikView tab.

Then you have to scroll down to product to find it!

Filed under: Uncategorized | Leave a comment »