PSA: Users no longer require read access to their own database object to work

[AdamWoodland]

Hi Everyone,

I do ClearSCADA database reviews for end users and one of the most common issue found on databases that have been around for a while are ACLs on users allowing non-administrative users access.

Up until CS2015R2 (I think, that era anyway, someone from the product team might be able to confirm exactly) users needed read access to their own database object for their settings to be correctly loaded at logon. Ideally you would spend effort and only give that user access to their object (along with user administrators) but in reality what happened was whole user groups were given read access to all user objects. In some extreme cases Everyone is given read allowing anyone to dump the entire user list and their config using some simple SQL (no passwords) and in one case I found Guest even had configure... that was fixed pretty quickly.

In recently released builds the user has implied access to their own database object so only those who administer users now need any ACLs on user accounts. An exception would be if there was some metadata stored against the user that might be used in mimic scripting or something, but those solutions are pretty rare.

An SQL query that might be useful is:

SELECT
ID, FULLNAME, ACLASTEXT AS "Local", PARENTGROUPID->ACLASTEXT AS "Parent"
FROM
CDBOBJECT
WHERE
ID <> 0 AND "Local" <> "Parent"
ORDER BY
"FullName" ASC

They query simply lists all objects where their ACLs do not exactly match their parent object. Should show you any custom security you have in place so you can audit it. It will highlight where the order in the items in the ACLs are different but the effective actual permissions are the same (i.e. not inherited, someone removed and then added someone with the exact same permissions), but those should be pretty rare and in those cases probably needs to be set back to inherited anyway.

[BevanWeiss · Accepted solution]

In addition there has been a new property added (to CDBObject) ACLInherited which tells you whether the security permissions have been inherited.

So my slight tweak on the query that Mr @AdamWoodland had.

SELECT
ID, FULLNAME, ACLInherited, ACLASTEXT AS "Local", PARENTGROUPID->ACLASTEXT AS "Parent"
FROM
CDBOBJECT
WHERE
ID <> 0 AND ("Local" <> "Parent" OR ACLInherited<>TRUE)
ORDER BY
"FullName" ASC

[AdamWoodland]

Hmm, great idea!