date:20170427

[jira] [Commented] (TRAFODION-2599) Restrict who can do EXPLAIN

2017-04-27 Thread Roberta Marton (JIRA)


[ 
https://issues.apache.org/jira/browse/TRAFODION-2599?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15987867#comment-15987867
 ] 

Roberta Marton commented on TRAFODION-2599:
---

EXPLAIN needs to have similar privileges as SHOWDDL and SHOW STATISTICS.  
Today, we have a component privilege called SHOW.  If granted SHOW, then the 
user can do both SHOWDDL and SHOW STATS.  We should still allow EXPLAIN if you 
have SELECT on all objects in the query.
 
The OSIM support has similar requirements.  To use OSIM, you need SHOWDDL and 
SHOW STATS privilege.  Whatever we choose to do for OSIM we should do for SHOW 
STATS.

> Restrict who can do EXPLAIN
> ---
>
> Key: TRAFODION-2599
> URL: https://issues.apache.org/jira/browse/TRAFODION-2599
> Project: Apache Trafodion
>  Issue Type: Improvement
>  Components: sql-cmp, sql-security
>Affects Versions: any
>Reporter: David Wayne Birdsall
>
> JIRA TRAFODION-2294 will fix a security hole in EXPLAIN: One can do an 
> EXPLAIN of a query, then execute the query because EXPLAIN places the 
> compiled plan in the query cache. Executing the query finds the cached plan 
> which bypasses the query cache.
> With the fix to that JIRA, anyone will still be able to do an EXPLAIN, but 
> privileges will always be checked before actually executing the query.
> But it is fair to ask: Should anyone be able to do EXPLAIN? An advantage of 
> the current situation is that a performance analyst can look at query plans 
> without having access to the data. But query plans do contain some 
> statistical data which may make a determined hacker able to deduce things 
> about the underlying data which they cannot directly see.
> So, perhaps the ability to do EXPLAIN should itself be a privileged 
> operation. Perhaps there should be a separate EXPLAIN privilege, either a 
> global privilege or perhaps on individual tables. A person would be able to 
> do EXPLAIN if they hold that privilege or if they hold SELECT privilege on 
> the underlying tables.



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)

[jira] [Updated] (TRAFODION-2599) Restrict who can do EXPLAIN

2017-04-27 Thread Roberta Marton (JIRA)


 [ 
https://issues.apache.org/jira/browse/TRAFODION-2599?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Roberta Marton updated TRAFODION-2599:
--
Component/s: sql-security

> Restrict who can do EXPLAIN
> ---
>
> Key: TRAFODION-2599
> URL: https://issues.apache.org/jira/browse/TRAFODION-2599
> Project: Apache Trafodion
>  Issue Type: Improvement
>  Components: sql-cmp, sql-security
>Affects Versions: any
>Reporter: David Wayne Birdsall
>
> JIRA TRAFODION-2294 will fix a security hole in EXPLAIN: One can do an 
> EXPLAIN of a query, then execute the query because EXPLAIN places the 
> compiled plan in the query cache. Executing the query finds the cached plan 
> which bypasses the query cache.
> With the fix to that JIRA, anyone will still be able to do an EXPLAIN, but 
> privileges will always be checked before actually executing the query.
> But it is fair to ask: Should anyone be able to do EXPLAIN? An advantage of 
> the current situation is that a performance analyst can look at query plans 
> without having access to the data. But query plans do contain some 
> statistical data which may make a determined hacker able to deduce things 
> about the underlying data which they cannot directly see.
> So, perhaps the ability to do EXPLAIN should itself be a privileged 
> operation. Perhaps there should be a separate EXPLAIN privilege, either a 
> global privilege or perhaps on individual tables. A person would be able to 
> do EXPLAIN if they hold that privilege or if they hold SELECT privilege on 
> the underlying tables.



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)

[jira] [Commented] (TRAFODION-2294) need add privilege checking for explain statement

2017-04-27 Thread ASF GitHub Bot (JIRA)


[ 
https://issues.apache.org/jira/browse/TRAFODION-2294?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15987755#comment-15987755
 ] 

ASF GitHub Bot commented on TRAFODION-2294:
---

Github user DaveBirdsall commented on a diff in the pull request:


https://github.com/apache/incubator-trafodion/pull/1074#discussion_r113809950
  
--- Diff: core/sql/arkcmp/CmpStatement.cpp ---
@@ -1072,7 +1082,8 @@ CmpStatement::process(const CmpMessageDDLwithStatus 
)
 odbcProcess,
 noTextCache,
 aqrPrepare,
-standaloneQuery))
+standaloneQuery,
+doNotCachePlan))
--- End diff --

Yes. In the separate compiler case, the CmpCompileInfo object is sent to 
the tdm_arkcmp process. In the embedded case, it is passed to 
CmpContext::compileDirect. Either way, the bit travels to CmpStatement::process 
and ultimately down to CmpMain::sqlcomp. I've unit tested both paths.


> need add privilege checking for explain statement
> -
>
> Key: TRAFODION-2294
> URL: https://issues.apache.org/jira/browse/TRAFODION-2294
> Project: Apache Trafodion
>  Issue Type: Bug
>  Components: sql-security
> Environment: CDH5.4.8
> Centos6.7
>Reporter: Gao, Rui-Xian
>Assignee: David Wayne Birdsall
>
> we don't have privilege checking for 'explain' statements.If a user doesn't 
> have privilege to execute a statement, then after explain, the user will be 
> able to execute the statement.
> Testuser1:
> SQL>create schema testsch1;
>  
> --- SQL operation complete.
>  
> SQL>set schema testsch1;
>  
> --- SQL operation complete.
>  
> SQL>create table tab1(a int, b int);
>  
> --- SQL operation complete.
>  
> SQL>insert into tab1 values(1,1);
>  
> --- 1 row(s) inserted.
>  
> SQL>select * from tab1;
>  
> A   B
> --- ---
>   1   1
>  
> --- 1 row(s) selected.
>  
> Tesuser2:
> SQL>set schema testsch1;
>  
> --- SQL operation complete.
>  
> SQL>get tables;
>  
> Tables in Schema TRAFODION.TESTSCH1
> ===
>  
> SB_HISTOGRAMS
> SB_HISTOGRAM_INTERVALS
> SB_PERSISTENT_SAMPLES
> TAB1
>  
> --- SQL operation complete.
>  
> SQL>select * from tab1;
>  
> *** ERROR[4481] The user does not have SELECT privilege on table or view 
> TRAFODION.TESTSCH1.TAB1. [2016-10-18 09:07:32]
>  
> SQL>explain options 'f' select * from tab1;
>  
>  
> LC   RC   OP   OPERATOR  OPT   DESCRIPTION   CARD
>          -
>  
> 1.2root  1.00E+002
> ..1trafodion_scan  TAB1  1.00E+002
>  
> --- SQL operation complete.
>  
> SQL>select * from tab1;
>  
> A   B
> --- ---
>   1   1
>  
> --- 1 row(s) selected.
>  
> SQL>insert into tab1 values(20,20);
>  
> *** ERROR[4481] The user does not have INSERT privilege on table or view 
> TRAFODION.TESTSCH1.TAB1. [2016-10-18 09:11:03]
>  
> SQL>explain options 'f' insert into tab1 values(20,20);
>  
>  
> LC   RC   OP   OPERATOR  OPT   DESCRIPTION   CARD
>          -
>  
> 1.2root  o   1.00E+000
> ..1trafodion_insertTAB1  1.00E+000
>  
> --- SQL operation complete.
>  
> SQL>insert into tab1 values(20,20);
>  
> --- 1 row(s) inserted.
>  
> SQL>select * from tab1;
>  
> A   B
> --- ---
>  20  20
>   1   1
>  
> --- 2 row(s) selected.
>  
> SQL>showddl tab1;
>  
>  
> CREATE TABLE TRAFODION.TESTSCH1.TAB1
>   (
> AINT DEFAULT NULL SERIALIZED
>   , BINT DEFAULT NULL SERIALIZED
>   )
> ;
>  
> -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON 
> TRAFODION.TESTSCH1.TAB1 TO TESTUSER1 WITH GRANT OPTION;
>  
> --- SQL operation complete.
>  



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)

[jira] [Commented] (TRAFODION-2294) need add privilege checking for explain statement

2017-04-27 Thread ASF GitHub Bot (JIRA)


[ 
https://issues.apache.org/jira/browse/TRAFODION-2294?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15987674#comment-15987674
 ] 

ASF GitHub Bot commented on TRAFODION-2294:
---

Github user sandhyasun commented on a diff in the pull request:


https://github.com/apache/incubator-trafodion/pull/1074#discussion_r113801793
  
--- Diff: core/sql/arkcmp/CmpStatement.cpp ---
@@ -1072,7 +1082,8 @@ CmpStatement::process(const CmpMessageDDLwithStatus 
)
 odbcProcess,
 noTextCache,
 aqrPrepare,
-standaloneQuery))
+standaloneQuery,
+doNotCachePlan))
--- End diff --

Have you fixed the code paths for both embedded compiling and separate 
compiler process compilation ? 


> need add privilege checking for explain statement
> -
>
> Key: TRAFODION-2294
> URL: https://issues.apache.org/jira/browse/TRAFODION-2294
> Project: Apache Trafodion
>  Issue Type: Bug
>  Components: sql-security
> Environment: CDH5.4.8
> Centos6.7
>Reporter: Gao, Rui-Xian
>Assignee: David Wayne Birdsall
>
> we don't have privilege checking for 'explain' statements.If a user doesn't 
> have privilege to execute a statement, then after explain, the user will be 
> able to execute the statement.
> Testuser1:
> SQL>create schema testsch1;
>  
> --- SQL operation complete.
>  
> SQL>set schema testsch1;
>  
> --- SQL operation complete.
>  
> SQL>create table tab1(a int, b int);
>  
> --- SQL operation complete.
>  
> SQL>insert into tab1 values(1,1);
>  
> --- 1 row(s) inserted.
>  
> SQL>select * from tab1;
>  
> A   B
> --- ---
>   1   1
>  
> --- 1 row(s) selected.
>  
> Tesuser2:
> SQL>set schema testsch1;
>  
> --- SQL operation complete.
>  
> SQL>get tables;
>  
> Tables in Schema TRAFODION.TESTSCH1
> ===
>  
> SB_HISTOGRAMS
> SB_HISTOGRAM_INTERVALS
> SB_PERSISTENT_SAMPLES
> TAB1
>  
> --- SQL operation complete.
>  
> SQL>select * from tab1;
>  
> *** ERROR[4481] The user does not have SELECT privilege on table or view 
> TRAFODION.TESTSCH1.TAB1. [2016-10-18 09:07:32]
>  
> SQL>explain options 'f' select * from tab1;
>  
>  
> LC   RC   OP   OPERATOR  OPT   DESCRIPTION   CARD
>          -
>  
> 1.2root  1.00E+002
> ..1trafodion_scan  TAB1  1.00E+002
>  
> --- SQL operation complete.
>  
> SQL>select * from tab1;
>  
> A   B
> --- ---
>   1   1
>  
> --- 1 row(s) selected.
>  
> SQL>insert into tab1 values(20,20);
>  
> *** ERROR[4481] The user does not have INSERT privilege on table or view 
> TRAFODION.TESTSCH1.TAB1. [2016-10-18 09:11:03]
>  
> SQL>explain options 'f' insert into tab1 values(20,20);
>  
>  
> LC   RC   OP   OPERATOR  OPT   DESCRIPTION   CARD
>          -
>  
> 1.2root  o   1.00E+000
> ..1trafodion_insertTAB1  1.00E+000
>  
> --- SQL operation complete.
>  
> SQL>insert into tab1 values(20,20);
>  
> --- 1 row(s) inserted.
>  
> SQL>select * from tab1;
>  
> A   B
> --- ---
>  20  20
>   1   1
>  
> --- 2 row(s) selected.
>  
> SQL>showddl tab1;
>  
>  
> CREATE TABLE TRAFODION.TESTSCH1.TAB1
>   (
> AINT DEFAULT NULL SERIALIZED
>   , BINT DEFAULT NULL SERIALIZED
>   )
> ;
>  
> -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON 
> TRAFODION.TESTSCH1.TAB1 TO TESTUSER1 WITH GRANT OPTION;
>  
> --- SQL operation complete.
>  



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)

[jira] [Commented] (TRAFODION-2598) Insert Select to/from Trafodion tables containing LOB columns

2017-04-27 Thread ASF GitHub Bot (JIRA)


[ 
https://issues.apache.org/jira/browse/TRAFODION-2598?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15987647#comment-15987647
 ] 

ASF GitHub Bot commented on TRAFODION-2598:
---

Github user DaveBirdsall commented on a diff in the pull request:


https://github.com/apache/incubator-trafodion/pull/1072#discussion_r113796988
  
--- Diff: core/sql/generator/GenPreCode.cpp ---
@@ -5570,18 +5570,36 @@ RelExpr * HbaseInsert::preCodeGen(Generator * 
generator,
   li->lobSize() = tgtValueId.getType().getPrecision();
  li->lobFsType() = tgtValueId.getType().getFSDatatype();
 
- li->lobNum() = col->lobNum();
+ li->lobNum() = tgtCol->lobNum();
   if ((child1Expr->getOperatorType() == ITM_CONSTANT) && 
   !(((ConstValue *)child1Expr)->isNull()))
-if (li->lobStorageType() != col->lobStorageType())
+{
+if (srcCol->lobStorageType() != 
tgtCol->lobStorageType())
   {
 *CmpCommon::diags() << DgSqlCode(-1432)
-<< 
DgInt0((Int32)li->lobStorageType())
-<< 
DgInt1((Int32)col->lobStorageType())
-<< 
DgString0(col->getColName());
+<< 
DgInt0((Int32)srcCol->lobStorageType())
+<< 
DgInt1((Int32)tgtCol->lobStorageType())
+<< 
DgString0(tgtCol->getColName());
 GenExit();
   }
- li->lobStorageLocation() = col->lobStorageLocation();
+}
+else
+  if ((child1Expr->getOperatorType() == 
ITM_BASECOLUMN)||
+  (child1Expr->getOperatorType() == 
ITM_INDEXCOLUMN))
--- End diff --

Can a LOB column be an index column?


> Insert Select to/from Trafodion tables containing LOB columns
> -
>
> Key: TRAFODION-2598
> URL: https://issues.apache.org/jira/browse/TRAFODION-2598
> Project: Apache Trafodion
>  Issue Type: Bug
>  Components: sql-exe
>Reporter: Sandhya Sundaresan
>Assignee: Sandhya Sundaresan
> Fix For: 2.2-incubating
>
>
> Get insert-select from LOB columns to target tables containing LOB columns to 
> work. Two cases need to be considered:
> 1. LOB columns thatt contain the lob data in HDFS (regular LOB columns)
> 2. LOB columns that contain external LOB data - the data is contained in 
> external LOB files - the Trafodion tables won't contain the LOB data. 
> Restrictions : If a table contains an external LOB column the target table 
> must also contain an external LOB column to be able to do an insert-select. 
> Cannot insert a regular LOB column into an external LOB column in target 
> table and vice versa. 



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)

[jira] [Commented] (TRAFODION-2598) Insert Select to/from Trafodion tables containing LOB columns

2017-04-27 Thread ASF GitHub Bot (JIRA)


[ 
https://issues.apache.org/jira/browse/TRAFODION-2598?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15987646#comment-15987646
 ] 

ASF GitHub Bot commented on TRAFODION-2598:
---

Github user DaveBirdsall commented on a diff in the pull request:


https://github.com/apache/incubator-trafodion/pull/1072#discussion_r113799176
  
--- Diff: core/sql/exp/ExpLOBaccess.cpp ---
@@ -2454,7 +2560,55 @@ Ex_Lob_Error ExLobsOper (
   err = lobPtr->writeDataSimple(source, sourceLen, subOperation, 
retOperLen,
bufferSize , replication , blockSize);
   break;
+case Lob_InsSel:
+  {
+
+ExLob *srcLobPtr;
+Int16 flags;
+Lng32  lobNum;
+Int32 lobType;
+Int64 uid, inDescSyskey, descPartnKey;
+short schNameLen;
+char schName[1024];
--- End diff --

I see several fixed-length char arrays here. Do we have guarantees that 
these won't overrun?


> Insert Select to/from Trafodion tables containing LOB columns
> -
>
> Key: TRAFODION-2598
> URL: https://issues.apache.org/jira/browse/TRAFODION-2598
> Project: Apache Trafodion
>  Issue Type: Bug
>  Components: sql-exe
>Reporter: Sandhya Sundaresan
>Assignee: Sandhya Sundaresan
> Fix For: 2.2-incubating
>
>
> Get insert-select from LOB columns to target tables containing LOB columns to 
> work. Two cases need to be considered:
> 1. LOB columns thatt contain the lob data in HDFS (regular LOB columns)
> 2. LOB columns that contain external LOB data - the data is contained in 
> external LOB files - the Trafodion tables won't contain the LOB data. 
> Restrictions : If a table contains an external LOB column the target table 
> must also contain an external LOB column to be able to do an insert-select. 
> Cannot insert a regular LOB column into an external LOB column in target 
> table and vice versa. 



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)

[jira] [Commented] (TRAFODION-2294) need add privilege checking for explain statement

2017-04-27 Thread ASF GitHub Bot (JIRA)


[ 
https://issues.apache.org/jira/browse/TRAFODION-2294?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15987002#comment-15987002
 ] 

ASF GitHub Bot commented on TRAFODION-2294:
---

GitHub user DaveBirdsall opened a pull request:

https://github.com/apache/incubator-trafodion/pull/1074

[TRAFODION-2294] Fix permissions hole in Explain

This set of changes does the following:

1. Changes EXPLAIN so that it does not cache the query plan of whatever 
query it is explaining. This closes a permissions hole that otherwise allows 
one to execute the query afterward without privilege checking.

2. Extract the SqlParser_Flags_Enum from parser/SqlParserGlobalsCmn.h into 
a new file parser/SqlParserGlobalsEnum.h, to make it easier to use these 
literals in executor code. I changed a few hard-coded constants to their 
associated literals in executor/ExExeUtilExplain.cpp as well.

3. Update privs1/TEST120 regression test to include a test of the hole 
fixed by item 1 above.

You can merge this pull request into a Git repository by running:

$ git pull https://github.com/DaveBirdsall/incubator-trafodion Trafodion2294

Alternatively you can review and apply these changes as the patch at:

https://github.com/apache/incubator-trafodion/pull/1074.patch

To close this pull request, make a commit to your master/trunk branch
with (at least) the following in the commit message:

This closes #1074


commit 9e37f06dcf033b6d95309ff4c401fa73abaf3314
Author: Dave Birdsall 
Date:   2017-04-27T16:43:05Z

[TRAFODION-2294] Fix permissions hole in Explain




> need add privilege checking for explain statement
> -
>
> Key: TRAFODION-2294
> URL: https://issues.apache.org/jira/browse/TRAFODION-2294
> Project: Apache Trafodion
>  Issue Type: Bug
>  Components: sql-security
> Environment: CDH5.4.8
> Centos6.7
>Reporter: Gao, Rui-Xian
>Assignee: David Wayne Birdsall
>
> we don't have privilege checking for 'explain' statements.If a user doesn't 
> have privilege to execute a statement, then after explain, the user will be 
> able to execute the statement.
> Testuser1:
> SQL>create schema testsch1;
>  
> --- SQL operation complete.
>  
> SQL>set schema testsch1;
>  
> --- SQL operation complete.
>  
> SQL>create table tab1(a int, b int);
>  
> --- SQL operation complete.
>  
> SQL>insert into tab1 values(1,1);
>  
> --- 1 row(s) inserted.
>  
> SQL>select * from tab1;
>  
> A   B
> --- ---
>   1   1
>  
> --- 1 row(s) selected.
>  
> Tesuser2:
> SQL>set schema testsch1;
>  
> --- SQL operation complete.
>  
> SQL>get tables;
>  
> Tables in Schema TRAFODION.TESTSCH1
> ===
>  
> SB_HISTOGRAMS
> SB_HISTOGRAM_INTERVALS
> SB_PERSISTENT_SAMPLES
> TAB1
>  
> --- SQL operation complete.
>  
> SQL>select * from tab1;
>  
> *** ERROR[4481] The user does not have SELECT privilege on table or view 
> TRAFODION.TESTSCH1.TAB1. [2016-10-18 09:07:32]
>  
> SQL>explain options 'f' select * from tab1;
>  
>  
> LC   RC   OP   OPERATOR  OPT   DESCRIPTION   CARD
>          -
>  
> 1.2root  1.00E+002
> ..1trafodion_scan  TAB1  1.00E+002
>  
> --- SQL operation complete.
>  
> SQL>select * from tab1;
>  
> A   B
> --- ---
>   1   1
>  
> --- 1 row(s) selected.
>  
> SQL>insert into tab1 values(20,20);
>  
> *** ERROR[4481] The user does not have INSERT privilege on table or view 
> TRAFODION.TESTSCH1.TAB1. [2016-10-18 09:11:03]
>  
> SQL>explain options 'f' insert into tab1 values(20,20);
>  
>  
> LC   RC   OP   OPERATOR  OPT   DESCRIPTION   CARD
>          -
>  
> 1.2root  o   1.00E+000
> ..1trafodion_insertTAB1  1.00E+000
>  
> --- SQL operation complete.
>  
> SQL>insert into tab1 values(20,20);
>  
> --- 1 row(s) inserted.
>  
> SQL>select * from tab1;
>  
> A   B
> --- ---
>  20  20
>   1   1
>  
> --- 2 row(s) selected.
>  
> SQL>showddl tab1;
>  
>  
> CREATE TABLE TRAFODION.TESTSCH1.TAB1
>   (
> AINT DEFAULT NULL SERIALIZED
>   , BINT DEFAULT NULL SERIALIZED
>   )
> ;
>  
> -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON 
> TRAFODION.TESTSCH1.TAB1 TO TESTUSER1 WITH GRANT OPTION;
>  
> --- SQL operation complete.
>  



--
This message was sent by

[jira] [Commented] (TRAFODION-2294) need add privilege checking for explain statement

2017-04-27 Thread David Wayne Birdsall (JIRA)


[ 
https://issues.apache.org/jira/browse/TRAFODION-2294?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15986981#comment-15986981
 ] 

David Wayne Birdsall commented on TRAFODION-2294:
-

I've changed my mind about the fix. I now propose to fix in this way: Explain 
will succeed, but will not cache the plan.

This allows a performance analyst to look at query plans without having direct 
access to the data.

Of course one might argue that "performance analyst access" should be 
controllable via privileges. A separate JIRA TRAFODION-2599 has been written to 
cover that aspect.

> need add privilege checking for explain statement
> -
>
> Key: TRAFODION-2294
> URL: https://issues.apache.org/jira/browse/TRAFODION-2294
> Project: Apache Trafodion
>  Issue Type: Bug
>  Components: sql-security
> Environment: CDH5.4.8
> Centos6.7
>Reporter: Gao, Rui-Xian
>Assignee: David Wayne Birdsall
>
> we don't have privilege checking for 'explain' statements.If a user doesn't 
> have privilege to execute a statement, then after explain, the user will be 
> able to execute the statement.
> Testuser1:
> SQL>create schema testsch1;
>  
> --- SQL operation complete.
>  
> SQL>set schema testsch1;
>  
> --- SQL operation complete.
>  
> SQL>create table tab1(a int, b int);
>  
> --- SQL operation complete.
>  
> SQL>insert into tab1 values(1,1);
>  
> --- 1 row(s) inserted.
>  
> SQL>select * from tab1;
>  
> A   B
> --- ---
>   1   1
>  
> --- 1 row(s) selected.
>  
> Tesuser2:
> SQL>set schema testsch1;
>  
> --- SQL operation complete.
>  
> SQL>get tables;
>  
> Tables in Schema TRAFODION.TESTSCH1
> ===
>  
> SB_HISTOGRAMS
> SB_HISTOGRAM_INTERVALS
> SB_PERSISTENT_SAMPLES
> TAB1
>  
> --- SQL operation complete.
>  
> SQL>select * from tab1;
>  
> *** ERROR[4481] The user does not have SELECT privilege on table or view 
> TRAFODION.TESTSCH1.TAB1. [2016-10-18 09:07:32]
>  
> SQL>explain options 'f' select * from tab1;
>  
>  
> LC   RC   OP   OPERATOR  OPT   DESCRIPTION   CARD
>          -
>  
> 1.2root  1.00E+002
> ..1trafodion_scan  TAB1  1.00E+002
>  
> --- SQL operation complete.
>  
> SQL>select * from tab1;
>  
> A   B
> --- ---
>   1   1
>  
> --- 1 row(s) selected.
>  
> SQL>insert into tab1 values(20,20);
>  
> *** ERROR[4481] The user does not have INSERT privilege on table or view 
> TRAFODION.TESTSCH1.TAB1. [2016-10-18 09:11:03]
>  
> SQL>explain options 'f' insert into tab1 values(20,20);
>  
>  
> LC   RC   OP   OPERATOR  OPT   DESCRIPTION   CARD
>          -
>  
> 1.2root  o   1.00E+000
> ..1trafodion_insertTAB1  1.00E+000
>  
> --- SQL operation complete.
>  
> SQL>insert into tab1 values(20,20);
>  
> --- 1 row(s) inserted.
>  
> SQL>select * from tab1;
>  
> A   B
> --- ---
>  20  20
>   1   1
>  
> --- 2 row(s) selected.
>  
> SQL>showddl tab1;
>  
>  
> CREATE TABLE TRAFODION.TESTSCH1.TAB1
>   (
> AINT DEFAULT NULL SERIALIZED
>   , BINT DEFAULT NULL SERIALIZED
>   )
> ;
>  
> -- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON 
> TRAFODION.TESTSCH1.TAB1 TO TESTUSER1 WITH GRANT OPTION;
>  
> --- SQL operation complete.
>  



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)

[jira] [Commented] (TRAFODION-2550) update sql reference manual about metadata clean up command

2017-04-27 Thread ASF GitHub Bot (JIRA)


[ 
https://issues.apache.org/jira/browse/TRAFODION-2550?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15986846#comment-15986846
 ] 

ASF GitHub Bot commented on TRAFODION-2550:
---

Github user DaveBirdsall commented on a diff in the pull request:


https://github.com/apache/incubator-trafodion/pull/1073#discussion_r113730462
  
--- Diff: docs/sql_reference/src/asciidoc/_chapters/sql_utilities.adoc ---
@@ -48,6 +49,326 @@ to connect to and issue SQL utilities, see the
 {docs-url}/client_installation/index.html[_{project-name} Client 
Installation Guide_].
 
 <<<
+[[cleanup_statement]]
+== CLEANUP Statement
+
+The CLEANUP Statement cleans up objects whose information is inconsistent 
in the metadata. This command can be used to clean up individual objects or all 
objects in a schema. This command can also be used to locate all inconsistent 
objects in the Trafodion metadata and then clean them up.
+
+Inconsistent objects can be cleaned up and removed from the metadata and 
HBase by using one of the following options.
+
+```
+CLEANUP {obj-type} {obj-name} [ ,UID {object-uid} ]
--- End diff --

Reading parser/sqlparser.y, it looks like [ , RETURN DETAILS ] is supported 
for CLEANUP obj-type obj-name  as well but oddly not when [ , UID object-uid ] 
is present. So perhaps modify this to CLEANUP obj-type obj-name [ , UID 
object-uid | , RETURN DETAILS ] ?


> update sql reference manual about metadata clean up command
> ---
>
> Key: TRAFODION-2550
> URL: https://issues.apache.org/jira/browse/TRAFODION-2550
> Project: Apache Trafodion
>  Issue Type: Documentation
>Reporter: liu ming
>Assignee: Liu Yu
>
> please refer to 
> https://cwiki.apache.org/confluence/display/TRAFODION/Metadata+Cleanup 



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)

[jira] [Commented] (TRAFODION-2550) update sql reference manual about metadata clean up command

2017-04-27 Thread ASF GitHub Bot (JIRA)


[ 
https://issues.apache.org/jira/browse/TRAFODION-2550?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15986845#comment-15986845
 ] 

ASF GitHub Bot commented on TRAFODION-2550:
---

Github user DaveBirdsall commented on a diff in the pull request:


https://github.com/apache/incubator-trafodion/pull/1073#discussion_r113556852
  
--- Diff: docs/sql_reference/src/asciidoc/_chapters/sql_utilities.adoc ---
@@ -48,6 +49,326 @@ to connect to and issue SQL utilities, see the
 {docs-url}/client_installation/index.html[_{project-name} Client 
Installation Guide_].
 
 <<<
+[[cleanup_statement]]
+== CLEANUP Statement
+
+The CLEANUP Statement cleans up objects whose information is inconsistent 
in the metadata. This command can be used to clean up individual objects or all 
objects in a schema. This command can also be used to locate all inconsistent 
objects in the Trafodion metadata and then clean them up.
+
+Inconsistent objects can be cleaned up and removed from the metadata and 
HBase by using one of the following options.
+
+```
+CLEANUP {obj-type} {obj-name} [ ,UID {object-uid} ]
--- End diff --

The syntax elements obj-type, obj-name and so on should not have curly 
braces around them. Curly braces indicate to the document formatter that this 
is a variable to be replace by other text. You'll see {project-name} here and 
there for exapmle. That gets replaced by the string 'Trafodion'. 


> update sql reference manual about metadata clean up command
> ---
>
> Key: TRAFODION-2550
> URL: https://issues.apache.org/jira/browse/TRAFODION-2550
> Project: Apache Trafodion
>  Issue Type: Documentation
>Reporter: liu ming
>Assignee: Liu Yu
>
> please refer to 
> https://cwiki.apache.org/confluence/display/TRAFODION/Metadata+Cleanup 



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)

[jira] [Commented] (TRAFODION-2550) update sql reference manual about metadata clean up command

2017-04-27 Thread ASF GitHub Bot (JIRA)


[ 
https://issues.apache.org/jira/browse/TRAFODION-2550?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15986847#comment-15986847
 ] 

ASF GitHub Bot commented on TRAFODION-2550:
---

Github user DaveBirdsall commented on a diff in the pull request:


https://github.com/apache/incubator-trafodion/pull/1073#discussion_r113731513
  
--- Diff: docs/sql_reference/src/asciidoc/_chapters/sql_utilities.adoc ---
@@ -48,6 +49,326 @@ to connect to and issue SQL utilities, see the
 {docs-url}/client_installation/index.html[_{project-name} Client 
Installation Guide_].
 
 <<<
+[[cleanup_statement]]
+== CLEANUP Statement
+
+The CLEANUP Statement cleans up objects whose information is inconsistent 
in the metadata. This command can be used to clean up individual objects or all 
objects in a schema. This command can also be used to locate all inconsistent 
objects in the Trafodion metadata and then clean them up.
+
+Inconsistent objects can be cleaned up and removed from the metadata and 
HBase by using one of the following options.
+
+```
+CLEANUP {obj-type} {obj-name} [ ,UID {object-uid} ]
+
+CLEANUP [ PRIVATE | SHARED ] SCHEMA {schema-name}
+
+CLEANUP UID {object-uid}
+
+CLEANUP METADATA [ , CHECK ] [ , RETURN DETAILS ]
+```
+
+```
+{obj-type} is: TABLE | INDEX | SEQUENCE | OBJECT
+
+{obj-name} is: Name of the object that needs to be cleaned up
+
+{object-uid} is: UID (Unique ID) of the object that is stored in metadata
+
+{schema-name} is: Name of the schema whose contents need to be cleaned up
+```
+
+[[cleanup_syntax]]
+=== Syntax Description of CLEANUP Statement
+
+* `CLEANUP {obj-type} {obj-name} [ , UID {object-uid} ]`
++
+This command will clean up the specified object from all Trafodion 
metadata tables
+and HBase. If an object cannot be found in the metadata but exists in 
HBase,
+then it will be removed from HBase. All dependent objects, such as indexes,
+views, LOBs, internal sequences, and constraints, are also removed from the
+metadata and HBase.
+
+** `obj-type` 
+
+*** If an object type is specified as TABLE, INDEX, or SEQUENCE and that 
object exists
+in the metadata, then a check will be done to make sure that the specified 
{obj-name} is of the specified type. An error is returned if they are not the 
same.
+
+*** If an object type is specified as OBJECT, then any object that matches 
the
+specified name is removed from the metadata and HBase.
+
+** `obj-name` 
++
+Specifies the name of the object to be removed.
+
+** `object-uid` 
++
+If an object exists in the OBJECTS metadata table, then the stored object 
UID is compared with the specified object UID. If they do not match, then 
cleanup is not done. If an object does not exist in the OBJECTS metadata table, 
then all entries matching the specified object UID are removed from the 
metadata tables.
+
+* `CLEANUP [ PRIVATE | SHARED ] SCHEMA {schema-name}`
++
+This command will clean up all objects that are created in the specified 
schema.
+
+* `CLEANUP UID {object-uid}`
++
+This command will remove all entries from all metadata tables that match 
the specified object UID. If the specified object UID exists in the OBJECTS 
metadata table and the object name associated with that UID can be retrieved, 
then that object is also removed from HBase.
+
+* `CLEANUP METADATA [ , CHECK ] [ , RETURN DETAILS ]`
++
+This command will look for inconsistent entries in the metadata, return 
information about them, and clean them up. Multiple checks are done to validate 
metadata consistency.
+
+** `CHECK`
++
+One can check for inconsistent metadata entries without actually cleaning 
them up.
+This can be done by specifying the `CHECK` option. If `CHECK` is 
specified, the following checks will be performed simultaneously, and a summary 
of inconsistent objects belonging to the categories listed below will be 
returned.
+
+*** Orphan Metadata Objects
++
+This check is done to validate that objects that exist in metadata also 
exist in HBase. If the object does not exist in HBase, then it is marked as an 
orphan metadata object.
+
+*** Orphan HBase Objects
++
+This check is done to validate that objects that exist in HBase also exist 
in metadata. If the object does not exist in metadata, then it is marked as an 
orphan HBase object.
+
+*** Inconsistent Object Entries
++
+This check is done to make sure that all entries in the OBJECTS table also 
exist in
+the OBJECTS unique index (OBJECTS_UNIQ_IDX) and that all entries in 
OBJECTS_UNIQ_IDX also exist in the OBJECTS table. It also checks to make sure 
that all entries in the COLUMNS table have a

[jira] [Commented] (TRAFODION-2599) Restrict who can do EXPLAIN

[jira] [Updated] (TRAFODION-2599) Restrict who can do EXPLAIN

[jira] [Commented] (TRAFODION-2294) need add privilege checking for explain statement

[jira] [Commented] (TRAFODION-2294) need add privilege checking for explain statement

[jira] [Commented] (TRAFODION-2598) Insert Select to/from Trafodion tables containing LOB columns

[jira] [Commented] (TRAFODION-2598) Insert Select to/from Trafodion tables containing LOB columns

[jira] [Commented] (TRAFODION-2294) need add privilege checking for explain statement

[jira] [Commented] (TRAFODION-2294) need add privilege checking for explain statement

[jira] [Commented] (TRAFODION-2550) update sql reference manual about metadata clean up command

[jira] [Commented] (TRAFODION-2550) update sql reference manual about metadata clean up command

[jira] [Commented] (TRAFODION-2550) update sql reference manual about metadata clean up command

11 matches

Site Navigation

Mail list logo

Footer information