Clone
 

roberta marton <roberta.marton@hp.com> in Trafodion

Added privilege manager upgrade support

Recently, column level privileges has been delivered which requires new

metadata tables. This delivery changes Trafodion upgrade to create the

new metadata tables in preparation for a future upgrade in release 2.0.

The version number has not changed.

Also removed two obsolete scripts.

Change-Id: I6d604640d76c2be72bab81eabb968874c01fec7a

    • -1051
    • +0
    /sqf/sql/scripts/sqerr73diags
    • -52
    • +148
    /sql/sqlcomp/CmpSeabaseDDLcommon.cpp
    • -56
    • +74
    /sql/sqlcomp/CmpSeabaseDDLupgrade.cpp
    • -134
    • +453
    /sql/sqlcomp/PrivMgrMD.cpp
Fixes for 1450193, 1370754, and performance change

1450193 - GRANT syntax for SHOWDDL output incorrect

1370754 - Objects with delimited names not displayed correctly for SHOWDDL

Made sure names in GRANT statements were qualified correctly and added the

object type, when required. Also changed SHOWDDL commands so they don't

start a tdm_arkcmp to get data details with the except of showddl user and

showddl role.

Performance: changed the default in PrivMgr requests to assume that

authorization is enabled. The PrivMgr code that checks to see if authorization

is enabled causes a new tdm_arkcmp to be started. Since all callers already

check authorization from a cached value before calling PrivMgr, this avoids the

tdm_arkcmp process overhead.

Patch set 2:

Updated copyright

Patch set 3:

Fixed problem in isDelimited method.

Patch set 4:

Fixed another problem in isDelimited method.

Change-Id: I43ff421ddd8e9c508c8fed79a1e2b2c6448456b7

    • -47
    • +47
    /sql/regress/catman1/EXPECTED132
    • -0
    • +10
    /sql/sqlcomp/CmpSeabaseDDLschema.cpp
    • -1
    • +11
    /sql/sqlcomp/CmpSeabaseDDLtable.cpp
Turned on privilege features, reorg'd PrivMgr code

Turned on GRANTED BY, WITH GRANT OPTION, GRANT OPTION FOR clauses

For GRANTED BY -> showddl now displays the GRANTED BY clause when

--> the current user is not the object owner and

--> the current user is not DB ROOT

added object_owner and schema_owner to the SeabaseLibraryDesc

ComTdb.h

Generator.cpp

desc.h

CmpSeabaseDDLtable.cpp ->getSeabaseLibraryDesc

added object owner in calls to PrivMgrPrivileges::getPrivTextForObject

CmpDescribe.cpp

PrivMgrCommands.cpp/.h

PrivMgrPrivileges.cpp/.h

Changed object grant and revoke to store the list of privileges associated

with the object and columns in the PrivMgrPrivileges class.

--> added new methods generateObjectRowList and generateColumnRowList,

changed the destructor to remove these lists, changed code to call

these new methods, and removed extra I/Os

--> removed member trafMetadataLocation_ (it is already stored in parent)

For WITH GRANT OPTION clause at GRANT time:

--> added checks at grant time to:

--> check for potential circular grants (error 1036)

--> added new method getTreeOfGrants to get list of grantors

that have previously granted to the current grantee

For GRANT OPTION FOR clause at REVOKE time:

--> changed error messages returned to be more meaningful

--> moved and activated call checkRevokeRestrict after call to

getAffectedObjects

Added new columns to the COLUMN_PRIVILEGES and SCHEMA_PRIVILEGES tables

to include the object_name, grantor_name, and grantee_name to match

OBJECT_PRIVILEGES, ROLE_USAGES, and COMPONENT_PRIVILEGES;

Reorganized the contents of PrivMgr files:

--> PrivMgr document exists that describes the .h/.cpp structure

--> Added new files PrivMgr.h/PrivMgr.cpp that describes the parent

class for all PrivMgr requests

--> moved existing defines, classes, etc around to match the PrivMgr

document

Fixed a couple of issues:

--> Fixed a bug in initialize authorization where the WGO was not set up

correctly for UDR's

--> Fixed a bug in PrivMgrObject::selectAllWhere where an error condition

was not returned

--> Fixed a bug in seabaseGrantRevoke where the incorrect object type was

sent for views

--> Fixed a bug in update statistics privilege checking that was not

handling HBase tables correctly

Added two regression tests (skipped until catman1 test directory is split up)

--> TEST132 - tests for privilege checking on libraries, populate index,

showddl, invoke, update statistics, and showstats

--> TEST140 - tests for WITH GRANT OPTION and GRANTED by option

Fixed expected result for catman1/TEST137

Change-Id: Iaf523aef763b0bce2101fedae0ee701606c369c7

    • -0
    • +2214
    /sql/regress/catman1/EXPECTED132
    • -12
    • +20
    /sql/regress/catman1/EXPECTED133
    • -19
    • +184
    /sql/regress/catman1/EXPECTED137
    • -55
    • +41
    /sql/regress/catman1/EXPECTED138
    • -0
    • +926
    /sql/regress/catman1/EXPECTED140
    • -0
    • +37
    /sql/regress/catman1/FILTER132
  1. … 37 more files in changeset.
Fix 1450193: User ... plans generated without using histogram stats

The issue occurs because the person compiling the query does not have select

privilege on the HISTOGRAMS table so statistics cannot be retrieved.

We support a special interface to allow metadata tables to be accessed without

privs for operations performed during compilations. However, this interface is

not being used for HISTOGRAMS tables during query compilation. The change is to

use this interface when gathering statistics during a query compilation.

Added exception handler around setting/resetting the parserflag.

Change-Id: I13f9a4bd870b3978e29aa1dad251c24ed400f8ff

Fix 1455585: Error 1234 returned trying to run SQL

If code is refreshed with https://review.trafodion.org/1635 (which adds the

first phase of column level privileges) authorization is enabled, and running

in debug mode; error 1234 is returned and you are not able to drop or

initialize authorization. SQL requests fail.

This delivery fixes the issue. First it allow operations to proceed even if

there are missing privilege manager tables. Second, we have turned off checks

for column level privileges (can be turned on by setting cqd CAT_TEST_BOOL).

Lastly, we do not run the column privileges regression test.

Change-Id: Ic427c4e9413b6c7208313c0e0755ca6aabd8a2cd

    • -1
    • +1
    /sql/regress/tools/runregr_catman1.ksh
Security fixes for 144553, 1414125, and 1393529

1445583: showstats command performance slow with security enabled

Several changes were made to improve performance:

Performance optimization:

NATable.cpp: NATable::setupPrivs

- If the current user is the object owner, then default the privilege bitmap

to object Owner values - no need to call PrivMgr to get privileges

Caching optimization:

We are now caching privmgr metadata tables in compiler cache when the compiler

context is instantiated. This avoids a metadata lookup for these tables.

- Added new methods that return if the table is part of the PrivMgr schema

- Adjusted CmpSeabaseDDL::createMDdescs to include privmgr metadata in the

cached entries

- Adjusted CmpSeabaseDDL::getMDtableInfo to check for privmgr metadata tables

from the cached entries

- Removed obsolete code CmpSeabaseDDL::alterSeabaseDropColumn

- changed CmpSeabaseDDL::getSeabaseTableDesc to check for both system and

privmgr metadata from compiler cache

- added new method CmpSeabaseDDL::getPKeyInfoForTable that returns the

primary key name and UID for a table. This is needed when dropping privmgr

metadata tables

Removed extraneous recompilations of HISTOGRAM structures:

Today, update statistics and showstats are reloading NATable entries

for HISTOGRAM tables on every access. This is because the parserflag

ALLOW_SPECIALTABLETYPE is turned on. When this flag is turned, the compiler

always reloads the cache entries - see code from CmpMain::sqlcomp:

//if using special tables e.g. using index as base table

//select * from table (index_table T018ibc);

//then refresh metadata cache

if(Get_SqlParser_Flags(ALLOW_SPECIALTABLETYPE) &&

CmpCommon::context()->schemaDB_->getNATableDB()->cachingMetaData())

CmpCommon::context()->schemaDB_->getNATableDB()->refreshCacheInThisStatement();

Changed code to not set ALLOW_SPECIALTABLETYPE and ALLOW_PHONYCHARACTERS

parserflags by default. Individual statements are setting these flags as needed.

1414125: User without priv can view data in metadata tables

The problem is that a user with priv cannot view data in metadata tables.

Even when a user had SELECT privilege on a system or privmgr metadata table,

the request failed.

The problem is that parameter 2 sent to CmpDescribeIsAuthorized in

hs_globals.cpp is NULL so SELECT priv is not checked. If the user has SHOW

component privilege, it works. A call was added to getPrivileges for metadata

tables before calling CmpDescribeIsAuthorized.

1393529: Core dump accessing MD table descriptors

When "UPDATE STATISTICS LOG [ON, OFF, CLEAR]" is specified by a non DB__ROOT

user, a core dump occurred. This happens because the isAuthorized check is

performed expecting a NATable structure. This command does not need any

special security checks.

Updated traf_authentication_setup script to support a new installation option

Change-Id: If7dbf3ec66e5beb7d88bda61ef32611401dd97b9

    • -83
    • +275
    /sql/sqlcomp/CmpSeabaseDDLcommon.cpp
    • -32
    • +145
    /sql/sqlcomp/CmpSeabaseDDLtable.cpp
Updated expected results for fullstack2/TEST062

The release version for fullstack2 test TEST062 was incorrect. Updated

expected results.

Change-Id: I82d46babf621cc20329aa0dc5f34e9c2ec38a560

LOAD and UNLOAD privilege check fixes

1437078 - LOAD fails with error 4481 even if user has priv

This problem happens because the table definition cached in NATableCache is

not being refreshed with the new values:

Generally, when a query is compiled and the user does not have privilege(s), a

call to checkPrivileges (called during binding) returns a special privilege

error. After compilation completes, the compiler (CmpMain::sqlcomp) checks to

see if a privilege error occurred. If so, the NATable entry is removed and the

request is recompiled. If a privilege error occurs the second time, the

privilege error is reported and the latest cached NATable structure is retained.

In the case of LOAD, the privilege checks are performed in the generator;

therefore checkPrivileges is not being called, the special privilege error is

not reported and the cached NATable entry is not being refreshed.

The fix moves authorization checks from the generator into the binder -

specifically checkPrivileges. A bindNode method was added to the bulk loader

code to verify privileges. The bindNode, checks to see if the user has the

MANAGE_LOAD privilege. If so, no additional checks are required. If not

bindNode sets up the privilege structure (stoi) and saves it in the binder work

area. Later, checkPrivileges is called and privileges checked as required.

1305015 - User with SELECT and INSERT privs unable to UNLOAD

This problem occurs during the generator phase when privileges are being

checked. When an unload statement is parsed, the parser creates the

ExeUtilHBaseBulkUnload class and set the table name to DUMMY. When the

privilege checks are later performed, the DUMMY table is checked which does not

exist.

The fix moves authorization checks from the generation phase into the binder.

A bindNode method was added to the bulk unload code to verify privileges. The

bindNode code, first checks to see if the user has the MANAGE_LOAD privilege.

If so, no additional checks are required. If not, it grabs the query expression

attached the the ExeUtilHBaseBulkUnLoad class and binds it. Binding the query

expression calls checkPrivileges and reports any violations.

This change requires that the query expression created during parsing be stored

in a new class member.

Other fixes related to load and unload:

While fixing the above issues, a problem was found when trying to load a table

with indexes if the user had MANAGE_LOAD privilege. A check was added to index

code to allow the operation to proceed.

The load code is not checking privileges on the source table

1438896 Internal error during create or replace view

Not found errors can be returned, so the error check was change to look for

STATUS_ERROR only.

Change-Id: I00b08eca6678b9c1a0f84848536de3bc93735853

    • -217
    • +0
    /sql/generator/GenRelExeUtil.cpp
Miscellaneous DDL and security bug fixes

Fixed a testware issue with fullstack2/TEST062 that occurred during

release testing

Bug 1415196 - Alter volatile table add column cores at CmpSeabaseDDL::alterSeabaseTableAddColumn()

Added a check to not allow add or drop column for volatile tables:

- sqlcomp/CmpSeabaseDDLtable.cpp

Bug 1415232 - A failed create view causes a volatile table to disappear

The code to bind a view does not correctly reset the volatile schema in use

session parameter in case of an error. Subsequent calls do not check for

volatile objects.

parser/BindStmtDDL.cpp

Bug 1371265 - should not allow grants to DB__ROOT or current user

Added a check at grant to prevent this

- sqlcomp/PrivMgrPrivileges.cpp

Bug 1392491 - Unavailability of privmgr metadata error is incomplete

If not all the privmgr metadata is available, then a new Compile context

flag called IS_AUTHORIZATION_READY is set. This flag is adjusted when

a new compiler context is started, and when authorization is enabled and

disabled.

When isAuthorizationEnabled is called and authorization is incomplete,

error 1234 is now returned by default.

After coding changes were added, a request to not check all privmgr metadata

table at context startup was requseted - a performance concern. Fix was

changed to check all tables for debug builds but check only one table for

release builds. If the performance problem is fixed, then we can go back

and check for all privmgr tables.

- arkcmp/CmpContext.h

- arkcmp/CmpContext.cpp

- sqlcomp/CmpSeabaseDDLcommon.cpp

- sqlcomp/nadefaults.cpp

Bug 1402009 - DB__ROOT is unable to grant privilege on object in private schema

When DB__ROOT executes a grant or revoke on objects it does not own, need to

change the grantor from DB__ROOT to the object owner. This matches the same

behavior for other DDL operations such as CREATE.

As part of this fix, the GRANTED BY clause is now allowed for GRANT

statements but it won't be complete until LP bug 1414225 is done.

- sqlcomp/CmpSeabaseDDLtable.cpp (seabaseGrantRevoke)

- sqlcomp/PrivMgrCommands.h

- sqlcomp/PrivMgrCommands.cpp

- sqlcomp/PrivMgrPrivileges.h

- sqlcomp/PrivMgrPrivileges.cpp

- sqlcomp/PrivMgrMD.h

Bug 1414125 - User without priv can view data in metadata tables

Fixes were in place for all metadata tables except the privmgr metadata

tables. The priv information was always being set to none in setupPrivInfo

(NATable) and revoking a privilege was not correctly removing privilege

information from object_privileges.

- optimizer/NATable.cpp

- sqlcomp/PrivMgrCommands.cpp

Bug - create library checking privileges when authorization is not enabled

- CmpSeabaseDDLroutine.cpp

Enhanced the sqlci env command:

- alphabetize the output

- add the following information

-- authentication status

-- authorization status

-- external (LDAP) user connected

A new session parameter called SESSION_EXTERNAL_USER_NAME was added to return

the external user name connected.

A new cli request called SQL_EXEC_GetAuthState_Internal was written to return

the authentication & authorization status. Code was also added, but not yet

supported, for auditing status.

Renamed member/methods that use ldap to external

Changed sqlci env command to return new format

- cli/sqlcli.h

- cli/SQLCLIdev.h

- cli/CliExtern.cpp

- cli/Cli.h

- cli/Cli.cpp

- cli/Context.h

- cli/Context.cpp

- qmscommon/QRQueries.cpp

- sqlci/SqlciEnv.h

- sqlci/SqlciEnv.cpp

- regress/fullstack2/EXPECTED062.SB

- regress/funnstack2/DIFF062.KNOWN.SB.OS

Change-Id: I04627435a0e644c6b14bbf6bd8aa1162d81224fb

    • -24
    • +36
    /sql/regress/fullstack2/EXPECTED062.SB
  1. … 13 more files in changeset.
Performance enhancement to avoid HBase file checks

Using hbaseAdmin to check if table exists in Hbase is an expensive call.

When a new compiler context is started, a call is made to see if all the

tables needed for privilege checking is performed. There are currently

5 tables in privmgr metadata, so the hbaseAdmin is called five times.

A change has been made to just check for one privmgr metadata table.

Change-Id: If753f400fb3c51e18bcd2b3ed0edd26aaf300b21

    • -35
    • +20
    /sql/sqlcomp/CmpSeabaseDDLcommon.cpp
Enable authorization by default for regress, plus

Patch 1:

Added TEST138 to catman1 - skipped files

Fixed wording in the traf_authentication_setup script from reviewer comments.

Original delivery:

change 1 - Enable authorization during development regression tests

change 2 – Added support for create schema IF NOT EXISTS and drop schema IF EXISTS

change 3 - Changed traf_authentication_setup script to support a new installation option

change 1 - Enable authorization during development regression tests

Authorization will be enabled during regressions runs

Since regressions run mostly as DB__ROOT, there should be few visible differences.

Developers may see GRANT statements displayed as part of SHOWDDL requests.

This can be controlled by a new CQD:SHOWDDL_DISPLAY_PRIVILEGE_GRANTS

SHOWDDL_DISPLAY_PRIVILEGE_GRANTS

ON - display GRANTS if authorization is enabled

OFF - do not display GRANTS

SYSTEM

if running with SQLMX_REGRESS set, do not display grants

otherwise, display grants

regress/tools/init_sb_regr_sql -- execute initialize authorization

regress/tools/runregr_catman1.ksh -- turn on TEST138

regress/catman1 -- various test and expected files to set the new SHOWDDL CQD

"Initialize authorization, drop;" can be performed to disable authorization

files:

sql/regress/catman1/EXPECTED135

sql/regress/catman1/EXPECTED137

sql/regress/catman1/EXPECTED138

sql/regress/catman1/TEST133

sql/regress/catman1/TEST135

sql/regress/catman1/TEST136

sql/regress/catman1/TEST137

sql/regress/catman1/TEST138

sql/regress/catman1/TEST139

sql/regress/tools/init_sb_regr.sql

sql/regress/tools/runregr_catman1.ksh

sql/sqlcomp/CmpDescribe.cpp

sql/sqlcomp/CmpSeabaseDDLauth.cpp

sql/sqlcomp/DefaultConstants.h

sql/sqlcomp/nadefaults.cpp

change 2: Added support for create schema IF NOT EXISTS and drop schema IF EXISTS

Added support for new schema syntax. Change update stats for HIVE tables to use this syntax

files:

sql/parser/StmtDDLCreate.cpp

sql/parser/StmtDDLCreateSchema.h

sql/parser/StmtDDLDrop.cpp

sql/parser/StmtDDLDropSchema.h

sql/parser/sqlparser.y

sql/sqlcomp/CmpSeabaseDDL.h

sql/sqlcomp/CmpSeabaseDDLcommon.cpp

sql/sqlcomp/CmpSeabaseDDLschema.cpp

sql/ustat/hs_globals.cpp

change 3: Changed traf_authentication_setup script

This file was changed to support a new option "--setup" that only enables authentication

This will be used by the installation script when the customer chooses not to

initialize trafodion.

sqf/sql/scripts/traf_authentication_setup

traf_authentication_setup --help

This script enables or disables security features for Trafodion

Usage: traf_authentication_setup [options]

Options:

--file <loc> Optional location of the OpenLDAP configuration file

--help Prints this message

--off Disables authentication and authorization

--on Enables authentication and authorization

--setup Enables authentication

--status Returns status of authentication enablement

Change-Id: Ia9a66364a6d74955a0833088874e0aaca044eae3

  1. … 10 more files in changeset.
Fixed catman1 TEST137 failure

And some minor changes from a previous review.

Change-Id: I01319b40e3ddf1bdba88c70d5a4b62057698491e

Fixes for security gaps

Fix summary:

1389791 – Create table with 128 character-long schema & table names hangs on HortonWorks

fix 1 - Privilege checks not working for UDRs

fix 2 - QI not working when UDR's are involved

fix 3 - Routines are not being removed from NARoutineDB cache

Code cleanup

Miscellaneous changes

1389791: Create table with 128 character-long schema & table names hangs on HortonWorks

Check to make sure the total name length is not longer than supported value,

see: https://issues.apache.org/jira/browse/HDFS-6055

bin/SqlciErrors.txt - new error message

sqlcomp/CmpCatSqlErrorCodes.h - new error message

sqlcomp/CmpSeabaseDDLmd.h - new literal describing length of generated HBase name

sqlcomp/CmpSeabaseDDLcommon.cpp - new check for maxmum HBase name length

fix 1: privilege checks are not working correctly for UDR's

The method RelRoot::checkPrivileges is called to verify privileges for all object types.

However, some UDR objects checks were skipped because they were not added to the UDR Stoi list.

optimizer/BindItemExpr.cpp - add function to Stoi list

optimizer/BindRelExpr.cpp - add procedures to Stoi List

optimzier/RelMisc.h - signature changes for privilege related work

optimizer/BindRelExpr.cpp - rewrote checkPrivileges

optimizer/NARoutine.h/NARoutineDB.cpp - added method

moveRoutineToDeleteList

fix 2: QI is not working when UDR's are dropped

Code to drop items from NARoutineDB cache was missing.

Code to set security keys for the user in the plan was missing

Code to set objectUIDs in the plan was missing

When security keys were added, they were incorrect

sqlcomp/CmpMain.h (.cpp) - added calls to compare invalidation keys with objects stored in

NARoutineDB cache; if found, then remove item from cache by

calling helper methods in NARoutineDB class.

optimizer/NARoutineDB.h (NARoutine.cpp) - added helper method to remove entries from the cache

free_entries_with_QI_key - based off of similar method for table cache

ComSecurityKey.h (.cpp) - new method to check invalidation keys shared by tables/routines

qiCheckForInvalidObject

optimizer/NATable.cpp - rewrote table invalidation code so it could be shared with routines.

generator/GenUdr.cpp - add the routine's object UID to the query plan

sqlcomp/CmpSeabaseDDLroutine.cpp - code to send invalidations keys during drop routine

common/ComSmallDefs.h - new QI actions for USAGE and REFERENCES

common/ComDistribution.cpp - add EXECUTE as a privilege for QI, also added USAGE and REFERENCES

sqlcomp/PrivMgrPrivileges.cpp - not generating correct security keys

fix 3: Routines were not being removed from NARoutineDB cache

Added new fields to the various routine structures for objectOwnerID, schemaOwnerID, and privInfo.

Set up the correct routineID in various routine structures

At drop time, made sure routine was removed from NARoutineDB cache

comexe/ComTdb.h - added new fields to routine descriptor and TDB

generator/Generator.cpp - new fields for routines

optimizer/NARoutine.h (.cpp) - new fields for routines

removeNARoutine - based off similar method for table cache

optimizer/NARoutine.cpp - added new field to store privilege information in NARoutine,

which also gets security keys needed for query invalidation

sqlcat/desc.h - new fields for routines

sqlcomp/CmpSeabaseDDLtable.cpp - set up new values in NARoutine structure

sqlcomp/CmpSeabaseDDLroutine.cpp - code to remove entries from cache at drop time

Other changes:

sqlcomp/PrivMgrCommand.h (.cpp) - performance change, don't check authorization enabled

sqlcomp/PrivMgrMD.h (.cpp) - performance change, don't check authorization enabled

sqlcomp/PrivMgrDesc.cpp - missing object_type

parser/sqlparser.y - incorrect object type set for grant/revoke on UDRs

ustat/hs_globals.cpp - incorrect error returned

Code cleanup:

cli/Statement.h - remove obsolete code

cli/Statement.cpp - remove obsolete code

common/Collections.h - remove obsolete code

generator/GenRelMisc.cpp - remove obsolete code

optimizer/ItemCache.cpp - remove obsolete code

optimizer/RelCache.cpp - remove obsolete code

optimizer/NARoutine.h - remove obsolete code

optimizer/NARoutine.cpp - remove obsolete code

executor/SqlTableOpenInfo.h - new helper methods to check privileges

sqlcomp/PrivMgrMD.h - new helper methods to check privileges and get text for error

sqlcomp/PrivMgrDefs.h - simplification of code for checkPrivileges method

Change-Id: I981ad7f094b79a25f5e0aca30dedea4601b424ea

    • -274
    • +271
    /sql/optimizer/BindRelExpr.cpp
  1. … 25 more files in changeset.
Fix for LP bugs 1404442 & 1401683

These are duplicate problems.

A QA test suite (arkcase/arkt1112.log) contains some update statistics

tests which are starting a transaction but the transaction is never

ended.

Since the rogue transaction is started in a secondary arkcmp, the master

arkcmp is unaware of this transaction.

What differentiates this from other tests is that QA test turns off

autocommit before performing a series of update statistics.

The fix is to encapsulate a begin/commit work around DDL operations

executed by update statistics.

Change-Id: Ibbed2133e5a79f7393c9ec1b25c884a7dd8b0c3e

Fixed security issues with showstats & update stats

Related to LP bug 1401330

As part of fixing bug 1401330, found two issues:

-- core dump while performing showstats by a non DB__ROOT user

-- an indentity with MANAGE_STATISTICS privilege gets unexpected error

during an update statistics command.

Patch set 1:

Since the authentication check was move, now it needs to

deallocate the EHI structure.

Change-Id: Ib295064e45aec26a3650f712131d6f803203494a

    • -14
    • +15
    /sql/sqlcomp/CmpSeabaseDDLtable.cpp
Skip running catman1/TEST138

Sometimes catman1/TEST138 fails with authorization errors on the

Hortonworks platform. There is no plan to fix this issue for release

9.1 so we are skipping the test. LP bug 1408155 has been created to

track this issue.

Change-Id: If7cfd67ed85be40a581de79a89a9d1dd1bc7c564

    • -2
    • +1
    /sql/regress/tools/runregr_catman1.ksh
Fixes for SQL security

LP bugs fixed:

1392805 – DB_ROOT incorrectly gets “NOT AUTHORIZED” messages

1398546 – revoke priv from role fails when view is present

1401233 – USAGE privilege not checked when creating procedure (and

revoking privileges)

1403995 – Update stats failures due to schema PUBLIC_ACCESS_SCHEMA

1401683 – (Partial) DDLoperations see error 8841 about transaction

started by SQL

Regressions updated:

catman1/TEST135 & EXPECTED138

catman1/EXPECTED138 (fix in common/ComUser.cpp)

Bug descriptions:

1392805:

Changed create view code to allow DB__ROOT to create views. Some

reorganization required to make sure create view sets the updatatable

and insertable privilege correctly. This also fixed the problem where

the incorrect privileges were set when created by DB__ROOT.

Sqlcomp/CmpSeabaseDDLview.cpp

Sqlcomp/PrivMgrPrivileges.h (sets default privileges)

1398546:

The check to see if the "select" privilege is still in existence needed

to be changed until after all the privilege descriptors were analyzed.

Sqlcomp/PrivMgrPrivileges.cpp (gatherViewPrivileges)

Sqlcomp/PrivMgrDesc.h

1401233:

Missing checks at create UDR and revoke USAGE privilege were added.

Sqlcomp/CmpSeabaseDDLroutine.cpp

Sqlcomp/PrivMgrMD (getUdrsThatReferenceLibraries)

Sqlcomp/PrivMgrPrivileges.cpp (dealWithUdrs)

1403995:

This is a critical case QA filed because the PUBLIC_ACCESS_SCHEMA does

not exist for temporary sample tables during Update Statistics. If the

PUBLIC_ACCESS_SCHEMA does not exist, the temporary sample table will be

created in the same schema as the source table. Also fixed an issue for

private schemas not owned by DB__ROOT to make the histogram table's

owner the current user.

ustat/hs_cli.cpp

ustat/hs_globals.cpp

1401683:

There are several 8841 issues being detected. This is a fix for one of

them related to Update Statistics where an embedded "get" statement

causes a transaction to be started in a child tdm_arkcmp process. The

fix is to not automatically start a transaction for the get request.

generator/GenRelScan.cpp

Change-Id: Ied42fdea6c6f8c43f29dab661b06b74f0f07ff99

    • -49
    • +368
    /sql/regress/catman1/EXPECTED135
    • -22
    • +22
    /sql/regress/catman1/EXPECTED138
    • -0
    • +33
    /sql/sqlcomp/CmpSeabaseDDLroutine.cpp
    • -61
    • +66
    /sql/sqlcomp/CmpSeabaseDDLview.cpp
    • -16
    • +90
    /sql/sqlcomp/PrivMgrPrivileges.cpp
Fix for several security/QI bugs

Amended: CmpSeabaseDDLtable to dynamically allocate the qi structure and

updated TEST122 to include a test for QI processing after a drop index

Fix for bug 1396774 & bug 1396746:

When indexes are dropped as part of drop table, cache entries are not

being removed correctly from the compiler process(es)

Fix for BY <owner> create table bug:

When using the undocumented BY <owner> clause for CREATE TABLE and an

unregistered user name is specified, the object owner is incorrectly

set to 0. Once 0, a subsequent drop table fails without giving an error

MANAGE_LIBRARY support:

Require a user to be assigned the MANAGE_LIBRARY component privilege in

order to create a library. This is in addition to other privileges.

Change-Id: I0379b1ab9cd26c334c5a4343577aec27a8d43401

    • -4
    • +115
    /sql/regress/executor/EXPECTED122
    • -0
    • +20
    /sql/sqlcomp/CmpSeabaseDDLroutine.cpp
    • -7
    • +47
    /sql/sqlcomp/CmpSeabaseDDLtable.cpp
Authorization checks for DDL & utilities

Fixed issues from code comments.

LOAD/UNLOAD authorization checks:

Code was added during code generation to make sure user has privileges,

if the user had necessary privileges, then the EXEUTIL parser flag is

turned on to avoid further privilege checks. When load/unload

completes, the parser flag is reset.

Update/showstats Statistics authorization checks:

Added a new error message

Changed hs_globals to support a new isAuthorized method and store

parser flags when class is instantiated and reset them when done

Changed hs_cli.cpp to use new IF NOT EXISTS syntax when creating

histogram tables, make owner of histogram tables DB__ROOT

(will need to adjust when schema privileges happen), and clean up

CreateHistTables method to remove old authorization mechanism

Changed hs_update.cpp which controls the update and showstats operation

to add authorization checks

Purgedata and populate index changes:

Changed CmpSeabaseDDLcommon.cpp to check privileges for purgedata

Changed CmpSeabaseDDLindex.cpp to check privileges for popindex

Additional component privileges and checks:

Added support for new component privileges in PrivMgrMD.h/.cpp

Added support for MANAGE_COMPONENTS

Added support for CREATE_INDEX and DROP_INDEX component privs

Fixes from last delivery that were postponed:

Context.cpp - fix for previous code review

CmpSeabaseDDLtable - added calls to deallocEHI

PrivMgrMD - fixed wording in a comment

Miscellaneous changes:

ComUser - added new convenience method - isRootUserID()

NATable.cpp (setupPrivInfo) to always set up privInfo_ and to call

the embedded compiler while extracting privileges

Privilege adjustments to take advantage of privInfo stored in NATable:

Added code to mark and rewind errors in diags.

Fix for LP bug 1392895

Change-Id: I6f7245ae7e66086769c0e92d901399c99e8f2af3

    • -0
    • +224
    /sql/generator/GenRelExeUtil.cpp
    • -0
    • +174
    /sql/regress/catman1/EXPECTED137
    • -29
    • +128
    /sql/regress/catman1/EXPECTED138
    • -2
    • +39
    /sql/sqlcomp/CmpSeabaseDDLcommon.cpp
  1. … 19 more files in changeset.
Drop view QI fix & fix for LP 1384485

Removed sqlparser.y change - it was not intended to be delivered.

Drop view QI fix:

At drop view time, we need to send a QI message for the view being

dropped and remove base tables referenced by the view from cache.

Added a structure which describes a base table reference

Added code to create a list of base table references by calling

getListOfReferencedTables

getListOfReferencedTables gets the list for the first layer of

referenced objects (getListOfDirectlyReferencedObjects).

If the referenced object is a view, it recursively calls

getListOfReferencedTables

If the referenced object is a table, it gets added to the base

table list

Set up a QI key during drop view

Removed naTable entries for all tables referenced by the view

Updated TEST122 with drop view tests

Fix for LP 1384485:

Alter table allows adding a constraint to a _MD_ table

Reworded error message 1289

Added checks for reserved schema in missing places, mostly

constraints

Change-Id: I258983f2a28199ae522ba72b831f57fd22730a2f

    • -13
    • +517
    /sql/regress/executor/EXPECTED122
    • -10
    • +115
    /sql/regress/executor/TEST122
    • -2
    • +51
    /sql/sqlcomp/CmpSeabaseDDLtable.cpp
    • -0
    • +144
    /sql/sqlcomp/CmpSeabaseDDLview.cpp
Delimited col name fix, and backout of upsert fix

This delivery fixes two launchpad bugs:

1383531: Create table .. like .. store by() does not take delimited

column names. See CmpSeabaseDDLtable.cpp for change.

Details:

When the create table like statement is requested, the create table like

code calls describe to get the description of the source table. After

getting the describe text back for the source table, the create table

like code adds a STORE BY clause. The code to add the STORE BY clause is

not handling delimited column names correctly.

1376835: initialize authorization failing with unique constraint error.

See PrivMgrPrivileges.cpp and PrivMgrRoles.cpp for change.

Details:

Previously delivered a fix to work around this problem (change-Id:

Id701d031ab9b9c2ebdc0584b01a2b5af9fc02b26) which changed the insert

.. selects to upsert .. selects. After this workaround was delivered

the correct fix was released (undo disable txns for DDL change-Id:

Ib37e202b9239305bd1e38e2761b587a4316ee439).

This delivery changes the upsert's back to insert's. It also fixes a

problem with the insertSelect statement when inserting into the

OBJECT_PRIVILEGES table because sequence generators (SG) were not being

initialized properly.

Change-Id: I296c49a446c11f2ec019c6eb7e723538cae79c27

Fix for initialize authorization failure

and reenabled catman1 TEST133:

Initialize authorization creates a set of metadata tables and then loads

data into the OBJECT_PRIVILEGES table to specify object ownership values

It also loads data into the ROLE_USAGE table to specify role ownership

values. Sometimes these insert..selects fail with an error 8102. This

change does not fix the 8102 problem but fixes the code so the insert ..

select succeeds. Bascially the insert was changed to an upsert and

sanity checks placed around calls to make sure the correct number of

rows were processed.

insertSelect code changes:

Added select count(*) from target table to make sure target table empty

Changed insert to an upsert command

After upsert added select count(*) on target table to get rows found

Compare rows in target table with expected rows -> return an error if

not equal.

This fixes the problem. The upsert ignores duplicate rows so we

avoid the 8102 error. The inserted versus expected number of rows make

sure the correct number of rows were processed.

Change-Id: Id701d031ab9b9c2ebdc0584b01a2b5af9fc02b26

    • -1
    • +1
    /sql/regress/tools/runregr_catman1.ksh
    • -12
    • +43
    /sql/sqlcomp/PrivMgrPrivileges.cpp
Security changes to support authorization

Added support for authorization commands:

- initialize authorization [, drop]

- create/drop roles

- register/unregister components

- create/drop component operations

- grant/revoke object privileges

- grant/revoke role privileges

- grant/revoke component privileges

- updates to GET and SHOWDDL statements

- checking of privileges for DML requests

- checking of privileges for DDL requests

- regression tests added to catman1 library

Fixed a testware problem in catman1 TEST135 and TEST139

Fixed a parser problem introduced by compGeneral/TESTTOK2 which was recently

introduced.

More details:

This delivery was part of code worked on by many people for several

months on a remote branch. This team held bi-weekly meetings

for several months to design and implement these features. These

meetings also included extensive code reviews.

The security features which include authentication (which was delivered

in June) and authorization is turned off by default. The

traf_authentication_setup script located in $MY_SQROOT/sql/scripts needs

to be run to enable both authentication and authorization. This

procedure is described on the Trafodion Twiki page and will be updated once this

delivery completed to include authorzation.

Delivery updates:

Updated traf_authentication_setup to return consistent error messages

and added a comment to ComSmallDefs.h to address a buf size issue for

metadata tables.

Change-Id: I896f1ee006590284653b2c9882901c05b5f2ba22

  1. … 86 more files in changeset.
User Managemente Enhancements

This delivery consists user management enhancements as described below.

commit c82e6c05ee2693cd7ac441b2317311c6a9567b42

Author: Roberta Marton <roberta.marton@hp.com>

Date: Thu Jun 19 08:33:08 2014 -0700

Fixed some issues in alter user

Problems fixed:

1. Now unable to alter the user to an external name that does not exist

on the directory server.

2. Now unable to set an existing directory server user account to an

existing database user account.

Updated regression test catman1/TEST136

Removed redundant file catman1/EXPECTED136.SB

Change-Id: I91c0d5b80c79d2617bc22c98d83f3c8babe848eb

commit 22884a0367d18d8a61514550e284b3b1530646bb

Author: Roberta Marton <roberta.marton@hp.com>

Date: Tue Jun 10 10:36:44 2014 -0700

User Management Enhancements, bug fix 1323874

User management enhancements: added support for ALTER USER.

Fixed bug 1323874

ALTER USER syntax:

ALTER USER <database username> SET <set-opt>;

<set-opts> ==> {EXTERNAL NAME <external username> | ONLINE | OFFLINE}

EXTERNAL NAME clause changes the external and database username mapping

OFFLINE marks the user as unavailable - authentications fail

ONLINE marks the user as available - authentications succeed

Added new regression tests to catman1/TEST136 for ALTER USER feature

Bug 1323874: a change was made to only allow the following

characters as part of the external and database usernames:

[A-Za-z0-9]-_@./

Change-Id: I49c0aa5fcda88a1f8328c7cf769a72e82f4a3e25

    • -11
    • +11
    /sql/regress/catman1/DIFF136.KNOWN
    • -18
    • +235
    /sql/regress/catman1/EXPECTED136
    • -674
    • +0
    /sql/regress/catman1/EXPECTED136.SB
    • -229
    • +435
    /sql/sqlcomp/CmpSeabaseDDLauth.cpp
    • -57
    • +72
    /sql/sqlcomp/CmpSeabaseDDLauth.h
    • -0
    • +12
    /sql/sqlcomp/CmpSeabaseDDLcommon.cpp
Code cleanup

Removed unsed files

Change-Id: I579d27cb37857f68ab1ae0884bf255343b87d1f3

Fixed bug 1323922 and added showddl user support

Changes with this commit have been reviewed by the DB Security team

and include the following:

- Fixed 1323922 which was caused by some prototype code added in

anticipation of privilege support. Changed code back to original

- Added support for SHOWDDL USER

Change-Id: I910383b23175c2374bc5630503120e457d0ba4e3

    • -11
    • +11
    /sql/regress/catman1/DIFF136.KNOWN
    • -61
    • +18
    /sql/regress/catman1/EXPECTED136
    • -61
    • +18
    /sql/regress/catman1/EXPECTED136.SB
    • -15
    • +99
    /sql/sqlcomp/CmpSeabaseDDLauth.cpp
    • -137
    • +0
    /sql/sqlcomp/CmpSeabaseDDLcommon.cpp
    • -22
    • +30
    /sql/sqlcomp/CmpSeabaseDDLtable.cpp
Fixed up catman1 regression test suite to remove non Trafodion based operations. Removed changes to sqlparser.y that are not required.

Change-Id: I588e9e040050e60880465463c813d69920b5a38d

    • -737
    • +246
    /sql/regress/catman1/EXPECTED136
    • -10
    • +2
    /sql/regress/catman1/EXPECTED136.SB
    • -227
    • +92
    /sql/regress/tools/runregr_catman1.ksh