db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From d..@apache.org
Subject svn commit: r594158 [2/3] - in /db/derby/code/trunk: java/engine/org/apache/derby/iapi/sql/compile/ java/engine/org/apache/derby/iapi/sql/conn/ java/engine/org/apache/derby/iapi/sql/dictionary/ java/engine/org/apache/derby/impl/sql/catalog/ java/engine...
Date Mon, 12 Nov 2007 14:24:28 GMT
Added: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DropRoleConstantAction.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DropRoleConstantAction.java?rev=594158&view=auto
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DropRoleConstantAction.java (added)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DropRoleConstantAction.java Mon Nov 12 06:24:25 2007
@@ -0,0 +1,122 @@
+/*
+
+   Derby - Class org.apache.derby.impl.sql.execute.DropRoleConstantAction
+
+   Licensed to the Apache Software Foundation (ASF) under one or more
+   contributor license agreements.  See the NOTICE file distributed with
+   this work for additional information regarding copyright ownership.
+   The ASF licenses this file to you under the Apache License, Version 2.0
+   (the "License"); you may not use this file except in compliance with
+   the License.  You may obtain a copy of the License at
+
+      http://www.apache.org/licenses/LICENSE-2.0
+
+   Unless required by applicable law or agreed to in writing, software
+   distributed under the License is distributed on an "AS IS" BASIS,
+   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+   See the License for the specific language governing permissions and
+   limitations under the License.
+
+ */
+
+package org.apache.derby.impl.sql.execute;
+
+import org.apache.derby.iapi.sql.execute.ConstantAction;
+
+import org.apache.derby.iapi.error.StandardException;
+import org.apache.derby.iapi.sql.Activation;
+import org.apache.derby.iapi.sql.conn.LanguageConnectionContext;
+import org.apache.derby.iapi.sql.dictionary.DataDictionary;
+import org.apache.derby.iapi.sql.dictionary.RoleDescriptor;
+import org.apache.derby.shared.common.reference.SQLState;
+import org.apache.derby.iapi.store.access.TransactionController;
+
+/**
+ *  This class  describes actions that are ALWAYS performed for a
+ *  DROP ROLE Statement at Execution time.
+ *
+ */
+
+class DropRoleConstantAction extends DDLConstantAction
+{
+
+
+    private final String roleName;
+
+
+    // CONSTRUCTORS
+
+    /**
+     *  Make the ConstantAction for a DROP ROLE statement.
+     *
+     *  @param  roleName  role name to be dropped
+     *
+     */
+    DropRoleConstantAction(String roleName)
+    {
+        this.roleName = roleName;
+    }
+
+    ///////////////////////////////////////////////
+    //
+    // OBJECT SHADOWS
+    //
+    ///////////////////////////////////////////////
+
+    public String toString()
+    {
+        // Do not put this under SanityManager.DEBUG - it is needed for
+        // error reporting.
+        return "DROP ROLE " + roleName;
+    }
+
+    // INTERFACE METHODS
+
+
+    /**
+     *  This is the guts of the Execution-time logic for DROP ROLE.
+     *
+     *  @see ConstantAction#executeConstantAction
+     *
+     * @exception StandardException     Thrown on failure
+     */
+    public void executeConstantAction( Activation activation )
+        throws StandardException
+    {
+        LanguageConnectionContext lcc =
+            activation.getLanguageConnectionContext();
+        DataDictionary dd = lcc.getDataDictionary();
+        TransactionController tc = lcc.getTransactionExecute();
+
+        /*
+        ** Inform the data dictionary that we are about to write to it.
+        ** There are several calls to data dictionary "get" methods here
+        ** that might be done in "read" mode in the data dictionary, but
+        ** it seemed safer to do this whole operation in "write" mode.
+        **
+        ** We tell the data dictionary we're done writing at the end of
+        ** the transaction.
+        */
+        dd.startWriting(lcc);
+
+        RoleDescriptor rd = dd.getRoleDefinitionDescriptor(roleName);
+
+        if (rd == null) {
+            throw StandardException.newException(
+                SQLState.ROLE_INVALID_SPECIFICATION, roleName);
+        }
+
+        rd.drop(lcc);
+
+        /*
+         * We dropped a role, now drop all dependents:
+         * - role grants to this role
+         * - grants of this role to other roles or users
+         * - privilege grants to this role
+         */
+
+        dd.dropRoleGrantsByGrantee(roleName, tc);
+        dd.dropRoleGrantsByName(roleName, tc);
+        dd.dropAllPermsByGrantee(roleName, tc);
+    }
+}

Propchange: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DropRoleConstantAction.java
------------------------------------------------------------------------------
    svn:eol-style = native

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GenericConstantActionFactory.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GenericConstantActionFactory.java?rev=594158&r1=594157&r2=594158&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GenericConstantActionFactory.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GenericConstantActionFactory.java Mon Nov 12 06:24:25 2007
@@ -264,6 +264,32 @@
 		return new CreateSchemaConstantAction(schemaName, aid);
 	}
 
+
+    /**
+	 * Make the ConstantAction for a CREATE ROLE statement.
+	 *
+	 * @param roleName	Name of role.
+	 */
+	public	ConstantAction	getCreateRoleConstantAction(String roleName)
+	{
+		return new CreateRoleConstantAction(roleName);
+	}
+
+
+	/**
+	 * Make the ConstantAction for a SET ROLE statement.
+	 *
+	 * @param roleName  Name of role
+	 * @param type      Literal (== 0)
+	 *                  or ?    (== StatementType.SET_ROLE_DYNAMIC)
+	 */
+	public ConstantAction getSetRoleConstantAction(String roleName,
+												   int type)
+	{
+		return new SetRoleConstantAction(roleName, type);
+	}
+
+
 	/**
 	 *	Make the ConstantAction for a CREATE TABLE statement.
 	 *
@@ -505,7 +531,19 @@
 	}
 
 	/**
-	 *	Make the ConstantAction for a DROP TABLE statement.
+	 *	Make the ConstantAction for a DROP ROLE statement.
+	 *
+	 *	@param	roleName			role name to be dropped
+	 *
+	 */
+	public ConstantAction getDropRoleConstantAction(String roleName)
+	{
+		return new DropRoleConstantAction(roleName);
+	}
+
+
+	/**
+	 *	Make the ConstantAction for a DROP SCHEMA statement.
 	 *
 	 *	@param	schemaName			Table name.
 	 *
@@ -955,6 +993,21 @@
 		return new GrantRevokeConstantAction( true, privileges, grantees);
 	}
 
+
+    /**
+	 * Make the ConstantAction for a GRANT role statement.
+	 *
+	 * @param roleNames list of roles to be granted
+	 * @param grantees  list of authentication ids (user or roles) to
+	 *                  which roles(s) are to be granted
+	 */
+	public ConstantAction getGrantRoleConstantAction(List roleNames,
+													 List grantees)
+	{
+		return new GrantRoleConstantAction(roleNames, grantees);
+	}
+
+
 	/**
 	 * Make the constant action for a Revoke statement
 	 * 
@@ -965,5 +1018,19 @@
 								List grantees)
 	{
 		return new GrantRevokeConstantAction( false, privileges, grantees);
+	}
+
+
+    /**
+	 * Make the ConstantAction for a REVOKE role statement.
+	 *
+	 * @param roleNames list of roles to be revoked
+	 * @param grantees  list of authentication ids (user or roles) for whom
+	 *                  roles are to be revoked
+	 */
+	public ConstantAction getRevokeRoleConstantAction(List roleNames,
+													  List grantees)
+	{
+		return new RevokeRoleConstantAction(roleNames, grantees);
 	}
 }

Added: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GrantRoleConstantAction.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GrantRoleConstantAction.java?rev=594158&view=auto
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GrantRoleConstantAction.java (added)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GrantRoleConstantAction.java Mon Nov 12 06:24:25 2007
@@ -0,0 +1,197 @@
+/*
+
+   Derby - Class org.apache.derby.impl.sql.execute.GrantRoleConstantAction
+
+   Licensed to the Apache Software Foundation (ASF) under one or more
+   contributor license agreements.  See the NOTICE file distributed with
+   this work for additional information regarding copyright ownership.
+   The ASF licenses this file to you under the Apache License, Version 2.0
+   (the "License"); you may not use this file except in compliance with
+   the License.  You may obtain a copy of the License at
+
+      http://www.apache.org/licenses/LICENSE-2.0
+
+   Unless required by applicable law or agreed to in writing, software
+   distributed under the License is distributed on an "AS IS" BASIS,
+   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+   See the License for the specific language governing permissions and
+   limitations under the License.
+
+ */
+
+package org.apache.derby.impl.sql.execute;
+
+import org.apache.derby.iapi.sql.execute.ConstantAction;
+
+import java.util.Iterator;
+import java.util.List;
+import org.apache.derby.iapi.error.StandardException;
+import org.apache.derby.iapi.sql.Activation;
+import org.apache.derby.iapi.sql.conn.LanguageConnectionContext;
+import org.apache.derby.iapi.sql.dictionary.DataDescriptorGenerator;
+import org.apache.derby.iapi.sql.dictionary.RoleDescriptor;
+import org.apache.derby.iapi.sql.dictionary.DataDictionary;
+import org.apache.derby.iapi.store.access.TransactionController;
+import org.apache.derby.shared.common.reference.SQLState;
+import org.apache.derby.iapi.services.sanity.SanityManager;
+
+/**
+ *  This class performs actions that are ALWAYS performed for a
+ *  GRANT role statement at execution time.
+ *
+ *  Descriptors corresponding to the grants are stored in the
+ *  SYS.SYSROLES table, along with the role definitions, cf
+ *  CreateRoleConstantAction.
+ *
+ */
+class GrantRoleConstantAction extends DDLConstantAction {
+
+    private List roleNames;
+    private List grantees;
+    private final boolean withAdminOption = false; // not impl.
+
+    // CONSTRUCTORS
+    /**
+     *  Make the ConstantAction for a CREATE ROLE statement.
+     *  When executed, will create a role by the given name.
+     *
+     *  @param roleNames     List of the names of the roles being granted
+     *  @param grantees       List of the authorization ids granted to role
+     */
+    public GrantRoleConstantAction(List roleNames, List grantees) {
+        this.roleNames = roleNames;
+        this.grantees = grantees;
+    }
+
+    // INTERFACE METHODS
+
+    /**
+     *  This is the guts of the Execution-time logic for GRANT role.
+     *
+     *  @see ConstantAction#executeConstantAction
+     *
+     * @exception StandardException     Thrown on failure
+     */
+    public void executeConstantAction(Activation activation)
+            throws StandardException {
+
+        LanguageConnectionContext lcc =
+            activation.getLanguageConnectionContext();
+        DataDictionary dd = lcc.getDataDictionary();
+        TransactionController tc = lcc.getTransactionExecute();
+        DataDescriptorGenerator ddg = dd.getDataDescriptorGenerator();
+
+        final String grantor = lcc.getAuthorizationId();
+
+        dd.startWriting(lcc);
+
+        for (Iterator rIter = roleNames.iterator(); rIter.hasNext();) {
+            String role = (String)rIter.next();
+
+            for (Iterator gIter = grantees.iterator(); gIter.hasNext();) {
+                String grantee = (String)gIter.next();
+
+                // check that role exists
+                RoleDescriptor rd = dd.getRoleDefinitionDescriptor(role);
+
+                if (rd == null) {
+                    throw StandardException.
+                        newException(SQLState.ROLE_INVALID_SPECIFICATION, role);
+                }
+
+                // Check that role is granted to us (or PUBLIC) with
+                // WITH ADMIN option so we can grant it. For database
+                // owner, a role definition always fulfills this
+                // requirement.  If we implement granting with WITH ADMIN
+                // option later, we need to look for a grant to us (or
+                // PUBLIC) which has WITH ADMIN. The role definition
+                // descriptor will not suffice in that case, so we
+                // need something like:
+                //
+                // rd = dd.findRoleGrantWithAdminToRoleOrPublic(grantor)
+                // if (rd != null) {
+                //   :
+                if (grantor.equals(rd.getGrantee())) {
+                    // All ok, we are database owner
+                    if (SanityManager.DEBUG) {
+                        SanityManager.ASSERT(
+                            lcc.getDataDictionary().
+                            getAuthorizationDatabaseOwner().
+                            equals(grantor),
+                            "expected database owner in role descriptor");
+                        SanityManager.ASSERT(
+                            rd.isWithAdminOption(),
+                            "expected role definition to have ADMIN OPTION");
+                    }
+                } else {
+                    throw StandardException.newException
+                        (SQLState.AUTH_ROLE_DBO_ONLY, "GRANT role");
+                }
+
+                rd = dd.getRoleGrantDescriptor(role, grantee, grantor);
+
+                if (rd != null && withAdminOption && !rd.isWithAdminOption()) {
+                    // NOTE: Never called yet, withAdminOption not yet
+                    // implemented.
+
+                    // Remove old descriptor and add a new one with admin
+                    // option: cf. SQL 2003, section 12.5, general rule 3
+                    rd.drop(lcc);
+                    rd.setWithAdminOption(true);
+                    dd.addDescriptor(rd,
+                                     null,  // parent
+                                     DataDictionary.SYSROLES_CATALOG_NUM,
+                                     false, // no duplicatesAllowed
+                                     tc);
+                } else if (rd == null) {
+                    RoleDescriptor gd = dd.getRoleDefinitionDescriptor(grantee);
+
+                    if (gd != null) {
+                        // FIXME: Grantee is role, need to check for circularity
+                    }
+
+                    rd = ddg.newRoleDescriptor(role,
+                                               grantee,
+                                               grantor, // dbo for now
+                                               withAdminOption,
+                                               false);  // not definition
+                    dd.addDescriptor(rd,
+                                     null,  // parent
+                                     DataDictionary.SYSROLES_CATALOG_NUM,
+                                     false, // no duplicatesAllowed
+                                     tc);
+                } // else exists already, no need to add
+            }
+        }
+    }
+
+
+    // OBJECT SHADOWS
+
+    public  String  toString()
+    {
+        // Do not put this under SanityManager.DEBUG - it is needed for
+        // error reporting.
+
+        StringBuffer sb1 = new StringBuffer();
+        for (Iterator it = roleNames.iterator(); it.hasNext();) {
+            if( sb1.length() > 0) {
+                sb1.append( ", ");
+            }
+            sb1.append( it.next().toString());
+        }
+
+        StringBuffer sb2 = new StringBuffer();
+        for (Iterator it = grantees.iterator(); it.hasNext();) {
+            if( sb2.length() > 0) {
+                sb2.append( ", ");
+            }
+            sb2.append( it.next().toString());
+        }
+        return ("GRANT " +
+                sb1.toString() +
+                " TO: " +
+                sb2.toString() +
+                "\n");
+    }
+}

Propchange: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GrantRoleConstantAction.java
------------------------------------------------------------------------------
    svn:eol-style = native

Added: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/RevokeRoleConstantAction.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/RevokeRoleConstantAction.java?rev=594158&view=auto
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/RevokeRoleConstantAction.java (added)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/RevokeRoleConstantAction.java Mon Nov 12 06:24:25 2007
@@ -0,0 +1,192 @@
+/*
+
+   Derby - Class org.apache.derby.impl.sql.execute.RevokeRoleConstantAction
+
+   Licensed to the Apache Software Foundation (ASF) under one or more
+   contributor license agreements.  See the NOTICE file distributed with
+   this work for additional information regarding copyright ownership.
+   The ASF licenses this file to you under the Apache License, Version 2.0
+   (the "License"); you may not use this file except in compliance with
+   the License.  You may obtain a copy of the License at
+
+      http://www.apache.org/licenses/LICENSE-2.0
+
+   Unless required by applicable law or agreed to in writing, software
+   distributed under the License is distributed on an "AS IS" BASIS,
+   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+   See the License for the specific language governing permissions and
+   limitations under the License.
+
+ */
+
+package org.apache.derby.impl.sql.execute;
+
+import org.apache.derby.iapi.sql.execute.ConstantAction;
+
+import java.util.Iterator;
+import java.util.List;
+import org.apache.derby.iapi.error.StandardException;
+import org.apache.derby.iapi.sql.Activation;
+import org.apache.derby.iapi.sql.conn.LanguageConnectionContext;
+import org.apache.derby.iapi.sql.dictionary.RoleDescriptor;
+import org.apache.derby.iapi.sql.dictionary.DataDictionary;
+import org.apache.derby.iapi.store.access.TransactionController;
+import org.apache.derby.shared.common.reference.SQLState;
+import org.apache.derby.iapi.services.sanity.SanityManager;
+
+
+/**
+ *  This class performs actions that are ALWAYS performed for a
+ *  REVOKE role statement at execution time.
+ *
+ */
+class RevokeRoleConstantAction extends DDLConstantAction {
+
+    private List roleNames;
+    private List grantees;
+    private final boolean withAdminOption = false; // not impl.
+
+    // CONSTRUCTORS
+    /**
+     *  Make the ConstantAction for a CREATE ROLE statement.
+     *  When executed, will create a role by the given name.
+     *
+     *  @param roleNames     List of the name of the role names being revoked
+     *  @param grantees       List of the authorization ids granted to role
+     */
+    public RevokeRoleConstantAction(List roleNames, List grantees) {
+        this.roleNames = roleNames;
+        this.grantees = grantees;
+    }
+
+    // INTERFACE METHODS
+
+    /**
+     *  This is the guts of the Execution-time logic for REVOKE role.
+     *
+     *  @see ConstantAction#executeConstantAction
+     *
+     * @exception StandardException     Thrown on failure
+     */
+    public void executeConstantAction(Activation activation)
+            throws StandardException {
+
+        LanguageConnectionContext lcc =
+            activation.getLanguageConnectionContext();
+        DataDictionary dd = lcc.getDataDictionary();
+        TransactionController tc = lcc.getTransactionExecute();
+
+        final String grantor = lcc.getAuthorizationId();
+
+        dd.startWriting(lcc);
+
+        for (Iterator rIter = roleNames.iterator(); rIter.hasNext();) {
+            String role = (String)rIter.next();
+
+            for (Iterator gIter = grantees.iterator(); gIter.hasNext();) {
+                String grantee = (String)gIter.next();
+
+                // check that role exists
+                RoleDescriptor rd = dd.getRoleDefinitionDescriptor(role);
+
+                if (rd == null) {
+                    throw StandardException.
+                        newException(SQLState.ROLE_INVALID_SPECIFICATION, role);
+                }
+
+                // Check that role is granted to us (or PUBLIC) with
+                // WITH ADMIN option so we can grant (and hence
+                // revoke) it. For database owner, a role definition
+                // always fulfills this requirement.  If we implement
+                // granting with WITH ADMIN option later, we need to
+                // look for a grant to us or to PUBLIC which has WITH
+                // ADMIN. The role definition descriptor will not
+                // suffice in that case, so we need something like:
+                //
+                // rd = dd.findRoleGrantWithAdminToRoleOrPublic(grantor)
+                // if (rd != null) {
+                //   :
+                if (grantor.equals(rd.getGrantee())) {
+                    // All ok, we are database owner
+                    if (SanityManager.DEBUG) {
+                        SanityManager.ASSERT(
+                            lcc.getDataDictionary().
+                            getAuthorizationDatabaseOwner().
+                            equals(grantor),
+                            "expected database owner in role descriptor");
+                        SanityManager.ASSERT(
+                            rd.isWithAdminOption(),
+                            "expected role definition to have ADMIN OPTION");
+                    }
+                } else {
+                    throw StandardException.newException
+                        (SQLState.AUTH_ROLE_DBO_ONLY, "REVOKE role");
+                }
+
+                rd = dd.getRoleGrantDescriptor(role, grantee, grantor);
+
+                if (rd != null && withAdminOption) {
+                    // NOTE: Never called yet, withAdminOption not yet
+                    // implemented.
+
+                    // revoke only the ADMIN OPTION from grantee
+                    //
+                    if (rd.isWithAdminOption()) {
+                        // Remove old descriptor and add a new one
+                        // without admin option.
+                        rd.drop(lcc);
+                        rd.setWithAdminOption(false);
+                        dd.addDescriptor(rd,
+                                         null,  // parent
+                                         DataDictionary.SYSROLES_CATALOG_NUM,
+                                         false, // no duplicatesAllowed
+                                         tc);
+                    } else {
+                        activation.addWarning
+                            (StandardException.newWarning
+                             (SQLState.LANG_WITH_ADMIN_OPTION_NOT_REVOKED,
+                              role, grantee));
+                    }
+                } else if (rd != null) {
+                    // normal revoke of role from grantee
+                    //
+                    rd.drop(lcc);
+                } else {
+                    activation.addWarning
+                        (StandardException.newWarning
+                         (SQLState.LANG_ROLE_NOT_REVOKED, role, grantee));
+                }
+            }
+        }
+    }
+
+
+    // OBJECT SHADOWS
+
+    public String toString()
+    {
+        // Do not put this under SanityManager.DEBUG - it is needed for
+        // error reporting.
+
+        StringBuffer sb1 = new StringBuffer();
+        for (Iterator it = roleNames.iterator(); it.hasNext();) {
+            if( sb1.length() > 0) {
+                sb1.append( ", ");
+            }
+            sb1.append( it.next().toString());
+        }
+
+        StringBuffer sb2 = new StringBuffer();
+        for (Iterator it = grantees.iterator(); it.hasNext();) {
+            if( sb2.length() > 0) {
+                sb2.append( ", ");
+            }
+            sb2.append( it.next().toString());
+        }
+        return ("REVOKE " +
+                sb1.toString() +
+                " FROM: " +
+                sb2.toString() +
+                "\n");
+    }
+}

Propchange: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/RevokeRoleConstantAction.java
------------------------------------------------------------------------------
    svn:eol-style = native

Added: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/SetRoleConstantAction.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/SetRoleConstantAction.java?rev=594158&view=auto
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/SetRoleConstantAction.java (added)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/SetRoleConstantAction.java Mon Nov 12 06:24:25 2007
@@ -0,0 +1,157 @@
+/*
+
+   Derby - Class org.apache.derby.impl.sql.execute.SetRoleConstantAction
+
+   Licensed to the Apache Software Foundation (ASF) under one or more
+   contributor license agreements.  See the NOTICE file distributed with
+   this work for additional information regarding copyright ownership.
+   The ASF licenses this file to you under the Apache License, Version 2.0
+   (the "License"); you may not use this file except in compliance with
+   the License.  You may obtain a copy of the License at
+
+      http://www.apache.org/licenses/LICENSE-2.0
+
+   Unless required by applicable law or agreed to in writing, software
+   distributed under the License is distributed on an "AS IS" BASIS,
+   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+   See the License for the specific language governing permissions and
+   limitations under the License.
+
+ */
+
+package org.apache.derby.impl.sql.execute;
+
+
+import org.apache.derby.iapi.sql.execute.ConstantAction;
+import org.apache.derby.iapi.sql.dictionary.DataDictionary;
+import org.apache.derby.iapi.sql.dictionary.RoleDescriptor;
+import org.apache.derby.iapi.sql.conn.LanguageConnectionContext;
+import org.apache.derby.iapi.sql.ParameterValueSet;
+import org.apache.derby.iapi.sql.StatementType;
+import org.apache.derby.iapi.sql.conn.Authorizer;
+import org.apache.derby.iapi.types.DataValueDescriptor;
+import org.apache.derby.iapi.reference.Limits;
+import org.apache.derby.iapi.error.StandardException;
+import org.apache.derby.iapi.sql.Activation;
+import org.apache.derby.iapi.reference.SQLState;
+import org.apache.derby.iapi.store.access.TransactionController;
+
+/**
+ *  This class describes actions that are ALWAYS performed for a
+ *  SET ROLE Statement at Execution time.
+ *
+ */
+
+class SetRoleConstantAction implements ConstantAction
+{
+
+    private final String  roleName;
+    private final int     type;
+
+    // CONSTRUCTORS
+
+    /**
+     * Make the ConstantAction for a SET ROLE statement.
+     *
+     *  @param roleName Name of role.
+     *  @param type     type of set role (literal role name or ?)
+     */
+    SetRoleConstantAction(String roleName, int type)
+    {
+        this.roleName = roleName;
+        this.type = type;
+    }
+
+    ///////////////////////////////////////////////
+    //
+    // OBJECT SHADOWS
+    //
+    ///////////////////////////////////////////////
+
+    public String toString()
+    {
+        // Do not put this under SanityManager.DEBUG - it is needed for
+        // error reporting.
+        // If the error happens after we have figured out the role name for
+        // dynamic we want to use it rather than ?
+        return "SET ROLE " +
+            ((type == StatementType.SET_ROLE_DYNAMIC && roleName == null) ?
+             "?" : roleName);
+    }
+
+    // INTERFACE METHODS
+
+    /**
+     *  This is the guts of the Execution-time logic for SET ROLE.
+     *
+     *  @see ConstantAction#executeConstantAction
+     *
+     * @exception StandardException     Thrown on failure
+     */
+    public void executeConstantAction( Activation activation )
+                        throws StandardException {
+
+        LanguageConnectionContext   lcc;
+        DataDictionary              dd;
+
+        // find the language context.
+        lcc = activation.getLanguageConnectionContext();
+
+        dd = lcc.getDataDictionary();
+        String thisRoleName = roleName;
+
+        final String currentAuthId = lcc.getAuthorizationId();
+        final String dbo = lcc.getDataDictionary().
+            getAuthorizationDatabaseOwner();
+
+        TransactionController tc = lcc.getTransactionExecute();
+
+        // SQL 2003, section 18.3, General rule 1:
+        if (!tc.isIdle()) {
+            throw StandardException.newException
+                (SQLState.INVALID_TRANSACTION_STATE_ACTIVE_CONNECTION);
+        }
+
+        if (type == StatementType.SET_ROLE_DYNAMIC) {
+            ParameterValueSet pvs = activation.getParameterValueSet();
+            DataValueDescriptor dvs = pvs.getParameter(0);
+            thisRoleName = dvs.getString();
+        }
+
+        RoleDescriptor rd = null;
+
+        if (thisRoleName != null) {
+            try {
+                rd = dd.getRoleDefinitionDescriptor(thisRoleName);
+
+                // SQL 2003, section 18.3, General rule 4:
+                if (rd == null) {
+                    throw StandardException.newException
+                        (SQLState.ROLE_INVALID_SPECIFICATION, thisRoleName);
+                }
+
+                if (!currentAuthId.equals(dbo)) {
+                    // is it granted to us mere mortals?
+                    rd = dd.getRoleGrantDescriptor(thisRoleName,
+                                                   currentAuthId,
+                                                   dbo);
+                    if (rd == null &&
+                        (dd.getRoleGrantDescriptor
+                         (thisRoleName,
+                          Authorizer.PUBLIC_AUTHORIZATION_ID,
+                          dbo) == null)) {
+
+                        throw StandardException.newException
+                            (SQLState.ROLE_INVALID_SPECIFICATION_NOT_GRANTED,
+                             thisRoleName);
+                    }
+                }
+            } finally {
+                // reading above changes idle state, so reestablish it
+                lcc.userCommit();
+            }
+        }
+
+        lcc.setCurrentRole(rd);
+    }
+}

Propchange: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/SetRoleConstantAction.java
------------------------------------------------------------------------------
    svn:eol-style = native

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/SetSchemaConstantAction.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/SetSchemaConstantAction.java?rev=594158&r1=594157&r2=594158&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/SetSchemaConstantAction.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/SetSchemaConstantAction.java Mon Nov 12 06:24:25 2007
@@ -88,7 +88,7 @@
 
 
 	/**
-	 *	This is the guts of the Execution-time logic for CREATE SCHEMA.
+	 *	This is the guts of the Execution-time logic for SET SCHEMA.
 	 *
 	 *	@see ConstantAction#executeConstantAction
 	 *

Modified: db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml?rev=594158&r1=594157&r2=594158&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml Mon Nov 12 06:24:25 2007
@@ -79,6 +79,20 @@
             </msg>
 
             <msg>
+                <name>01007</name>
+                <text>Role {0} not revoked from authentication id {1}.</text>
+                <arg>authorizationID</arg>
+                <arg>authorizationID</arg>
+            </msg>
+
+            <msg>
+                <name>01008</name>
+                <text>WITH ADMIN OPTION of role {0} not revoked from authentication id {1}.</text>
+                <arg>authorizationID</arg>
+                <arg>authorizationID</arg>
+            </msg>
+
+            <msg>
                 <name>0100E</name>
                 <text>XX Attempt to return too many result sets. </text>
             </msg>
@@ -468,6 +482,24 @@
 
 
         <family>
+            <title>Class 0P: Invalid role specification</title>
+
+            <msg>
+                <name>0P000</name>
+                <text>Invalid role specification, role does not exist: '{0}'.</text>
+                <arg>roleName</arg>
+            </msg>
+
+            <msg>
+                <name>0P000.S.1</name>
+                <text>Invalid role specification, role not granted to current user or PUBLIC: '{0}'.</text>
+                <arg>roleName</arg>
+            </msg>
+
+        </family>
+
+
+        <family>
             <title>Class 21: Cardinality Violation</title>
 
             <msg>
@@ -745,6 +777,11 @@
             </msg>
 
             <msg>
+                <name>25001.S.1</name>
+                <text>Invalid transaction state: active SQL transaction.</text>
+            </msg>
+
+            <msg>
                 <name>25501</name>
                 <text>Unable to set the connection read-only property in an active transaction.</text>
             </msg>
@@ -1068,6 +1105,12 @@
                 <name>4250E</name>
                 <text>Internal Error: invalid {0} id in statement permission list.</text>
                 <arg>authorizationID</arg>
+            </msg>
+
+            <msg>
+                <name>4251A</name>
+                <text>Statement {0} can only be issued by database owner.</text>
+                <arg>value</arg>
             </msg>
 
             <msg>

Modified: db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java?rev=594158&r1=594157&r2=594158&view=diff
==============================================================================
--- db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java (original)
+++ db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java Mon Nov 12 06:24:25 2007
@@ -653,11 +653,17 @@
 	String LANG_SYNONYM_UNDEFINED                                      = "01522";
 	String LANG_NULL_ELIMINATED_IN_SET_FUNCTION						   = "01003";
 	String LANG_PRIVILEGE_NOT_REVOKED						   		   = "01006";
-	
+	String LANG_ROLE_NOT_REVOKED                                       = "01007";
+	String LANG_WITH_ADMIN_OPTION_NOT_REVOKED                          = "01008";
+
 	String LANG_NO_ROW_FOUND									   	   = "02000";
 
 	String LANG_TOO_MANY_DYNAMIC_RESULTS_RETURNED					   = "0100E";
-    
+
+
+	// Invalid role specification: standard says class 0P, no subclass.
+	String ROLE_INVALID_SPECIFICATION                                  = "0P000";
+	String ROLE_INVALID_SPECIFICATION_NOT_GRANTED                      = "0P000.S.1";
 
 	// TRANSACTION severity language errors. These are in the range:
 	// 40XC0 - 40XCZ
@@ -724,6 +730,7 @@
 	String LANG_XQUERY_SERIALIZATION_ERROR                             = "2200W";
 
     String CANNOT_CLOSE_ACTIVE_CONNECTION                              = "25001";
+    String INVALID_TRANSACTION_STATE_ACTIVE_CONNECTION                 = "25001.S.1";
 
 
 	String LANG_UNEXPECTED_USER_EXCEPTION                              = "38000";
@@ -750,6 +757,7 @@
 	String AUTH_USER_IN_READ_AND_WRITE_LISTS                           = "4250C";
 	String AUTH_DUPLICATE_USERS                                        = "4250D";
 	String AUTH_INTERNAL_BAD_UUID                                      = "4250E";
+    String AUTH_ROLE_DBO_ONLY                                          = "4251A";
 
 	String LANG_DB2_NOT_NULL_COLUMN_INVALID_DEFAULT                    = "42601";
 	String LANG_DB2_INVALID_HEXADECIMAL_CONSTANT                    = "42606";

Modified: db/derby/code/trunk/java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.java?rev=594158&r1=594157&r2=594158&view=diff
==============================================================================
--- db/derby/code/trunk/java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.java (original)
+++ db/derby/code/trunk/java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.java Mon Nov 12 06:24:25 2007
@@ -48,6 +48,7 @@
 import org.apache.derby.iapi.sql.dictionary.RoutinePermsDescriptor;
 import org.apache.derby.iapi.sql.dictionary.SPSDescriptor;
 import org.apache.derby.iapi.sql.dictionary.SchemaDescriptor;
+import org.apache.derby.iapi.sql.dictionary.RoleDescriptor;
 import org.apache.derby.iapi.sql.dictionary.SubKeyConstraintDescriptor;
 import org.apache.derby.iapi.sql.dictionary.TableDescriptor;
 import org.apache.derby.iapi.sql.dictionary.TablePermsDescriptor;
@@ -157,6 +158,32 @@
 		return false;
 	}
 
+	public void	dropRoleDescriptor(String roleName,
+                                   String grantee,
+                                   String grantor,
+								   TransactionController tc)
+			throws StandardException {
+		// TODO Auto-generated method stub
+	}
+
+	public void	dropRoleGrantsByGrantee(String grantee,
+										TransactionController tc)
+			throws StandardException {
+		// TODO Auto-generated method stub
+	}
+
+	public void	dropRoleGrantsByName(String roleName,
+									 TransactionController tc)
+			throws StandardException {
+		// TODO Auto-generated method stub
+	}
+
+	public void	dropAllPermsByGrantee(String authid,
+									  TransactionController tc)
+			throws StandardException {
+		// TODO Auto-generated method stub
+	}
+
 	public void dropSchemaDescriptor(String schemaName, TransactionController tc)
 			throws StandardException {
 		// TODO Auto-generated method stub
@@ -166,6 +193,20 @@
 	public boolean isSchemaEmpty(SchemaDescriptor sd) throws StandardException {
 		// TODO Auto-generated method stub
 		return false;
+	}
+
+	public RoleDescriptor getRoleDefinitionDescriptor(String roleName)
+			throws StandardException {
+		// TODO Auto-generated method stub
+		return null;
+	}
+
+	public RoleDescriptor getRoleGrantDescriptor(String roleName,
+												 String grantee,
+												 String grantor)
+		throws StandardException {
+		// TODO Auto-generated method stub
+		return null;
 	}
 
 	public TableDescriptor getTableDescriptor(String tableName,

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/altertable.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/altertable.out?rev=594158&r1=594157&r2=594158&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/altertable.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/altertable.out Mon Nov 12 06:24:25 2007
@@ -672,6 +672,7 @@
 SYSTABLEPERMS                                                                                                                   |1          
 SYSCOLPERMS                                                                                                                     |1          
 SYSROUTINEPERMS                                                                                                                 |1          
+SYSROLES                                                                                                                        |1          
 ij> -- verify the consistency of the indexes on the user tables
 select tablename, SYSCS_UTIL.SYSCS_CHECK_TABLE('APP', tablename)
 from sys.systables where tabletype = 'T';

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/compressTable.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/compressTable.out?rev=594158&r1=594157&r2=594158&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/compressTable.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/compressTable.out Mon Nov 12 06:24:25 2007
@@ -1084,6 +1084,7 @@
 SYS                                                                                                                             |SYSFILES                                                                                                                        |1          
 SYS                                                                                                                             |SYSFOREIGNKEYS                                                                                                                  |1          
 SYS                                                                                                                             |SYSKEYS                                                                                                                         |1          
+SYS                                                                                                                             |SYSROLES                                                                                                                        |1          
 SYS                                                                                                                             |SYSROUTINEPERMS                                                                                                                 |1          
 SYS                                                                                                                             |SYSSCHEMAS                                                                                                                      |1          
 SYS                                                                                                                             |SYSSTATEMENTS                                                                                                                   |1          

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/ij7.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/ij7.out?rev=594158&r1=594157&r2=594158&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/ij7.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/ij7.out Mon Nov 12 06:24:25 2007
@@ -34,6 +34,7 @@
 SYS                 |SYSFILES                      |                    
 SYS                 |SYSFOREIGNKEYS                |                    
 SYS                 |SYSKEYS                       |                    
+SYS                 |SYSROLES                      |                    
 SYS                 |SYSROUTINEPERMS               |                    
 SYS                 |SYSSCHEMAS                    |                    
 SYS                 |SYSSTATEMENTS                 |                    
@@ -77,6 +78,7 @@
 SYS                 |SYSFILES                      |                    
 SYS                 |SYSFOREIGNKEYS                |                    
 SYS                 |SYSKEYS                       |                    
+SYS                 |SYSROLES                      |                    
 SYS                 |SYSROUTINEPERMS               |                    
 SYS                 |SYSSCHEMAS                    |                    
 SYS                 |SYSSTATEMENTS                 |                    

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/views.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/views.out?rev=594158&r1=594157&r2=594158&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/views.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/views.out Mon Nov 12 06:24:25 2007
@@ -273,6 +273,7 @@
 SYSTABLEPERMS                                                                                                                   |1          
 SYSCOLPERMS                                                                                                                     |1          
 SYSROUTINEPERMS                                                                                                                 |1          
+SYSROLES                                                                                                                        |1          
 ij> -- test inserts from a view
 insert into insert_test select * from sv5;
 3 rows inserted/updated/deleted
@@ -339,6 +340,7 @@
 SYSTABLEPERMS                                                                                                                   |1          
 SYSCOLPERMS                                                                                                                     |1          
 SYSROUTINEPERMS                                                                                                                 |1          
+SYSROLES                                                                                                                        |1          
 ij> -- bug 2745
 CREATE TABLE orgtable (
 	name VARCHAR(255),

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/DMDBugsTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/DMDBugsTest.java?rev=594158&r1=594157&r2=594158&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/DMDBugsTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/DMDBugsTest.java Mon Nov 12 06:24:25 2007
@@ -78,7 +78,7 @@
 	                  {{"","APP","TSYN","SYNONYM","",null,null,null,null,null}});
 
 		rs = dmd.getTables( "%", "%", "%", new String[] {"SYSTEM TABLE"});
-		assertEquals(19, JDBC.assertDrainResults(rs));
+		assertEquals(20, JDBC.assertDrainResults(rs));
 		s.executeUpdate("DROP VIEW APP.V");
 		s.executeUpdate("DROP TABLE APP.TAB");
 		s.executeUpdate("DROP SYNONYM APP.TSYN");

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CollationTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CollationTest.java?rev=594158&r1=594157&r2=594158&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CollationTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CollationTest.java Mon Nov 12 06:24:25 2007
@@ -579,7 +579,7 @@
     //The query below will work for the same reason. 
     checkLangBasedQuery(s, "SELECT count(*) FROM SYS.SYSTABLES WHERE CASE " +
     		" WHEN 1=1 THEN TABLENAME ELSE TABLEID END = TABLENAME",
-    		new String[][] {{"22"} });   
+    		new String[][] {{"23"} });
 
     //Do some testing using CONCATENATION
     //following will fail because result string of concatenation has 

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeDDLTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeDDLTest.java?rev=594158&r1=594157&r2=594158&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeDDLTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeDDLTest.java Mon Nov 12 06:24:25 2007
@@ -7695,6 +7695,7 @@
             {"SYSTABLEPERMS"},
             {"SYSCOLPERMS"},
             {"SYSROUTINEPERMS"},
+            {"SYSROLES"},
             {"T1"},
             {"T2"},
             {"T2"},

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/PrimaryKeyTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/PrimaryKeyTest.java?rev=594158&r1=594157&r2=594158&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/PrimaryKeyTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/PrimaryKeyTest.java Mon Nov 12 06:24:25 2007
@@ -231,7 +231,8 @@
                         {"SYSSTATISTICS","1"},
                         {"SYSTABLEPERMS","1"},
                         {"SYSCOLPERMS","1"},
-                        {"SYSROUTINEPERMS","1"}};
+                        {"SYSROUTINEPERMS","1"},
+						{"SYSROLES", "1"}};
                 JDBC.assertFullResultSet(rs,expectedCheckTables); 
 		//-- drop tables
 		assertUpdateCount(s , 0 , "drop table pos1");

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/RolesTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/RolesTest.java?rev=594158&r1=594157&r2=594158&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/RolesTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/RolesTest.java Mon Nov 12 06:24:25 2007
@@ -1,6 +1,6 @@
 /*
 
-   Derby - Class org.apache.derbyTesting.functionTests.tests.jdbcapi.RolesTest
+   Derby - Class org.apache.derbyTesting.functionTests.tests.lang.RolesTest
 
    Licensed to the Apache Software Foundation (ASF) under one or more
    contributor license agreements.  See the NOTICE file distributed with
@@ -22,16 +22,15 @@
 package org.apache.derbyTesting.functionTests.tests.lang;
 
 import java.sql.SQLException;
+import java.sql.SQLWarning;
 import java.sql.Connection;
 import java.sql.Statement;
 import java.sql.PreparedStatement;
-import javax.sql.DataSource;
+import java.sql.ResultSet;
 import junit.framework.Test;
 import junit.framework.TestSuite;
 import org.apache.derbyTesting.junit.BaseJDBCTestCase;
 import org.apache.derbyTesting.junit.DatabasePropertyTestSetup;
-import org.apache.derbyTesting.junit.JDBC;
-import org.apache.derbyTesting.junit.JDBCDataSource;
 import org.apache.derbyTesting.junit.TestConfiguration;
 
 /**
@@ -63,8 +62,11 @@
     /* SQL states */
     private final static String sqlAuthorizationRequired = "42Z60";
     private final static String syntaxError = "42X01";
-    // temporary until feature fully implemented:
-    private final static String notImplemented = "0A000";
+    private final static String roleDboOnly = "4251A";
+    private final static String invalidRole = "0P000";
+    private final static String tooLongId   = "42622";
+    private final static String revokeWarn  = "01007";
+    private final static String notIdle     = "25001";
 
     /**
      * Users used by all suites when when authLevel == SQLAUTHORIZATION.
@@ -73,8 +75,11 @@
      * 'dbsqlauth', not 'wombat'.
      */
     private final static String[] users = {"TEST_DBO", "DonaldDuck"};
+    private final static int dboIndex        = 0;
+    private final static int nonDboIndex = 1;
 
-    private boolean isDbo() {
+    private boolean isDbo()
+    {
         return users[0].equals(this._user);
     }
 
@@ -112,17 +117,24 @@
         /* Negative syntax tests */
         suite.addTest(negativeSyntaxSuite("suite: negative syntax, embedded"));
 
-        // suite.addTest(
-        //     TestConfiguration.clientServerDecorator(
-        //         negativeSyntaxSuite("suite: negative syntax, client")));
+        suite.addTest(
+            TestConfiguration.clientServerDecorator(
+                negativeSyntaxSuite("suite: negative syntax, client")));
+
+        /* Positive syntax tests */
+        suite.addTest(positiveSyntaxSuite("suite: positive syntax, embedded"));
+
+        suite.addTest(
+            TestConfiguration.clientServerDecorator(
+                positiveSyntaxSuite("suite: positive syntax, client")));
 
         /* Positive tests */
         suite.addTest(
             positiveSuite("suite: positive, embedded"));
 
-        // suite.addTest(
-        //     TestConfiguration.clientServerDecorator(
-        //         positiveSuite("suite: positive, client")));
+        suite.addTest(
+            TestConfiguration.clientServerDecorator(
+                positiveSuite("suite: positive, client")));
 
         return suite;
     }
@@ -140,7 +152,7 @@
      */
     private static Test negativeSyntaxSuite(String framework)
     {
-        Test tests[] = new Test[SQLAUTHORIZATION+1]; // one per authLevel
+        TestSuite suite = new TestSuite("roles:"+framework);
 
         /* Tests running without sql authorization set.
          */
@@ -150,29 +162,39 @@
                                           NO_SQLAUTHORIZATION,
                                           null,
                                           null));
-        tests[NO_SQLAUTHORIZATION] = noauthSuite;
-
-        /* Tests running with sql authorization set.
-         * First decorate with users, then with authentication +
-         * sqlAuthorization.
-         */
-        tests[SQLAUTHORIZATION] = wrapTest("testNegativeSyntax");
-
+        suite.addTest(noauthSuite);
 
-        TestSuite suite = new TestSuite("roles:"+framework);
-        suite.addTest(tests[NO_SQLAUTHORIZATION]);
-        suite.addTest(tests[SQLAUTHORIZATION]);
+        // Tests running with sql authorization set.
+        suite.addTest(wrapInAuthorization("testNegativeSyntax"));
 
         return suite;
     }
 
 
-
     /**
-     * Wraps the negative syntax fixture in decorators to run with
-     * data base owner and one other valid user in sqlAuthorization
-     * mode.
+     *
+     * Construct suite of tests for positive syntax (edge cases)
+     *
+     * @param framework Derby framework indication
+     * @return A suite containing the test cases for  syntax
+     * edge cases. Incarnated only for sqlAuthorization, dbo.
      */
+    private static Test positiveSyntaxSuite(String framework)
+    {
+        String dbo = users[dboIndex];
+        String dbopw = dbo.concat(pwSuffix);
+
+        Test t = (TestConfiguration.changeUserDecorator
+                  (new RolesTest("testPositiveSyntax",
+                                 SQLAUTHORIZATION,
+                                 dbo, dbopw),
+                   dbo, dbopw));
+
+        return TestConfiguration.sqlAuthorizationDecorator(
+            DatabasePropertyTestSetup.builtinAuthentication(
+                t, users, pwSuffix));
+    }
+
 
     /**
      * Test negative syntax for roles.
@@ -191,6 +213,54 @@
                syntaxError, syntaxError, syntaxError);
         doStmt("create role current_role", // current_role is reserved word
                syntaxError, syntaxError, syntaxError);
+        String nameWithMoreThan30Chars = ("r123456789" +
+                                          "0123456789" +
+                                          "01234567890"); // 31 long
+        doStmt("create role " + nameWithMoreThan30Chars,
+               sqlAuthorizationRequired, tooLongId, tooLongId);
+
+        _stm.close();
+    }
+
+
+    /**
+     * Test syntax edge cases (positive)
+     *
+     * @throws SQLException
+     */
+    public void testPositiveSyntax() throws SQLException
+    {
+        println("testPositiveSyntax");
+        String n_a     = null; // auth level not used for this test
+        int    n_a_cnt = -1;   //
+
+        // sanity check:
+        if (!isDbo()) { throw new SQLException("test error"); }
+
+
+        _conn = getConnection();
+        _stm  = _conn.createStatement();
+
+        // "trigger" is not reserved word, but required tweaking of grammar
+        // so we add a regression test for it.
+        doStmt("create role trigger", n_a, null, n_a);
+
+        // "role" is not a reserved word, either:
+        doStmt("create role role", n_a, null, n_a);
+
+        assertSysRolesRowCount(n_a_cnt, 2, n_a_cnt);
+
+        doStmt("grant trigger to foo", n_a, null, n_a);
+        doStmt("grant role to foo", n_a, null, n_a);
+        doStmt("revoke trigger from foo", n_a, null, n_a);
+        doStmt("revoke role from foo", n_a, null, n_a);
+
+        doStmt("drop role trigger", n_a, null, n_a);
+        doStmt("drop role role", n_a, null, n_a);
+
+        assertSysRolesRowCount(n_a_cnt, 0, n_a_cnt);
+
+        _stm.close();
     }
 
     /**
@@ -207,8 +277,9 @@
      */
     private static Test positiveSuite(String framework)
     {
-        Test tests[] = new Test[SQLAUTHORIZATION+1]; // one per authLevel
-        /* Tests running without sql authorization set.
+        /*
+         * Tests running without sql authorization set.  The purpose
+         * of this is just to make sure the proper errors are given.
          */
         TestSuite noauthSuite = new TestSuite(
             "suite: security level=noSqlAuthorization");
@@ -216,27 +287,26 @@
                                           NO_SQLAUTHORIZATION,
                                           null,
                                           null));
-        tests[NO_SQLAUTHORIZATION] = noauthSuite;
+
         /* Tests running with sql authorization set.
          * First decorate with users, then with authentication +
          * sqlAuthorization.
          */
         TestSuite suite = new TestSuite("roles:"+framework);
-        tests[SQLAUTHORIZATION] = wrapTest("testPositive");
 
-        suite.addTest(tests[NO_SQLAUTHORIZATION]);
-        suite.addTest(tests[SQLAUTHORIZATION]);
+        suite.addTest(noauthSuite);
+        suite.addTest(wrapInAuthorization("testPositive"));
 
         return suite;
     }
 
     /**
-     * Wraps in decorators to run with data base owner and one other
+     * Wrap in decorators to run with data base owner and one other
      * valid user in sqlAuthorization mode.
      *
      * @param testName test to wrap
      */
-    private static Test wrapTest(String testName)
+    private static Test wrapInAuthorization(String testName)
     {
         // add decorator for different users authenticated
         TestSuite usersSuite =
@@ -262,6 +332,8 @@
 
     /**
      * Positive tests for roles (well, positive for dbo at least!)
+     * Side effect from the dbo run are needed for the nonDbo run
+     * which follows (since only dbo can create and grant roles).
      *
      * @throws SQLException
      */
@@ -273,134 +345,376 @@
         _conn = getConnection();
         _stm  = _conn.createStatement();
 
-        // create
+
+        /*
+         * CREATE ROLE
+         */
         doStmt("create role foo",
-               sqlAuthorizationRequired, notImplemented, notImplemented);
+               sqlAuthorizationRequired, null , roleDboOnly);
         doStmt("create role bar",
-               sqlAuthorizationRequired, notImplemented, notImplemented);
+               sqlAuthorizationRequired, null , roleDboOnly);
         doStmt("create role role", // role is not reserved word
-               sqlAuthorizationRequired, notImplemented, notImplemented);
-        doStmt("create role trigger",
-               sqlAuthorizationRequired, notImplemented, notImplemented);
+               sqlAuthorizationRequired, null , roleDboOnly);
+        doStmt("create role admin",
+                sqlAuthorizationRequired, null , roleDboOnly);
         doStmt("create role \"NONE\"", // quoted role id should work
-               sqlAuthorizationRequired, notImplemented, notImplemented);
+                sqlAuthorizationRequired, null , roleDboOnly);
+
 
-        // grant
+        /*
+         * GRANT <role>
+         */
         doStmt("grant foo to authid", // authid: user or role
-               sqlAuthorizationRequired, notImplemented, notImplemented);
-        doStmt("grant foo, role, bar to authid1, authid2, authid3",
-               sqlAuthorizationRequired, notImplemented, notImplemented);
-
-        // grant: parser look-ahead tests to discern grant role from
-        // grant privilege
-        doStmt("grant trigger to authid",
-               sqlAuthorizationRequired, notImplemented, notImplemented);
-        doStmt("grant trigger, foo to authid",
-               sqlAuthorizationRequired, notImplemented, notImplemented);
-        doStmt("grant trigger, foo to public",
-               sqlAuthorizationRequired, notImplemented, notImplemented);
+               sqlAuthorizationRequired, null , roleDboOnly);
 
+        // this grant also grant role bar to the non-dbo user
+        // so we can set it when running as non-dbo.
+        doStmt("grant foo, role, bar to authid1, authid2, " +
+               users[nonDboIndex],
+               sqlAuthorizationRequired, null , roleDboOnly);
+
+        doStmt("grant admin to authid",
+               sqlAuthorizationRequired, null , invalidRole);
+        doStmt("grant admin, foo to authid",
+               sqlAuthorizationRequired, null , invalidRole);
+        doStmt("grant admin, foo to public",
+               sqlAuthorizationRequired, null , invalidRole);
+
+        // These grants will no be explicitly revoked, count on drop
+        // role to void them!
+        doStmt("grant admin to a,b,c",
+               sqlAuthorizationRequired, null , invalidRole);
+        doStmt("grant foo,bar to admin",
+               sqlAuthorizationRequired, null , roleDboOnly);
+
+        assertSysRolesRowCount(0, 23,
+                               // nonDbo run: foo, bar still in
+                               // place, used for testing SET ROLE for
+                               // non-dbo user. foo granted to public,
+                               // bar granted to nonDbo, so 4!
+                               4);
 
-        // set
+
+        /*
+         * SET ROLE
+         */
         doStmt("set role foo",
-               sqlAuthorizationRequired, notImplemented, notImplemented);
+               sqlAuthorizationRequired, null , null /* through public */);
         doStmt("set role 'FOO'",
-               sqlAuthorizationRequired, notImplemented, notImplemented);
+               sqlAuthorizationRequired, null, null);
         doStmt("set role none",
-               sqlAuthorizationRequired, notImplemented, notImplemented);
+               sqlAuthorizationRequired, null , null);
         doDynamicSetRole(_conn);
+        doStmt("set role bar",
+               sqlAuthorizationRequired, null , null /* direct grant */);
+        doStmt("set role role",
+               sqlAuthorizationRequired, null , invalidRole);
+
+        /* Test that we cannot set role while in non-idle state */
+        _conn.setAutoCommit(false);
+        doStmt("select * from SYS.SYSROLES", null, null, null);
+        doStmt("set role role",
+               sqlAuthorizationRequired, notIdle , notIdle);
+        _conn.commit();
+        _conn.setAutoCommit(true);
 
-        // revoke
+        /*
+         * CURRENT_ROLE
+         */
+        ResultSet rs = doQuery("values current_role",
+                               sqlAuthorizationRequired, null , null);
+        assertCurrentRole(rs, "ROLE", "BAR");
 
+        /*
+         * REVOKE role
+         */
         doStmt("revoke foo from authid", // authid: user or role
-               sqlAuthorizationRequired, notImplemented, notImplemented);
-        doStmt("revoke foo, role, bar from authid1, authid2, authid3",
-               sqlAuthorizationRequired, notImplemented, notImplemented);
-
-        // revoke: parser look-ahead tests to discern revoke role from
-        // revoke privilege
-        doStmt("revoke trigger from authid",
-               sqlAuthorizationRequired, notImplemented, notImplemented);
-        doStmt("revoke trigger, foo from authid",
-               sqlAuthorizationRequired, notImplemented, notImplemented);
-        doStmt("revoke trigger, foo from public",
-               sqlAuthorizationRequired, notImplemented, notImplemented);
-
-        // drop
-        doStmt("drop role foo",
-               sqlAuthorizationRequired, notImplemented, notImplemented);
+               sqlAuthorizationRequired, null , roleDboOnly);
+        doStmt("revoke foo, role, bar from authid1, authid2",
+               sqlAuthorizationRequired, null , roleDboOnly);
+        // revoke everything from nonDbo also, except bar
+        doStmt("revoke foo, role from " + users[nonDboIndex],
+               sqlAuthorizationRequired, null , roleDboOnly);
+
+
+        doStmt("revoke admin from authid",
+               sqlAuthorizationRequired, null , invalidRole);
+        doStmtWithWarnings("revoke admin from authid",
+                           new String[]{sqlAuthorizationRequired, null},
+                           new String[]{null, revokeWarn},
+                           new String[]{invalidRole, null},
+                           false);
+        doStmt("revoke admin, foo from authid",
+               sqlAuthorizationRequired, null , invalidRole);
+        // leave foo granted to public
+        doStmt("revoke admin from public",
+               sqlAuthorizationRequired, null , invalidRole);
+
+
+
+        /*
+         * DEFAULT CURRENT_ROLE
+         */
+        doStmt("create table foo(str varchar(128) default current_role)",
+               sqlAuthorizationRequired, null , null );
+
+        /*
+         * GRANT TABLE PERMISSION to role
+         * Should get auto-dropped when role is dropped
+         */
+        doStmt("grant select, insert on foo to admin",
+               sqlAuthorizationRequired, null , null );
+        /*
+         * GRANT COLUMN PERMISSION to role
+         * Should get auto-dropped when role is dropped
+         */
+        doStmt("grant select (str), update (str) on foo to admin",
+               sqlAuthorizationRequired, null , null );
+        /*
+         * GRANT ROUTINE PERMISSION to role
+         * Should get auto-dropped when role is dropped
+         */
+        doStmt("create function f1() returns int" +
+               "  language java parameter style java" +
+               "  external name 'org.apache.derbyTesting." +
+               "functionTests.tests.lang.RolesTest.t1'" +
+               "  no sql called on null input",
+               null, null, null);
+        doStmt("grant execute on function f1 to admin",
+               sqlAuthorizationRequired, null , null );
+
+        assertSysTablePermsRowCount(0,
+                                    // role admin not dropped yet:
+                                    1,
+                                    // role admin has been dropped, so
+                                    // this run's grant to admin is de
+                                    // facto to a user named admin:
+                                    1);
+
+        assertSysColPermsRowCount(0, 2, 2);
+
+        assertSysRoutinePermsRowCount(5, // 5 pre-existing grants to PUBLIC
+                                      6,
+                                      6);
+
+        /*
+         * DROP ROLE
+         */
+
+        // Dbo run: don't drop foo and bar, so they survive to next run,
+        // a non-dbo can set them, otherwise drop all roles and
+        // premissions.
         doStmt("drop role role",
-               sqlAuthorizationRequired, notImplemented, notImplemented);
-        doStmt("drop role trigger",
-               sqlAuthorizationRequired, notImplemented, notImplemented);
+               sqlAuthorizationRequired, null , roleDboOnly);
+
+        doStmt("drop role admin",
+               sqlAuthorizationRequired, null , roleDboOnly);
+        assertSysTablePermsRowCount(0, 0,
+                                    // nonDbo run: role admin has
+                                    // been dropped, so this run's
+                                    // grant to admin is de facto to a
+                                    // user named admin:
+                                    1);
+        assertSysColPermsRowCount(0, 0,
+                                  // nonDbo run: role admin has
+                                  // been dropped, so this run's grant
+                                  // to admin is de facto to a user
+                                  // named admin:
+                                  2);
+        assertSysRoutinePermsRowCount(5, 5,
+                                      //  nonDbo run: role admin
+                                      // has been dropped, so this
+                                      // run's grant to admin is de
+                                      // facto to a user named admin:
+                                      6);
+
         doStmt("drop role \"NONE\"",
-               sqlAuthorizationRequired, notImplemented, notImplemented);
+               sqlAuthorizationRequired, null , roleDboOnly);
 
-        // current_role
-        doStmt("values current_role",
-               sqlAuthorizationRequired, notImplemented, notImplemented);
 
-        // column default current_role
-        doStmt("create table foo(str varchar(128) default current_role)",
-               sqlAuthorizationRequired, notImplemented, notImplemented);
+        /*
+         * REVOKE permissions for nonDbo run
+         */
+        doStmt("revoke select, insert on foo from admin",
+               sqlAuthorizationRequired, null , null );
+        doStmt("revoke select (str), update (str) on foo from admin",
+               sqlAuthorizationRequired, null , null );
+        doStmt("revoke execute on function f1 from admin restrict",
+               sqlAuthorizationRequired, null , null );
+
+        // assert blank slate
+        assertSysTablePermsRowCount(0,0,0);
+        assertSysColPermsRowCount(0,0,0);
+        assertSysRoutinePermsRowCount(5,5,5);
+
+        // roles foo and bar survive to nonDbo run and beyond:
+        assertSysRolesRowCount(0, 4, 4);
+
+        _stm.close();
+    }
+
+
+    protected void setUp() throws Exception
+    {
+        super.setUp();
+
+        _stm = createStatement();
+
+        if (_authLevel == SQLAUTHORIZATION && isDbo()) {
+
+            // We need to clean away roles when we run the dbo run the
+            // second time around (client/server). The reason is that
+            // the dbo run has a side-effect: it leaves roles for the
+            // non-dbo run to play with, and that run can't remove the
+            // roles (since non-dbo can't drop roles).
+            try {
+                _stm.executeUpdate("drop role foo");
+                _stm.executeUpdate("drop role bar");
+            } catch (SQLException se) {
+            }
+        }
+
+        try {
+            _stm.executeUpdate("drop function f1");
+            _stm.executeUpdate("drop table foo");
+        } catch (SQLException se) {
+        }
+
+        _stm.close();
+    }
+
+
+    protected void tearDown() throws Exception
+    {
+        if (_stm != null) {
+            _stm.close();
+            _stm = null;
+        }
+
+        if (_conn != null) {
+            _conn = null;
+        }
+
+        super.tearDown();
+    }
+
+
+    private void doStmt(String stmt,
+                             String noAuthState,
+                             String authDboState,
+                             String authNotDboState)
+    {
+        doStmt(stmt, noAuthState, authDboState, authNotDboState, false);
+    }
+
+
+    private ResultSet doQuery(String stmt,
+                              String noAuthState,
+                              String authDboState,
+                              String authNotDboState)
+    {
+        return doStmt(stmt, noAuthState, authDboState, authNotDboState, true);
+    }
+
+
+    private ResultSet doStmt(String stmt,
+                             String noAuthState,
+                             String authDboState,
+                             String authNotDboState,
+                             boolean query)
+    {
+        return doStmtWithWarnings(stmt,
+                                  new String[]{noAuthState,null},
+                                  new String[]{authDboState, null},
+                                  new String[]{authNotDboState, null},
+                                  query);
     }
 
     // Minion to analyze outcome. If state string is empty, we expect success
     // for that combination of authentication level and user (dbo or not).
-    private void doStmt(String stmt,
-                        String noAuthState,
-                        String authDboState,
-                        String authNotDboState) {
+    // State arrays: element 0: expected error, element 1: expected warning
+    private ResultSet doStmtWithWarnings(String stmt,
+                                         String[] noAuthState,
+                                         String[] authDboState,
+                                         String[] authNotDboState,
+                                         boolean query)
+    {
+        ResultSet result = null;
+
         try {
-            _stm.execute(stmt);
+            if (query) {
+                result = _stm.executeQuery(stmt);
+            } else {
+                _stm.execute(stmt);
+            }
+
             if (_authLevel == NO_SQLAUTHORIZATION) {
-                if (noAuthState != null) {
-                    fail("exception " + noAuthState + " expected: (" + stmt);
+                if (noAuthState[0] != null) {
+                    fail("exception " + noAuthState[0] + " expected: (" + stmt);
                 }
+                if (noAuthState[1] != null) {
+                    SQLWarning w = _stm.getWarnings();
+                    assertNotNull("Expected warning but found none", w);
+                    assertSQLState(noAuthState[1], w);
+                }
+
             } else { // SQLAUTHORIZATION
                 if (isDbo()) {
-                    if (authDboState != null) {
-                        fail("exception " + noAuthState + " expected: (" +
+                    if (authDboState[0] != null) {
+                        fail("exception " + noAuthState[0] + " expected: (" +
                              stmt);
                     }
+                    if (authDboState[1] != null) {
+                        SQLWarning w = _stm.getWarnings();
+                        assertNotNull("Expected warning but found none", w);
+                        assertSQLState(authDboState[1], w);
+                    }
                 } else {
-                    if (authNotDboState != null) {
-                        fail("exception " + noAuthState + " expected: (" +
+                    if (authNotDboState[0] != null) {
+                        fail("exception " + noAuthState[0] + " expected: (" +
                              stmt);
                     }
+                    if (authNotDboState[1] != null) {
+                        SQLWarning w = _stm.getWarnings();
+                        assertNotNull("Expected warning but found none", w);
+                        assertSQLState(authNotDboState[1], w);
+                    }
                 }
             }
         } catch (SQLException e) {
             if (_authLevel == NO_SQLAUTHORIZATION) {
-                if (noAuthState == null) {
+                if (noAuthState[0] == null) {
                     fail("stmt " + stmt + " failed with exception " +
                          e.getSQLState());
                 } else {
-                    assertSQLState("Stmt " + stmt, noAuthState, e);
+                    assertSQLState("Stmt " + stmt, noAuthState[0], e);
                 }
 
             } else { // SQLAUTHORIZATION
                 if (isDbo()) {
-                    if (authDboState == null) {
+                    if (authDboState[0] == null) {
                         fail("stmt " + stmt + " failed with exception " +
                              e.getSQLState());
                     } else {
-                        assertSQLState("Stmt " + stmt, authDboState, e);
+                        assertSQLState("Stmt " + stmt, authDboState[0], e);
                     }
                 } else {
-                    if (authNotDboState == null) {
+                    if (authNotDboState[0] == null) {
                         fail("stmt " + stmt + " failed with exception " +
                              e.getSQLState());
                     } else {
-                        assertSQLState("Stmt " + stmt, authNotDboState, e);
+                        assertSQLState("Stmt " + stmt, authNotDboState[0], e);
                     }
                 }
             }
         }
+
+        return result;
     }
 
 
-    private void doDynamicSetRole(Connection conn) {
+    private void doDynamicSetRole(Connection conn)
+    {
         PreparedStatement pstmt = null;
 
         try {
@@ -414,34 +728,249 @@
                  assertSQLState(sqlAuthorizationRequired, e);
                  return;
              } else {
-                 // fail("prepare of set role ? failed:" + e);
-                 assertSQLState(notImplemented, e);
-                 return;
+                 fail("prepare of set role ? failed:" + e);
              }
         }
 
         try {
-            pstmt.setString(1, "foo");
+            pstmt.setString(1, "BAR");
             int rowcnt = pstmt.executeUpdate();
             assertEquals(rowcnt, 0, "rowcount from set role ? not 0");
         } catch (SQLException e) {
-            assertSQLState(notImplemented, e);
+            fail("execute of set role ? failed: [foo]" + e);
         }
 
 
         try {
-            pstmt.setString(1, "\"NONE\"");
+            pstmt.setString(1, null);
             int rowcnt = pstmt.executeUpdate();
             assertEquals(rowcnt, 0, "rowcount from set role ? not 0");
         } catch (SQLException e) {
-            assertSQLState(notImplemented, e);
+            fail("execute of set role ? failed: [NONE] " + e);
+        }
+
+        if (isDbo()) {
+            // not granted to non-dbo, so don't try..
+            String n_a     = null; // auth level not used for this test
+
+            try {
+                pstmt.setString(1, "NONE");
+                int rowcnt = pstmt.executeUpdate();
+                assertEquals(rowcnt, 0, "rowcount from set role ? not 0");
+                ResultSet rs = doQuery("values current_role", n_a, null , n_a );
+                assertCurrentRole(rs, "NONE", n_a);
+                rs.close();
+            } catch (SQLException e) {
+                fail("execute of set role ? failed: [NONE] " + e);
+            }
+        }
+
+        if (pstmt != null) {
+            try {
+                pstmt.close();
+            } catch (SQLException e) {
+            }
+        }
+    }
+
+
+    private void assertSystableRowCount(String table,
+                                        int rcNoAuth,
+                                        int rcDbo,
+                                        int rcMereMortal)
+        throws SQLException
+    {
+        ResultSet rs = _stm.executeQuery(
+                "SELECT COUNT(*) FROM " + table);
+        rs.next();
+        assertEquals(table + " row count:",
+                     _authLevel == NO_SQLAUTHORIZATION ? rcNoAuth :
+                     (isDbo() ? rcDbo : rcMereMortal),
+                     rs.getInt(1));
+        rs.close();
+    }
+
+
+    private void assertSysRolesRowCount(int rcNoAuth,
+                                        int rcDbo,
+                                        int rcMereMortal)
+        throws SQLException
+    {
+
+        if (TestConfiguration.getCurrent().isVerbose()) {
+            dumpSysRoles();
+        }
+
+        assertSystableRowCount("SYS.SYSROLES",
+                               rcNoAuth, rcDbo, rcMereMortal);
+    }
+
+
+    private void assertSysTablePermsRowCount(int rcNoAuth,
+                                             int rcDbo,
+                                             int rcMereMortal)
+        throws SQLException
+    {
+
+        if (TestConfiguration.getCurrent().isVerbose()) {
+            dumpSysTablePerms();
         }
+
+        assertSystableRowCount("SYS.SYSTABLEPERMS",
+                               rcNoAuth, rcDbo, rcMereMortal);
     }
 
 
-    private void assertEquals(int a, int b, String txt) {
+    private void assertSysColPermsRowCount(int rcNoAuth,
+                                           int rcDbo,
+                                           int rcMereMortal)
+        throws SQLException
+    {
+
+        if (TestConfiguration.getCurrent().isVerbose()) {
+            dumpSysColPerms();
+        }
+
+        assertSystableRowCount("SYS.SYSCOLPERMS",
+                               rcNoAuth, rcDbo, rcMereMortal);
+    }
+
+
+    private void assertSysRoutinePermsRowCount(int rcNoAuth,
+                                               int rcDbo,
+                                               int rcMereMortal)
+        throws SQLException
+    {
+
+        if (TestConfiguration.getCurrent().isVerbose()) {
+            dumpSysRoutinePerms();
+        }
+
+        assertSystableRowCount("SYS.SYSROUTINEPERMS",
+                               rcNoAuth, rcDbo, rcMereMortal);
+    }
+
+
+    private void dumpSysRoles() throws SQLException
+    {
+
+        ResultSet rs = _stm.executeQuery
+            ("SELECT * FROM SYS.SYSROLES ORDER BY ROLEID");
+
+        println("SYS.SYSROLES:");
+
+        while (rs.next()) {
+            println("r=" + rs.getString(1) + " -ee:" + rs.getString(2) +
+                    " -or:" + rs.getString(3) + " a:" + rs.getString(4) +
+                    " d:" + rs.getString(5));
+        }
+
+        rs.close();
+    }
+
+
+    private void dumpSysTablePerms() throws SQLException
+    {
+        ResultSet rs = _stm.executeQuery
+            ("SELECT * FROM SYS.SYSTABLEPERMS");
+
+        println("SYS.SYSTABLEPERMS:");
+
+        while (rs.next()) {
+            println("id: " + rs.getString(1) +
+                    " -ee:" + rs.getString(2) +
+                    " -or:" + rs.getString(3) +
+                    " tableid:" + rs.getString(4) +
+                    " S:" + rs.getString(5) +
+                    " D:" + rs.getString(6) +
+                    " I:" + rs.getString(7) +
+                    " U:" + rs.getString(8) +
+                    " R:" + rs.getString(9) +
+                    " T:" + rs.getString(10));
+        }
+
+        rs.close();
+    }
+
+
+    private void dumpSysColPerms() throws SQLException
+    {
+
+        ResultSet rs = _stm.executeQuery
+            ("SELECT * FROM SYS.SYSCOLPERMS");
+
+        println("SYS.SYSCOLPERMS:");
+
+        while (rs.next()) {
+            println("id: " + rs.getString(1) +
+                    " -ee:" + rs.getString(2) +
+                    " -or:" + rs.getString(3) +
+                    " tableid:" + rs.getString(4) +
+                    " type:" + rs.getString(5) +
+                    " col#:" + rs.getString(6));
+        }
+
+        rs.close();
+    }
+
+
+    private void dumpSysRoutinePerms() throws SQLException
+    {
+
+        ResultSet rs = _stm.executeQuery
+            ("SELECT * FROM SYS.SYSROUTINEPERMS");
+
+        println("SYS.SYSROUTINEPERMS:");
+
+        while (rs.next()) {
+            println("id: " + rs.getString(1) +
+                    " -ee:" + rs.getString(2) +
+                    " -or:" + rs.getString(3) +
+                    " alias:" + rs.getString(4) +
+                    " grantopt:" + rs.getString(5));
+        }
+
+        rs.close();
+    }
+
+
+    private void assertCurrentRole(ResultSet rs,
+                                   String dboRole,
+                                   String notDboRole)
+        throws SQLException
+    {
+
+        if (_authLevel == NO_SQLAUTHORIZATION) {
+            assertNull(rs);
+        } else {
+            assertTrue("result set empty", rs.next());
+
+            if (isDbo()) {
+                assertTrue(dboRole.equals(rs.getString(1)));
+            } else {
+                assertTrue(notDboRole.equals(rs.getString(1)));
+            }
+
+            // cardinality should be 1
+            assertFalse("result set not empty", rs.next());
+            rs.close();
+        }
+    }
+
+    private void assertEquals(int a, int b, String txt)
+    {
         if (a!=b) {
             fail(txt);
         }
+    }
+
+    /**
+     * Utility function used to test auto-drop of grant routine
+     * permission to a role
+     * @return 1
+     */
+    public static int f1()
+    {
+        return 1;
     }
 }

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SystemCatalogTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SystemCatalogTest.java?rev=594158&r1=594157&r2=594158&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SystemCatalogTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SystemCatalogTest.java Mon Nov 12 06:24:25 2007
@@ -289,6 +289,11 @@
 				{"SYSFOREIGNKEYS", "UPDATERULE", "5", "CHAR(1) NOT NULL"},
 				{"SYSKEYS", "CONGLOMERATEID", "2", "CHAR(36) NOT NULL"},
 				{"SYSKEYS", "CONSTRAINTID", "1", "CHAR(36) NOT NULL"},
+				{"SYSROLES", "GRANTEE", "2", "VARCHAR(128) NOT NULL"},
+				{"SYSROLES", "GRANTOR", "3", "VARCHAR(128) NOT NULL"},
+				{"SYSROLES", "ISDEF", "5", "CHAR(1) NOT NULL"},
+				{"SYSROLES", "ROLEID", "1", "VARCHAR(128) NOT NULL"},
+				{"SYSROLES", "WITHADMINOPTION", "4", "CHAR(1) NOT NULL"},
 				{"SYSROUTINEPERMS", "ALIASID", "4", "CHAR(36) NOT NULL"},
 				{"SYSROUTINEPERMS", "GRANTEE", "2", "VARCHAR(128) NOT NULL"},
 				{"SYSROUTINEPERMS", "GRANTOPTION", "5", "CHAR(1) NOT NULL"},
@@ -399,6 +404,9 @@
 				{"SYSFOREIGNKEYS", "SYSFOREIGNKEYS_INDEX1", "true"},
 				{"SYSKEYS", "SYSKEYS_HEAP", "false"},
 				{"SYSKEYS", "SYSKEYS_INDEX1", "true"},
+				{"SYSROLES", "SYSROLES_HEAP", "false"},
+				{"SYSROLES", "SYSROLES_INDEX2", "true"},
+				{"SYSROLES", "SYSROLES_INDEX1", "true"},
 				{"SYSROUTINEPERMS", "SYSROUTINEPERMS_HEAP", "false"},
 				{"SYSROUTINEPERMS", "SYSROUTINEPERMS_INDEX3", "true"},
 				{"SYSROUTINEPERMS", "SYSROUTINEPERMS_INDEX2", "true"},

Modified: db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/ij/ij.jj
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/ij/ij.jj?rev=594158&r1=594157&r2=594158&view=diff
==============================================================================
--- db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/ij/ij.jj (original)
+++ db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/ij/ij.jj Mon Nov 12 06:24:25 2007
@@ -827,6 +827,38 @@
 	}
 
 	/**
+	   Return a resultset of roles. No database metadata
+	   available, so select from SYS.SYSROLES directly. This has
+	   the side effect of starting a transaction if one is not
+	   already active, so we should perhaps give warning when not
+	   in autocommit mode.
+	*/
+	public ijResult showRoles() throws SQLException {
+		ResultSet rs = null;
+		try {
+			haveConnection();
+
+			rs = theConnection.createStatement().executeQuery
+				("SELECT ROLEID FROM SYS.SYSROLES WHERE ISDEF='Y' " +
+				 "ORDER BY ROLEID ASC");
+
+			int[] displayColumns = new int[] {
+				rs.findColumn("ROLEID")
+			};
+			int[] columnWidths = new int[] {
+				30
+			};
+
+			return new ijResultSetResult(rs, displayColumns, columnWidths);
+		} catch (SQLException e) {
+			if(rs!=null)
+				rs.close();
+			throw e;
+		}
+	}
+
+
+	/**
 	   Outputs the names of all fields of given table. Outputs field
 	   names and data type.
 	 */
@@ -978,6 +1010,7 @@
 |	<RELATIVE: "relative">
 |	<REMOVE: "remove">
 |	<RESOURCE: "resource">
+|       <ROLES: "roles">
 |	<ROLLBACK: "rollback">
 |	<RUN: "run">
 |	<TO: "to">
@@ -1776,6 +1809,10 @@
 	{
 		return showSchemas();
 	}
+|       <SHOW> <ROLES>
+        {
+            return showRoles();
+        }
 }
 
 /**



Mime
View raw message