Add Security Filter for Value Entry

johanna

1. I added security filter for Value Entry table to filter based on Global Dimension Code 2 = XXX.

2. I opened Item Card and got warning "You do not have permission to read the Value Entry table."

3. I used debugger and got "break on error" in Form Item Card and Codeunit 5804 ItemCostManagement\CalculateAverageCost(Item,AverageCost,AverageCostACY)

4. I added SETPERMISSIONFILTER like this :

CalculateAverageCost(Item,AverageCost,AverageCostACY)
WITH ValueEntry DO BEGIN
  RESET;
  SETCURRENTKEY("Item No.","Valuation Date","Location Code","Variant Code");

  SETRANGE("Item No.",Item."No.");
  SETFILTER("Valuation Date",Item.GETFILTER("Date Filter"));
  SETFILTER("Location Code",Item.GETFILTER("Location Filter"));
  SETFILTER("Variant Code",Item.GETFILTER("Variant Filter"));
  
  SETPERMISSIONFILTER; //add by johanna

  CALCSUMS(
    "Item Ledger Entry Quantity",
    "Cost Amount (Actual)",
    "Cost Amount (Actual) (ACY)",
    "Cost Amount (Expected)",
    "Cost Amount (Expected) (ACY)");
  ...

5. I opened Item Card again and did not get warning "You do not have permission to read the Value Entry table.", but I got this another warning :

The sum of the values in the Item Ledger Entry Quantity field cannot be calculated because the current key does not contain all the fields being filtered.

You must select a key that contains all the fields in the filter. The order of the fields is unimportant.

Filters: Item No.: 1000001K102
Table: Value Entry
Key fields: Item No.,Valuation Date,Location Code,Variant Code

6. I guess I got this warning because I don't have key in Value Entry table that contains my security filter field (Global Dimension 2 Code), so :
a. In the Value Entry table I add new key :

Key fields : Item No.,Valuation Date,Location Code,Variant Code,Global Dimension 2 Code

SumIndexField : Cost Amount (Expected),Cost Amount (Actual),Cost Amount (Expected) (ACY),Cost Amount (Actual) (ACY),Item Ledger Entry Quantity

b. In the Codeunit 5804, I change the SETCURRENTKEY :

CalculateAverageCost(VAR Item : Record Item;VAR AverageCost : Decimal;VAR AverageCostACY : Decimal) : Boolean
WITH ValueEntry DO BEGIN
  RESET;

  //remove by johanna - SETCURRENTKEY("Item No.","Valuation Date","Location Code","Variant Code");
  SETCURRENTKEY("Item No.","Valuation Date","Location Code","Variant Code","Global Dimension 2 Code"); //add by johanna
  FILTERGROUP(6);//add by johanna

  SETRANGE("Item No.",Item."No.");
  SETFILTER("Valuation Date",Item.GETFILTER("Date Filter"));
  SETFILTER("Location Code",Item.GETFILTER("Location Filter"));
  SETFILTER("Variant Code",Item.GETFILTER("Variant Filter"));
  
  SETPERMISSIONFILTER; //add by johanna
  
  CALCSUMS(
    "Item Ledger Entry Quantity",
    "Cost Amount (Actual)",
    "Cost Amount (Actual) (ACY)",
    "Cost Amount (Expected)",
    "Cost Amount (Expected) (ACY)");
  ...

7. Finally, I did not get this warning again :

The sum of the values in the Item Ledger Entry Quantity field cannot be calculated because the current key does not contain all the fields being filtered.

You must select a key that contains all the fields in the filter. The order of the fields is unimportant.

Filters: Item No.: 1000001K102
Table: Value Entry
Key fields: Item No.,Valuation Date,Location Code,Variant Code

But, I got this warning again : "You do not have permission to read the Value Entry table.
Contact your system manager if you need to have your permissions changed."

I'm so confused and did not know what should I do then..

Please help me..

Thank you..

rdebath

johanna wrote:

8. I used debugger and got "break on error" in ...

There may be more than one location.<-<-<-^W WILL

If it's in the same place you may be running into the problem that 'enhanced' security doesn't work the same as 'standard'. Try switching to standard.

johanna

Thank you for your reply ^^

After I got the last warning, I used debugger and got "break on error" in the same place, Codeunit 5804, where I took my coding. Yes, I think my coding (FILTERGROUP and SETPERMISSIONFILTER) is not works..:(

Do you mean in Codeunit 5804 I must return back to the standard code?
If yes, what should I do if I want to add security filter for Value Entry table and I cannot open Item Card?

I found that in Codeunit 5804 there already exists a read permission for Value Entry table and I have added a read permission for Value Entry table in Item Card. I have added SETPERMISSIONFILTER and FILTERGROUP but I still cannot open Item Card because I do not have permission to read the Value Entry table..

rdebath

The Security Model is an option on Version 4 (about SP2) or later.
It's in File->Database->Alter, Advanced Tab, on the Left, Half way down.

If it's set to 'Enhanced' extra 'protections' are put in at the SQL level. The protection is more or less worthless because 'Indirect' permissions have to be converted, by Navision, to 'Yes' permissions at that level. BUT not all the indirect permissions are actually converted properly so if you need to keep 'Enhanced' security you have to explicitly allow more access, sometimes more than you want.

In addition Enhanced Mode makes "Tools->Security->Synchronize All Logins" VERY VERY VERY slow.

As an aside; with the RTC in NAV2009 it's worse than useless as it forces the use of the delegation technique which subverts database security by allowing direct access to the database not mediated by the service tier.

johanna

Wow, yes, you are right, rdebath!! :thumbsup:

After I tried to change the security from 'enhanced' to 'standard', I can open Item Card successfully..=D>

Thank you so much for your help..