db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dag H. Wanvik (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-3223) SQL roles: make use of privileges granted to roles in actual privilege checking
Date Wed, 19 Dec 2007 01:43:43 GMT

    [ https://issues.apache.org/jira/browse/DERBY-3223?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12553193
] 

Dag H. Wanvik commented on DERBY-3223:
--------------------------------------

>From derby-dev thread:

From: "Dag H. Wanvik" <Dag.Wanvik@Sun.COM>
Date: Mon, 17 Dec 2007 18:03:30 +0100

Working on SQL roles, I have encountered a problem related to
invalidation of prepared statements. 

My understanding at this point: Presently, at execute time, permission
is only checked the first time a prepared statement is executed for a
connection, as part of creating a result set object structure which is
subsequently reused (BaseActivation#resultSet).

When permissions are revoked, invalidation is signaled from
GrantRevokeConstantAction (see TablePrivilegeInfo and
RoutinePrivilegeInfo respectively) via the dependency the prepared
statement has on the *SQL object* (e.g. table) for which premission is
revoked, cf. DERBY-2594, via the action USER_RECOMPILE_REQUEST
(*not* via dependency on the permission descriptor itself). 

This causes the prepare statement to be recompiled, and hence also a
new permissions check (the call to authorize happen as part of
fillResultSet which is conditional, c.f code generation in
StatementNode#generate which generates call to fillResultSet which in
turn calls authorize (see CursorNode#generate which calls
generateAuthorizeCheck to accomplish this). Earlier, permissions
checking happened every time a prepared statement was executed, but
this was changed with the work to reuse result sets, cf. DERBY-827.

Now, with the introduction of roles, when checking permissions at
execute time, an activation can come to rely on a privilege obtained
indirectly via a role grant to the user of the current session. But
when this role is revoked from the user, execution of the (prepared)
statement should (possibly) no longer be allowed. (Note: there is no
revoke of a privilege here, since the grant of the privilege is to the
role, so the current mechanism to force recompilation is
unsufficient).

To accomplish the required behavior, the authorize check needs to be
executed anew, possibly leading to an error if no other applicable
privilege can be found to satisfy the query in question.

I can imagine the following ways of making this happen:

a) going back to checking permissions for every execution
   Any revoked role grant would then be effective immediately.

b) as part of authorize() (e.g. in
   StatementTablePermission#hasPermissionOnTable), introduce a
   dependency for the activation on a role grant descriptor (that is,
   if indeed a role is required to satisfy the required privilege),
   causing the dropping of that descriptor to invalidate any dependent
   activation, so a new permission check can take place when the
   statement is executed next time.

c) as part of authorize() introduce a dependency on the Prepared
   statement on the role grant descriptor, causing the dropping of
   that descriptor to invalidate any dependent prepared statement.

Currently, a prepared statement can be a Dependent, but not the
activation as far as i can see. I tried c) and it seems to work (but
is it safe to register a dependency at execute time for a prepared
statement?). This is essentially extending the present solution.  It
seems a bit heavy, though, to have to recompile, when all that is
needed is a new check in the current connection? Maybe there is a
reason why we cannot make an activation a Dependent?

I would also like to avoid a) for performance reasons, so b) seems to
have the correct granularity. Comments?

-------------------------
From: Rick Hillegas <Richard.Hillegas@Sun.COM>
Date: Mon, 17 Dec 2007 10:06:01 -0800

Hi Dag,

It will take me a while to understand the complexity of the issue you
are wrestling with. But let me ask some initial questions:

1) You can't set yourself to a role which is not granted to you. Is
that right?

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.

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?

Thanks,
-Rick

-----------------------------------------------------------------
From: Dag.Wanvik@Sun.COM (Dag H. Wanvik)
Date: Mon, 17 Dec 2007 23:51:23 +0100


Thanks for your answer, Rick,

Rick Hillegas <Richard.Hillegas@Sun.COM> 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.


> 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

-----------------------------------------------------------------------

From: Rick Hillegas <Richard.Hillegas@Sun.COM>
Subject: Re: How to invalidate when a role is revoked, so privileges will be rechecked?
To: derby-dev@db.apache.org
Date: Tue, 18 Dec 2007 06:17:26 -0800
Reply-to: derby-dev@db.apache.org

Dag H. Wanvik wrote:
> Thanks for your answer, Rick,
>
> Rick Hillegas <Richard.Hillegas@Sun.COM> 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

--------------------------------------------------------------------
From: Dag.Wanvik@Sun.COM (Dag H. Wanvik)
Subject: Re: How to invalidate when a role is revoked, so privileges will be rechecked?
To: derby-dev@db.apache.org
Date: Tue, 18 Dec 2007 17:14:02 +0100
Reply-to: derby-dev@db.apache.org

Rick Hillegas <Richard.Hillegas@Sun.COM> writes:

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

The way I read the standard, the revoke is effective immediately. In
my current implementation, a session's current role is still
(technically) set although no longer valid (see the field
GenericLanguageConnectionContext#currentRole, which is a role
descriptor)

However, it is lazily (re)set to none as soon as the session tries to
"use" it after it has been revoked.  So, if you try to reprepare the
same statement after the revoke, the authorization would fail, as it
should.

The issue here is that the statement has already been prepared at a
time at which the role was not yet revoked, so the activation for the
prepared statement thinks it is still valid. A reprepare, for example
provoked by my alternative c) approach, causes all connections to
regenerate the result set structure and as a part of that one-time
set-up, also do the authorization check, and hence assure that the
needed permission is available, or fail the execute. This is a bit
heavy-handed, though, since connections not affected by the role
revoke will be forced to do extra work, hence my alternative b) to
invalidate just the affected activations. This does away with the
reprepare, but would just make the affected connections reinitialize
the activation (including redoing the authorization check).

Dag


> SQL roles: make use of privileges granted to roles in actual privilege checking
> -------------------------------------------------------------------------------
>
>                 Key: DERBY-3223
>                 URL: https://issues.apache.org/jira/browse/DERBY-3223
>             Project: Derby
>          Issue Type: New Feature
>          Components: Security, SQL
>            Reporter: Dag H. Wanvik
>            Assignee: Dag H. Wanvik
>


-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


Mime
View raw message