Variation on personalised information link - need to use Function Call to work out permissions, and now cannot select more than one row (on demand)

[Stephen Mackenzie 2]

Hi,

I have to do a variation of a personalised information link. We have to unpack the permissions on a document that the user has when running a report, by unpacking the ACL on a document (documentum), and then unpacking the ACL->Group membership, and then then the Group->user membership for the current logged on user. The Oracle function takes the document_id and the user_logon parameters, and then works out a "Yes xxx" or "No xxx" value.

The function looks like this. The definition isn't important, it takes a document primary key and a user logon and returns a string (Yes/No) based on the users access.

 

create or replace FUNCTION fnLabel_CheckDocumentAccess(p_logon in nvarchar2, p_r_object_id in nvarchar2) RETURN varchar2 AS

 

r_user_name nvarchar2(500);

r_return_access nvarchar2(50);

r_permissions int;

 

BEGIN

 

-- First we need to obtain look up the logon ID and return the

BEGIN

select UPPER(USER_NAME) into r_user_name from FD_DM_USER_S WHERE UPPER(USER_OS_NAME) = upper(p_logon);

EXCEPTION

when no_data_found then

BEGIN

r_return_access := 'No - Not a DM User';

return r_return_access;

END;

END;

/* GET the maximum permissions to this document for this user */

 

BEGIN

select

MAX(dm_repeating1_1.r_accessor_permit) into r_permissions

 

from

fd_rd_document_sp f,

fd_acl_sp a,

fd_group_sp g,

fd_dm_user_s s,

fd_acl_rp dm_repeating1_1,

fd_group_rp dm_repeating1_2,

(select distinct gr1.users_names as i_all_users_names, gr2.i_supergroups_names as group_name from fd_group_r gr1, fd_group_r gr2 where gr1.r_object_id = gr2.r_object_id and gr1.users_names is not NULL) gr3

Where

f.acl_name=a.object_name

and

s.user_name=dm_repeating1_1.r_accessor_name

and

s.user_name=g.group_name

and

--f.r_object_id='090388208132cf4c'

f.r_object_id = p_r_object_id

and

UPPER(gr3.i_all_users_names) = r_user_name

and

f.i_is_deleted = 0

and

dm_repeating1_1.r_object_id=a.r_object_id

and

dm_repeating1_2.r_object_id=g.r_object_id

and

gr3.group_name = g.group_name;

 

EXCEPTION

when no_data_found then

BEGIN

r_return_access := 'No - User not Found Against Document ACL';

return r_return_access;

END;

END;

 

IF r_permissions < 2 THEN

r_return_access := 'No - User Does Not Have Browse Minimum';

return r_return_access;

ELSE

r_return_access := 'Yes - Has Minimum of Browse Permission';

return r_return_access;

END IF;

 

END;

 

 

They way we use this in ORACLE, is to simply run a query for documents, and then use this function in the query and pass in the document identifer and a user name.

 

SELECT

A.r_object_id,

CASE WHEN fnLabel_CheckDocumentAccess('mp9293q',A.r_object_id) Like N'No%' THEN N'You are unauthorised to view this document in CARA' ELSE A.OBJECT_NAME END AS DOCUMENT_NAME

FROM FD_REGULATORY_DOCUMENT_SP A;

 

Basically the function simply takes the document id, and a users logon, and returns YES or NO based on whether they are authorised to view the document data.

 

This way, the document name gets "obfuscated" where the person has no access to the document in the base system. Something we need to reflect in the reporting environment.

I tried updating the SQL manually in the Information link, to use this function and pass in %CURRENT_USER%

SELECT

V1."COMMENTS" AS "COMMENTS",

V1."CONTENT_TYPE" AS "CONTENTTYPE",

V1."DM_CHRONICLE_ID" AS "DMCHRONICLEID",

V1."DM_DOCBASE" AS "DMDOCBASE",

V1."DM_DOCUMENT_INDEX" AS "DMDOCUMENTINDEX",

V1."DM_MODIFY_DATE" AS "DMMODIFYDATE",

V1."DM_OBJECT_ID" AS "DMOBJECTID",

V1."DM_OBJECT_TYPE" AS "DMOBJECTTYPE",

V1."DOC_LANGUAGE" AS "DOCLANGUAGE",

V1."DOCUMENT_BASIC_INFO_ID" AS "DOCUMENTBASICINFOID",

V1."DOCUMENT_FILE_ID" AS "DOCUMENTFILEID",

V1."DOCUMENT_ID" AS "DOCUMENTID",

V1."DOCUMENT_SUBTYPE" AS "DOCUMENTSUBTYPE",

V1."DOCUMENT_SUBUNIT" AS "DOCUMENTSUBUNIT",

V1."DOCUMENT_TYPE" AS "DOCUMENTTYPE",

V1."DOCUMENT_TYPE_GROUP" AS "DOCUMENTTYPEGROUP",

V1."DOCUMENT_TYPE_ID" AS "DOCUMENTTYPEID",

V1."DOCUMENT_UNIT" AS "DOCUMENTUNIT",

V1."EVMPD_CODE" AS "EVMPDCODE",

V1."EXPORTED_STATUS" AS "EXPORTEDSTATUS",

V1."FILE_SIZE" AS "FILESIZE",

V1."FILE_TYPE" AS "FILETYPE",

V1."FINAL" AS "FINAL",

V1."FLAG_RESOLVED" AS "FLAGRESOLVED",

V1."IS_AUTO_CREATED" AS "ISAUTOCREATED",

V1."IS_FOLDER" AS "ISFOLDER",

V1."ISSUE_DATE" AS "ISSUEDATE",

V1."LAST_UPDATED_ON" AS "LASTUPDATEDON",

V1."MERGED" AS "MERGED",

V1."RECORD_ID" AS "RECORDID",

V1."REFERENCE_NUMBER" AS "REFERENCENUMBER",

V1."REGISTRATION_UID" AS "REGISTRATIONUID",

V1."SAFE_COPY" AS "SAFECOPY",

V1."SUPPLEMENT" AS "SUPPLEMENT",

V1."TIME_STAMP" AS "TIMESTAMP",

CASE WHEN fnLabel_CheckDocumentAccess(%CURRENT_USER%, V1.REFERENCE_NUMBER) Like N'No%' THEN N'You are unauthorised to view this document' ELSE V1.OBJECT_NAME END AS DOCUMENT_NAME,

V1."UPP_REFERENCE_NUMBER" AS "UPPREFERENCENUMBER",

V1."UPP_SUPPLEMENT" AS "UPPSUPPLEMENT",

V1."VALID" AS "VALID",

V1."VERSION_ID" AS "VERSIONID",

V1."VERSION_SEQ" AS "VERSIONSEQ",

V1."COUNTRY_DISPLAY_LABEL" AS "COUNTRYDISPLAYLABEL",

V1."PREFERRED_TRADE_NAME" AS "PREFERREDTRADENAME",

V1."PROCEDURE_TYPE" AS "PROCEDURETYPE"

FROM

"ARIEL"."V_ARIEL_AT_REG_SUPPORTING_DOCS" V1

WHERE

(V1."REGISTRATION_UID" = REGISTRATION_UID)

AND

 

 

Now this Information link is parameterised by REGISTRATION_UID also, as you can see in the WHERE condition at the end. This always worked fine.

What happens in the player/client is - it works, IF I ONLY SELECT A SINGLE RECORD TO DRIVE ON DEMAND.

As soon as I select more than one value, the on-demand retrieval fails and returns an error. This was never the case before we introduced this function logic.

Any help appreciated, have tried using Oracle functions which return a sys_refcursor, but to no avail.

[Khushboo Rabadia]

First from spotfire you will need to pass in multiple values you can use map function like below in your input expression:

"$map("${RegId}", ",")"

Then in query you will need "in" clause instead of "=" if you are passing in multiple values

If your procedure is able to process multiple values passed on by spotfire then it will work otherwise your procedure needs to be changed to work with multiple values

If you are still facing issue, you can raise support case to work on this.