Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <1600172.1156912223310.JavaMail.jira@brutus>
Date: Tue, 29 Aug 2006 21:30:23 -0700 (PDT)
From: "Mamta A. Satoor (JIRA)" <derby-dev@db.apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Commented: (DERBY-1686) Grant/Revoke: Attempt to GRANT
 access to another user on a VIEW, created by the current user with only
 SELECT privilege on the base table does not fail
In-Reply-To: <5962508.1155358093826.JavaMail.jira@brutus>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit

    [ http://issues.apache.org/jira/browse/DERBY-1686?page=comments#action_12431456 ] 
            
Mamta A. Satoor commented on DERBY-1686:
----------------------------------------

Yip, I think you are right about my logic not working for a view created by the database owner but it references objects not owned by the schema owner of the view. Considering that and Dan's comment "The count of number of rows in SYSDEPENDS seems like a kludge to me, checking indirectly for some state rather than checking directly. ", we probably should go with your proposed logic.

I thought of one case where the logic in the patch might not do the right stuff. If a user creates a view on a table owned by another user. Then dependency manager will keep the view's dependency on that table. Now, if the same user creates another view which is based on the view it created earlier, the dependency manager will not know of the new view's dependency on the base table at the outer view's level. We will need to go through view chains to make sure that we are not granting access to something that we are not allowed to. Here is an eg
user1
create table t1(c11 int)
grant select on t1 to user2
user2
create view v1 as select * from user1.t1 -- dependency manager will track v1's dependency on user1.t1
create view v2 as select * from v1 -- dependency manager will track v2's dependency on v1
grant select on v2 to user3 -- we shouldn't just check v2's direct providers, but also see if those providers have any providers of their own
-- And hence the grant above should not succeed


> Grant/Revoke: Attempt to GRANT access to another user on a VIEW, created by the current user with only SELECT privilege on the base table does not fail
> -------------------------------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-1686
>                 URL: http://issues.apache.org/jira/browse/DERBY-1686
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.2.1.0
>         Environment: Any
>            Reporter: Rajesh Kartha
>         Assigned To: Yip Ng
>             Fix For: 10.2.1.0
>
>         Attachments: derby1686-trunk-diff01.txt, derby1686-trunk-diff02.txt, derby1686-trunk-diff03.txt, derby1686-trunk-stat01.txt, derby1686-trunk-stat02.txt, derby1686-trunk-stat03.txt, select_table_no_privilege.sql
>
>
> With authentication on, attempting to execute a GRANT privilege  to 'user3' on a VIEW created by the 'user2' - who has only SELECT privilege
> on the base table created by 'user1' does not fail. This results in 'user3' getting access to the table created by 'user1' through the view.
> I remember a discussion on the list to raise an error when an attempt is execute a GRANT on the view, until WITH GRANT option is implemented.
> Here is the repro:
> java -cp derby.jar;.\derbytools.jar -Dderby.database.sqlAuthorization=true -Dij.exceptionTrace=true   org.apache.derby.tools.ij select_table_no_privilege.sql
> ij version 10.2
> ij> --
> --create db as user1
> --
> connect 'jdbc:derby:grntrevokedb;create=true' user 'user1';
> WARNING 01J14: SQL authorization is being used without first enabling authentication.
> ij> create table t1(id int);
> 0 rows inserted/updated/deleted
> ij> insert into t1 values(100);
> 1 row inserted/updated/deleted
> ij> insert into t1 values(200);
> 1 row inserted/updated/deleted
> ij> --
> --Grant select to user2
> --
> grant select on t1 to user2;
> 0 rows inserted/updated/deleted
> ij> --
> --Connect as user2
> --
> connect 'jdbc:derby:grntrevokedb;create=true' user 'user2';
> WARNING 01J01: Database 'grntrevokedb' not created, connection made to existingdatabase instead.
> WARNING 01J14: SQL authorization is being used without first enabling authentication.
> ij(CONNECTION1)> select * from user1.t1;
> ID
> -----------
> 100
> 200
> 2 rows selected
> ij(CONNECTION1)> --
> --Create view
> --
> create view v1 as select * from user1.t1;
> 0 rows inserted/updated/deleted
> ij(CONNECTION1)> select * from v1;
> ID
> -----------
> 100
> 200
> 2 rows selected
> ij(CONNECTION1)> --
> --Grant select on view to user3. With the WITH GRANT option this should have failed
> --
> grant select on v1 to user3;
> 0 rows inserted/updated/deleted
> ij(CONNECTION1)> --
> --Connect as user3
> --
> connect 'jdbc:derby:grntrevokedb;create=true' user 'user3';
> WARNING 01J01: Database 'grntrevokedb' not created, connection made to existing
> database instead.
> WARNING 01J14: SQL authorization is being used without first enabling authentication.
> ij(CONNECTION2)> --
> --No select privilege on base table user1.t1, hence will FAIL
> --
> select * from user1.t1;
> ERROR 28508: User 'USER3' does not have select permission on column 'ID' of table 'USER1'.'T1'.
> ERROR 28508: User 'USER3' does not have select permission on column 'ID' of table 'USER1'.'T1'.
>         at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
>         at org.apache.derby.iapi.sql.dictionary.StatementColumnPermission.check(Unknown Source)
>         at org.apache.derby.impl.sql.conn.GenericAuthorizer.authorize(Unknown Source)
>         at org.apache.derby.exe.ac295dc08bx010dx00a2x500ax00000011df100.fillResultSet(Unknown Source)
>         at org.apache.derby.exe.ac295dc08bx010dx00a2x500ax00000011df100.execute(Unknown Source)
>         at org.apache.derby.impl.sql.GenericActivationHolder.execute(Unknown Source)
>         at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
>         at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
>         at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)
>         at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)
>         at org.apache.derby.impl.tools.ij.ij.executeImmediate(Unknown Source)
>         at org.apache.derby.impl.tools.ij.utilMain.doCatch(Unknown Source)
>         at org.apache.derby.impl.tools.ij.utilMain.runScriptGuts(Unknown Source)
>         at org.apache.derby.impl.tools.ij.utilMain.go(Unknown Source)
>         at org.apache.derby.impl.tools.ij.Main.go(Unknown Source)
>         at org.apache.derby.impl.tools.ij.Main.mainCore(Unknown Source)
>         at org.apache.derby.impl.tools.ij.Main14.main(Unknown Source)
>         at org.apache.derby.tools.ij.main(Unknown Source)
> ij(CONNECTION2)> --
> --Select from the view on the base table should also FAIL, but does not
> --
> select * from user2.v1;
> ID
> -----------
> 100
> 200
> 2 rows selected
> ij(CONNECTION2)>

-- 
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