locked
Referencing Active directory users inside my asp.net vmc web application RRS feed

  • Question

  • User-540818677 posted

    I am working on an asset management system using asp.net mvc framework. Now I have the following general business rules:-

    1. I have my own tables for storing securityRole info and Securityrole users. As follow:-

    enter image description here

    1. And I have created a table representing AD users, and I populate the table using sync job that read the users info from AD.
    2. Now when a user create an asset , I will add the UserID as a FK inside the asset item.

    This worked well, so if I want to know the username who create the asset I simply query the AD User table inside my DB. and also let say the user changed his last name inside the AD (an employee for example get married) , then the AD User table will be updated when the sync job runs, and i will base my sync job to match users based on the AD GUID.

    Now if a user leave the company , we remove him from the AD , and when the sync job run it will remove all the users that no more exists inside the AD. But here the problem start, because if I try to remove a user who have created an asset a Referential integrity exception will be raised, because the AD user record inside my DB is being referenced from the Asset table.

    So can anyone advice about the approaches I can follow to manage this ? I am thinking of the following scenario :-

    1. Inside the sync job , when it detects that a user no more exists inside the AD , to change the “Deleted” flag to true, while if the user no more exists inside the AD and at the same time he did not add any asset to remove him permanently from the AD User table.

    Can anyone advice on this please?

    Thanks

    Saturday, January 24, 2015 7:27 AM

All replies

  • User784151913 posted

    From what I can make out of your requirement, it seems you are trying to get a list of all users from the windows active directory, fill your tables with them, and then track who edits/creates /updates "assets". Additionally, if there are changes to object properties in the active directory, you would want your application to be aware of those as well.

    To access the objects inside the AD, I would suggest you use the API contained within the System.DirectoryServices namespace and its sub-namespaces (https://msdn.microsoft.com/en-us/library/system.directoryservices(v=vs.110).aspx). Deploy this part of your code as a windows service and have it run under domain administrator privileges.

    PS

    I am a bit intrigued by your requirement. Do you really need to duplicate all the data that is already maintained by the active directory? Wouldn't it be sufficient if you implemented windows authentication in your application and then made use of it to track who was making changes to your assets? Maybe if you could share more information about what the end goals of your application are, we would be happy to help you design your system better.

    Hope that helps. 

    Brutus 

    Monday, February 2, 2015 2:48 PM
  • User-540818677 posted

    I am a bit intrigued by your requirement. Do you really need to duplicate all the data that is already maintained by the active directory? Wouldn't it be sufficient if you implemented windows authentication in your application and then made use of it to track who was making changes to your assets? Maybe if you could share more information about what the end goals of your application are, we would be happy to help you design your system better.

    i will not be duplicating the whole info , i will be retrieving the following info :-

    1. first name, last name, email address, sam account name + telephone number. and still these info will be managed by the AD,, but having these info inside my own table will ease sorting , searching the info.

    2. now i am using windows authentication for log-in users, but we want to have our own tables for managing the security roles.

    now the problem i am facing is that if a user leave the company we remove him from the AD, but i can not remove him from my users table since he might have created assets inside the system ,, and i can not delete the user if he is being referenced by a FK from another table,, so my question is how usually enterprise systems manages these scenarios ...  so if a parent record (in my case the users table records) is no more active (leave the company) how i can remove him from the table without causing a referential integrity exception to be raised if there are child records referencing it ...

    Monday, February 2, 2015 5:22 PM
  • User784151913 posted

    Cross check against the active directory, and if you find that a user account has been disabled, perform a soft delete on the corresponding record in your 'User' table. I see that you have columns named IsActive and IsDeleted. Or if they stand for something else, then introduce a new column to denote the status of a user in the AD. That will take care of your referential integrity concerns. 

    Or if you are particular about performing a hard delete, then alter your 'User' table with the ON DELETE CASCADE clause and then delete the user record if it has been disabled in active directory. It will then permanently delete all records that reference it, including those of assets created or edited by the user. 

    Brutus 

    Monday, February 2, 2015 10:48 PM
  • User-540818677 posted

    Or if you are particular about performing a hard delete, then alter your 'User' table with the ON DELETE CASCADE clause and then delete the user record if it has been disabled in active directory. It will then permanently delete all records that reference it, including those of assets created or edited by the user. 

    of course i do not want to do this ,, as the assets record will still be valid in case the user leave the company.. what i am thinking of is that if the sync job finds that the user is disabled or no more exsits inside the AD ,, to do these checks:-

    1. if the user did not create any asset , to delete permanently.

    2. if the user added some assets to set the IsDeleted to true.

    actually this what i start implementing ..

    Tuesday, February 3, 2015 6:48 AM
  • User784151913 posted

    A suggestion - is every user in the AD a potential user of your application? If not, then I would suggest that you load just those users into the database who are going to be potential users of your application, as part of initial setup. Have an administrative module from where you can add /view /edit users. Your windows service can then watch the active directory for the status of the authorized  users of the application only. 

    Good luck! 

    Brutus 

    Tuesday, February 3, 2015 7:46 AM