SQL Anywhere 16. We had someone retire, and I am removing his role as a dba. He feels that removing his dba role will affect any authorities he granted as dba. I'm sure that's not true, as it's not like I'm removing the dba user! But I said that I would ask.

asked 04 Jun, 07:09

rsnyder's gravatar image

rsnyder
3166821
accept rate: 0%

You are asking with the lesson learnt from that question, right?

(04 Jun, 07:43) Volker Barth

Yes, I am! And everyone is nervous about removing anything. :-)

(04 Jun, 07:45) rsnyder

What exactly statement(s) are you going to run, and how was the DBA role assigned?

Note, I'm just asking for details, I won't be able to answer your question...

(04 Jun, 07:47) Volker Barth

Grant DBA to "JDoe" is how it was assigned. and Revoke DBA from "JDoe" is how I plan to remove.

(04 Jun, 07:59) rsnyder
Replies hidden

As stated, I do not know, and besides the obvious "Just test this on a copy of your database or a sample database" the following quote makes me wonder...:

From the REVOKE statement (deprecated):

If you revoke a privilege for a user and they also had WITH GRANT OPTION for that privilege, then everyone who that user granted the privilege to also has their privilege revoked. For example, suppose you granted UserA SELECT...WITH GRANT OPTION privileges on a table and UserA then grants the SELECT privilege on the table to UserB. If you revoke the SELECT privilege from UserA, it is revoked from UserB as well.

In my understanding, the former "DBA authority" / new "SYS_AUTH_DBA_ROLE compatibility role" has no explicit "WITH GRANT OPTION", so the above effect may or may not apply.


Resume: I won't be able to answer your question...

(04 Jun, 08:30) Volker Barth

Change the password, and otherwise leave well enough alone :)

permanent link

answered 04 Jun, 10:18

Breck%20Carter's gravatar image

Breck Carter
29.3k486649957
accept rate: 20%

1

Well, better drop the password, right?

That being said, it would be somewhat disappointing if the fancy and mighty v16 role-based access model would be soo complex that "never drop a user" would make the common rule of thumb...

(04 Jun, 10:27) Volker Barth
Replies hidden

Point taken, Breck. I guess I need to stop trying to clean up users. And totally agree with you, Volker.

(04 Jun, 12:19) rsnyder

I did ask our paid support. As I think this should work; not sure how warm and fuzzy I feel about it. Will likely go with altering password.

Hi,

After some testing, as long as the user is not getting deleted. It is not going to effect other database object that the user added, edited while logging in as DBA,

Regards,

xxxxxxx

04.06.2019 11:41:34 CST

(04 Jun, 13:56) rsnyder

> Well, better drop the password, right?

Either that, or make the new password strong, secret, and in the possession of someone authorized to be a DBA. Having a password avoids the extra step of adding a password should the need arise to use that id.

> fancy and mighty v16 role-based access model

...a solution looking for a problem most people simply ... do ... not ... have.

(most people don't read in-flight magazine articles about security :)

(05 Jun, 10:37) 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

question asked: 04 Jun, 07:09

question was seen: 169 times

last updated: 05 Jun, 10:37