Securing User Access: Auditing Email Domains for Cloud Migration and Security
Summary
This documentation offers guidance on how to Audit user email domains for cloud migration or security checks.
Overview
When migrating to the Cloud or conducting user audits for security reasons, it's essential to verify that only authorized users have access to the instance. One effective method is to audit the email domains, as emails are crucial for password resets and overall user safety.
This article outlines steps to assist in obtaining a comprehensive list of email domains and the number of users associated with each domain. Using this information, you can collaborate with your security team to conduct a thorough audit.
User Audit from On-Premise Database
Execute the following SQL query to retrieve a report that includes all the domains utilized in user emails along with the count of users for each domain.
Confluence
SELECT RIGHT(cwd_user.email_address, Strpos(Reverse(cwd_user.email_address), '@') - 1),
Count(*)
FROM cwd_user
INNER JOIN cwd_directory cd ON cd.id = cwd_user.directory_id
WHERE cd.active = 'T'
GROUP BY 1
ORDER BY 2 DESC; SELECT RIGHT(cwd_user.email_address, LOCATE('@', REVERSE(cwd_user.email_address))-1) as DOMAIN, COUNT(*) as COUNT
FROM cwd_user
INNER JOIN cwd_directory cd on cd.id = cwd_user.directory_id where cd.active = 'T'
GROUP BY 1
ORDER BY 2 desc;SELECT DOMAIN, COUNT(*) as COUNT
FROM
(
SELECT RIGHT(cwd_user.email_address, CHARINDEX('@', REVERSE(cwd_user.email_address))-1) as DOMAIN
FROM cwd_user
INNER JOIN cwd_directory cd ON cd.id = cwd_user.directory_id
WHERE cd.active = 'T' AND cwd_user.email_address LIKE '%@%'
) domains
GROUP BY DOMAIN
ORDER BY COUNT desc;SELECT DOMAIN, COUNT(*) as COUNT
FROM
(
SELECT SUBSTR(cwd_user.email_address, INSTR(cwd_user.email_address, '@', -1) + 1 ) as DOMAIN
FROM cwd_user
INNER JOIN cwd_directory cd ON cd.id = cwd_user.directory_id
WHERE cd.active = 'T'
)
GROUP BY DOMAIN
ORDER BY COUNT Desc;Jira
SELECT RIGHT(cwd_user.email_address, strpos(reverse(cwd_user.email_address), '@') - 1), count(*)
FROM cwd_user
INNER JOIN cwd_directory cd on cd.id = cwd_user.directory_id
WHERE cd.active = 1
GROUP BY 1
ORDER BY 2 desc;SELECT RIGHT(cwd_user.email_address, LOCATE('@', REVERSE(cwd_user.email_address))-1) as DOMAIN, COUNT(*) as COUNT
FROM cwd_user
INNER JOIN cwd_directory cd on cd.id = cwd_user.directory_id where cd.active = 1
GROUP BY 1
ORDER BY 2 desc;SELECT DOMAIN, COUNT(*) as COUNT
FROM
(
SELECT RIGHT(cwd_user.email_address, CHARINDEX('@', REVERSE(cwd_user.email_address))-1) as DOMAIN
FROM cwd_user
INNER JOIN cwd_directory cd ON cd.id = cwd_user.directory_id
WHERE cd.active = 1 AND cwd_user.email_address LIKE '%@%'
) domains
GROUP BY DOMAIN
ORDER BY COUNT desc;
SELECT DOMAIN, COUNT(*) as COUNT
FROM
( SELECT SUBSTR(cwd_user.email_address, INSTR(cwd_user.email_address, '@', -1) + 1) as DOMAIN
FROM cwd_user
INNER JOIN cwd_directory cd ON cd.id = cwd_user.directory_id
WHERE cd.active = 1
)
GROUP BY DOMAIN
ORDER BY COUNT Desc;Work with the security team to assess each email domain. Email domains should be classified as "not trusted" only if the security team is
- concerned about the origin of a domain and user emails using it.
- If they are unable to trust the organization that creates emails using that domain.
If the security team doesn’t have any concerns about the domain, then it should be considered "Trusted".
For each domain, if you find that it is necessary to investigate the emails using them, we recommend listing all users using that domain from the following:
In Jira: Administration console > Users menu > List users tab
In Confluence: Administration console > User Management tab > Users menu