You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Dag H. Wanvik" <Da...@Sun.COM> on 2008/04/16 15:12:18 UTC
Rationale for REVOKE EXECUTE.. RESTRICT vs. REVOKE
Hi,
A while back I asked if anyone knew the rationale for requiring
RESTRICT on the revoke statement when an execute privilege is revoked,
but I got no reply so I am trying again :)
Similaryly, who do we not allow RESTRICT for table/column privilege?
Knowing the answer might help inform a decision I have to make when
specifying REVOKE <role> as part of the roles work.
Short background:
* Derby requires the RESTRICT semantics when revoking execution privileges.
Derby does not allow RESTRICT semantics when revoking table/column privileges.
That is, the revoke semantics for the two kinds of privileges are
incompatible.
* All kinds of privileges can be granted to a role, say role A. A
user who is granted the use of role A, can then make use of
privileges granted to A, both execution and table privileges,
possibly relying on the for creating persistent objects
(constraints, trigger or views).
* If role A is now revoked from the user, should we use RESTRICT or
CASCADE semantics? Since the role may enjoy both kinds of privileges
at the same time, no matter which semantics we choose it would
potentially be surprising given that Derby has imcompatible revoke
semantics as described.
If we let REVOKE <role>role have RESTRICT semantics, we would get RESTRICT
semantics for table/column privileges, if such a privilege had been
granted to A.
Conversely, if we let REVOKE <role> have CASCADE semantics, it could
surprise users if an execute privilege were had been granted to A.
If I have to choose, I would say that RESTRICT seems less risky in
that any surprise is less potentially damaging. What do you think?
Dag
Re: Rationale for REVOKE EXECUTE.. RESTRICT vs. REVOKE
Posted by "Dag H. Wanvik" <Da...@Sun.COM>.
Mamta Satoor <ms...@gmail.com> writes:
> Dag, I am busy right now with a customer issue and apologize for not
> finding time to look at this Revoke Restrict issue. May be someone
> else remembers the discussion about it fromcouple years back. My
> memory is definitely failing on me and I would need to look through
> the past mails to refresh my memory if there was any discussion about
> this topic. My apology, but I do not think I will be able to get to it
> anytime soon.
No problem, I'll try to dig back and see what I can find in the
archives :)
Dag
Re: Rationale for REVOKE EXECUTE.. RESTRICT vs. REVOKE
Posted by "Dag H. Wanvik" <Da...@Sun.COM>.
Daniel John Debrunner <dj...@apache.org> writes:
> Maybe it wasn't anyone's itch to implement the required behaviour when
> RESTRICT is omitted.
Thanks, Dan. Yes, I realize it could be as simple as that. The reason I was
curious, is that the revoke behavior for the table privileges are
CASCADE, and I didn't understand the assymmetry.
Dag
Re: Rationale for REVOKE EXECUTE.. RESTRICT vs. REVOKE
Posted by Daniel John Debrunner <dj...@apache.org>.
Dag H. Wanvik wrote:
> Dag.Wanvik@Sun.COM (Dag H. Wanvik) writes:
>
>> RESTRICT for revoke routine is not not given a rationale here,
>> though.
Maybe it wasn't anyone's itch to implement the required behaviour when
RESTRICT is omitted.
Dan.
Re: Rationale for REVOKE EXECUTE.. RESTRICT vs. REVOKE
Posted by "Dag H. Wanvik" <Da...@Sun.COM>.
Dag.Wanvik@Sun.COM (Dag H. Wanvik) writes:
> RESTRICT for revoke routine is not not given a rationale here,
> though. Looking further...
I see this version of DB2 also requires RESTRICT:
http://publib.boulder.ibm.com/infocenter/db2luw/v9/topic/com.ibm.db2.udb.admin.doc/doc/r0007700.htm
So, possibly, the rationale for the Derby behavior here is merely DB2
compatibility.
However, some docs for DB2 makes it optional:
http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db29.doc.sqlref/db2z_sql_revokefunctionorprocedureprivileges.htm
It seems the initial specification for GRANT/REVOKE had mandatory
RESTRICT for execution privilege, no rationale given (DERBY-464):
https://issues.apache.org/jira/secure/attachment/12323047/grantRevokeSpec.html
Dag
Re: Rationale for REVOKE EXECUTE.. RESTRICT vs. REVOKE
Posted by "Dag H. Wanvik" <Da...@Sun.COM>.
Found this quote in DERBY-1330's attachment
"AuthorizationModelForDerbySQLStandardAuthorizationV2.html" which
explains the absence of an explicit CASCADE syntax in Derby:
> Derby SQL Standard Authorization will support SQL2003 CASCADE behavior
> on revoke(with the exception of revoke routine, where RESTRICT
> behavior will be supported and the RESTRICT will be a mandatory
> keyword on the revoke routine syntax). Although CASCADE will be the
> default behavior of Derby SQL Standard Authorization's revoke
> statement, syntactically, there will be no way to specify the keyword
> CASCADE on the revoke syntax.
>
> This decision was based on the fact that none of the other major
> databases, including Oracle, SQL Server, DB2, MySQL provide a way to
> specify RESTRICT/CASCADE behavior on their revoke statement
> syntax).*
RESTRICT for revoke routine is not not given a rationale here,
though. Looking further...
Dag
Re: Rationale for REVOKE EXECUTE.. RESTRICT vs. REVOKE
Posted by Mamta Satoor <ms...@gmail.com>.
Dag, I am busy right now with a customer issue and apologize for not
finding time to look at this Revoke Restrict issue. May be someone
else remembers the discussion about it fromcouple years back. My
memory is definitely failing on me and I would need to look through
the past mails to refresh my memory if there was any discussion about
this topic. My apology, but I do not think I will be able to get to it
anytime soon.
Mamta
On 4/16/08, Rick Hillegas <Ri...@sun.com> wrote:
> Dag H. Wanvik wrote:
> > Hi,
> >
> > A while back I asked if anyone knew the rationale for requiring
> > RESTRICT on the revoke statement when an execute privilege is revoked,
> > but I got no reply so I am trying again :)
> >
> > Similaryly, who do we not allow RESTRICT for table/column privilege?
> >
> > Knowing the answer might help inform a decision I have to make when
> > specifying REVOKE <role> as part of the roles work.
> >
> > Short background:
> >
> > * Derby requires the RESTRICT semantics when revoking execution
> privileges.
> > Derby does not allow RESTRICT semantics when revoking table/column
> privileges.
> >
> > That is, the revoke semantics for the two kinds of privileges are
> > incompatible.
> >
> > * All kinds of privileges can be granted to a role, say role A. A
> > user who is granted the use of role A, can then make use of
> > privileges granted to A, both execution and table privileges,
> > possibly relying on the for creating persistent objects
> > (constraints, trigger or views).
> >
> > * If role A is now revoked from the user, should we use RESTRICT or
> > CASCADE semantics? Since the role may enjoy both kinds of privileges
> > at the same time, no matter which semantics we choose it would
> > potentially be surprising given that Derby has imcompatible revoke
> > semantics as described.
> >
> > If we let REVOKE <role>role have RESTRICT semantics, we would get
> RESTRICT
> > semantics for table/column privileges, if such a privilege had been
> > granted to A.
> >
> > Conversely, if we let REVOKE <role> have CASCADE semantics, it could
> > surprise users if an execute privilege were had been granted to A.
> > If I have to choose, I would say that RESTRICT seems less risky in
> > that any surprise is less potentially damaging. What do you think?
> >
> > Dag
> >
> >
> Hi Dag,
>
> I agree that RESTRICT seems less damaging. But it would be good to know why
> RESTRICT was not allowed for table/column privileges. Maybe Mamta or Dan
> could you shed some light on this.
>
> Thanks,
> -Rick
>
Re: Rationale for REVOKE EXECUTE.. RESTRICT vs. REVOKE
Posted by Rick Hillegas <Ri...@Sun.COM>.
Dag H. Wanvik wrote:
> Hi,
>
> A while back I asked if anyone knew the rationale for requiring
> RESTRICT on the revoke statement when an execute privilege is revoked,
> but I got no reply so I am trying again :)
>
> Similaryly, who do we not allow RESTRICT for table/column privilege?
>
> Knowing the answer might help inform a decision I have to make when
> specifying REVOKE <role> as part of the roles work.
>
> Short background:
>
> * Derby requires the RESTRICT semantics when revoking execution privileges.
> Derby does not allow RESTRICT semantics when revoking table/column privileges.
>
> That is, the revoke semantics for the two kinds of privileges are
> incompatible.
>
> * All kinds of privileges can be granted to a role, say role A. A
> user who is granted the use of role A, can then make use of
> privileges granted to A, both execution and table privileges,
> possibly relying on the for creating persistent objects
> (constraints, trigger or views).
>
> * If role A is now revoked from the user, should we use RESTRICT or
> CASCADE semantics? Since the role may enjoy both kinds of privileges
> at the same time, no matter which semantics we choose it would
> potentially be surprising given that Derby has imcompatible revoke
> semantics as described.
>
> If we let REVOKE <role>role have RESTRICT semantics, we would get RESTRICT
> semantics for table/column privileges, if such a privilege had been
> granted to A.
>
> Conversely, if we let REVOKE <role> have CASCADE semantics, it could
> surprise users if an execute privilege were had been granted to A.
>
> If I have to choose, I would say that RESTRICT seems less risky in
> that any surprise is less potentially damaging. What do you think?
>
> Dag
>
Hi Dag,
I agree that RESTRICT seems less damaging. But it would be good to know
why RESTRICT was not allowed for table/column privileges. Maybe Mamta or
Dan could you shed some light on this.
Thanks,
-Rick