Removing DBA authority

[former_SQLA_member1694892]

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.

[VolkerBarth]

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

[former_SQLA_member1694892]

Yes, I am! And everyone is nervous about removing anything. 🙂

[VolkerBarth]

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

[former_SQLA_member1694892]

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

[VolkerBarth]

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

[Breck_Carter]

Change the password, and otherwise leave well enough alone 🙂

[VolkerBarth]

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

[former_SQLA_member1694892]

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

[former_SQLA_member1694892]

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

[Breck_Carter]

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