probably the asset table need to be rebuilded on group delete.

Indeed. This does not look simple to do as we just need to delete all rules belonging to that group.

Because the assets table can be large. This may need an AJAX based task to do such a cleanup, loading ALL assets, removing the references and resaving them. But this is only a minor / small issue, unless someone deletes and creates usergroups routinely

But i think it is simple to do with 2 sql queries on Usergroup deletion (without need for a REGEX):

Example

{
    "core.login.site":{"6":1,"2":1,"3":1,"4":1,"5":1},
    "core.login.admin":{"6":1,"4":1},
    "core.login.offline":{"6":1},
    "core.admin":{"8":1},
    "core.manage":{"6":1,"4":1},
    "core.create":{"6":1,"3":1,"4":0},
    "core.delete":{"6":1},
    "core.edit":{"6":1,"4":1},
    "core.edit.state":{"6":1,"5":1},
    "core.edit.own":{"6":1,"3":1,"4":1}
}

To delete user group 4

First replace:

,"4":1}
and
,"4":0}

with

}

Then replace

"4":0,
and
"4":1,

with nothing

UPDATE #__assets
SET rules = REPLACE(REPLACE(rules, '"4":1}', '}'), '"4":0}', '}')

UPDATE #__assets
SET rules = REPLACE(REPLACE(rules, '"4":1,', ''), '"4":0,', '')

I think the above is safe and fast, in worst case that a space exists in the rules ... it will not delete the usergroup related rule

The above may be the fastest but I don't think it's safe as it involves direct manipulation of a serialized JSON string. Ya you get a much less performant routine if you start decoding/encoding the data string to remove something, but that is the safest option. If it were a serialized PHP object that would be the only option.

@mbabker
i agree it does not look clean,
neither does it look safe (although it should be)

yes, better do with a routine that examines all assets using PHP code

still i would say that for great majority of websites this is minor issue,

unless ones creates and deletes hundrends or thousands of usergoups,

• and in the meantime of creation / deletion saves many forms with assets

then the web-site should not see any significant change in performance

I suggest having a #__permissions or #__asset_rules (any good name) table with columns asset_id, usergroup_id, rules etc.

I understand it's a work too much, and I'd come forward myself if this idea sounds good to everyone.

This will solve the said issue as it's apparent.

From the looks of things #14268 could fix this if that change is accepted into 4.0.

@infograf768 completed where?