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's gravatar image

rsnyder
436121429
accept rate: 0%

2

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

(20 Apr '19, 21:49) Chris Keating
1

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

(21 Apr '19, 11:19) Breck Carter
Replies hidden

Or just drop the password...

(21 Apr '19, 12:07) Volker Barth

...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.

(22 Apr '19, 07:30) Breck Carter

SETUSER is your friend here:)

(22 Apr '19, 10:43) Volker Barth

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...

permanent link

answered 21 Apr '19, 15:27

Volker%20Barth's gravatar image

Volker Barth
40.0k361549819
accept rate: 34%

converted 21 Apr '19, 15:27

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:

  • 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
(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.

permanent link

answered 21 Apr '19, 14:04

rsnyder's gravatar image

rsnyder
436121429
accept rate: 0%

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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×28
×7

question asked: 20 Apr '19, 19:07

question was seen: 1,247 times

last updated: 23 Apr '19, 04:38