How can I find all ACL that has no roles?

[AtacanK]

How can I find all ACL that has no roles? 

thank you

[Bert_c1]

You can try the following script, after elevating your role to 'security_admin'

var aclRecord = new GlideRecord('sys_security_acl');
aclRecord.query();
while (aclRecord.next()) {
	var aclRole = new GlideRecord('sys_security_acl_role');
	aclRole.addQuery('sys_security_acl', aclRecord.sys_id.toString());
	aclRole.query();
	if (!aclRole.next()) {
		gs.info("ACL: " + aclRecord.name + " has no roles.");
	}
}

I got:

*** Script: ACL: incident.knowledge has no roles.
*** Script: ACL: pc_vendor_cat_item.category has no roles.
*** Script: ACL: clone_profile_exclusions.* has no roles.
*** Script: ACL: sys_template has no roles.
*** Script: ACL: sys_language_region has no roles.
[0:00:00.002] Expanding large row block (file.read: sys_security_acl, 10000 rows, 160000 dataSize)
[0:00:00.001] Compacting large row block (file.write: sys_security_acl 10000 rows 160000 saveSize)
*** Script: ACL: clone_profile_cleanup_scripts.* has no roles.
*** Script: ACL: alm_transfer_order_line.request_line has no roles.
[0:00:00.002] Expanding large row block (file.read: sys_security_acl, 10000 rows, 160000 dataSize)
[0:00:00.001] Compacting large row block (file.write: sys_security_acl 10000 rows 160000 saveSize)
*** Script: ACL: pm_project_task.work_end has no roles.
*** Script: ACL: pm_project_task.work_start has no roles.
*** Script: ACL: alm_transfer_order_line.transfer_order has no roles.
*** Script: ACL: alm_entitlement.* has no roles.
*** Script: ACL: sp_favorite.* has no roles.
*** Script: ACL: sys_db_object has no roles.
[0:00:00.001] Expanding large row block (file.read: sys_security_acl, 4155 rows, 66480 dataSize)
[0:00:00.002] Compacting large row block (file.write: sys_security_acl 10000 rows 160000 saveSize)
*** Script: ACL: clone_profile_preservers.* has no roles.
*** Script: ACL: incident.close_code has no roles.
*** Script: ACL: incident.close_notes has no roles.
Time: 0:00:00.173 id: dev181262_1[glide.9] primary_hash=-149912 (connpid=204028) for: SELECT sys_security_acl_role0.`sys_id` FROM (sys_security_acl_role sys_security_acl_role0  INNER JOIN sys_metadata sys_metadata0 ON sys_security_acl_role0.`sys_id` = sys_metadata0.`sys_id` )  WHERE sys_security_acl_role0.`sys_security_acl` = 'fe42451c43d02110bb9e89e90db8f25c' ORDER BY sys_security_acl_role0.`sys_id` /* dev181262002, gs:E39C25B653315AD463141AB0A0490E8A, tx:7e6f217253b55ad463141ab0a0490e0d, hash:-149912 */

You'll need to check the results. I added a check for Active ACLs and got none with no roles:

var aclRecord = new GlideRecord('sys_security_acl');
aclRecord.addActiveQuery();		// check Active ACLs
aclRecord.query();
while (aclRecord.next()) {
	var aclRole = new GlideRecord('sys_security_acl_role');
	aclRole.addQuery('sys_security_acl', aclRecord.sys_id.toString());
	aclRole.query();
	if (!aclRole.next()) {
		gs.info("ACL: " + aclRecord.name + " has no roles.");
	}
}

[AtacanK]

Hi, thank you for your response.

I get this from the script:

[0:00:00.002] Compacting large row block (file.write: sys_security_acl 10000 rows 160000 saveSize)
[0:00:00.001] Compacting large row block (file.write: sys_security_acl 10000 rows 160000 saveSize)
[0:00:00.001] Compacting large row block (file.write: sys_security_acl 10000 rows 160000 saveSize)
[0:00:00.001] Expanding large row block (file.read: sys_security_acl, 10000 rows, 160000 dataSize)
[0:00:00.001] Compacting large row block (file.write: sys_security_acl 5889 rows 94224 saveSize)

[AtacanK]

Hi, thank you for your response.

I get this from the script:

[0:00:00.002] Compacting large row block (file.write: sys_security_acl 10000 rows 160000 saveSize)
[0:00:00.001] Compacting large row block (file.write: sys_security_acl 10000 rows 160000 saveSize)
[0:00:00.001] Compacting large row block (file.write: sys_security_acl 10000 rows 160000 saveSize)
[0:00:00.001] Expanding large row block (file.read: sys_security_acl, 10000 rows, 160000 dataSize)
[0:00:00.001] Compacting large row block (file.write: sys_security_acl 5889 rows 94224 saveSize)

[Bert_c1]

Yes, if you remove the 'addActiveQuery' you may see results.