Delphix Products

Expand all | Collapse all

Hook to restrict DB Access once provisioned

Jump to Best Answer
  • 1.  Hook to restrict DB Access once provisioned

    Posted 12 days ago
    Hi

    Does anyone have any experience in creating hooks for a SQL VDB provision which will restrict access to the VDB for the user who created it?

    even if it's a powershell script which can grab the user variable from somewhere.

    Basically we want to be able to give our devs the ability to provision unmasked VDBs when they need to look at production issues, but we want to restrict access to just that user....it will be a windows user account which we currently add to the VDB name, so we can extract from there if needed.

    ------------------------------
    Mark Hayter
    DBA Specialist
    Gain Capital UK Limited (City Index Limited)
    ------------------------------


  • 2.  RE: Hook to restrict DB Access once provisioned

    Posted 12 days ago
    Hi Mark,

    You can compute the value from one of those variables (VDB_INSTANCE_NAME, VDB_DATABASE_NAME), as you prefix it in the vdb name.

    BR,

    Mouhssine

    ------------------------------
    Mouhssine SAIDI
    Community Member
    Delphix Community Members
    ------------------------------



  • 3.  RE: Hook to restrict DB Access once provisioned

    Posted 11 days ago
    Hi Mark,

    May I suggest to put out a Self Service container for an unmasked database for Production issues? In this case, it is possible to provision a Bugfix database, better to be in a Self Service and provision it to a Windows Server that has restricted access to only certain Bug Fix team members.

    It is always possible to use PowerShell scripts and an example can be found here: https://docs.delphix.com/docs-old/delphix-administration/sql-server-environments-and-data-sources/customizing-delphix-for-sql-server/hooks-for-sql-server/cookbook-of-common-scripts-for-hooks-on-sql-server/example-powershell-script-for-debugging



    ------------------------------
    Rahim Cetinel
    Solution Architect | Delphix Blackbelt
    Accuras, Turkey
    ------------------------------



  • 4.  RE: Hook to restrict DB Access once provisioned

    Posted 11 days ago
    Hi Rahim,

    Indeed it's a very good suggestion to restrict access/actions on a given VDB, but in my assumption Mark's need is to restrict the access inside the database (access to data).


    For this limiting user access and granting have to be done at the database level, but could be a good deal to combine both restricting access from database and set self-service container to get accurate control end-to -end on the VDB

    BR,

    Mouhssine

    ------------------------------
    Mouhssine SAIDI
    Community Member
    Delphix Community Members
    ------------------------------



  • 5.  RE: Hook to restrict DB Access once provisioned
    Best Answer

    Posted 11 days ago
    Hi all

    Thanks
    I have solved this by updating our custom powershell module that we use to provision new VDBs.
    This will only allow a user to provision to a particular server and then takes the AD username and runs a post script ps that runs a stored proc to create that user in the VDB and remove all others.  then a SQL agent job is created which will remove the user after 24 hours.

    ------------------------------
    Mark Hayter
    DBA Specialist
    Gain Capital UK Limited (City Index Limited)
    ------------------------------



  • 6.  RE: Hook to restrict DB Access once provisioned

    Posted 11 days ago
    Hi Mark,

    Happy to hear that you solved it

    Regards,

    Mouhssine

    ------------------------------
    Mouhssine SAIDI
    Community Member
    Delphix Community Members
    ------------------------------