Symantec IGA

Expand all | Collapse all

IdM 12.6 900 bytes index limit problem for challenge / responses

Jump to Best Answer
  • 1.  IdM 12.6 900 bytes index limit problem for challenge / responses

    Posted 08-29-2017 09:29 AM

    Hi all,

     

    I have my users store on a MS-SQL Server, which has a limit of 900 bytes for the index. When I try to load my challenge/responses (I have 8 mandatory), insert can not be saved on database.

     

    Log entry is below. I checked value from {RC2} to last value and is 882 chars, then plus the user id, the sum exceeds the 900 bytes index limit. I understand that this encrypted string has all pairs of challenge/responses concatenated (because one question and answer should be far smaller than this 882 chars).

     

    How can I control how the questions and answers are saved to database? Can I explicitly configure that every pair goes to a single row? (for example userid1:qa1 in a row, userid2:qa2 in other row, etc..). Or perhaps somebody can tell me how to by pass the 900 bytes index limit for SQL-Server? Many thanks!

     

    Failed to add tblUserPasswordhints.passwordhint with value: {RC2}:BNN9rJO6R7SGI9842TfKbnCRihJEG/+rSP6pe340qjS+iYOIXThs3VWomxmF4B22mDtfeUFtmtygxwa5TTBYeJaDiihW/SgE9///Wwrcgz7ZNP5/1q7u8q9a4eEk0TRRl1qNnMHX12Am5kfgrjx9davBPMc9C4w8G+Z8rAtxEDY12fUHVer5ea+wMN23+DGBIFNMTETlrYsCfskXHgtteyZeGk4Ji64cWS2xWtOBVXWCsM61pNyQVSqCjHjI+hU1u+/CNSilAQdsDOPQdwxjLD8U9pHkiFHblYrDbxO/UgFaKxIfCPQxnWKhd7H+DsOkgg7xDgGNjC/SwLHBYP6IUkOHzpzGxxQnxmFXNfAwUXgw8MP56+7eIlcD78/sD6LtPGu70T5q7xive40EWOpPJZv2lxuGGRcFkFEHeWrhDTeMl8a+6tm6dD9CuXEMMuzYeQQncDy6xWGyUGfDUs8dz/bpJJ7F5//Jbz0EfYCruVslZYt8oEeNePe5Gd/xkETzMi0ROuA0lxlY1BMIdaYJ4OA+NWR5Fj9WHBi4V1d0Tp+sH/5LqiOS73TIPbj6cXfKc/L54ooPBhh/otqlhgR1z4PyrBT8Vn/1ue+JSFBx1f802LsZ3xe8KwORY27T48KCz0XwEic5ThQx1e+zpN5E7zvweqVgVO9OcmyLabN1z/Bhai7Dfr7oUCopdp+paYyATQE5moTMftSzMgnz11q7vba1qowSKv2zU2MRk0WQyp5+W7FU8LUafNom2EvIesDhrNvJR/k8Q5Bgy67wjNfuP3JGPvGcMEesx9XAUCE+P4xesn8ifEnzQveOvdOhfO9Q06T4GuJc9jJsHqNlWtyD9OsF8U0I7cFaruWPmVDkXp0= to ObjectType::USER managed object superadmin



  • 2.  Re: IdM 12.6 900 bytes index limit problem for challenge / responses

    Posted 12-11-2017 08:09 AM

    Hi all!

     

    I found a way to achieve this. First of all, I had to delete the index restriction because it does not allow to store index values more than 1k. If we have only one row for each user, only user id must be primary key so rebuilding table with this column as pk only gets it works. Second, I modified User directory entry on Management Console. In Password Hint attribute, I added this modifier: AttributeLevelEncrypt So, all questions and answers are encrypted before being stored on database. Now DB stores only one entry for each user. 

     

    I also added an Encryption Key to the Forgotten Password Handler, and saw that longer the key, longer the value stored for all questions and answers. So, more secure I think.

     

    Thanks everybody for your contributions.



  • 3.  Re: IdM 12.6 900 bytes index limit problem for challenge / responses

    Broadcom Employee
    Posted 08-30-2017 12:18 PM

    Hi docldap,

    You cannot bypass this index size limit;
    (https://technet.microsoft.com/en-us/library/ms163207(v=sql.105).aspx)

     

    Based on the uniqueness of the userid column, I think the passwordhint column can be removed from the index definition.
    So just define a primary key against this userid column.

    e.g.:
    CREATE TABLE [dbo].[tblUserPasswordhints](
    [userid] [int] NOT NULL PRIMARY KEY,
    [passwordhint] [nvarchar](2000) NOT NULL
    )
    GO
    ALTER TABLE [dbo].[tblUserPasswordhints] WITH CHECK ADD CONSTRAINT [FK_tblUserPasswordhints_tblUsers] FOREIGN KEY([userid])
    REFERENCES [dbo].[tblUsers] ([id])
    GO
    ALTER TABLE [dbo].[tblUserPasswordhints] CHECK CONSTRAINT [FK_tblUserPasswordhints_tblUsers]
    GO

     

    So backup the data (e.g. as INSERT statements) drop the foreign key constraint, drop the tblUserPasswordhints table.and redefine the table with such previous statements then re-insert your data.

    Regards,

    Philippe.



  • 4.  Re: IdM 12.6 900 bytes index limit problem for challenge / responses

    Broadcom Employee
    Posted 08-29-2017 11:33 AM

    Hi, I am not an expert on SQL Server but I don't think you can remove the 900 byte index limit. What you can possibly change however is the way index is created. i.e. which columns the index will contain or which columns it include. Similar to the issue that is described in the IM documentation here for the snapshot db: 

     

    General Issues - CA Identity Manager - 14.1 - CA Technologies Documentation 

     

    I recommend you work with CA Support.

     

    KR
    Russi



  • 5.  Re: IdM 12.6 900 bytes index limit problem for challenge / responses

    Posted 08-29-2017 01:51 PM

    Sorry I missed the last part of this. As I said, index is on both columns because it is a primary key. I can not alter this index, without modifying the primary key.

     

    I thought that there could be a way (that is one thing I was asking about), if I recreate this table with only userid as primary key. Problem is that User Console stores each question/answer pair per row, and each row with the user id. So, I should have (assuming ? as separator):

     

    userid ... passwordhint

    1        question1?answer1

    1        question2?answer2

    2        question1?anotheranswer1

    ...........................

     

    and that's why I can not make userid only to be primary key.

     

    So, I am figuring if there is some configuration I can do, so all challenges are saved in a unique row (and then I do be able to have userid as primarykey, and index will be always shorter than 900 bytes):

     

    userid.........passwordhint

    1...........question1?answer1#question2?answer2...

    2...........question1?anotheranswer1#question2?anotheranswer2...



  • 6.  Re: IdM 12.6 900 bytes index limit problem for challenge / responses

    Posted 08-31-2017 08:38 AM

    Hi guiph01, not luck yet. With singlevalued_attributes and separate_attributes I have an error that says "|question1|, |answer1|, |question2|....... nullPointerExeption" (this on screen, nothing is shown on server.log).

     

    So I am trying with multivalue_attribute option.

     

    With unencrypted option, result is like pic below. This is the reason I can not let only userid to be the primary key, because there are several rows with userid 9 as value (in this example).

     

    Problem is when I encrypt questions and answers. It appears that the encrypted value is getting to long for the 900 bytes of the (userid, passwordhint) index..

     

     



  • 7.  Re: IdM 12.6 900 bytes index limit problem for challenge / responses

    Posted 08-30-2017 02:31 PM

    Thanks  guiph01, I am trying this tomorrow on the client environments.

     

    I think this should work. Also, I found the ForgottenPasswordHandler class Javadoc, and saw the posible values for schema option in the Management Console configuration (i did not find this documented yet).

     

    As Javadoc stays, these are the possible values (last column):

     

    public static final java.lang.StringSCHEMA_MV_ATTRIBUTE"multivalued_attribute"
    public static final java.lang.StringSCHEMA_SEPARATE_ATTRIBUTES"separate_attributes"
    public static final java.lang.StringSCHEMA_SIN_ATTRIBUTES"singlevalued_attributes"

     

    And description for these values are as follows

     

    SCHEMA_MV_ATTRIBUTE

    public static final java.lang.String SCHEMA_MV_ATTRIBUTE
    Constant used to get questions and answers stored in a single, multi-valued attribute from the Logical Attribute handler.

     

    See Also:
    Constant Field Values

    SCHEMA_SIN_ATTRIBUTES

    public static final java.lang.String SCHEMA_SIN_ATTRIBUTES
    Constant used to get a question and answer pair stored in a single attribute from the Logical Attribute handler.

     

    See Also:
    Constant Field Values

    SCHEMA_SEPARATE_ATTRIBUTES

    public static final java.lang.String SCHEMA_SEPARATE_ATTRIBUTES
    Constant used to get each question and each answer when stored separately from the Logical Attribute handler.

     

    See Also:
    Constant Field Values

     

    I think the 2 firsts can work.. ootb is set to multivalued_attributes. I will try altering the index like you say and playing a little with these 2 options.

     

    I will confirm you tomorrow how did it go.. many thanks to all.



  • 8.  Re: IdM 12.6 900 bytes index limit problem for challenge / responses

    Posted 08-29-2017 01:46 PM

    Hi losru01 many thanks for your response. I revised this doc, but unfortunately it does not apply here.

     

    tlbUserPasswordhints has 2 columns, one for user id and the other for the password hint (question/answer), so at least ootb, the 2 columns are the primary key of the table.

     

    So, at the moment of creating this table, a unique index is created for both columns. Sadly, the 900 bytes limit applies to both columns. So, question+answer+userid has to be lower than this 900 bytes.

     

    That's why I was wondering a way to bypass this limit.



  • 9.  Re: IdM 12.6 900 bytes index limit problem for challenge / responses
    Best Answer

    Posted 08-31-2017 09:06 AM

    I am seeing something that can help.

     

    I have 2 working environments, with encryption option enabled for forgotten password handler. In these environments, all password hints columns begins with

     

    {PBES} and after that, a very short string (also it appears that the string length is proportional to the question/answer size.

     

    On the other side, this environment that does not work with encryption, where log shows a value beginning with {RC2} followed by this very long string.

     

    This non working environment was inherited to me by other providers and does not have implementation documentation. Do you know where to change the encryption algorithm? I will ask this in a new thread to separate themes.