4
Currently database logs for Security User Role changes including Insert, Update and Delete cannot be reported (extracted) for auditing purposes .
Current Database Logging Report does not show any related information of what was actually changed
This issue was reported to Microsoft via support case # 2111070060000569
Resolution was provided as a "unofficial" code customization.
We would like Microsoft to add a better way to report for auditing to the core product.
>>>>>>>>>>>
Resolution for MS case # 2111070060000569:
Steps to capture database logs for Security User Role changes were shared. Also provided suggestion on using customization to capture Security Role changes in “EePersonalDataAccessLogging” table and using table browser for viewing it.
[ExtensionOf(classStr(EePersonalDataAccessLogging))]
final class EePersonalDataAccessLogging_extension
{
[SubscribesTo(formStr(SysSecRoleAssignOM), delegateStr(SysSecRoleAssignOM, userRoleChange))]
public static void logUserRoleChange(
RefRecId _securityRole,
RefRecId _omInternalOrganization,
UserId _userId,
AddRemove _addRemove)
{
next logUserRoleChange(_securityRole,_omInternalOrganization,_userId, _addRemove);
unchecked(Uncheck::TableSecurityPermission)
{
CompanyInfo companyInfo;
EeUserRoleChangeLog log;
boolean doInsert;
int addCount;
int removeCount;
doInsert = true;
if (doInsert)
{
//When we grant organizations, we have to turn off access to all
//organizations. The application does this implicitly, but the
//log will do it explictly.
if (_omInternalOrganization && _addRemove == AddRemove::Add)
{
select firstOnly AddRemove, RecId
from log
order by log.recId desc
where log.SecurityRole == _securityRole
&& log.OMInternalOrganization == 0
&& log.UserId == _userId;
if (log.recid && log.AddRemove == AddRemove::Add)
{
log.clear();
log.SecurityRole = _securityRole;
log.omInternalOrganization = 0;
log.UserId = _userId;
log.AddRemove = AddRemove::Remove;
log.ChangedBy = curUserId();
log.insert();
}
}
log.clear();
log.SecurityRole = _securityRole;
log.omInternalOrganization = _omInternalOrganization;
log.UserId = _userId;
log.AddRemove = _addRemove;
log.ChangedBy = curUserId();
log.insert();
//When we revoke access to the last organization, they have implicit
//access to all organizations. The log will do this explictly.
if (_omInternalOrganization && _addRemove == AddRemove::Remove)
{
while select AddRemove, RecId
from log
order by log.recId desc
where log.SecurityRole == _securityRole
&& log.OMInternalOrganization != 0
&& log.UserId == _userId
{
if (log.AddRemove == AddRemove::Add)
{
addCount++;
}
else
{
removeCount++;
}
}
if (addCount == removeCount)
{
log.clear();
log.SecurityRole = _securityRole;
log.omInternalOrganization = 0;
log.UserId = _userId;
log.AddRemove = AddRemove::Add;
log.ChangedBy = curUserId();
log.insert();
}
}
EePersonalDataAccessLogging::logSecurityRightsChange(_securityRole, _userId, _addRemove, _omInternalOrganization);
}
}
}
}
Current Database Logging Report does not show any related information of what was actually changed
This issue was reported to Microsoft via support case # 2111070060000569
Resolution was provided as a "unofficial" code customization.
We would like Microsoft to add a better way to report for auditing to the core product.
>>>>>>>>>>>
Resolution for MS case # 2111070060000569:
Steps to capture database logs for Security User Role changes were shared. Also provided suggestion on using customization to capture Security Role changes in “EePersonalDataAccessLogging” table and using table browser for viewing it.
[ExtensionOf(classStr(EePersonalDataAccessLogging))]
final class EePersonalDataAccessLogging_extension
{
[SubscribesTo(formStr(SysSecRoleAssignOM), delegateStr(SysSecRoleAssignOM, userRoleChange))]
public static void logUserRoleChange(
RefRecId _securityRole,
RefRecId _omInternalOrganization,
UserId _userId,
AddRemove _addRemove)
{
next logUserRoleChange(_securityRole,_omInternalOrganization,_userId, _addRemove);
unchecked(Uncheck::TableSecurityPermission)
{
CompanyInfo companyInfo;
EeUserRoleChangeLog log;
boolean doInsert;
int addCount;
int removeCount;
doInsert = true;
if (doInsert)
{
//When we grant organizations, we have to turn off access to all
//organizations. The application does this implicitly, but the
//log will do it explictly.
if (_omInternalOrganization && _addRemove == AddRemove::Add)
{
select firstOnly AddRemove, RecId
from log
order by log.recId desc
where log.SecurityRole == _securityRole
&& log.OMInternalOrganization == 0
&& log.UserId == _userId;
if (log.recid && log.AddRemove == AddRemove::Add)
{
log.clear();
log.SecurityRole = _securityRole;
log.omInternalOrganization = 0;
log.UserId = _userId;
log.AddRemove = AddRemove::Remove;
log.ChangedBy = curUserId();
log.insert();
}
}
log.clear();
log.SecurityRole = _securityRole;
log.omInternalOrganization = _omInternalOrganization;
log.UserId = _userId;
log.AddRemove = _addRemove;
log.ChangedBy = curUserId();
log.insert();
//When we revoke access to the last organization, they have implicit
//access to all organizations. The log will do this explictly.
if (_omInternalOrganization && _addRemove == AddRemove::Remove)
{
while select AddRemove, RecId
from log
order by log.recId desc
where log.SecurityRole == _securityRole
&& log.OMInternalOrganization != 0
&& log.UserId == _userId
{
if (log.AddRemove == AddRemove::Add)
{
addCount++;
}
else
{
removeCount++;
}
}
if (addCount == removeCount)
{
log.clear();
log.SecurityRole = _securityRole;
log.omInternalOrganization = 0;
log.UserId = _userId;
log.AddRemove = AddRemove::Add;
log.ChangedBy = curUserId();
log.insert();
}
}
EePersonalDataAccessLogging::logSecurityRightsChange(_securityRole, _userId, _addRemove, _omInternalOrganization);
}
}
}
}
STATUS DETAILS
New