Derby
  1. Derby
  2. DERBY-5591

Imprecise wording in documentation in ref man on DROP VIEW

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Minor Minor
    • Resolution: Fixed
    • Affects Version/s: 10.8.2.2
    • Fix Version/s: 10.9.1.0
    • Component/s: Documentation
    • Labels:
      None

      Description

      This sentence is misleading: "Any statements referencing the view are invalidated on a DROP VIEW statement. DROP VIEW is disallowed if there are any views or open cursors dependent on the view. The view must be dropped before any objects that it is dependent on can be dropped."

      This isn't quite true. If a column is dropped with the CASCADE option, any dependent view(s) will be dropped. Are there other cases?

      1. DERBY-5591.diff
        1 kB
        Kim Haase
      2. repro.sh
        0.8 kB
        Dag H. Wanvik
      3. rrefsqlj61102.html
        5 kB
        Kim Haase

        Issue Links

          Activity

          Hide
          Dag H. Wanvik added a comment -

          Linking to DERBY-5567, which popped this issue.

          Show
          Dag H. Wanvik added a comment - Linking to DERBY-5567 , which popped this issue.
          Hide
          Dag H. Wanvik added a comment -

          Btw, the view will get dropped if privileges to objects it depends on are revoked, too, cf REVOKE statement doc, http://db.apache.org/derby/docs/10.8/ref/rrefsqljrevoke.html.
          Of course, REVOKE isn't strictly "dropping", but DROP ROLE will lead to privilege revocation, so DROP ROLE is another example negating the sentence in question.

          Show
          Dag H. Wanvik added a comment - Btw, the view will get dropped if privileges to objects it depends on are revoked, too, cf REVOKE statement doc, http://db.apache.org/derby/docs/10.8/ref/rrefsqljrevoke.html . Of course, REVOKE isn't strictly "dropping", but DROP ROLE will lead to privilege revocation, so DROP ROLE is another example negating the sentence in question.
          Hide
          Kim Haase added a comment -

          Would the following statements be more precise or am I misunderstanding this?

          Normally, you must drop a view before you drop any objects that the view depends on.

          However, if you issue an ALTER TABLE DROP COLUMN command with the CASCADE option, any views that depend on the column will be dropped. Also, if you use a REVOKE or DROP ROLE statement to revoke privileges on objects that a view depends on, the view will be dropped.

          Show
          Kim Haase added a comment - Would the following statements be more precise or am I misunderstanding this? Normally, you must drop a view before you drop any objects that the view depends on. However, if you issue an ALTER TABLE DROP COLUMN command with the CASCADE option, any views that depend on the column will be dropped. Also, if you use a REVOKE or DROP ROLE statement to revoke privileges on objects that a view depends on, the view will be dropped.
          Hide
          Dag H. Wanvik added a comment -

          Thanks, Kim. I found permissions revocation doesn't only affect the roles actions mention, but also plain revoke, cf. the repro attched ("repro.sh"):
          Running it gives this result:

          $ bash repro.sh
          ij version 10.9
          ij> connect 'jdbc:derby:wombat;create=true;user=dag;password=wanvik' as c1;
          ij> call syscs_util.syscs_create_user('DAG', 'wanvik');
          0 rows inserted/updated/deleted
          ij> call syscs_util.syscs_create_user('DONALD', 'duck');
          0 rows inserted/updated/deleted
          ij> call syscs_util.syscs_set_database_property('derby.database.sqlAuthorization', 'true');
          0 rows inserted/updated/deleted
          ij> disconnect c1;
          ij> connect 'jdbc:derby:wombat;shutdown=true';
          ERROR 08006: Database 'wombat' shutdown.
          ij> connect 'jdbc:derby:wombat;user=dag;password=wanvik' as c1;
          ij> create table t ( i int);
          0 rows inserted/updated/deleted
          ij> insert into t values 1,2,3;
          3 rows inserted/updated/deleted
          ij> grant select on t to donald;
          0 rows inserted/updated/deleted
          ij> connect 'jdbc:derby:wombat;user=donald;password=duck' as c2;
          ij(C2)> create table dummy ( i int);
          0 rows inserted/updated/deleted
          ij(C2)> create view myview as select i from dag.t;
          0 rows inserted/updated/deleted
          ij(C2)> select * from myview;
          I
          -----------
          1
          2
          3

          3 rows selected
          ij(C2)> set connection c1;
          ij(C1)> revoke select on t from donald;
          0 rows inserted/updated/deleted
          WARNING 01501: The view MYVIEW has been dropped.
          ij(C1)> set connection c2;
          ij(C2)> select * from myview;
          ERROR 42X05: Table/View 'MYVIEW' does not exist.
          ij(C2)> exit;

          Show
          Dag H. Wanvik added a comment - Thanks, Kim. I found permissions revocation doesn't only affect the roles actions mention, but also plain revoke, cf. the repro attched ("repro.sh"): Running it gives this result: $ bash repro.sh ij version 10.9 ij> connect 'jdbc:derby:wombat;create=true;user=dag;password=wanvik' as c1; ij> call syscs_util.syscs_create_user('DAG', 'wanvik'); 0 rows inserted/updated/deleted ij> call syscs_util.syscs_create_user('DONALD', 'duck'); 0 rows inserted/updated/deleted ij> call syscs_util.syscs_set_database_property('derby.database.sqlAuthorization', 'true'); 0 rows inserted/updated/deleted ij> disconnect c1; ij> connect 'jdbc:derby:wombat;shutdown=true'; ERROR 08006: Database 'wombat' shutdown. ij> connect 'jdbc:derby:wombat;user=dag;password=wanvik' as c1; ij> create table t ( i int); 0 rows inserted/updated/deleted ij> insert into t values 1,2,3; 3 rows inserted/updated/deleted ij> grant select on t to donald; 0 rows inserted/updated/deleted ij> connect 'jdbc:derby:wombat;user=donald;password=duck' as c2; ij(C2)> create table dummy ( i int); 0 rows inserted/updated/deleted ij(C2)> create view myview as select i from dag.t; 0 rows inserted/updated/deleted ij(C2)> select * from myview; I ----------- 1 2 3 3 rows selected ij(C2)> set connection c1; ij(C1)> revoke select on t from donald; 0 rows inserted/updated/deleted WARNING 01501: The view MYVIEW has been dropped. ij(C1)> set connection c2; ij(C2)> select * from myview; ERROR 42X05: Table/View 'MYVIEW' does not exist. ij(C2)> exit;
          Hide
          Dag H. Wanvik added a comment -

          This applies even if there are other permissions available that might
          have saved the day (current impl limitation): as long as a dependency
          on a specific permission is lost, the view dies. Cf DERBY-1632.

          But see also DERBY-1631 and DERBY-1782..

          Show
          Dag H. Wanvik added a comment - This applies even if there are other permissions available that might have saved the day (current impl limitation): as long as a dependency on a specific permission is lost, the view dies. Cf DERBY-1632 . But see also DERBY-1631 and DERBY-1782 ..
          Hide
          Kim Haase added a comment -

          Thanks, Dag! I think we say in the REVOKE statement topic that "This applies even if there are other permissions available that might have saved the day", or words to that effect.

          Would it be clearer to draw out REVOKE and DROP ROLE into two separate sentences, like this?

          "...Also, if you use a REVOKE statement to revoke privileges on objects that a view depends on, the view will be dropped. Similarly, if you use a DROP ROLE statement to drop a role that has privileges on objects that a view depends on, the view will be dropped."

          That seems to be the case in the following variation on your script:

          jdench 158 =>java -jar /export/home/chaase/javadbmore/codetrunk/trunk/jars/insane/derbyrun.jar ij
          ij version 10.9
          ij> connect 'jdbc:derby:wombat;create=true;user=dag;password=wanvik' as c1;
          ij> call syscs_util.syscs_create_user('DAG', 'wanvik');
          0 rows inserted/updated/deleted
          ij> call syscs_util.syscs_create_user('DONALD', 'duck');
          0 rows inserted/updated/deleted
          ij> call syscs_util.syscs_set_database_property('derby.database.sqlAuthorization', 'true');
          0 rows inserted/updated/deleted
          ij> disconnect c1;
          ij> connect 'jdbc:derby:wombat;shutdown=true';
          ERROR 08006: Database 'wombat' shutdown.
          ij> connect 'jdbc:derby:wombat;user=dag;password=wanvik' as c1;
          ij> create role quacker;
          0 rows inserted/updated/deleted
          ij> create table t ( i int);
          0 rows inserted/updated/deleted
          ij> insert into t values 1,2,3;
          3 rows inserted/updated/deleted
          ij> grant quacker to donald;
          0 rows inserted/updated/deleted
          ij> grant select on t to quacker;
          0 rows inserted/updated/deleted
          ij> connect 'jdbc:derby:wombat;user=donald;password=duck' as c2;
          ij(C2)> create table dummy ( i int);
          0 rows inserted/updated/deleted
          ij(C2)> set role quacker;
          0 rows inserted/updated/deleted
          ij(C2)> create view myview as select i from dag.t;
          0 rows inserted/updated/deleted
          ij(C2)> select * from myview;
          I
          -----------
          1
          2
          3

          3 rows selected
          ij(C2)> set connection c1;
          ij(C1)> drop role quacker;
          0 rows inserted/updated/deleted
          WARNING 01501: The view MYVIEW has been dropped.
          ij(C1)> set connection c2;
          ij(C2)> select * from myview;
          ERROR 42X05: Table/View 'MYVIEW' does not exist.
          ij(C2)> exit;

          Show
          Kim Haase added a comment - Thanks, Dag! I think we say in the REVOKE statement topic that "This applies even if there are other permissions available that might have saved the day", or words to that effect. Would it be clearer to draw out REVOKE and DROP ROLE into two separate sentences, like this? "...Also, if you use a REVOKE statement to revoke privileges on objects that a view depends on, the view will be dropped. Similarly, if you use a DROP ROLE statement to drop a role that has privileges on objects that a view depends on, the view will be dropped." That seems to be the case in the following variation on your script: jdench 158 =>java -jar /export/home/chaase/javadbmore/codetrunk/trunk/jars/insane/derbyrun.jar ij ij version 10.9 ij> connect 'jdbc:derby:wombat;create=true;user=dag;password=wanvik' as c1; ij> call syscs_util.syscs_create_user('DAG', 'wanvik'); 0 rows inserted/updated/deleted ij> call syscs_util.syscs_create_user('DONALD', 'duck'); 0 rows inserted/updated/deleted ij> call syscs_util.syscs_set_database_property('derby.database.sqlAuthorization', 'true'); 0 rows inserted/updated/deleted ij> disconnect c1; ij> connect 'jdbc:derby:wombat;shutdown=true'; ERROR 08006: Database 'wombat' shutdown. ij> connect 'jdbc:derby:wombat;user=dag;password=wanvik' as c1; ij> create role quacker; 0 rows inserted/updated/deleted ij> create table t ( i int); 0 rows inserted/updated/deleted ij> insert into t values 1,2,3; 3 rows inserted/updated/deleted ij> grant quacker to donald; 0 rows inserted/updated/deleted ij> grant select on t to quacker; 0 rows inserted/updated/deleted ij> connect 'jdbc:derby:wombat;user=donald;password=duck' as c2; ij(C2)> create table dummy ( i int); 0 rows inserted/updated/deleted ij(C2)> set role quacker; 0 rows inserted/updated/deleted ij(C2)> create view myview as select i from dag.t; 0 rows inserted/updated/deleted ij(C2)> select * from myview; I ----------- 1 2 3 3 rows selected ij(C2)> set connection c1; ij(C1)> drop role quacker; 0 rows inserted/updated/deleted WARNING 01501: The view MYVIEW has been dropped. ij(C1)> set connection c2; ij(C2)> select * from myview; ERROR 42X05: Table/View 'MYVIEW' does not exist. ij(C2)> exit;
          Hide
          Dag H. Wanvik added a comment - - edited

          Thanks, Kim! This makes it clear +1

          Show
          Dag H. Wanvik added a comment - - edited Thanks, Kim! This makes it clear +1
          Hide
          Kim Haase added a comment -

          Attaching DERBY-5591.diff and rrefsqlj61102.html, with modifications to the DROP VIEW topic.

          M src/ref/rrefsqlj61102.dita

          Hope this captures the situation – further edits are welcome.

          Show
          Kim Haase added a comment - Attaching DERBY-5591 .diff and rrefsqlj61102.html, with modifications to the DROP VIEW topic. M src/ref/rrefsqlj61102.dita Hope this captures the situation – further edits are welcome.
          Hide
          Dag H. Wanvik added a comment -

          +1, thanks!

          Show
          Dag H. Wanvik added a comment - +1, thanks!
          Hide
          Kim Haase added a comment -

          Thank you, Dag!

          Committed patch DERBY-5591.diff to documentation trunk at revision 1339595.

          Show
          Kim Haase added a comment - Thank you, Dag! Committed patch DERBY-5591 .diff to documentation trunk at revision 1339595.
          Hide
          Kim Haase added a comment -

          Changes have appeared in Latest Alpha Manuals.

          Show
          Kim Haase added a comment - Changes have appeared in Latest Alpha Manuals.

            People

            • Assignee:
              Kim Haase
              Reporter:
              Dag H. Wanvik
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development