[ http://issues.apache.org/jira/browse/DERBY-1592?page=comments#action_12423683 ] Yip Ng commented on DERBY-1592: -------------------------------
Right, there is a syntactical difference comparing to DB2's revoke statement where it does not take privilege column list. >From looking at the current design spec, I had an impression that this >functionality is allowed from Derby's revoke statement syntax. Thanks for the clarification. > Update statement is allowed to execute even though the column that the > statement access has been revoked. > --------------------------------------------------------------------------------------------------------- > > Key: DERBY-1592 > URL: http://issues.apache.org/jira/browse/DERBY-1592 > Project: Derby > Issue Type: Bug > Components: SQL > Affects Versions: 10.2.0.0 > Environment: Sun JDK 142 > Reporter: Yip Ng > > Update statement is allowed to execute even though the column that the > statement access has been revoked. > The same applies for a select statement. Example shown below: user2 is able > to execute the update statement > but update privilege on c2 has already been revoked. > ij version 10.2 > ij> connect 'jdbc:derby:authtest;create=true' user 'user1' as user1; > ij> create table t1 (c1 int, c2 int); > 0 rows inserted/updated/deleted > ij> insert into t1 values (1,1); > 1 row inserted/updated/deleted > ij> grant select,update,insert on t1 to user2; > 0 rows inserted/updated/deleted > ij> select * from sys.systableperms; > TABLEPERMSID |GRANTEE > > |GRANTOR > |TABLEID > |&|&|&|&|&|& > ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- > 67d0407f-010c-aa11-4d39-000000101010|USER2 > > |USER1 > > |2753c07b-010c-aa11-4d39-000000101010|y|N|y|y|N|N > 1 row selected > ij> select * from sys.syscolperms; > COLPERMSID |GRANTEE > > |GRANTOR > |TABLEID > |&|COLUMNS > ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- > 0 rows selected > ij> revoke update (c2) on t1 from user2; > 0 rows inserted/updated/deleted > ij> select * from sys.systableperms; > TABLEPERMSID |GRANTEE > > |GRANTOR > |TABLEID > |&|&|&|&|&|& > ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- > 67d0407f-010c-aa11-4d39-000000101010|USER2 > > |USER1 > > |2753c07b-010c-aa11-4d39-000000101010|y|N|y|y|N|N > 1 row selected > ij> select * from sys.syscolperms; > COLPERMSID |GRANTEE > > |GRANTOR > |TABLEID > |&|COLUMNS > ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- > 0 rows selected > ij> connect 'jdbc:derby:authtest' user 'user2' as user2; > ij(USER2)> update user1.t1 set c2=10; > 1 row inserted/updated/deleted > ij(USER2)> > sysinfo: > C:\derby\trunk>java -classpath classes;. org.apache.derby.tools.sysinfo > ------------------ Java Information ------------------ > Java Version: 1.4.2_12 > Java Vendor: Sun Microsystems Inc. > Java home: C:\jdk142\jre > Java classpath: classes;. > OS name: Windows XP > OS architecture: x86 > OS version: 5.1 > Java user name: yip > Java user home: C:\Documents and Settings\Administrator > Java user dir: C:\derby\trunk > java.specification.name: Java Platform API Specification > java.specification.version: 1.4 > --------- Derby Information -------- > JRE - JDBC: J2SE 1.4.2 - JDBC 3.0 > [C:\derby\trunk\classes] 10.2.0.5 alpha - (425559M) > ------------------------------------------------------ > ----------------- Locale Information ----------------- > Current Locale : [English/United States [en_US]] > Found support for locale: [de_DE] > version: 10.2.0.5 alpha - (425559M) > Found support for locale: [es] > version: 10.2.0.5 alpha - (425559M) > Found support for locale: [fr] > version: 10.2.0.5 alpha - (425559M) > Found support for locale: [it] > version: 10.2.0.5 alpha - (425559M) > Found support for locale: [ja_JP] > version: 10.2.0.5 alpha - (425559M) > Found support for locale: [ko_KR] > version: 10.2.0.5 alpha - (425559M) > Found support for locale: [pt_BR] > version: 10.2.0.5 alpha - (425559M) > Found support for locale: [zh_CN] > version: 10.2.0.5 alpha - (425559M) > Found support for locale: [zh_TW] > version: 10.2.0.5 alpha - (425559M) > ------------------------------------------------------ -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
