Clone Tools
Constraints: committers
Constraints: files
Constraints: dates
Security bug fixes

Corrects a number of bugs in security and SQL DDL.

1. 1439316: Internal error when creating view with sequence.

2. 1441825: User in role-owned schema is not given grant option.

3. 1447328: DB__ROOT required to revoke component privileges

by other users.

4. 1447330: DB__ROOT required to revoke object and

column privileges granted by other users.

In addition, Launchpad bugs 1350627 and 1438886 were tested and

found to no longer be a defect.

Two new component-level privileges were added, MANAGE and


Users who have the MANAGE_PRIVILEGE component-level privilege can

grant and revoke privileges at the schema, object, and column

level on behalf of other users and roles. Users who have the

MANAGE_PRIVILEGE component-level privilege can also grant

"WITH GRANT OPTION" any privilege they have.

A user granted the MANAGE privilege has all the MANAGE_* privileges,



Change-Id: I10873de95f75114b937e1144f3af9aba76884eae

  1. … 7 more files in changeset.
Column-level privileges - part 2

Support for column-level privileges will be in multiple deliveries.

This delivery add the following portions:

1. DML operations (SELECT, INSERT, UPDATE) now recognize granted

column-level privileges.

2. CREATE VIEW now recognizes granted column-level privileges.

3. Revoke of object-level privileges now revokes the corresponding

column-level privilege.

Missing functionality:

1. Privileges can be granted to roles and revoked from roles, but

REVOKE ROLE does not consider column-level privileges when


if an object depends on a role's granted privileges.

2. Column-level revoke does not enforce RESTRICT, i.e., privileges

may be revoked even if there are dependent privileges.

3. ALTER TABLE DROP COLUMN does not remove associated column-level

privileges, nor does it check for dependent objects.

Change-Id: Ieba04c77edb945dfeb1994e9949b54072289465e

  1. … 13 more files in changeset.
Turned on privilege features, reorg'd PrivMgr code


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




CmpSeabaseDDLtable.cpp ->getSeabaseLibraryDesc

added object owner in calls to PrivMgrPrivileges::getPrivTextForObject




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


Added new columns to the COLUMN_PRIVILEGES and SCHEMA_PRIVILEGES tables

to include the object_name, grantor_name, and grantee_name to match


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


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

  1. … 51 more files in changeset.
Fix 1455585: Error 1234 returned trying to run SQL

If code is refreshed with (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. … 2 more files in changeset.
Column-level privileges

Support for column-level privileges will be in multiple deliveries.

This delivery add the following portions:

1. Creation of the metadata table COLUMN_PRIVILEGE.

This table is created when the INITIALIZE AUTHORIZATION command is run.

Existing privileges are preserved, but warnings are issued referring to

existing metadata tables. An UPDATE option will be added later.

2. Granting of column-level privileges

Full support is present for granting column-level privileges.

Privileges can be added and updated for one or more columns on a table or view.

Support for WITH GRANT OPTION is coded, though not enabled until WITH GRANT

OPTION is enabled at the object level.


The SHOWDDL command displays column-level privileges. Regardless of

the order the privileges were granted, SHOWDDL displays them in column

order, and within each column, in the order they appear in the bitmap


4. Revoking of column-level privileges

Only partially implemented. The basic operation of revoking granted

column-level privileges and grant option for is implemented. All

relevant security checks are performed. GRANTED BY is not implemented.

RESTRICT and CASCADE options are not supported. Hence, any dependent

objects remain when column-level privileges are revoked.

Missing functionality

In addition to column-level revoke only be partially implemented,

here are other items not present in this delivery:

1. Privileges can be granted to roles and revoked from roles,

but REVOKE ROLE does not consider column-level privileges when

determining if an object depends on a role's granted privileges.

2. Similarly, revoke at the object level does not consider

column-level privileges that may allow an object to remain after

an object-level privilege is revoked.

3. CREATE VIEW does not consider column-level privileges

when determining if the user has authority on the referenced

tables and views.

4. Run-time DML operations do not considered column-level when

determining if the user has authority to perform the query.

Change-Id: Icd3db88708d1e0ae7e9236e10b2a760bba287155

    • -335
    • +2022
  1. … 17 more files in changeset.
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.


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


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

  1. … 27 more files in changeset.
Support cancel for DDL, update stats, and utils

This change propagates parent query ID to queries created

by DDL, update stats and utils. In this way, preexisting

code sets up child query IDs and the preexisting cancel

broker code in RMS cancels the entire query tree below

the indicated parent. Also added is logic to check for a

canceled parent at the time a child query ID is setup in

Statement::execute, to make the scheme more robust and

to support a common scenario where the parent is compiling

a child query at the time of cancel.

This change also makes ex_root_tcb::cancel to wait for

cancel broker message completion. This fixes a problem where

a query (e.g., CREATE INDEX or DROP INDEX) is prepared,

executed once and gets an error, then re-execed. In this

case, sqlci was not closing the statement after the error.

To improve robustness, after an error we will now wait for

message completion, if needed.

Since we do not yet have transaction protection for DDL,

it will usually be required to cleanup canceled DDL

operations by using the CLEANUP command.

Change-Id: I8940f7108906d5d8d1a8aa4574aacf2b9ffcf0b6

  1. … 27 more files in changeset.
Correct security keys generated for REVOKE ROLE

Launchpad Bug 1392086: DML commands continued to succeed after

REVOKE ROLE command removed privilege.

DML commands will now report an error if a REVOKE ROLE command

results in the user no longer having the requisite DML privilege.

Previously incorrect QI security keys were generated and the

query would succeed until the session was restarted.

Change-Id: I2df4fb922c86e36f8989537d3475385a912b08dc

  1. … 1 more file in changeset.
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


ON - display GRANTS if authorization is enabled

OFF - do not display GRANTS


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

















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











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.


traf_authentication_setup --help

This script enables or disables security features for Trafodion

Usage: traf_authentication_setup [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. … 24 more files in changeset.
SHOWDDL, QUERY Cancel, rework

This delivery addresses security issues with SHOWDDL, adds initial

support for security in query cancel, and implements part of the

proposed GIVE commands.

Bug 1414234: SHOWDDL command now check component privileges.

SHOW is granted to PUBLIC by default, so effectively there are

no new restrictions unless SHOW is revoked from PUBLIC.


SHOWDDL ROLE now checks for MANAGE_ROLES or SHOW privilege.

SHOWDDL SCHEMA now checks for SHOW privilege.

SHOWDDL USER now checks for MANAGE_USERS or SHOW privilege.

SHOWDDL LIBRARY is implemented. A user must have the USAGE

privilege on the library, or the MANAGE_LIBRARY or SHOW privilege.

New function to determine if the user canceling the query has

the authority: either DB__ROOT, or the user owns the query, or

the user has the QUERY_CANCEL privilege. Note, the code is

delivered in an inactive state pending future integration.

Three new component privileges are added: QUERY_ACTIVATE,

QUERY_CANCEL, and QUERY_SUSPEND. These will be added if

authorization is dropped and reinitialized. A future


command that will add these privileges to an existing

instance with authorization enabled.

Support for library objects was added to NATable, but the code

is currently not used. May be integrated into CREATE ROUTINE

and GRANT for libraries in the future.

Also included is minor rework from delivery 1082, and the

GIVE SCHEMA command now updates associated privileges when object

ownership is changed. Note, GIVE commands are still prototype.

A detailed blueprint for GIVE will be released shortly.

This patch merges with changes from 1177 and addresses a couple of

minor comments from the initial submittal.

Change-Id: I60419228f886555ed0e066441bb824c5246ee498

  1. … 28 more files in changeset.
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,


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


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


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

  1. … 39 more files in changeset.
The following Launchpad bugs are fixed in this change:

Bug 1370749: Now using MAX_USERNAME_LEN instead of hardcoded value

Bug 1413760: CREATE TABLE LIKE was failing in some circumstances because

SHOWDDL was including the BY clause. Ownership rules changes in

CREATE TABLE changed when ANSI schemas was implemented, so the BY clause

is no longer needed.

Bug 1392107: Privileges granted on a view are no longer lost if the

view is replaced via CREATE OR REPLACE VIEW.

Bug 1370740: A potential memory corruption problem is now avoided

by reworking the authorization name lookup functions.

Bug 1413767: Previously DROP SCHEMA CASCADE would fail to drop a

table with an IDENTITY column.

Bug 1413758: Previously DROP TABLE CASCADE did not drop nested views.

Bug 1412891: Previously DROP TABLE CASCADE failed if a dependent object

contained a delimited name.

Changes are present for 1392086, but the work is not yet completed.

This problem is related to roles and security keys.

Code changes are also present for giving ownership of an object to

another authorization ID, but these changes are not complete. A

description of

the changes is included.

The GIVE command transfers ownership of a SQL item from one

authorization ID to another. Implemented in this delivery is


GIVE ALL transfers all SQL items owned by an authorization ID to another

authorization ID. Current or new owner can be a user or a role. The

GIVE ALL command requires the ALTER privilege.


GIVE SCHEMA behavior depends on the type of schema and whether RESTRICT

or CASCADE is specified. For private schemas, all the objects in the

schema are given, as well as the schema itself. For shared schemas,

only the

schema is given, unless the CASCADE option is specified. In that case,


of all the objects in the shared schema is given to the new owner. Use


the CASCADE option requires the ALTER_SCHEMA privilege. Otherwise, GIVE

SCHEMA only requires the user to be the owner of the schema.


NOTE: RESTRICT and CASCADE are not applicable to private schemas and are


GIVE OBJECT is added to the syntax but is not implemented and may not

be implemented.

A more detailed blueprint will be provided prior to the final delivery

of GIVE.

Change-Id: I7449da599dc80de1c0659164e684841cda4647c8

  1. … 34 more files in changeset.
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:


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/PrivMgrPrivileges.h (sets default privileges)


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)



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


Sqlcomp/PrivMgrMD (getUdrsThatReferenceLibraries)

Sqlcomp/PrivMgrPrivileges.cpp (dealWithUdrs)


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.




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.


Change-Id: Ied42fdea6c6f8c43f29dab661b06b74f0f07ff99

  1. … 14 more files in changeset.
ANSI Schema changes

ANSI Schema

Implements the changes to support ANSI schemas. For more information

see the blueprint at:

The syntax changes for REGISTER USER and CREATE ROLE were not

implemented in this delivery.

NOTE: This code was reviewed internally prior to merging with the

main branch.

Change-Id: I1c7937dbcd067e792dcacb65f12c43e4f84a25ad

Change-Id: I98395eeef1e8bde424d9e83f96928358f0b1991b

  1. … 75 more files in changeset.
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

  1. … 33 more files in changeset.
Query Invalidation triggered by DDL, phase 1

This first check-in implements most of the framework which will

be used to complete the QI DDL feature. It redefines the old

security invalidation key (SQL_SIKEY) to handle DDL operations in

addition to REVOKE. In a limited number of DDL operations, the object

UIDs of affected Seabase objects are propagated to all nodes for

use by the compiler to invalidate NATable cache entries, as

well as a limited number of types of cached queries. Later this

month, the framework will be complete by allowing prepared queries

that have already been returned from the compiler to be invalidated.

Then the next step for the framework will be support for invalidating

the HTable cache. Finally an effort will be made to cover all of

the necessary DDL operations and all types of cached queries.

The check-in include a new regression test (executor/TEST122) that

demonstrates the cases that are covered. Specifically, a table will

be dropped and recreated with the same name but different definition

in one sqlci session. In another session, which has already populated

NATable cache and query cache for INSERT, UPDATE, DELETE, SELECT,

SELECT COUNT(*), INVOKE and SHOWDDL statements, those some types

of statements will be resubmitted and correctly compiled.

Change-Id: Ie61ce751089b57ce1894f1764c338e9400bb7b8a

Closes-Bug: #1329358

Implements: blueprint ddl-query-invalidation

  1. … 41 more files in changeset.
Set authorization enabled/Sequence generator privs

Code to set authorization enabled at startup

Contains changes to check authorization at process startup time and

code review comments from previous deliveries

Description of changes to check authorization at process startup time:

At process && compiler context startup time a check has been added to

see if authorization is enabled. Based on this check a new flag is set

in the compiler context.

Any operation wishing to see what the authorization status is, just need

to look at this flag.

This code has been reviewed internally by the security team.

There will be a subsequent set of changes in the PrivMgr code to return

better errors.



Added a new flag containing authorization status and methods that get

and set this flag.


In method: NADefaults::readFromSQLTables added code that checks to see

if authorization is enabled and sets the flag in CmpContext.

It calls CmpSeabaseDDL::isPrivMgrMetadataInitialized to determine

privmgr metadata status


Implementation of method isPrivMgrMetadataInitialized

Changed isAuthorizationEnabled to look at the CmpContext flag instead of

the flag (which was removed) in the CmpSeabaseDDL class

Changed initSeabaseAuthorization and dropSeabaseAuthorization to change

the flag in the context and kill compiler processes

Changed all calls to PrivMgrnnnn::isAuthorizationEnabled to use the

CmpSeabaseDDL::isAuthorizationEnabled or directly from CmpContext

Bin/SqlciErrors.txt & sqlcomp/CmpDDLCatErrorCodes.h to create a new

error 1234 (currently unused)

Sqlcomp/PrivMgrMD.cpp changed mapping of PrivMDStatus to match what was

done in nadefaults.cpp

Optimizer/BindRelExpr.cpp && sqlcomp/nadefaults.cpp to look in

CmpContext for authorization enabled flag

Check privileges for Sequence generator

Adds the code in compiler to check for usage privilege

for any sequence generators used in a query.

Additional privilege checks, plus

This delivery includes:

Verifying that user had correct privileges to perform all DDL

operations. This is performed through a call to

isDDDLOperationAuthorized. The signature changed to pass the object

owner instead of the object name. This eliminates an I/O and made the

method simpler. All callers were changed to use the new signature and

all DDL operations now call this method after the NATable structure has

been retrieved. A new regression test was added (TEST138).

As part of DDL privilege checking, the ALTER and DELETE component

privilege is no longer granted during initialize authorization.

Updated files to address code review checkin for change ID:


Fixed a problem where SHOWDDL was not returning an error when user does

not have appropriate privilege.

Made the PRIVMGR_MD schema a reserved schema.

Added code to switch contexts for several PrivMgr operations. This

required a change to not grant owner privileges when creating the


Added a KNOWN diff file for TEST133. There is an issue where rows are

not being loaded into OBJECT_PRIVILEGES during an error test.

Change-Id: I7448e7171e5f1f09feb6d1f688470b72dc1f43d4

  1. … 26 more files in changeset.
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.


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.


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:


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

  1. … 2 more files in changeset.
Interim DBSecurity deliver for December

Change-Id: Iff416cea17286bc580409f5e00641cfa54820252

  1. … 2 more files in changeset.
Interim DBSecurity deliver for December

1) Implement REVOKE ROLE RESTRICT. Previously dependent objects were

not detected. Launchpad bug #1370739.

2) REVOKE ROLE with a list of grantees would fail for all grantees after

the first. Now works for the entire list. Launchpad bug #1375494.

3) SHOWDDL ROLE now shows the GRANTED BY clause if the grantor is not

DB__ROOT. Launchpad bug #1374586.

4) Component privilege names can now be reserved names. Launchpad bug

5) Added tests to catman1/test135 for privileges and RI constraints.

6) Added support for REVOKE RESTRICT for RI constraints.

7) Added support for USAGE privilege for sequence generator.

This code has been reviewed by the database security team but additional

input is encouraged and welcomed.

Change-Id: I88266fca6d13d6852f046e553ba3505ff878b7f8

  1. … 29 more files in changeset.
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. … 2 more files in changeset.
PrivMgr code review rework/fixes

This code has been reviewed and/or tested by some of the members of the

security team.


1) Replaced calls to sprintf with calls to std::to_string. This avoids the

problem of the buffer potentially being too small for the written data.

2) Eliminated use of STATUS_INTERNAL error return in PrivMgr code. Usage was

inconsistent and could lead to confusion and errors. Internal errors are

now reported as STATUS_ERROR.

3) Previously construction of internal errors was not supplying the filename.

Two defines were created, one for PrivMgr (PRIVMGR_INTERNAL_ERROR) and one

for SeabaseDDL (SEABASEDDL_INTERNAL_ERROR) to generate the error completely,

using the provided string.

4) Code was aligned where previous changes had left it misaligned.

5) The command GET COMPONENT PRIVILEGES ON component FOR authID was

implemented. Also, the header for the related GET COMPONENT PRIVILEGES ON

component was updated.

6) ALTER USER now supports remapping the DB__ROOT user. Also, the command

now correctly checks the REMAP_USER privilege.

7) Checks for ID/Name mapping were not always checking the return code.

Checks were added and errors (sometimes internal) were added.

8) Buffer size for usernames was previously hardcoded, now


9) DROP ROLE now checks for granted privileges prior to removing the system

grant for the role.

10) GRANT/REVOKE ROLE now support the MANAGE_ROLES privilege for using the

GRANTED BY clause. Now a non-DB__ROOT user can grant or revoke a role on

behalf of another user, but only if they have the MANAGE_ROLES privilege.

This authority may be moved to a separate privilege in the future.

11) A member variable (myTable_) was not being freed in the destructor.

12) GRANT/REVOKE ROLE is now checking the return from insert, delete, and

update operations.


Previously error messages 1356 and 1357 were missing a parameter.


*** ERROR 1356 *** Cannot create the component privilege specified.

Component privilege code for the component already exists.

is now:

*** ERROR 1356 *** Cannot create the component privilege specified.

Component privilege code CR for the component already exists.

Error 1357 reports an existing component operation name.




Only change is alignment changes from a previous review.



o Fixed the getComponentPrivilegesForUser query-missing a parenthesis.

o Updated the headers generated for GET COMPONENT PRIVILEGES ON component and


o Within the work() function, corrected code for handling dual GET COMPONENT

PRIVILEGES command based on presence/absence of param1 (authID).



Reverted component operations back to component privileges for use in dual







o verifyAuthority() in the base class was removed, and the version in the user

class was enhanced to support verifying the authority to remap users.

o Internal errors are now generated using the SEABASEDDL_INTERNAL_ERROR define.

o CmpSeabaseDDLuser::alterUser now supports remap the external username for

DB__ROOT. Previously reserved names were prohibited by the ALTER USER

command, now an exception is made for DB__ROOT if the operation is setting a

new external name.

o CmpSeabaseDDLrole::dropRole now checks for privileges granted to a role

before removing the system grant of the role. Previously the system grant

could have been removed leaving the role ungrantable.


o Internal errors are now generated using the SEABASEDDL_INTERNAL_ERROR define.

o grantRevokeSeabaseRole() now uses the MANAGE_ROLES privilege to determine if

a user can grant or revoke a role on behalf of another user. Previously

this was restricted to DB__ROOT. This authority may be moved to a separate

component privilege in the future.



o Replaced usage of STATUS_INTERNAL with STATUS_ERROR.

o Removed unused grantRoleToCreator() - CREATE ROLE calls PrivMgrRoles class



o Member variable myTable_ is now deleted when the instance is freed.

o Corrected generation of error message for existing component name or code to

use the correct parameter constructor type.

o Internal errors are now generated with PRIVMGR_INTERNAL_ERROR define.

o UIDToString (which calls std::to_string) used instead of sprintf to convert

component UIDs to strings.


o Member variable myTable_ is now deleted when the instance is freed.

o Internal errors are now generated with PRIVMGR_INTERNAL_ERROR define.

o UIDToString (which calls std::to_string) used instead of sprintf to convert

component UIDs to strings. Similarly, authIDToString is called to convert

granteeIDs and grantorIDs to strings.

o grantPrivilege() no longer removes WITH GRANT OPTION when a privilege a user

has WITH GRANT OPTION is re-granted to the user without WITH GRANT OPTION.

Instead, the grant is now a nop.


o Internal errors are now generated with PRIVMGR_INTERNAL_ERROR define.

o UIDToString (which calls std::to_string) used instead of sprintf to convert

component UIDs to strings.


Removed STATUS_INTERNAL from the list of PrivStatus enums.



o Added static inline functions authIDToString and UIDToString. Respectively

they take an int32_t and int64_t and return a std:string. Internally they

both cast the value to long long int as that is the only equivalent type

supported by the current (4.4.6) gcc compiler.


o Internal errors are now generated with PRIVMGR_INTERNAL_ERROR define.

o UIDToString (which calls std::to_string) used instead of sprintf to convert

component UIDs to strings.

o A grant of select to the AUTHS table to PUBLIC was removed. This was added

as a workaround, but another workaround (allow SELECT on all metadata tables)

superseded this one.


o Internal errors are now generated with PRIVMGR_INTERNAL_ERROR define.

o Checks for STATUS_INTERNAL were removed.


o Member variable myTable_ is now deleted when the instance is freed.

o Internal errors are now generated with PRIVMGR_INTERNAL_ERROR define.

o authToString is called to convert roleIDs, granteeIDs, and grantorIDs to strings.

o grantRole() no longer removes WITH ADMIN OPTION when a privilege a user has

been granted a role WITH ADMIN OPTION and the role is re-granted to the user

without WITH ADMIN OPTION. Instead, the grant is now a nop.

o The error return from insert or update is checked following a grant as well

as from delete and update following a revoke, and an internal error is

reported if the operation fails.

Change-Id: I6d9fc31455222f28cdc5d0db65dc75fc8bb4a99e

  1. … 16 more files in changeset.
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


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

    • -0
    • +2573
  1. … 100 more files in changeset.