Deleted user - lost permissions

[former_SQLA_member1694892]

We had a long time user that administered to some of our databases. She has retired, and before I removed her userid, I made sure that any objects that she was owner of were benign (test tables, for instance) or recreated under another user. (Events, etc.) However, upon deleting her userid, we have found that permissions that she granted also went away. I had a backed up version of the database, but I cannot find a way to find which permissions that she granted. Can someone tell me how to find this and recreate?

[chris_keating]

This information is stored in views *perm such as SYSTABLEPERM.

[Breck_Carter]

Lesson Learned: Never delete a user id, just change the password 🙂

[VolkerBarth]

Or just drop the password...

[Breck_Carter]

...which would work if you did not need to sign on to make changes.

From a security point of view, it is OK for one person to have multiple user ids, and when someone leaves it should bo OK to pass that user id on to a replacement. Auditing is still effective if each user id can only be used by one person.

[VolkerBarth]

SETUSER is your friend here:)

[VolkerBarth]

If you have the backup, I'd suggest to do an unload with option - n (just the schema) and look for the according grant statements with this grantor. Probably easier than to re-engineer the system catalog...

[former_SQLA_member1694892]

Thanks. And yes... a bit tedious to look but better than re-engineering.

[former_SQLA_member1694892]

Going through the unload as suggested (thanks) to make sure that we've recovered everything, and it appears that GRANT EXECUTE doesn't care who granted it. Only tables and columns. Is that true? And I wonder why that would be the case.

[VolkerBarth]

I can't tell why that is the case but both the statements and the according system tables differ in that respect:

• The syntax for GRANT EXECUTE does not offer a "FROM clause" nor does it contain a "WITH GRANT OPTION" clause, so only the procedure's owner or DBA can grant that privilege.
• The system table SYSPROCAUTH does not contain a "grantor" column

[former_SQLA_member1694892]

Lesson learned for sure. I honestly thought that by searching the entire db that I could find anything related to her. It found all objects owned by her, which I had already reviewed manually. But it didn't return results of permissions granted by her. Ugh.

[VolkerBarth]

I would not have thought that permissions would be dropped either, so you are not the only one with something learnt here...

[former_SQLA_member1694892]

Honestly, that makes me feel just a tad better. 🙂

[Breck_Carter]

"Never delete a user id" is actually an extrapolation from a more basic rule of databases: "Never delete anything" 🙂