Delphix Products

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)
------------------------------

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
------------------------------

Original Message:
Sent: 11-06-2019 10:01:50 AM
From: Mark Hayter
Subject: Hook to restrict DB Access once provisioned

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)
------------------------------

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
------------------------------

Original Message:
Sent: 11-06-2019 10:01:50 AM
From: Mark Hayter
Subject: Hook to restrict DB Access once provisioned

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)
------------------------------

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
------------------------------

Original Message:
Sent: 11-07-2019 03:42:07 AM
From: Rahim Cetinel
Subject: Hook to restrict DB Access once provisioned

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

Original Message:
Sent: 11-06-2019 10:01:50 AM
From: Mark Hayter
Subject: Hook to restrict DB Access once provisioned

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)
------------------------------

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)
------------------------------

Original Message:
Sent: 11-06-2019 10:01:50 AM
From: Mark Hayter
Subject: Hook to restrict DB Access once provisioned

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)
------------------------------

Hi Mark,

Happy to hear that you solved it

Regards,

Mouhssine

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

Original Message:
Sent: 11-07-2019 05:48:12 AM
From: Mark Hayter
Subject: Hook to restrict DB Access once provisioned

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)

Original Message:
Sent: 11-06-2019 10:01:50 AM
From: Mark Hayter
Subject: Hook to restrict DB Access once provisioned

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)
------------------------------