Dag H. Wanvik wrote:
Thanks for your answer, Rick,
Rick Hillegas <[EMAIL PROTECTED]> writes:
1) You can't set yourself to a role which is not granted to you. Is
that right?
It must be granted to the current user or to PUBLIC, yes, cf. section
18.3 GR 4.
2) What does it mean to revoke a role from a user whose session is
currently operating as that role? It seems to me that this should
either be forbidden (that is, the revoke should fail) or the user's
session should be switched to having a null role or the user should be
allowed to continue operating as that role until she logs out.
Those are possible semantics, but not how I read the standard. As far
as I can interpret it, the necessary privilege should always (still)
be available/valid at execute time for the execution to be
legal. Also, I can't see in the standard any constraint for revoke
role that prohibits revoke of the role even if it is still set as
current role in a session. The optional RESTRICT described in 12.7 SR
36 does nto mention any such condition. Hence my wish to invalidate.
Let me know if you found something that indicates I am wrong in this,
please.
Thanks, Dag. This is helping me understand the problem. I'm still
unclear on the following point. Here's the setup:
a) User U sets her session S to operate under role R
b) The DBO revokes role R from U
c) Now U does more work in session S
But what is the role associated with session S now? Is it still R? If so
I would expect that the problematic statement would still be able to
execute in session S until the role of session S is changed.
Thanks,
-Rick
3) So how does the situation you are describing arise? Don't you
always have to check at runtime whether the session is operating as
the necessary role?
Yes, I think this is true. I guess I would be willing to accept that
this is checked only the first time the execute is attempted, though,
so avoid having to check on every execute (since nothing has been
revoked), although this is not quite according to the letter of the
standard. A semantically correct solution would be to *also*
invalidate activations and/or prepared statements (cf alternatives b
or c) dependent on the current role being every time a SET ROLE
statement is performed. This is just one *more* possibility for
invalidation in addition to the revoke of the role I described in my
previous message. I am less inclined to allow the prepared statement
to "survive" *revoke* of a needed role.. So, the invalidation of
prepared statement is needed, as far as I can see (or check every
time).
Dag
