on 2019 Apr 20 8:07 PM
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?
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...
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
I can't tell why that is the case but both the statements and the according system tables differ in that respect:
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
I would not have thought that permissions would be dropped either, so you are not the only one with something learnt here...
"Never delete a user id" is actually an extrapolation from a more basic rule of databases: "Never delete anything" 🙂
User | Count |
---|---|
73 | |
10 | |
8 | |
7 | |
7 | |
6 | |
6 | |
6 | |
6 | |
6 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.