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? asked 20 Apr '19, 19:07 rsnyder |
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... answered 21 Apr '19, 15:27 Volker Barth Thanks. And yes... a bit tedious to look but better than re-engineering.
(21 Apr '19, 16:22)
rsnyder
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.
(22 Apr '19, 08:34)
rsnyder
Replies hidden
I can't tell why that is the case but both the statements and the according system tables differ in that respect:
(23 Apr '19, 04:34)
Volker Barth
|
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. answered 21 Apr '19, 14:04 rsnyder 1
I would not have thought that permissions would be dropped either, so you are not the only one with something learnt here...
(21 Apr '19, 15:30)
Volker Barth
Honestly, that makes me feel just a tad better. :-)
(21 Apr '19, 16:21)
rsnyder
Replies hidden
2
"Never delete a user id" is actually an extrapolation from a more basic rule of databases: "Never delete anything" :)
(22 Apr '19, 07:34)
Breck Carter
|
This information is stored in views *perm such as SYSTABLEPERM.
Lesson Learned: Never delete a user id, just change the password :)
Or just drop the password...
...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.
SETUSER is your friend here:)