db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From rhille...@apache.org
Subject svn commit: r896883 - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/catalog/ engine/org/apache/derby/impl/sql/compile/ engine/org/apache/derby/impl/sql/execute/ testing/org/apache/derbyTesting/functionTests/tests/lang/ testing/org/apac...
Date Thu, 07 Jan 2010 13:58:17 GMT
Author: rhillegas
Date: Thu Jan  7 13:56:35 2010
New Revision: 896883

URL: http://svn.apache.org/viewvc?rev=896883&view=rev
Log:
DERBY-712: Wire in USAGE privilege for SEQUENCEs.

Added:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SequencePermsTest.java
  (with props)
Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/NextSequenceNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DDLConstantAction.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/JDBC.java

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java?rev=896883&r1=896882&r2=896883&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
Thu Jan  7 13:56:35 2010
@@ -2254,7 +2254,7 @@
         {
             if (isSchemaReferenced(tc, getNonCoreTI(SYSSEQUENCES_CATALOG_NUM),
                                    SYSSEQUENCESRowFactory.SYSSEQUENCES_INDEX2_ID,
-                                   2,
+                                   1,
                                    schemaIdOrderable))
             {
                 return false;

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/NextSequenceNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/NextSequenceNode.java?rev=896883&r1=896882&r2=896883&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/NextSequenceNode.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/NextSequenceNode.java
Thu Jan  7 13:56:35 2010
@@ -74,8 +74,16 @@
 
         ValueNode returnNode = this;
 
-        return returnNode;
+        // set up dependency on sequence and compile a check for USAGE
+        // priv if needed
+        getCompilerContext().createDependency( sequenceDescriptor );
+
+        if ( isPrivilegeCollectionRequired() )
+        {
+            getCompilerContext().addRequiredUsagePriv( sequenceDescriptor );
+        }
 
+        return returnNode;
     }
 
 

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DDLConstantAction.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DDLConstantAction.java?rev=896883&r1=896882&r2=896883&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DDLConstantAction.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DDLConstantAction.java
Thu Jan  7 13:56:35 2010
@@ -55,6 +55,7 @@
 import org.apache.derby.iapi.sql.dictionary.SchemaDescriptor;
 import org.apache.derby.iapi.sql.dictionary.StatementColumnPermission;
 import org.apache.derby.iapi.sql.dictionary.StatementPermission;
+import org.apache.derby.iapi.sql.dictionary.StatementGenericPermission;
 import org.apache.derby.iapi.sql.dictionary.StatementSchemaPermission;
 import org.apache.derby.iapi.sql.dictionary.StatementRolePermission;
 import org.apache.derby.iapi.sql.dictionary.StatementRoutinePermission;
@@ -332,7 +333,7 @@
 			PermissionsDescriptor permDesc;
 			// Now, it is time to add into dependency system the FOREIGN
 			// constraint's dependency on REFERENCES privilege, or, if it is a
-			// CHECK constraint, any EXECUTE privileges. If the REFERENCES is
+			// CHECK constraint, any EXECUTE or USAGE privileges. If the REFERENCES is
 			// revoked from the constraint owner, the constraint will get
 			// dropped automatically.
 			List requiredPermissionsList = activation.getPreparedStatement().getRequiredPermissionsList();
@@ -361,7 +362,8 @@
 						if (!statementTablePermission.getTableUUID().equals(refTableUUID))
 							continue;
 					} else if (statPerm instanceof StatementSchemaPermission
-						    || statPerm instanceof StatementRolePermission) {
+						    || statPerm instanceof StatementRolePermission
+                               || statPerm instanceof StatementGenericPermission ) {
 						continue;
 					} else {
 						if (SanityManager.DEBUG) {
@@ -380,14 +382,14 @@
 					}
 
 
-					// We know that we are working with a REFERENCES or EXECUTE
+					// We know that we are working with a REFERENCES, EXECUTE, or USAGE
 					// privilege. Find all the PermissionDescriptors for this
 					// privilege and make constraint depend on it through
 					// dependency manager.  The REFERENCES privilege could be
 					// defined at the table level or it could be defined at
 					// individual column levels. In addition, individual column
 					// REFERENCES privilege could be available at the user
-					// level, PUBLIC or role level.  EXECUTE privilege could be
+					// level, PUBLIC or role level.  EXECUTE and USAGE privileges could be
 					// available at the user level, PUBLIC or role level.
 					permDesc = statPerm.getPermissionDescriptor(lcc.getAuthorizationId(), dd);				
 					if (permDesc == null) 

Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SequencePermsTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SequencePermsTest.java?rev=896883&view=auto
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SequencePermsTest.java
(added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SequencePermsTest.java
Thu Jan  7 13:56:35 2010
@@ -0,0 +1,327 @@
+/*
+
+   Derby - Class org.apache.derbyTesting.functionTests.tests.lang.SequencePermsTest
+
+   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.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 java.sql.ResultSet;
+import java.sql.DriverManager;
+import java.util.ArrayList;
+import junit.framework.Test;
+import junit.framework.TestSuite;
+import org.apache.derby.iapi.util.StringUtil;
+import org.apache.derby.catalog.DefaultInfo;
+import org.apache.derbyTesting.junit.BaseJDBCTestCase;
+import org.apache.derbyTesting.junit.JDBC;
+import org.apache.derbyTesting.junit.DatabasePropertyTestSetup;
+import org.apache.derbyTesting.junit.JDBC;
+import org.apache.derbyTesting.junit.TestConfiguration;
+import org.apache.derbyTesting.junit.CleanDatabaseTestSetup;
+import org.apache.derbyTesting.junit.JDBC;
+
+/**
+ * <p>
+ * Test permissions on sequences. See DERBY-712.
+ * </p>
+ */
+public class SequencePermsTest extends GeneratedColumnsHelper
+{
+    ///////////////////////////////////////////////////////////////////////////////////
+    //
+    // CONSTANTS
+    //
+    ///////////////////////////////////////////////////////////////////////////////////
+
+    private static  final   String      TEST_DBO = "TEST_DBO";
+    private static  final   String      RUTH = "RUTH";
+    private static  final   String      ALICE = "ALICE";
+    private static  final   String      FRANK = "FRANK";
+    private static  final   String      IRMA = "IRMA";
+    private static  final   String[]    LEGAL_USERS = { TEST_DBO, ALICE, RUTH, FRANK, IRMA
 };
+
+    ///////////////////////////////////////////////////////////////////////////////////
+    //
+    // STATE
+    //
+    ///////////////////////////////////////////////////////////////////////////////////
+
+    ///////////////////////////////////////////////////////////////////////////////////
+    //
+    // CONSTRUCTOR
+    //
+    ///////////////////////////////////////////////////////////////////////////////////
+
+    /**
+     * Create a new instance.
+     */
+
+    public SequencePermsTest(String name)
+    {
+        super(name);
+    }
+
+    ///////////////////////////////////////////////////////////////////////////////////
+    //
+    // JUnit BEHAVIOR
+    //
+    ///////////////////////////////////////////////////////////////////////////////////
+
+
+    /**
+     * Construct top level suite in this JUnit test
+     */
+    public static Test suite()
+    {
+        TestSuite suite = (TestSuite) TestConfiguration.embeddedSuite(SequencePermsTest.class);
+
+        Test        cleanTest = new CleanDatabaseTestSetup( suite );
+        Test        authenticatedTest = DatabasePropertyTestSetup.builtinAuthentication
+            ( cleanTest, LEGAL_USERS, "sequencePermissions" );
+        Test        authorizedTest = TestConfiguration.sqlAuthorizationDecorator( authenticatedTest
);
+
+        return authorizedTest;
+    }
+
+    ///////////////////////////////////////////////////////////////////////////////////
+    //
+    // TESTS
+    //
+    ///////////////////////////////////////////////////////////////////////////////////
+
+    /**
+     * <p>
+     * Test that you need USAGE privilege on a sequence in order to issue a NEXT VALUE FOR
+     * on it and in order to declare objects which mention that type.
+     * </p>
+     */
+    public  void    test_001_basicGrant()
+        throws Exception
+    {
+        Connection  dboConnection = openUserConnection( TEST_DBO );
+        Connection  ruthConnection = openUserConnection( RUTH );
+        Connection  aliceConnection = openUserConnection( ALICE );
+        Connection  frankConnection = openUserConnection( FRANK );
+
+        //
+        // Create a sequence and view. Make the view
+        // public. Verify that it is still not generally usable because the
+        // sequence is not public yet.
+        //
+        goodStatement
+            (
+             ruthConnection,
+             "create sequence seq_01\n"
+             );
+        goodStatement
+            (
+             ruthConnection,
+             "create table t_01( c int )\n"
+             );
+        goodStatement
+            (
+             ruthConnection,
+             "insert into t_01( c ) values ( 1 )\n"
+             );
+        goodStatement
+            (
+             ruthConnection,
+             "create view v_01( a, b ) as select c, next value for seq_01 from t_01\n"
+             );
+        goodStatement
+            (
+             ruthConnection,
+             "grant select on v_01 to alice\n"
+             );
+
+        expectExecutionError
+            (
+             aliceConnection,
+             LACK_USAGE_PRIV,
+             "values ( next value for ruth.seq_01 )\n"
+             );
+        expectExecutionError
+            (
+             aliceConnection,
+             LACK_COLUMN_PRIV,
+             "select * from ruth.t_01\n"
+             );
+
+        // but this succeeds because of definer's rights on the view
+        goodStatement
+            (
+             ruthConnection,
+             "select * from ruth.v_01\n"
+             );
+
+        //
+        // The DBO however is almighty.
+        //
+        goodStatement
+            (
+             ruthConnection,
+             "values ( next value for ruth.seq_01 )\n"
+             );
+
+        //
+        // Now grant USAGE on the sequence. User Alice should now have all the
+        // privileges she needs.
+        //
+        goodStatement
+            (
+             ruthConnection,
+             "grant usage on sequence seq_01 to alice\n"
+             );
+        goodStatement
+            (
+             aliceConnection,
+             "values( next value for ruth.seq_01 )\n"
+             );
+
+    }
+    
+    /**
+     * <p>
+     * Test that you need USAGE privilege on a sequence in order to issue a NEXT VALUE FOR
+     * on it the privilege can't be revoked while the object still exists.
+     * </p>
+     */
+    public  void    test_002_basicRevoke()
+        throws Exception
+    {
+        Connection  ruthConnection = openUserConnection( RUTH );
+        Connection  frankConnection = openUserConnection( FRANK );
+        
+        goodStatement
+            (
+             ruthConnection,
+             "create sequence seq_02\n"
+             );
+        goodStatement
+            (
+             frankConnection,
+             "create table t_01( c int )\n"
+             );
+        expectExecutionError
+            (
+             frankConnection,
+             LACK_USAGE_PRIV,
+             "values ( next value for ruth.seq_02 )\n"
+             );
+
+        //
+        // Only RESTRICTed revokes allowed.
+        //
+        goodStatement
+            (
+             ruthConnection,
+             "grant usage on sequence seq_02 to public\n"
+             );
+        expectCompilationError( ruthConnection, SYNTAX_ERROR, "revoke usage on sequence seq_02
from public\n" );
+        goodStatement
+            (
+             ruthConnection,
+             "revoke usage on sequence seq_02 from public restrict\n"
+             );
+
+        //
+        // Now test revokes when objects depend on the sequence.
+        //
+        
+        String grantUsage = "grant usage on sequence seq_02 to frank\n";
+        String revokeUsage = "revoke usage on sequence seq_02 from frank restrict\n";
+        String createStatement;
+        String dropStatement;
+        String badRevokeSQLState;
+        
+        // view
+        createStatement = "create view v_01( a, b ) as select c, next value for ruth.seq_02
from t_01\n";
+        dropStatement = "drop view v_01\n";
+        badRevokeSQLState = VIEW_DEPENDS_ON_PRIVILEGE;
+        verifyRevokePrivilege
+            (
+             ruthConnection,
+             frankConnection,
+             grantUsage,
+             revokeUsage,
+             createStatement,
+             dropStatement,
+             badRevokeSQLState
+             );
+
+        // trigger
+        createStatement = "create trigger trig_01 after update on t_01 for each statement
insert into t_01( c ) values ( next value for ruth.seq_02 )\n";
+        dropStatement = "drop trigger trig_01\n";
+        badRevokeSQLState = OPERATION_FORBIDDEN;
+        verifyRevokePrivilege
+            (
+             ruthConnection,
+             frankConnection,
+             grantUsage,
+             revokeUsage,
+             createStatement,
+             dropStatement,
+             badRevokeSQLState
+             );
+
+        // constraint
+        createStatement = "create table t_02( c int check ( ( next value for ruth.seq_02
) < c ) )\n";
+        dropStatement = "drop table t_02\n";
+        badRevokeSQLState = OPERATION_FORBIDDEN;
+        verifyRevokePrivilege
+            (
+             ruthConnection,
+             frankConnection,
+             grantUsage,
+             revokeUsage,
+             createStatement,
+             dropStatement,
+             badRevokeSQLState
+             );
+
+        
+    }
+
+  /**
+     * <p>
+     * Test that you can't drop a schema if it contains a sequence.
+     * </p>
+     */
+    public  void    test_003_dropSchema()
+        throws Exception
+    {
+        Connection  dboConnection = openUserConnection( TEST_DBO );
+        Connection  irmaConnection = openUserConnection( IRMA );
+
+        goodStatement
+            ( irmaConnection, "create sequence seq_01\n" );
+        expectExecutionError( dboConnection, NON_EMPTY_SCHEMA, "drop schema irma restrict\n"
);
+
+        goodStatement
+            (irmaConnection, "drop sequence seq_01\n" );
+       goodStatement
+            ( dboConnection, "drop schema irma restrict\n" );
+    }
+
+}

Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SequencePermsTest.java
------------------------------------------------------------------------------
    svn:eol-style = native

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java?rev=896883&r1=896882&r2=896883&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
Thu Jan  7 13:56:35 2010
@@ -207,6 +207,7 @@
         suite.addTest(UserLobTest.suite());
         suite.addTest(OffsetFetchNextTest.suite());
         suite.addTest(SequenceTest.suite());
+        suite.addTest(SequencePermsTest.suite());
         suite.addTest(OrderByInSubqueries.suite());
         suite.addTest(OLAPTest.suite());
 

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/JDBC.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/JDBC.java?rev=896883&r1=896882&r2=896883&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/JDBC.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/JDBC.java Thu Jan  7 13:56:35
2010
@@ -314,6 +314,20 @@
         
         dropUsingDMD(s, rs, schema, "TABLE_NAME", "SYNONYM");
                 
+        // sequences
+        if ( sysSequencesExists( conn ) )
+        {
+            psf = conn.prepareStatement
+                (
+                 "SELECT SEQUENCENAME FROM SYS.SYSSEQUENCES A, SYS.SYSSCHEMAS S" +
+                 " WHERE A.SCHEMAID = S.SCHEMAID " +
+                 " AND S.SCHEMANAME = ?");
+            psf.setString(1, schema);
+            rs = psf.executeQuery();
+            dropUsingDMD(s, rs, schema, "SEQUENCENAME", "SEQUENCE");
+            psf.close();
+        }
+
 		// Finally drop the schema if it is not APP
 		if (!schema.equals("APP")) {
 			s.executeUpdate("DROP SCHEMA " + JDBC.escape(schema) + " RESTRICT");
@@ -321,6 +335,31 @@
 		conn.commit();
 		s.close();
 	}
+
+    /**
+     * Return true if the SYSSEQUENCES table exists.
+     */
+    private static boolean sysSequencesExists( Connection conn ) throws SQLException
+    {
+        PreparedStatement ps = null;
+        ResultSet rs =  null;
+        try {
+            ps = conn.prepareStatement
+                (
+                 "select count(*) from sys.systables t, sys.sysschemas s\n" +
+                 "where t.schemaid = s.schemaid\n" +
+                 "and ( cast(s.schemaname as varchar(128)))= 'SYS'\n" +
+                 "and ( cast(t.tablename as varchar(128))) = 'SYSSEQUENCES'" );
+            rs = ps.executeQuery();
+            rs.next();
+            return ( rs.getInt( 1 ) > 0 );
+        }
+        finally
+        {
+            if ( rs != null ) { rs.close(); }
+            if ( ps != null ) { ps.close(); }
+        }
+    }
 	
 	/**
 	 * DROP a set of objects based upon a ResultSet from a



Mime
View raw message