Is there a way to keep tracking of what gets edited and from who in a Data Extension on Marketing Cloud? Audit Trail doesn’t track such thing and even salesforce support isn’t able to provide such info on request.
Perhaps a way with SSJS to write any eventual edits on a log DE?
The result I’m after is something like:
DE Name | Id record edited | Field edited | Previous Value | Current Value | Edit Date | User who edited
Thanks in advance
Outside the box answer:
If you need record level permissioning, field historization etc., consider if you can leverage a connected Sales Cloud, which is built for this sort of stuff, just not for large data volume, and with the 15 min data stream delay.
easy answer:
x
custom answer:
This is not a quick thing to do, as it is 100% custom and involves both frontend and backend development, but it is technically possible, obviously with some assumptions.
The solution approach described will be for specific, custom built DEs, with some degree of abstraction and log files, you might be able to extend this a bit.
Main caveat: you have to ensure that ALL interaction happens through the custom solution. For some handpicked tables, this probably is not an issue, but you probably wont be able to just ban access to all tables unless your solution is absolutely flawless, scales etc.
The solution approach probably wont be "audit safe". By using auth mechanisms, It can be made safe to the degree that no one without a login could breach, but you'll have to stretch to withstand criminal energy or other creative attempts from a logged in user. It's meant to "help users /admins" make less mistakes or trace errors, not meet any legal requirements or pass an audit.
==
If all thats not a deterrent, then the (only) solution approach that I know to this is to build an authenticated custom frontend & a handler page on cloudpages, and disallow normal UI interaction with the tables.
find the logged in user
On an authed cloudpage frontend, is possible to retrieve the logged in user's email etc. so you know who is interacting with the table.
see here for some useful ones: User Authentication AMPscript functions
disallow access to specific DEs
How to disallow access depends on your org, creativity and how safe this needs to be.
You can move the tables to local in parent BU, out of range for people in a child BU (which is the only true way to remove access).
Less securely, reduce specific roles' access rights to Shared DEs or DEs altogether (then they can still get there via script), move the DE to a hidden folder (which is just security by obscurity and you'll probably forget yourself that it still exists), ...
custom insert / update / etc
Custom methods for retrieving rows / inserting / updating rows can be written using e.g. WSProxy on a handler page you call with AJAX. Make sure your handler page is not be publicly accessible or you expose the capability to the public.
As this works with the API, you'll only be able to retrieve rows within the limits of an API call or it will get slow and potentially API call heavy.
Of course, now when you have someone retrieve the table, insert a row" / update a row etc. through your frontend and the API, you can log the user's email etc. with the row in a specific field. And you could go as far and retrieve & save each row to a log before you allow a row to be updated etc. etc.
construct a frontend representing your table
e.g. I've built this in in a fashion that the "createdBy" field is part of the DE itself, not in a separate logfile. Thought it was a good idea to also prefill it on the frontend in the "row" representation, visible to the user, but of course that might open this up for dev tools level "injection".
UI to enter a row:
there are good frontend libraries like DataTables that you can use for make a JSON data structure from a WSproxy Retrieve call visually appealing, incl. full text search on the displayed portion of the table etc.
==
full instructions on authed cloudpage
here is a resource how to set up an authed cloudpage frontend: https://sf-marketing.com/marketing-cloud-user-validation-on-cloudpages/
...which is how I got introduced to the general idea, and where the code snippet below is sourced. Not mine! Just found the linked resource too verbose (sorry!), so here is my VERY brief step by step version.
and get a 16 digit for salt, a 32 digit for IV, also think of a password.
fill in the gaps (lines 3 to 12 in code) and paste the code below (the code assumes you simply hardcode everything - if you save the keys in key mgmt, you have to change the encryptSymmetric function slightly)
add users that should be allowed onto this page to the installed package under "access"
gotchas
it's session based - Make sure your SFMC session is in the same browser.
Make sure your session is logged into the BU where the Installed package is located.
Don't try it with a session box / multi tab solution unless your SFMC session in the same one.
%%[
/*fill the following variables with your data*/
SET @cloudpageURL = 'https://mmcxxxxxxx-xxxxxx.pub.sfmc-content.com/xxxxxxxx'
SET @baseURI = 'mmcxxxxxxx-xxxxxx'
SET @clientid = 'xxxxxx'
SET @clientsecret = 'xxxxx'
/*define password (64), Initialization vector (32) and salt (16) for encryption
- change for other pages*/
set @ivector = 'xxxxxxx'
set @salt = 'xxxxx'
set @encrpswd = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'
/*get code if passed with the URL*/
SET @code = RequestParameter('code')
/*check if session key is passed with the URL and decrypt it*/
Set @passedencrsessionkey = RequestParameter('sessionkey')
IF IsNullDefault(@passedencrsessionkey, 'null') != 'null' AND @passedencrsessionkey != '' THEN
SET @passedsessionkey=DecryptSymmetric(@passedencrsessionkey, 'AES', @null, @encrpswd, @null, @salt, @null, @ivector)
ENDIF
/*create encrypted session key with 1 hour lifetime for usage in forms and redirects*/
set @newsessionkey = DateAdd(Now(),1,"H")
SET @sessionkey=EncryptSymmetric(@newsessionkey, 'AES', @null, @encrpswd, @null, @salt, @null, @ivector)
/*check if the passed session key is valid if available*/
IF IsNullDefault(@passedencrsessionkey, 'null') != 'null' AND @passedencrsessionkey != '' AND @passedsessionkey >= Now()
AND @passedsessionkey <= @newsessionkey
THEN
/*load Cloudpage*/
/*if passed session key is not valid check if a code is available and check it via API request, redirect through auth-page if the code is not valid*/
ELSEIF IsNullDefault(@code, 'null') != 'null' AND @code != ''
THEN
]%%
<script runat="server">
Platform.Load("Core","1.1.1");
try {
/* get OAuth 2.0 access token */
var code = Platform.Variable.GetValue("@code");
var payload = '{"grant_type": "authorization_code",';
payload += '"code": "' + code + '",';
payload += '"client_id": "' + Variable.GetValue("@clientid") + '",';
payload += '"client_secret": "' + Variable.GetValue("@clientsecret") + '",';
payload += '"scope": null,';
payload += '"redirect_uri": "' + Variable.GetValue("@cloudpageURL") + '"}';
var OAuth2URL = 'https://' + Variable.GetValue("@baseURI") + '.auth.marketingcloudapis.com/v2/token';
var contentType = 'application/json';
var checkCode = HTTP.Post(OAuth2URL, contentType, payload);
/* start optional personalization */
/* make the userinfo API call and get the response */
var tokenObj = Platform.Function.ParseJSON(checkCode["Response"][0]);
var accessToken = tokenObj.access_token;
var headerNames = ["Authorization"];
var headerValues = ["Bearer " + accessToken];
var urlCreate = 'https://' + Variable.GetValue("@baseURI") + '.auth.marketingcloudapis.com/v2/userinfo';
var userinfo = HTTP.Get(urlCreate, headerNames, headerValues);
var userinfojson = Platform.Function.ParseJSON(userinfo["Content"]);
Variable.SetValue("@preferred_username",userinfojson.user.preferred_username);
Variable.SetValue("@name",userinfojson.user.name);
Variable.SetValue("@email",userinfojson.user.email);
Variable.SetValue("@locale",userinfojson.user.locale);
Variable.SetValue("@zoneinfo",userinfojson.user.zoneinfo);
Variable.SetValue("@timezone_longName",userinfojson.user.timezone.longName);
Variable.SetValue("@timezone_shortName",userinfojson.user.timezone.shortName);
Variable.SetValue("@timezone_offset",userinfojson.user.timezone.offset);
Variable.SetValue("@timezone_dst",userinfojson.user.timezone.dst);
Variable.SetValue("@org_member_id",userinfojson.organization.member_id);
Variable.SetValue("@org_enterprise_id",userinfojson.organization.enterprise_id);
Variable.SetValue("@org_enterprise_name",userinfojson.organization.enterprise_name);
Variable.SetValue("@org_account_type",userinfojson.organization.account_type);
Variable.SetValue("@org_stack_key",userinfojson.organization.stack_key);
Variable.SetValue("@org_region",userinfojson.organization.region);
Variable.SetValue("@org_locale",userinfojson.organization.locale);
Variable.SetValue("@org_zoneinfo",userinfojson.organization.zoneinfo);
Variable.SetValue("@org_timezoneLongName",userinfojson.organization.timezone.longName);
Variable.SetValue("@org_timezoneShortName",userinfojson.organization.timezone.shortName);
Variable.SetValue("@org_timezoneOffset",userinfojson.organization.timezone.offset);
Variable.SetValue("@org_timezoneDST",userinfojson.organization.timezone.dst);
//Variable.SetValue("@json",Stringify(userinfojson));
/* end optional personalization */
}
catch(e) {
</script>
%%[Redirect(CONCAT('https://', @baseURI, '.auth.marketingcloudapis.com/v2/authorize?response_type=code&client_id=', @clientid, '&redirect_uri=',@cloudpageURL))]%%
<script runat="server">}</script>
%%[
/*If there is no code and no valid session key redirect through auth-page*/
ELSE
Redirect(CONCAT('https://', @baseURI, '.auth.marketingcloudapis.com/v2/authorize?response_type=code&client_id=', @clientid, '&redirect_uri=',@cloudpageURL))
ENDIF
]%%
<br><b>userinfo</b>
<br>preferred_username:%%=v(@preferred_username)=%%
<br>name: %%=v(@name)=%%
<br>email: %%=v(@email)=%%
<br>locale: %%=v(@locale)=%%
<br>zoneinfo: %%=v(@zoneinfo)=%%
<br>timezone.longName: %%=v(@timezone_longName)=%%
<br>timezone.shortName: %%=v(@timezone_shortName)=%%
<br>timezone.offset: %%=v(@timezone_offset)=%%
<br>timezone.dst: %%=v(@timezone_dst)=%%
<br>
<br><b>organization info</b>
<br>member_id: %%=v(@org_member_id)=%%
<br>enterprise_id: %%=v(@org_enterprise_id)=%%
<br>enterprise_name: %%=v(@org_enterprise_name)=%%
<br>account_type: %%=v(@org_account_type)=%%
<br>stack_key: %%=v(@org_stack_key)=%%
<br>region: %%=v(@org_region)=%%
<br>locale: %%=v(@org_locale)=%%
<br>zoneinfo: %%=v(@org_zoneinfo)=%%
<br>org_timezoneLongName: %%=v(@org_timezoneLongName)=%%
<br>org_timezoneShortName: %%=v(@org_timezoneShortName)=%%
<br>org_timezoneOffset: %%=v(@org_timezoneOffset)=%%
<br>org_timezoneDST: %%=v(@org_timezoneDST)=%%
<br> - you're authenticated! other code can be placed here.
