Links (Linked Documents) from deleted users are showing as <<Protected>> for non master users and are not reachable anymore

Version 1

    Details

    Links (Linked Documents) from deleted users are showing as <<Protected>> for non master users and are not reachable anymore

    [PRE-REQUISITES]
    - have any file (we are using the carback.jpg) available at any location
    - have 2 non master users (RUEM and ANNEB) available in the same user group
    - have both users in the same user group (group1)


    [STEPS TO PREPARE]

    - Set user settings for this scenario
    1. Logged in with master rights
    2. Tools >> Users Settings
    3. Tab Access
    4. Set in Access to others >> Links access to the user group group1

    5. Ok >> Close

    -While this happens usually automatically when receiving attachments from emails as linked documents, also manually linked documents can be set to a certain ownership via the following steps 
    1. Logged in with master rights
    2. Browse to Donald Dunst
    3. Tab Links  
    4. Right Click >> New
    a. Document name = test
    b. Document Owner = RUEM
    c. in File name browse to the GoldMine share and select carback.jpg
    5. OK
    6. Log out as user with master rights

    7. Log in as ANNEB (a non master user)
    8. Browse to Donald Dunst
    9. Tab Links  
    10. Verify if you can see and access the link (e.g. via the preview or really by opening it)
    >> RESULT: ANNEB has access to the Linked Document




    [STEPS TO REPRODUCE]
    1. Log in with master rights
    2. Tools >> Users Settings
    3. Highlight RUEM
    4. Button Delete
    5. Verify in the upcoming dialog that there is no option what should happen with linked documents (select any option)
    6. OK and really delete the user
    7. Browse to Donald Dunst
    8. Tab Links  if you were there already make sure to change the tab and get back to Links tab)
    9. Only look at the link without opening or editing it
    >> RESULT: The value still shows RUEM although the user full name is not visible anymore

    10. Log out


    11. Log in as ANNEB (a non master user)
    12. Browse to Donald Dunst
    13. Tab Links  
    14. Verify if you can see and access the link (e.g. via the preview or really by opening it)
    >> RESULT: ANNEB has no access anymore to the Linked Document and it shows as <protected> (the owner is still showing as RUEM but without a full name which is logical as the user does not exist anymore)


    Resolution

    [CAUSE]
    - the reason is 'simply' that the back end data still has the value RUEM as the document owner but RUEM is no user anymore and also not in the user group group1. As ANNEB does have access only to documents from users in group1 the user cannot see the document anymore,
    - This is RM # 233353

    Additional Note:
    - in GMPE 2015.2 and higher this will have impact on GoldMine Web as the intended behavior is that <protected> documents are not shown at all and users would not be aware that there is even a document linked to the contact 



    [WORKAROUNDS]
    A. Put the users back in place and make sure that they are also in the original user groups


    1. Login with master rights
    2. Tools >> Users Settings
    3. Button New
    4.a. Username = RUEM
    4.b. User Full Name = not available or any other entry making sure for other users that the user is not valid anymore)
    4.c. Password = make sure to set a password
    4.d. Tab Membership = Make sure to include the user in all user groups the user was originally (as only this make the linked documents (Links) available for other users again)


    4.e. Tab Access = it is recommended to remove any right + set the complete Access to Others section only to 'own' items by unchecking all check boxes.

    5. OK >> Close
    6. Verify access on the document again as user ANNEB






    B. Update the related Linked documents records in the CONTSUPP to set those documents to public or another matching user, maybe the succeeding user (who should then be also in the specific user groups so that other users can access again the documents

    - Please make sure to have a full running backup prior updating the records in the back end
    - These back end queries will not be sync aware, which means if applicable need to be run also on remote systems


    1. In SQL Server Management Studio
    2. Browse to the GoldMine database
    3. Button New Query
    4. Make sure that in the database drop down the current GoldMine database is selected
    5. It is recommended to verify first of all how many linked documents will be affected for the user, therefor the following query should be executed first
    6. Copy + Paste and Adjust the following query as desired for the deleted user

    select count(*) from
    CONTSUPP where RECTYPE = 'L' and Left(CONTACT,10) like 'RUEM%'
    or
    select * from
    CONTSUPP where RECTYPE = 'L' and Left(CONTACT,10) like 'RUEM%'



    7. Execute and note the result of the query
    8. Copy + Paste and Adjust the following query as desired
    - '          ' + these must be 10 characters which means if there are only 10 blank characters the document will be public otherwise replace with the desired user name (maybe the succeeding user) and fill up with further blanks so the string is 10 characters
    - The username needs to be adjusted for making the documents as (public) documents
     
    update CONTSUPP set CONTACT = '          '+ substring(
    CONTACT,11,30), U_CONTACT = '          '+ substring(U_CONTACT,11,30) where RECTYPE = 'L' and Left(CONTACT,10) like 'RUEM%'

    or to set with a different username (for example succeeding user - please make sure that the succeeding user must be also in the matching user groups)

    update
    CONTSUPP set CONTACT = 'JODIEF    '+ substring(CONTACT,11,30), U_CONTACT = 'JODIEF    '+ substring(U_CONTACT,11,30) where RECTYPE = 'L' and Left(CONTACT,10) like 'RUEM%'


    9. Execute and note the result of the query
    10. Verify in GoldMine the accessibility to the linked document as the non master user who dd not have access before adjusting the linked documents




    Additional Note:
    The following query may help to find further affected linked documents which may not accessible for non master users as the document owner is not in the user list anymore

    SELECT * FROM CONTSUPP WHERE RECTYPE = 'L' AND LEFT(CONTACT,10) NOT IN (SELECT CAST(USERNAME AS CHAR(10)) FROM USERS) AND LEFT(CONTACT,10) <> '          ' ORDER BY LEFT(CONTACT,10)