Breakpoint to see Query when Security Policy has been added

(0) Share

Report

Posted on by Sophs@4Sight

223

Good Morning

I hope you can help. I have created a Security Policy with role and am testing it, but when I add the role to my test user no records are shown even when there should be showing records.

The Policy and role has been added to the LedgerJournalTrans record and should validate on the LedgerDimension field.

I have removed everything from my Simple Query just to get it working first, but it is still not showing any records.

My question is on the form LedgerJournalTransDaily form, where can I add a Debug breakpoint so that I can see the final sql query that is created.

I have added a breakpoint to the DataSource LedgerJournalTrans.executequery(), but the result of Query does not contain the added Security policy bit. It looks the same when I test with a user that does not have the policy added.

Is there a way to see what the final sql select is.

If I do a sql select : 'select MODELEDQUERYDEBUGINFO from ModelSecPolRuntimeEx where name like '%_LedgerJournalTrans%'

the result is

'SELECT T1.RECID FROM LEDGERJOURNALTRANS T1 WHERE ((T1.PARTITION=5637144576) AND (T1.DATAAREAID=N'dat')) AND NOT (EXISTS (SELECT 'x' FROM DIMENSIONATTRIBUTEVALUECOMBINATION T2 WHERE ((T2.PARTITION=5637144576) AND ((T1.LEDGERDIMENSION=?) OR (T1.LEDGERDIMENSION=T2.RECID))))) ORDER BY T1.JOURNALNUM,T1.DATAAREAID,T1.LINENUM,T1.PARTITION'

As you can see I have removed all the additional validations and am only checking where the LedgerJournalTrans.LedgerDimension exists in DIMENSIONATTRIBUTEVALUECOMBINATION.RecId. This should make all records valid. But still no data is being shown for the user that has this role added.

Please can someone advise where I can add a breakpoint to see what the final query is, or why this Security Policy is not working.

I have the same question (0)

All responses (9)

Answers (1)

André Arnaud de Cal... 301,109 Super User 2025 Season 2 on at

Like (0)

Report

Hi Sophia,

Can you tell about the details of the Security policy? Can you share what exact query is used and what tables are constrained via the policy?

Was this reply helpful? Yes No
Verified answer

Sophs@4Sight 223 on at

Like (0)

Report

Good day Andre

I am now just trying to see if it is possible on the LedgerJournalTransDaily form to filter the data based on the Policy. On Contoso USRT General Ledger > General Journals > All I select Journal 25119. Below is the data. Just to test a policy I want toonly show records where the Account Type = Ledger. (So 5 record)

My Query is now the simplest that I could think to test it with

The security Policy I created is as follows :

The Role I created and assign to my test user is :

But the result is always : So it is showing nothing not even the records that should be showing.

From Sql

select MODELEDQUERYDEBUGINFO from ModelSecPolRuntimeEx
where name = 'ACCRecSec_LedgerJournalTrans'

The result is

SELECT T1.RECID FROM LEDGERJOURNALTRANS T1 WHERE (((PARTITION=5637144576) AND (DATAAREAID=N'dat')) AND (ACCOUNTTYPE=?)) ORDER BY T1.JOURNALNUM,T1.DATAAREAID,T1.LINENUM,T1.PARTITION

And if I debug LedgerJournalTrans and add breakpoint to LedgerJournalTrans.executequery() after super() the result in this.query is :

{Query object a7173b20: SELECT FIRSTFAST FORUPDATE * FROM LedgerJournalTrans(LedgerJournalTrans) USING INDEX NumVoucherIdx ORDER BY LedgerJournalTrans.Partition ASC, LedgerJournalTrans.dataAreaId ASC, LedgerJournalTrans.JournalNum ASC, LedgerJournalTrans.Voucher ASC, LedgerJournalTrans.LineNum ASC WHERE ((Invisible = 0)) AND LedgerJournalTable.JournalNum=LedgerJournalTrans.JournalNum OUTER JOIN FORUPDATE Id FROM LedgerVoucherType_CN(Ref_LedgerVoucherType_CN_LedgerVoucherType_CN) ON LedgerJournalTrans.LedgerVoucherType_CN = LedgerVoucherType_CN.RecId OUTER JOIN FORUPDATE DisplayValue, DisplayValue FROM DimensionAttributeValueCombination(Ref_DimensionAttributeValueCombination_LedgerDimension) ON LedgerJournalTrans.LedgerDimension = DimensionAttributeValueCombination.RecId OUTER JOIN FORUPDATE DisplayValue FROM FinTag(Ref_FinTag_FinTag) ON LedgerJournalTrans.FinTag = FinTag.RecId OUTER JOIN FORUPDATE DisplayValue, DisplayValue FROM DimensionAttributeValueCombination(Ref_DimensionAttributeValueCombination_OffsetLedgerDimension) ON LedgerJournalTrans.OffsetLedgerDimension = DimensionAttributeValueCombination.RecId OUTER JOIN FORUPDATE DisplayValue FROM FinTag(Ref_FinTag_OffsetFinTag) ON LedgerJournalTrans.OffsetFinTag = FinTag.RecId OUTER JOIN FORUPDATE TaxWithholdItemGroup FROM TaxWithholdItemGroupHeading_TH(Ref_TaxWithholdItemGroupHeading_TH_TaxWithholdItemGroupHeading_TH) ON LedgerJournalTrans.TaxWithholdItemGroupHeading_TH = TaxWithholdItemGroupHeading_TH.RecId OUTER JOIN FORUPDATE PersonnelNumber FROM HcmWorker(Ref_HcmWorker_Approver_FK) ON LedgerJournalTrans.Approver = HcmWorker.RecId OUTER JOIN FORUPDATE Tax1099Box FROM Tax1099Fields(Ref_Tax1099Fields_Tax1099Fields) ON LedgerJournalTrans.Tax1099Fields = Tax1099Fields.RecId OUTER JOIN FORUPDATE InvoiceDeclarationId FROM VendInvoiceDeclaration_IS(Ref_VendInvoiceDeclaration_IS_InvoiceDeclarationId_FK) ON LedgerJournalTrans.VendInvoiceDeclaration_IS = VendInvoiceDeclaration_IS.RecId OUTER JOIN FORUPDATE BankCurrencyTransferId FROM BankCurrencyTransferLog_RU(Ref_BankCurrencyTransferLog_RU_BankCurrencyTransferLog_RU) ON LedgerJournalTrans.BankCurrencyTransferLog_RU = BankCurrencyTransferLog_RU.RecId OUTER JOIN FORUPDATE Description FROM LogisticsLocation(Ref_LogisticsLocation_RemittanceLocation_FK) ON LedgerJournalTrans.RemittanceLocation = LogisticsLocation.RecId}.

The above query is exactly the same that I get when I run the same form as system admin and get all the records.

What am I doing wrong and how can I see the last select that determines why some of the records are now not showing.

I will greatly appreciate any feedback. Please note that I am trying to actually filter the LedgerJournalTrans records by the LedgerDimension field, but have now just made the query and policy as simple as possible to see if it works.

Kind regards

Was this reply helpful? Yes No
André Arnaud de Cal... 301,109 Super User 2025 Season 2 on at

Like (0)

Report

Hi Sophia,

Have you performed a database sync after the build of your project or solution? That is required to register the policy correctly. If this is the case, can you check if there are other policies active that also restricts the journal lines?

Was this reply helpful? Yes No
André Arnaud de Cal... 301,109 Super User 2025 Season 2 on at

Like (0)

Report

Hi Sophia,

I did create the same policy in my environment, and this is working fine after a build and database synchronization. So, please check the comments in my previous reply.

Was this reply helpful? Yes No
Sophs@4Sight 223 on at

Like (0)

Report

Good morning Andre and compliments of the season.

I have verified that I have no additional roles for this user that I am testing with, only 1 role for the Ledger Journal Trans Policy. I have also done a full model build and db sync. And when I test then none of the records on the lines are showing.

This is result of the test User

It gives me some hope if it is working on your side. Is there no way to debug to see what the final query is, so that I can see what is being included to cause the incorrect results.

Kind regards

Was this reply helpful? Yes No
André Arnaud de Cal... 301,109 Super User 2025 Season 2 on at

Like (0)

Report

Hi Sophia,

Are you able to send the objects of your XDS policy to me? Then I can verify it in my playground. I'm also happy to share my test objects with you.

Was this reply helpful? Yes No
Sophs@4Sight 223 on at

Like (0)

Report

Hi Andre

I am busy spinning up a new development box to start with a clean environment that does not have any custom code. Once that is complete I will create A simple query, policy and Role and test. If that works I will start working back to see if I can deermine why this is not working on the current solution.

Is it possible to send the code where you created the policy in your environments so that I can test that.

Kind regards

Was this reply helpful? Yes No
Sophs@4Sight 223 on at

Like (1)

Report

Good day Andre

I am happy to say that I was able to successfully get the XDS policy to work on a clean environment. I will now start adding my other custom developments back to see what is causing it to break.

Thank you for all your patience, help and assistance.

Was this reply helpful? Yes No
André Arnaud de Cal... 301,109 Super User 2025 Season 2 on at

Like (0)

Report

Thanks for the feedback and glad it is working in another environment. Still, I would be interested to know why it was not working in your initial environment. Anyway, your priority now is continuing with your other customizations.

Was this reply helpful? Yes No