What can a user with SET ANY SECURITY OPTION do?

[Breck_Carter]

Apparently there is no list, or even a Help topic that describes exactly what SET ANY SECURITY OPTION allows.

Oh, no, wait, there's this: SET ANY SECURITY OPTION Allows a user to set any PUBLIC security database options.

...well, that was useful ...not ...there is no such thing as "security database option" according to a search of the Help.

[Breck_Carter]

A Hallmark of the security model: So complex only hackers understand it 🙂

[VolkerBarth · Accepted solution]

In particular: I want to give a user the ability to "SET OPTION PUBLIC.database_authentication", and I want to know what the implications are.

If that is the only option required, wouldn't a separate wrapper procedure do the trick?

[Breck_Carter · Accepted solution]

> wouldn't a separate wrapper procedure do the trick?

Yes, indeed, that is the best answer... I am forever grateful for the CREATE PROCEDURE ... SQL SECURITY DEFINER clause.

Every few years I have to check "Is that really the default?" and then I re-discover The Watcom Rule... yes, that is the way it should be done, so yes, that is the way SQL Anywhere does it 🙂

[VolkerBarth]

Hm, apparently the PUBLIC database options are separated in

• PUBLIC security database options (requiring SET ANY SECURITY OPTION system privilege),
• PUBLIC system database options (requiring SET ANY SYSTEM OPTION system privilege),
• PUBLIC user-defined database options (requiring SET ANY USER DEFINED OPTION system privilege)
• and all other PUBLIC database options (requiring SET ANY PUBLIC OPTION system privilege).

The required system privilege seems to be documented for the according option. IMHO, this seems adequate... (And practically, SYS_AUTH_DBA_ROLE comprises all those privileges...:) )

[Breck_Carter]

You answered a different question: "What privileges are required to do [some individual action]?"

You pointed out that the documentation for [each individual action] clearly specifies the privilege(s) required.

That's not what I asked.

I asked the question "What [list of actions] require the SET ANY SECURITY OPTION system privilege?"

Putting it another way: "What [list of actions] will the user SUDDENLY be able to perform if I grant the SET ANY SECURITY OPTION privilege?"

In particular: I want to give a user the ability to "SET OPTION PUBLIC.database_authentication", and I want to know what the implications are.

These "grouping" privileges like SET ANY SECURITY OPTION are poorly designed... they are too broad, and the lack of thorough documentation makes them a security risk ...folks will GRANT powerful privileges just get through the day, without understanding the implications.

You mentioned SYS_AUTH_DBA_ROLE... that's exactly my point... I do NOT want to grant too much 🙂

[VolkerBarth]

That's not what I asked.

I asked the question "What [list of actions] require the SET ANY SECURITY OPTION system privilege?"

Yes, I'm aware, and obviously the docs do not contain a separate list of those options (or an explanation, what exactly would an option qualify as security option vs. system option).

As you already stated, searching for the according privilege in the help (only) lists the according individual option pages - so you got your list, I'd think...

I just wanted to note that these four option categories seem to be disjunct, so your search result should at least be non-overlapping with other option privileges. Otherwise, your search result would list options that might as well be allowed for a different option privilege.

[Breck_Carter]

> you got your list, I'd think

Yeah, but it took me tooooo long to think of that.

And with your other answer, I don't have to... I can go back to not thinking about the "role model" at all 🙂

[VolkerBarth]

That's what I generally do with the "role model", as well 🙂

[Breck_Carter]

A crude search-by-example yields a list of topics: "with SET ANY SECURITY OPTION"

allow_read_client_file Option
Product: SAP SQL Anywhere
Yes, with SET ANY SECURITY OPTION Yes, with SET ANY SECURITY OPTION Yes, with SET ANY SECURITY OPTION ... Yes, with SET ANY SECURITY OPTION Yes (current connection only), with SET ANY SECURITY OPTION No ...
Guide: SQL Anywhere Database Administration
Last updated: December 10, 2020

allow_write_client_file Option
Product: SAP SQL Anywhere
Yes, with SET ANY SECURITY OPTION Yes, with SET ANY SECURITY OPTION Yes, with SET ANY SECURITY OPTION ... Yes, with SET ANY SECURITY OPTION Yes (current connection only), with SET ANY SECURITY OPTION No ...
Guide: SQL Anywhere Database Administration
Last updated: December 10, 2020

...and so on