Clone Tools
Constraints: committers
Constraints: files
Constraints: dates
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.