db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Satheesh Bandaram (JIRA)" <derby-...@db.apache.org>
Subject [jira] Commented: (DERBY-1592) Update statement is allowed to execute even though the column that the statement access has been revoked.
Date Wed, 26 Jul 2006 16:37:14 GMT
    [ http://issues.apache.org/jira/browse/DERBY-1592?page=comments#action_12423680 ] 
            
Satheesh Bandaram commented on DERBY-1592:
------------------------------------------

This functionality is not supported. If a table level privilege has been granted, revoking
part of that privilege (say from one or two columns only) is not supported. That is why revoke
did not do anything (though would have been nice to have raised a message... Dan has already
filed a defect for that). May be this should be clarified in the functional spec.

Looking at DB2 manuals, it doesn't look like DB2 can do that too. (ftp://ftp.software.ibm.com/ps/products/db2/info/vr82/pdf/en_US/db2s2e81.pdf)


> 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

        

Mime
View raw message