Synchronizing Security is quite slow

sdcharle · 2005-05-06

Are there some helpful hints for speeding up the 'synchronize security' process?

Even on a development server with not many users (<10), this process takes a long time (minutes). It's not clear WHY it would take so long, but what we hear from the consultants is it's not unusual for this to take a long time. Is this the case, or are we getting bad info?

Thanks.

ara3n · 2005-05-06

What version are u running? In 4.0, yes synchronization will take 10-20 minutes.

sdcharle · 2005-05-06

Yes, we're running 4.0. Should have mentioned that earlier, sorry.

That's what I'm seeing, in the neighborhood of 10 minutes.

ara3n wrote:

What version are u running? In 4.0, yes synchronization will take 10-20 minutes.

Trevor_Norcross · 2005-05-09

Does it lock up the client/workstation so it is unuseable (at least in Navision) until Sync is done?

sdcharle · 2005-05-10

Well, it freezes up the app doing the sync. It appears to be possible for others to continue using Navision. I am just wondering what it could possibly doing to make the process take that long, partially? Is it trying to do mathematical proofs that the new security settings won't cause any problems, or something?

Trevor Norcross wrote:

Does it lock up the client/workstation so it is unuseable (at least in Navision) until Sync is done?

Heltborg · 2005-05-11

The problem with the synchronisation is that for each user it creates and APP_role which is given acces to the appropriate tables. It runs through each user and creates the access. I dont know if you run on native or SQL. I work for a customer whos running on SQL with 1000 named users.
The sync takes 4 ½ hours to complete and if theres a lock from another client and its not released so that the sync. can continue it halts and has to start from the beginning again. So we have a job that closes alle external access to the database and start the sync every sunday. We just lucky that we can close Navision down for 6 hours. Otherwise we had no chance of sync. the users.

And the best part is that if you add a new user, that user needs to be synchronised before he can access the data. And the synchronize takes all users every time. It drops the access token and then creates it for scratch.

Ive tried to trace the changes when i do a sync and it ends up in more then 1.8 mill. statements for the whole sync. on the system.

There is not to my knowledge a way to make a partial sync. Its all or nothing.

Its a "feature" that according to MBS, maybe changed in the second service release for 4.0 12-18 month from now. ´

Hopefully MBS will see that this is not the optimal solution and send an update where it does partial sync for only the involved tables/users.

Regards Bo Heltborg

sdcharle · 2005-05-11

Well, I'm not exactly happy to hear that, but I do appreciate the information. Thanks for the response. We'll be looking forward to the fix for that.

Heltborg wrote:

The problem with the synchronisation is that for each user it creates and APP_role which is given acces to the appropriate tables. It runs through each user and creates the access. I dont know if you run on native or SQL. I work for a customer whos running on SQL with 1000 named users.
The sync takes 4 ½ hours to complete and if theres a lock from another client and its not released so that the sync. can continue it halts and has to start from the beginning again. So we have a job that closes alle external access to the database and start the sync every sunday. We just lucky that we can close Navision down for 6 hours. Otherwise we had no chance of sync. the users.

And the best part is that if you add a new user, that user needs to be synchronised before he can access the data. And the synchronize takes all users every time. It drops the access token and then creates it for scratch.

Ive tried to trace the changes when i do a sync and it ends up in more then 1.8 mill. statements for the whole sync. on the system.

There is not to my knowledge a way to make a partial sync. Its all or nothing.

Its a "feature" that according to MBS, maybe changed in the second service release for 4.0 12-18 month from now. ´

Hopefully MBS will see that this is not the optimal solution and send an update where it does partial sync for only the involved tables/users.

Regards Bo Heltborg

sdcharle · 2005-05-12

Bo,

On a practical note, what are you using to implement that job? Is there a good tool you can recommend? I have heard some people on these forums referring to a tool from ExpandIT, I believe. Are you using that or something else.

Thanks,
Steve

Heltborg wrote:

So we have a job that closes alle external access to the database and start the sync every sunday. We just lucky that we can close Navision down for 6 hours. Otherwise we had no chance of sync. the users.

Regards Bo Heltborg

Heltborg · 2005-05-23

Its a self developed SQL job that kicks users of the database. And close it for further acces

Afterwards we manually start the Sync job, because with the tools we have available cant start the Sync job.

Afterwards we open the database again

I know the utility you are referring to but we are not using it

Regards Bo

Jochen · 2005-05-31

We ran into exactly the same problem. We have only 250 users but the process still needs 1 hour and requires our SQL Server database reserved for this particular job.

Where did you get this release schedule information (that in second service release in about 12 - 18 this problem will be fixed) ? We've opened a support ticket and have tried to get a precise and valuable statement without any success so far.

Our workaround is to assign to all SQL Server users the db_owner role, which overrules the app_role concept in SQl Server, i.e. then synchronization ist not neccessary anymore. We have chosen this workaround because we don't have a time slot for a batch job of one our per night. But of course, therefore we have a security problem now.

Best regards
Jochen Anderko

Heltborg wrote:

The problem with the synchronisation is that for each user it creates and APP_role which is given acces to the appropriate tables. It runs through each user and creates the access. I dont know if you run on native or SQL. I work for a customer whos running on SQL with 1000 named users.
The sync takes 4 ½ hours to complete and if theres a lock from another client and its not released so that the sync. can continue it halts and has to start from the beginning again. So we have a job that closes alle external access to the database and start the sync every sunday. We just lucky that we can close Navision down for 6 hours. Otherwise we had no chance of sync. the users.

And the best part is that if you add a new user, that user needs to be synchronised before he can access the data. And the synchronize takes all users every time. It drops the access token and then creates it for scratch.

Ive tried to trace the changes when i do a sync and it ends up in more then 1.8 mill. statements for the whole sync. on the system.

There is not to my knowledge a way to make a partial sync. Its all or nothing.

Its a "feature" that according to MBS, maybe changed in the second service release for 4.0 12-18 month from now. ´

Hopefully MBS will see that this is not the optimal solution and send an update where it does partial sync for only the involved tables/users.

Regards Bo Heltborg

JanStepanek · 2005-06-07

Hello,

our customer database contain 1000 named users and security synchronization takes 10 hours. When I post this problem to www.partnersource.com I get this answer from Microsoft developpers:
***
In SP1 here are changes in xp_ndo.dll and system goes faster, but not so much as customer expects.
We have looked at alternative ways of handling the 'Security Sync' --> online, differential etc.
BUT we got stucked everytime trying to handle 'indirect' permissions. If Navision only had 'standard' permissions we would be able to port this one to one on the SQL Server. The indirect permission is not a group and not a per user permissions and needs to be handled using the 'app-role' functionallity of SQL Server.
At this point in time (not in any close future...) we are not able to change this behaviour. I can only recommend large installations to use 'sync security' outside office hours to prevent these locking timeouts.
***

Heltborg · 2005-06-14

I know this is a difficult issue regarding the problem in getting enough time for the syncronization. And making users DB_owner solves this problem. But this is a procedure I cannot recommend. By making all users members of the fixed role DB_OWNER you are giving away all security. Any users can connect to the database and perform all actions on the specifik database. And that includes adding new users, dropping tables, adding tables and so on.

This gives you no control over the actions the users are doing to the database.

In my opinion this is hazardous solution that can lead to even worse problems than the downtime during synchronization. Problems like rogue users and datainconsistency.

Unfortunatly i cannot give you any other solution to the sync. problem. I just wanted to point out some of the risks involving adding users to the DB_OWNER role.

Regards Bo Heltborg

darrenc · 2005-06-23

I recieved this from support yesterday working on a simular issue.

"There is a review of the SQL Security going on right now and they are looking to allow the synch to be run for just one user at a time and to also see if we can get better performance out of it."

If they can make it work with one user at a time I think it would make everyone's life easier.

Scott_Frappier · 2005-06-25

Just an FYI:

I have been working with Microsoft regarding this issue since the beginning of the year. The code that they are probably reviewing is a concept that I created for one of our customer sites that was having issues (they are a 100 user site, and cannot re-sync throughout the day.

I am in the final stages of testing the functionality, and I will post it in this thread as soon as the basic functionality works. I'm hoping that someone else in the Navision community can take it and modify it as needed, since I am not what I classify as a "hard-core" developer. I just get to a solution that works 99% of the time and leave the theoretical stuff to the pros

.

I will post it within the next couple days.

- Scott

Scott_Frappier · 2005-06-27

Hello:

Here is the objects and code that I promised. This is a work in process (it works with the basic Navision system, but linked tables do not currently work within the design, along with database users).

Please test thoroughly before introducing this into a Production environment! You have been warned!

Navision Objects:

OBJECT Table 2000000053 Windows Access Control
{
  OBJECT-PROPERTIES
  {
    Date=04/27/05;
    Time=[ 1:57:00 PM];
    Modified=Yes;
    Version List=NAVW14.00,SECURITY;
  }
  PROPERTIES
  {
    DataPerCompany=No;
    CaptionML=[ENU=Windows Access Control;
               ENC=Windows Access Control];
  }
  FIELDS
  {
    { 1   ;   ;Login SID           ;Text119       ;TableRelation="Windows Login".SID;
                                                   CaptionML=[ENU=Login SID;
                                                              ENC=Login SID] }
    { 2   ;   ;Role ID             ;Code20        ;TableRelation="User Role"."Role ID";
                                                   CaptionML=[ENU=Role ID;
                                                              ENC=Role ID] }
    { 3   ;   ;Company Name        ;Text30        ;TableRelation=Company.Name;
                                                   CaptionML=[ENU=Company Name;
                                                              ENC=Company Name] }
    { 5   ;   ;Login ID            ;Text64        ;FieldClass=FlowField;
                                                   CalcFormula=Lookup("SID - Account ID".ID WHERE (SID=FIELD(Login SID)));
                                                   CaptionML=[ENU=Login ID;
                                                              ENC=Login ID] }
    { 7   ;   ;Role Name           ;Text30        ;FieldClass=FlowField;
                                                   CalcFormula=Lookup("User Role".Name WHERE (Role ID=FIELD(Role ID)));
                                                   CaptionML=[ENU=Role Name;
                                                              ENC=Role Name] }
  }
  KEYS
  {
    {    ;Login SID,Role ID,Company Name           }
    {    ;Role ID                                  }
  }
  CODE
  {
    VAR
      SYM000@1000000000 : TextConst 'ENU=Could not find %1 to synchronize security.';
      SYM001@1000000001 : TextConst 'ENU=server';
      SYM002@1000000002 : TextConst 'ENU=database';
      SYM003@1000000003 : TextConst 'ENU=Unable to dynamically synchronize the security.\\Please run the synchronize batch process to update the security within the database.';
      Text001@1000000004 : TextConst 'ENU=S-1-';

    PROCEDURE SynchronizeSecurity@1000000000(UserID@1000000003 : Text[65];ClearPermissions@1000000005 : Integer);
    VAR
      Server@1000000001 : Record 2000000047;
      Database@1000000002 : Record 2000000048;
      ADOConn@1000000000 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000514-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'.Connection";
      SIDValues@1000000004 : ARRAY [7] OF Integer;
    BEGIN
      Server.RESET;
      Server.SETRANGE("My Server",TRUE);
      IF (NOT Server.ISEMPTY) AND (Server.COUNT = 1) THEN
        Server.FIND('-')
      ELSE
        ERROR(SYM000,SYM001);

      Database.RESET;
      Database.SETRANGE("My Database",TRUE);
      IF (NOT Database.ISEMPTY) AND (Database.COUNT = 1) THEN
        Database.FIND('-')
      ELSE
        ERROR(SYM000,SYM002);

      ParseSIDString(SIDValues);

      IF GetADOConnection(ADOConn,Server."Server Name",Database."Database Name") THEN BEGIN
        ADOConn.Execute('exec sp_updatesecurity ' + '''' + UserID + '''' + ',' + '''' + "Role ID" + '''' + ',' +
          '''' + "Company Name" + '''' + ',' + '''' + FORMAT(SIDValues[1]) + '''' + ',' + '''' + FORMAT(SIDValues[2]) +
          '''' + ',' + '''' + FORMAT(SIDValues[3]) + '''' + ',' +
           '''' + FORMAT(SIDValues[4]) + '''' + ',' + '''' + FORMAT(SIDValues[5]) + '''' + ',' + '''' +
           FORMAT(SIDValues[6]) + '''' + ',' + '''' + FORMAT(SIDValues[7]) + '''' + ',' + '''' + FORMAT(ClearPermissions) + '''');
      END ELSE
        ERROR(SYM003);
    END;

    LOCAL PROCEDURE GetADOConnection@1000000008(VAR ADOConn@1000000000 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000514-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'.Connection";DataSource@1000000002 : Text[250];InitialCatalog@1000000003 : Text[250]) : Boolean;
    VAR
      ConnectionString@1000000001 : Text[250];
    BEGIN
      ConnectionString := 'PROVIDER=SQLOLEDB.1;Persist Security Info=False;Trusted_Connection=Yes;Initial Catalog='+InitialCatalog+';';
      ConnectionString := ConnectionString + 'Data Source=' + DataSource + ';';

      IF CREATE(ADOConn) THEN BEGIN
        ADOConn.ConnectionString := ConnectionString;
        ADOConn.CursorLocation := 3;
        ADOConn.Open;
        IF (ADOConn.Errors.Count = 0) AND (ADOConn.State = 1) THEN
          EXIT(TRUE);
      END;

      EXIT(FALSE);
    END;

    LOCAL PROCEDURE ParseSIDString@1000000002(VAR PartValue@1000000000 : ARRAY [7] OF Integer);
    VAR
      PIDValue@1000000004 : Text[30];
      CurrentPartNo@1000000001 : Integer;
      PIDLength@1000000002 : Integer;
      CharCounter@1000000003 : Integer;
    BEGIN
      IF COPYSTR("Login SID",1,4) = Text001 THEN BEGIN

        PIDLength := STRLEN("Login SID");
        CharCounter := 2;
        CurrentPartNo := 1;

        REPEAT
          CharCounter += 1;
          IF COPYSTR("Login SID",CharCounter,1) = '-' THEN BEGIN
            EVALUATE(PartValue[CurrentPartNo],PIDValue);
            PIDValue := '';
            CurrentPartNo += 1;
          END ELSE
            PIDValue := PIDValue + COPYSTR("Login SID",CharCounter,1);
        UNTIL CharCounter = PIDLength;
        EVALUATE(PartValue[CurrentPartNo],PIDValue);

      END;
    END;

    BEGIN
    END.
  }
}

OBJECT Form 50097 User Synchronization
{
  OBJECT-PROPERTIES
  {
    Date=04/27/05;
    Time=[ 1:57:54 PM];
    Modified=Yes;
    Version List=SECURITY;
  }
  PROPERTIES
  {
    Width=14850;
    Height=6710;
    Editable=No;
    TableBoxID=1000000000;
    SourceTable=Table2000000054;
  }
  CONTROLS
  {
    { 1000000000;TableBox;220 ;220  ;14410;5500 ;HorzGlue=Both;
                                                 VertGlue=Both }
    { 1000000001;TextBox;0    ;0    ;4400 ;0    ;HorzGlue=Both;
                                                 ParentControl=1000000000;
                                                 InColumn=Yes;
                                                 SourceExpr=SID }
    { 1000000002;Label  ;0    ;0    ;0    ;0    ;ParentControl=1000000001;
                                                 InColumnHeading=Yes }
    { 1000000003;TextBox;0    ;0    ;4400 ;0    ;ParentControl=1000000000;
                                                 InColumn=Yes;
                                                 SourceExpr=ID }
    { 1000000004;Label  ;0    ;0    ;0    ;0    ;ParentControl=1000000003;
                                                 InColumnHeading=Yes }
    { 1000000005;TextBox;0    ;0    ;4400 ;0    ;ParentControl=1000000000;
                                                 InColumn=Yes;
                                                 SourceExpr=Name }
    { 1000000006;Label  ;0    ;0    ;0    ;0    ;ParentControl=1000000005;
                                                 InColumnHeading=Yes }
    { 1000000007;CommandButton;5170;5940;2200;550;
                                                 HorzGlue=Right;
                                                 VertGlue=Bottom;
                                                 Default=Yes;
                                                 PushAction=LookupOK;
                                                 InvalidActionAppearance=Hide }
    { 1000000008;CommandButton;7590;5940;2200;550;
                                                 HorzGlue=Right;
                                                 VertGlue=Bottom;
                                                 Cancel=Yes;
                                                 PushAction=LookupCancel;
                                                 InvalidActionAppearance=Hide }
    { 1000000009;CommandButton;12430;5940;2200;550;
                                                 HorzGlue=Right;
                                                 VertGlue=Bottom;
                                                 PushAction=FormHelp }
    { 1000000010;CommandButton;10010;5940;2200;550;
                                                 CaptionML=ENU=Quick Synchronize;
                                                 OnPush=VAR
                                                          WindowsAccessControl@1000000000 : Record 2000000053;
                                                          ClearPermissions@1000000003 : Integer;
                                                          Window@1000000002 : Dialog;
                                                        BEGIN
                                                          IF CONFIRM(SYM000,TRUE) THEN BEGIN
                                                            WindowsAccessControl.RESET;
                                                            WindowsAccessControl.SETRANGE("Login SID",SID);
                                                            IF NOT WindowsAccessControl.ISEMPTY THEN BEGIN
                                                              WindowsAccessControl.FIND('-');
                                                              Window.OPEN(SYM002);
                                                              ClearPermissions := 1;
                                                              REPEAT
                                                                Window.UPDATE(1,WindowsAccessControl."Role ID");
                                                                WindowsAccessControl.SynchronizeSecurity(ID,ClearPermissions);
                                                                ClearPermissions := 0;
                                                              UNTIL WindowsAccessControl.NEXT = 0;
                                                              Window.CLOSE;
                                                            END;
                                                          END;

                                                          MESSAGE(SYM001);
                                                        END;
                                                         }
  }
  CODE
  {
    VAR
      SYM000@1000000000 : TextConst 'ENU=Do you want to synchronize the roles for this user?';
      SYM001@1000000001 : TextConst 'ENU=Synchronized Successfully!';
      SYM002@1000000002 : TextConst 'ENU=Role:     #1##########\Progress: @2@@@@@@@@@@';

    BEGIN
    END.
  }
}

SQL Object:

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE  PROCEDURE dbo.sp_updatesecurity 
@UserID VARCHAR(65),
@RoleID VARCHAR(20),
@CompanyName VARCHAR(30),
@InVersionNo INTEGER,
@InSecurityNTAuthority INTEGER,
@InSecurityNTNonUnique INTEGER,
@InMachineIdentification  INTEGER,
@InMachineIdentification2 INTEGER,
@InMachineIdentification3 INTEGER,
@InUniqueUserID INTEGER,
@ClearPriorPermissions INTEGER
AS

SET XACT_ABORT ON
BEGIN TRANSACTION SecurityUpdate

/* Declarations */
DECLARE @VersionNo BINARY(1)
DECLARE @SecurityNTAuthority BINARY(1)
DECLARE @SecurityNTNonUnique BINARY(7)
DECLARE @MachineIdentification BINARY(4)
DECLARE @MachineIdentification2 BINARY(4)
DECLARE @MachineIdentification3 BINARY(4)
DECLARE @UniqueUserID BINARY(4)
DECLARE @SystemObjects CURSOR
DECLARE @SystemObjectsClear CURSOR
DECLARE @Permissions CURSOR
DECLARE @SIFTPermissions CURSOR
DECLARE @SystemObjectsSIFT CURSOR
DECLARE @ApplicationRoleID VARCHAR(250)
DECLARE @VersionNoString VARCHAR(4)
DECLARE @SecurityNTAuthorityString VARCHAR(4)
DECLARE @SecurityNTNonUniqueString VARCHAR(16)
DECLARE @MachineIdentificationString VARCHAR(10)
DECLARE @MachineIdentification2String VARCHAR(10)
DECLARE @MachineIdentification3String VARCHAR(10)
DECLARE @UniqueUserIDString VARCHAR(10)
DECLARE @TableString VARCHAR(250)
DECLARE @TableName VARCHAR(250)
DECLARE @ObjectType INTEGER
DECLARE @ObjectID INTEGER
DECLARE @ReadPermission INTEGER
DECLARE @InsertPermission INTEGER
DECLARE @ModifyPermission INTEGER
DECLARE @DeletePermission INTEGER
DECLARE @ExecutePermission INTEGER

/* Convert incoming integers to binary values */
SET @VersionNo = CAST(@InVersionNo AS BINARY(1))
SET @SecurityNTAuthority = CAST(@InSecurityNTAuthority AS  BINARY(1))
SET @SecurityNTNonUnique = CAST(@InSecurityNTNonUnique AS  BINARY(7))
SET @MachineIdentification = CAST(@InMachineIdentification AS  BINARY(4))
SET @MachineIdentification2 = CAST(@InMachineIdentification2  AS  BINARY(4))
SET @MachineIdentification3 = CAST(@InMachineIdentification3 AS  BINARY(4))
SET @UniqueUserID = CAST(@InUniqueUserID AS  BINARY(4))

/* Convert binary values to binary string values */
exec master.dbo.xp_varbintohexstr @VersionNo, @VersionNoString OUTPUT 
exec master.dbo.xp_varbintohexstr @SecurityNTAuthority, @SecurityNTAuthorityString OUTPUT 
exec master.dbo.xp_varbintohexstr @SecurityNTNonUnique, @SecurityNTNonUniqueString OUTPUT 
exec master.dbo.xp_varbintohexstr @MachineIdentification, @MachineIdentificationString OUTPUT 
exec master.dbo.xp_varbintohexstr @MachineIdentification2, @MachineIdentification2String OUTPUT 
exec master.dbo.xp_varbintohexstr @MachineIdentification3, @MachineIdentification3String OUTPUT 
exec master.dbo.xp_varbintohexstr @UniqueUserID, @UniqueUserIDString OUTPUT 

/* Set the application role information */
SET @ApplicationRoleID = '$ndo$ar$000005' + RIGHT(@VersionNoString,2) + RIGHT(@SecurityNTAuthorityString,2) +
  RIGHT(@SecurityNTNonUniqueString,14) + RIGHT(@MachineIdentificationString,8) + 
  RIGHT(@MachineIdentification2String,8) + RIGHT(@MachineIdentification3String,8) +
  RIGHT(@UniqueUserIDString,8)

/* Revoke all permissions on tables in database */
IF @ClearPriorPermissions = 1 
BEGIN
  SET @SystemObjectsClear = CURSOR FAST_FORWARD FOR
    SELECT name FROM sysobjects WHERE xtype = 'U'
  OPEN @SystemObjectsClear
  FETCH NEXT FROM @SystemObjectsClear INTO @TableName

  WHILE (@@FETCH_STATUS = 0)
  BEGIN  
    exec ('REVOKE SELECT ON ' + '[' + @TableName + ']' +  ' TO ' + '[' + @ApplicationRoleID + ']')
    exec ('REVOKE INSERT ON ' + '[' + @TableName + ']' +  ' TO ' + '[' + @ApplicationRoleID + ']')
    exec ('REVOKE UPDATE ON ' + '[' + @TableName + ']' +  ' TO ' + '[' + @ApplicationRoleID + ']')
    exec ('REVOKE DELETE ON ' + '[' + @TableName + ']' +  ' TO ' + '[' + @ApplicationRoleID + ']')
    FETCH NEXT FROM @SystemObjectsClear INTO @TableName
  END

  CLOSE @SystemObjectsClear
  DEALLOCATE @SystemObjectsClear
END

/* Loop through Role Permissions and Update Tables */
SET @Permissions = CURSOR FAST_FORWARD FOR
  SELECT [Role ID], [Object Type], [Object ID], [Read Permission], [Insert Permission], [Modify Permission], [Delete Permission], [Execute Permission] 
  FROM Permission 
  WHERE ([Role ID] = @RoleID AND [Object Type] = 0 AND [Object ID] != 0)
OPEN @Permissions
FETCH NEXT FROM @Permissions INTO @RoleID, @ObjectType, @ObjectID, @ReadPermission, @InsertPermission, @ModifyPermission, @DeletePermission, @ExecutePermission
WHILE (@@FETCH_STATUS = 0)
BEGIN
  IF @CompanyName != '' 
  BEGIN
    IF @ObjectID = '2000000001' OR @ObjectID = '2000000007' OR @ObjectID = '2000000009' OR @ObjectID = '2000000010' OR
       @ObjectID = '2000000020' OR @ObjectID = '2000000022' OR @ObjectID = '2000000024' OR @ObjectID = '2000000026' OR
       @ObjectID = '2000000028' OR @ObjectID = '2000000029' OR @ObjectID = '2000000037' OR @ObjectID = '2000000038' OR
       @ObjectID = '2000000039' OR @ObjectID = '2000000040' OR @ObjectID = '2000000041' OR @ObjectID = '2000000042' OR
       @ObjectID = '2000000043' OR @ObjectID = '2000000044' OR @ObjectID = '2000000045' OR @ObjectID = '2000000046' OR
       @ObjectID = '2000000047' OR @ObjectID = '2000000048' OR @ObjectID = '2000000049' OR @ObjectID = '2000000050' OR
       @ObjectID = '2000000052' OR @ObjectID = '2000000055' OR @ObjectID = '2000000056' OR @ObjectID = '2000000058' OR
       @ObjectID = '2000000059'
    BEGIN
      IF @ObjectID = '2000000001' SET @TableString = 'Object'
      IF @ObjectID = '2000000007' SET @TableString = 'Date'
      IF @ObjectID = '2000000009' SET @TableString = 'Session'
      IF @ObjectID = '2000000010' SET @TableString = 'Database File'
      IF @ObjectID = '2000000020' SET @TableString = 'Drive'
      IF @ObjectID = '2000000022' SET @TableString = 'File'
      IF @ObjectID = '2000000024' SET @TableString = 'Monitor'
      IF @ObjectID = '2000000026' SET @TableString = 'Integer'
      IF @ObjectID = '2000000028' SET @TableString = 'Table Information'
      IF @ObjectID = '2000000029' SET @TableString = 'System Object'
      IF @ObjectID = '2000000037' SET @TableString = 'Performance'
      IF @ObjectID = '2000000038' SET @TableString = 'AllObj'
      IF @ObjectID = '2000000039' SET @TableString = 'Printer'
      IF @ObjectID = '2000000040' SET @TableString = 'License Information'
      IF @ObjectID = '2000000041' SET @TableString = 'Field'
      IF @ObjectID = '2000000042' SET @TableString = 'OLE Control'
      IF @ObjectID = '2000000043' SET @TableString = 'License Permission'
      IF @ObjectID = '2000000044' SET @TableString = 'Permission Range'
      IF @ObjectID = '2000000045' SET @TableString = 'Windows Language'
      IF @ObjectID = '2000000046' SET @TableString = 'Automation Servers'
      IF @ObjectID = '2000000047' SET @TableString = 'Server'
      IF @ObjectID = '2000000048' SET @TableString = 'Database'
      IF @ObjectID = '2000000049' SET @TableString = 'Code Coverage'
      IF @ObjectID = '2000000050' SET @TableString = 'Windows Object'
      IF @ObjectID = '2000000052' SET @TableString = 'Windows Group Member'
      IF @ObjectID = '2000000055' SET @TableString = 'SID - Account ID'
      IF @ObjectID = '2000000056' SET @TableString = 'User SID'
      IF @ObjectID = '2000000058' SET @TableString = 'AllObjWithCaption'
      IF @ObjectID = '2000000059' SET @TableString = 'Breakpoints'
    END ELSE
    BEGIN
      SET @TableString = @CompanyName + '$' + (SELECT Name FROM Object WHERE ([ID] = @ObjectID AND Type = @ObjectType AND[Company Name] = @CompanyName))
    END
  END ELSE
  BEGIN 
    IF @ObjectID = '2000000001' OR @ObjectID = '2000000007' OR @ObjectID = '2000000009' OR @ObjectID = '2000000010' OR
       @ObjectID = '2000000020' OR @ObjectID = '2000000022' OR @ObjectID = '2000000024' OR @ObjectID = '2000000026' OR
       @ObjectID = '2000000028' OR @ObjectID = '2000000029' OR @ObjectID = '2000000037' OR @ObjectID = '2000000038' OR
       @ObjectID = '2000000039' OR @ObjectID = '2000000040' OR @ObjectID = '2000000041' OR @ObjectID = '2000000042' OR
       @ObjectID = '2000000043' OR @ObjectID = '2000000044' OR @ObjectID = '2000000045' OR @ObjectID = '2000000046' OR
       @ObjectID = '2000000047' OR @ObjectID = '2000000048' OR @ObjectID = '2000000049' OR @ObjectID = '2000000050' OR
       @ObjectID = '2000000052' OR @ObjectID = '2000000055' OR @ObjectID = '2000000056' OR @ObjectID = '2000000058' OR
       @ObjectID = '2000000059'
    BEGIN
      IF @ObjectID = '2000000001' SET @TableString = 'Object'
      IF @ObjectID = '2000000007' SET @TableString = 'Date'
      IF @ObjectID = '2000000009' SET @TableString = 'Session'
      IF @ObjectID = '2000000010' SET @TableString = 'Database File'
      IF @ObjectID = '2000000020' SET @TableString = 'Drive'
      IF @ObjectID = '2000000022' SET @TableString = 'File'
      IF @ObjectID = '2000000024' SET @TableString = 'Monitor'
      IF @ObjectID = '2000000026' SET @TableString = 'Integer'
      IF @ObjectID = '2000000028' SET @TableString = 'Table Information'
      IF @ObjectID = '2000000029' SET @TableString = 'System Object'
      IF @ObjectID = '2000000037' SET @TableString = 'Performance'
      IF @ObjectID = '2000000038' SET @TableString = 'AllObj'
      IF @ObjectID = '2000000039' SET @TableString = 'Printer'
      IF @ObjectID = '2000000040' SET @TableString = 'License Information'
      IF @ObjectID = '2000000041' SET @TableString = 'Field'
      IF @ObjectID = '2000000042' SET @TableString = 'OLE Control'
      IF @ObjectID = '2000000043' SET @TableString = 'License Permission'
      IF @ObjectID = '2000000044' SET @TableString = 'Permission Range'
      IF @ObjectID = '2000000045' SET @TableString = 'Windows Language'
      IF @ObjectID = '2000000046' SET @TableString = 'Automation Servers'
      IF @ObjectID = '2000000047' SET @TableString = 'Server'
      IF @ObjectID = '2000000048' SET @TableString = 'Database'
      IF @ObjectID = '2000000049' SET @TableString = 'Code Coverage'
      IF @ObjectID = '2000000050' SET @TableString = 'Windows Object'
      IF @ObjectID = '2000000052' SET @TableString = 'Windows Group Member'
      IF @ObjectID = '2000000055' SET @TableString = 'SID - Account ID'
      IF @ObjectID = '2000000056' SET @TableString = 'User SID'
      IF @ObjectID = '2000000058' SET @TableString = 'AllObjWithCaption'
      IF @ObjectID = '2000000059' SET @TableString = 'Breakpoints'
    END ELSE
    BEGIN
      SET @TableString = '%' + (SELECT TOP 1 Name FROM Object WHERE [ID] = @ObjectID AND Type = @ObjectType) + '%'
    END
  END
   
  /* Eliminate invalid characters */
  SET @TableString = REPLACE(@TableString,'.','_')
  SET @TableString = REPLACE(@TableString,'"','_')
  SET @TableString = REPLACE(@TableString,'\','_')
  SET @TableString = REPLACE(@TableString,'/','_')
  SET @TableString = REPLACE(@TableString,'''','_')
  
  SET @SystemObjects = CURSOR FAST_FORWARD FOR
    SELECT name FROM sysobjects WHERE name LIKE @TableString AND xtype = 'U'
  OPEN @SystemObjects
  FETCH NEXT FROM @SystemObjects INTO @TableName
  WHILE (@@FETCH_STATUS = 0)
  BEGIN  
    IF @ReadPermission = 1
      exec ('GRANT SELECT ON ' + '[' + @TableName + ']' + ' TO ' + '[' + @ApplicationRoleID + ']')
    IF @InsertPermission = 1
      exec ('GRANT INSERT ON ' + '[' + @TableName + ']' + ' TO ' + '[' + @ApplicationRoleID + ']')
    IF @ModifyPermission = 1
      exec ('GRANT UPDATE ON ' + '[' + @TableName + ']' + ' TO ' + '[' + @ApplicationRoleID + ']')
    IF @DeletePermission = 1
      exec ('GRANT DELETE ON ' + '[' + @TableName + ']' + ' TO ' + '[' + @ApplicationRoleID + ']')
    FETCH NEXT FROM @SystemObjects INTO @TableName
  END 
  FETCH NEXT FROM @Permissions INTO @RoleID, @ObjectType, @ObjectID, @ReadPermission, @InsertPermission, @ModifyPermission, @DeletePermission, @ExecutePermission
END

CLOSE @SystemObjects
DEALLOCATE @SystemObjects
CLOSE @Permissions
DEALLOCATE @Permissions

/* Update SIFT Tables */
SET @SIFTPermissions = CURSOR FAST_FORWARD FOR
  SELECT [Role ID], [Object Type], [Object ID], [Read Permission], [Insert Permission], [Modify Permission], [Delete Permission], [Execute Permission] 
  FROM Permission 
  WHERE ([Role ID] = @RoleID AND [Object Type] = 0 AND [Object ID] != 0)
OPEN @SIFTPermissions
FETCH NEXT FROM @SIFTPermissions INTO @RoleID, @ObjectType, @ObjectID, @ReadPermission, @InsertPermission, @ModifyPermission, @DeletePermission, @ExecutePermission
WHILE (@@FETCH_STATUS = 0)
BEGIN
  IF @CompanyName != '' 
  BEGIN
    SET @TableString = @CompanyName + '$' + CAST((SELECT [ID] FROM Object WHERE ([ID] = @ObjectID AND Type = @ObjectType AND[Company Name] = @CompanyName)) AS VARCHAR) + '$' + '%'
  END ELSE
  BEGIN 
    SET @TableString = '%' + CAST((SELECT TOP 1 [ID] FROM Object WHERE [ID] = @ObjectID AND Type = @ObjectType) AS VARCHAR) + '$' + '%'
  END
  
  /* Elminate invalid characters */
  SET @TableString = REPLACE(@TableString,'.','_')
  SET @TableString = REPLACE(@TableString,'"','_')
  SET @TableString = REPLACE(@TableString,'\','_')
  SET @TableString = REPLACE(@TableString,'/','_')
  SET @TableString = REPLACE(@TableString,'''','_')
  
  SET @SystemObjectsSIFT = CURSOR FAST_FORWARD FOR
    SELECT name FROM sysobjects WHERE name LIKE @TableString AND xtype = 'U'
  OPEN @SystemObjectsSIFT
  FETCH NEXT FROM @SystemObjectsSIFT INTO @TableName
  WHILE (@@FETCH_STATUS = 0) 
  BEGIN
    IF @ReadPermission = 1
      exec ('GRANT SELECT ON ' + '[' + @TableName + ']' + ' TO ' + '[' + @ApplicationRoleID + ']')
    IF @InsertPermission = 1
      exec ('GRANT INSERT ON ' + '[' + @TableName + ']' + ' TO ' + '[' + @ApplicationRoleID + ']')
    IF @ModifyPermission = 1
      exec ('GRANT UPDATE ON ' + '[' + @TableName + ']' + ' TO ' + '[' + @ApplicationRoleID + ']')
    IF @DeletePermission = 1
      exec ('GRANT DELETE ON ' + '[' + @TableName + ']' + ' TO ' + '[' + @ApplicationRoleID + ']')
    FETCH NEXT FROM @SystemObjectsSIFT INTO @TableName
  END
  FETCH NEXT FROM @SIFTPermissions INTO @RoleID, @ObjectType, @ObjectID, @ReadPermission, @InsertPermission, @ModifyPermission, @DeletePermission, @ExecutePermission
END

CLOSE @SystemObjectsSIFT
DEALLOCATE @SystemObjectsSIFT
CLOSE @SIFTPermissions
DEALLOCATE @SIFTPermissions

COMMIT TRANSACTION SecurityUpdate



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Also, there has been some great news from Microsoft regarding this topic. Currently, the US team is evaluating a new executable that contains the capability to do user-specific synchronization. This is implemented in the core .exe code. From what I have heard, there are some enhancements for timeout times, but there has not been any drastic performance increases (other then the capability to do user specific synchronization).

Hopefully, it will be introduced sooner rather then later!

Enjoy, and please give any feedback that you can!

- Scott

sdcharle · 2005-07-15

Scott-

Thanks for the update. We'll check this out in our development environment and see what happens.

Also, let us know if you hear anymore from Microsoft re: the new executable with user-specific synchronization. I can't be the only one who can't wait to get a hold of that...

Thanks again
Steve

Scott Frappier wrote:

Hello:

Here is the objects and code that I promised. This is a work in process (it works with the basic Navision system, but linked tables do not currently

sdcharle · 2005-07-20

Scott,

Thanks again for posting this. It answered some questions that until now I wasn't getting answers to!

I had a couple questions. I am looking primarily at the SQL piece (testing that, then once that's good, I'll look at the UI).

First off I notice this works for most roles but not for SUPER, since SUPER doesn't have entries for all items in the Permissions table. I'm thinking what you do in this case is grant permissions on all non-company specific tables and either all company specific tables, or just the company specific tables for the company you want to grant permissions for. It was not too hard to make changes for this, just wanted to verify this approach was OK.

The other big question is about this chunk of code:

Scott Frappier wrote:

Hello:
BEGIN
IF @ObjectID = '2000000001' SET @TableString = 'Object'
IF @ObjectID = '2000000007' SET @TableString = 'Date'
IF @ObjectID = '2000000009' SET @TableString = 'Session'
IF @ObjectID = '2000000010' SET @TableString = 'Database File'
IF @ObjectID = '2000000020' SET @TableString = 'Drive'
IF @ObjectID = '2000000022' SET @TableString = 'File'
IF @ObjectID = '2000000024' SET @TableString = 'Monitor'
IF @ObjectID = '2000000026' SET @TableString = 'Integer'
IF @ObjectID = '2000000028' SET @TableString = 'Table Information'
IF @ObjectID = '2000000029' SET @TableString = 'System Object'
IF @ObjectID = '2000000037' SET @TableString = 'Performance'
IF @ObjectID = '2000000038' SET @TableString = 'AllObj'
IF @ObjectID = '2000000039' SET @TableString = 'Printer'
IF @ObjectID = '2000000040' SET @TableString = 'License Information'
IF @ObjectID = '2000000041' SET @TableString = 'Field'
IF @ObjectID = '2000000042' SET @TableString = 'OLE Control'
IF @ObjectID = '2000000043' SET @TableString = 'License Permission'
IF @ObjectID = '2000000044' SET @TableString = 'Permission Range'
IF @ObjectID = '2000000045' SET @TableString = 'Windows Language'
IF @ObjectID = '2000000046' SET @TableString = 'Automation Servers'
IF @ObjectID = '2000000047' SET @TableString = 'Server'
IF @ObjectID = '2000000048' SET @TableString = 'Database'
IF @ObjectID = '2000000049' SET @TableString = 'Code Coverage'
IF @ObjectID = '2000000050' SET @TableString = 'Windows Object'
IF @ObjectID = '2000000052' SET @TableString = 'Windows Group Member'
IF @ObjectID = '2000000055' SET @TableString = 'SID - Account ID'
IF @ObjectID = '2000000056' SET @TableString = 'User SID'
IF @ObjectID = '2000000058' SET @TableString = 'AllObjWithCaption'
IF @ObjectID = '2000000059' SET @TableString = 'Breakpoints'
[/code]

Actually in our database I don't see those. We are running 4.0 with presumably a different license so maybe that's the reason for the difference.

We have some tables in the 20..... block, but with different names.

One other thing: In my case some of the machineID input parameters didn't fit into the Integer type. I had to use BIGINT for those. The functionality mapping the SID to the Navision security role still works after those changes on the tiny test database where I'm testing this.

Aside from those issues, the code does appear to work and it does its work QUICKLY. I will test it further of course.

Thanks,
Steve

jamagby · 2005-08-15

I'm amazed that MBS still hasn't provided a proper solution to the problem of security synchronization. I wasn't even aware of the changes made which caused this problem until the first customer we upgraded to 4.0 back in May. Having waited for an unreasonable amount of time for a resolution, I took what information I could get and fashioned one that runs much more efficiently.

From what I can tell, the technique currently used drops all permissions to all objects and then runs statements to re-grant permissions according to what is configured in Navision (the other post on this group works similarly). This is an utterly inefficient technique, particularly when there are many users in the system. I decided to take the approach of ascertaining the differences in permissions configured in Navision versus what is actually granted in SQL. Then knowing the differences, run only the statements necessary to bring the SQL permissions into synchronization. The results are dramatic. In a database of 100 users, I am able to synchronize permissions in about 1 minute compared to 45-60 minutes for the Navision synchronization.

I've validated my technique by comparing the permissions set by my script compared to the Navision sync and confirmed that it results in setting the exact same permissions. I've tested this with a number of different roles/permissions on a database with 8 companies in it. My implementation uses a Navision form to call SQL stored procedures which are supplied. You can either sync a specific user or all users although there is not a lot of difference in execution time.

Why MBS couldn't come up with this is beyond me. My company has approached MBS with this solution but have not yet actually been asked for the implementation. So in the interest of helping those living with this nightmare, I'm making the solution available. Since the script is rather large, we've decided to make it available for download from our website. Currently the script only synchronizes Windows logins but it should be easily adaptable to Database logins if an algorithm for matching the logins to the corresponding application roles can be derived.

Download from here (http://www.choicesolutions.com/navision.cfm?PN=Support)

Savatage · 2005-08-15

"jamagby wrote:

So in the interest of helping those living with this nightmare, I'm making the solution available. Since the script is rather large, we've decided to make it available for download from our website. Currently the script only synchronizes Windows logins but it should be easily adaptable to Database logins if an algorithm for matching the logins to the corresponding application roles can be derived.

Download from here (http://www.choicesolutions.com/navision.cfm?PN=Support)

=D>

sdcharle · 2005-08-25

Very nice.

Thanks for making this available. It is odd that there's secrecy around mapping database logins to app roles. It wasn't until the earlier post that I was able to obtain info about the relationship between windows logins and app roles. Fortunately we made a point of using the Windows Integrated authentication, so this solution will work for us.

Steve

jamagby wrote:

I'm amazed that MBS still hasn't provided a proper solution to the problem of security synchronization. I wasn't even aware of the changes made which caused this problem until the first customer we upgraded to 4.0 back in May. Having waited for an unreasonable amount of time for a resolution, I took what information I could get and fashioned one that runs much more efficiently.

From what I can tell, the technique currently used drops all permissions to all objects and then runs statements to re-grant permissions according to what is configured in Navision (the other post on this group works similarly). This is an utterly inefficient technique, particularly when there are many users in the system. I decided to take the approach of ascertaining the differences in permissions configured in Navision versus what is actually granted in SQL. Then knowing the differences, run only the statements necessary to bring the SQL permissions into synchronization. The results are dramatic. In a database of 100 users, I am able to synchronize permissions in about 1 minute compared to 45-60 minutes for the Navision synchronization.

I've validated my technique by comparing the permissions set by my script compared to the Navision sync and confirmed that it results in setting the exact same permissions. I've tested this with a number of different roles/permissions on a database with 8 companies in it. My implementation uses a Navision form to call SQL stored procedures which are supplied. You can either sync a specific user or all users although there is not a lot of difference in execution time.

Why MBS couldn't come up with this is beyond me. My company has approached MBS with this solution but have not yet actually been asked for the implementation. So in the interest of helping those living with this nightmare, I'm making the solution available. Since the script is rather large, we've decided to make it available for download from our website. Currently the script only synchronizes Windows logins but it should be easily adaptable to Database logins if an algorithm for matching the logins to the corresponding application roles can be derived.

Download from here (http://www.choicesolutions.com/navision.cfm?PN=Support)

Laser5000 · 2010-05-18

Hi,
i know the thread is old but we have still this problem!
Have anybody the Script? I tried to download with no success...

br,
Mathias

ara3n · 2010-05-18

You can change to standard security model.

Howdy, Stranger!

Quick Links

Categories

Synchronizing Security is quite slow

Comments