Stephen Mackenzie 2 Posted January 29, 2019 Share Posted January 29, 2019 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. Link to comment Share on other sites More sharing options...
Khushboo Rabadia Posted March 4, 2019 Share Posted March 4, 2019 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. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now