User without priv can view data in metadata tables

[Roberta Marton]

Inconsistent privilege checking for metadata access.

Users can always select data from tables in the "_MD_" schema independent of assigned privileges.

sqlci -u DB__ROOT

showddl "_MD_".objects;
..

CREATE TABLE TRAFODION."_MD_".OBJECTS
  (
    CATALOG_NAME VARCHAR(256 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , SCHEMA_NAME VARCHAR(256 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , OBJECT_NAME VARCHAR(256 BYTES) CHARACTER SET UTF8
      COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , OBJECT_TYPE CHAR(2) CHARACTER SET ISO88591 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , OBJECT_UID LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
  , CREATE_TIME LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
  , REDEF_TIME LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
  , VALID_DEF CHAR(2) CHARACTER SET ISO88591 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , DROPPABLE CHAR(2) CHARACTER SET ISO88591 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , OBJECT_OWNER INT NO DEFAULT NOT NULL NOT DROPPABLE
  , SCHEMA_OWNER INT NO DEFAULT NOT NULL NOT DROPPABLE
  , FLAGS LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
  , PRIMARY KEY (CATALOG_NAME ASC, SCHEMA_NAME ASC, OBJECT_NAME ASC,
    OBJECT_TYPE ASC)
  )
;

CREATE UNIQUE INDEX OBJECTS_UNIQ_IDX ON TRAFODION."_MD_".OBJECTS
  (
    OBJECT_UID ASC
  )
;

-- GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON TRAFODION."_MD_"."OBJECTS" TO DB__ROOT WITH GRANT OPTION;

--- SQL operation complete.

Connect as sql_user1

sqlci -u sql_user1

select count(*) from "_MD_".objects;

(EXPR)
--------------------

                 130

--- 1 row(s) selected.

Rows are returned even though sql_user1 has no select privilege.

However, if trying to access tables in the "_REPOS_" schema, SELECT privilege is required:

showddl "_REPOS_".metric_session_table;

CREATE TABLE TRAFODION."_REPOS_".METRIC_SESSION_TABLE
  (
    INSTANCE_ID INT UNSIGNED NO DEFAULT NOT NULL NOT
      DROPPABLE
  , TENANT_ID INT UNSIGNED NO DEFAULT NOT NULL NOT
      DROPPABLE
  , COMPONENT_ID INT UNSIGNED DEFAULT NULL
  , PROCESS_ID INT DEFAULT NULL
  , THREAD_ID INT UNSIGNED DEFAULT NULL
  , NODE_ID INT UNSIGNED DEFAULT NULL
  , PNID_ID INT UNSIGNED DEFAULT NULL
  , HOST_ID INT UNSIGNED NO DEFAULT NOT NULL NOT
      DROPPABLE
  , IP_ADDRESS_ID CHAR(32) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  , SEQUENCE_NUMBER INT UNSIGNED DEFAULT NULL
  , PROCESS_NAME CHAR(32) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  , SESSION_ID CHAR(108) CHARACTER SET ISO88591 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , SESSION_STATUS CHAR(5) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  , SESSION_START_UTC_TS TIMESTAMP(6) DEFAULT NULL
  , SESSION_END_UTC_TS TIMESTAMP(6) DEFAULT NULL
  , USER_ID LARGEINT DEFAULT NULL
  , USER_NAME CHAR(256 BYTES) CHARACTER SET UTF8 COLLATE
      DEFAULT DEFAULT NULL
  , ROLE_NAME CHAR(256 BYTES) CHARACTER SET UTF8 COLLATE
      DEFAULT DEFAULT NULL
  , CLIENT_NAME VARCHAR(256 CHARS) CHARACTER SET UTF8
      COLLATE DEFAULT DEFAULT NULL
  , CLIENT_USER_NAME CHAR(256 BYTES) CHARACTER SET UTF8 COLLATE
      DEFAULT DEFAULT NULL
  , APPLICATION_NAME CHAR(130) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  , TOTAL_EXECUTION_TIME LARGEINT DEFAULT NULL
  , TOTAL_ELAPSED_TIME LARGEINT DEFAULT NULL
  , TOTAL_INSERT_STMTS_EXECUTED LARGEINT DEFAULT NULL
  , TOTAL_DELETE_STMTS_EXECUTED LARGEINT DEFAULT NULL
  , TOTAL_UPDATE_STMTS_EXECUTED LARGEINT DEFAULT NULL
  , TOTAL_SELECT_STMTS_EXECUTED LARGEINT DEFAULT NULL
  , TOTAL_CATALOG_STMTS LARGEINT DEFAULT NULL
  , TOTAL_PREPARES LARGEINT DEFAULT NULL
  , TOTAL_EXECUTES LARGEINT DEFAULT NULL
  , TOTAL_FETCHES LARGEINT DEFAULT NULL
  , TOTAL_CLOSES LARGEINT DEFAULT NULL
  , TOTAL_EXECDIRECTS LARGEINT DEFAULT NULL
  , TOTAL_ERRORS LARGEINT DEFAULT NULL
  , TOTAL_WARNINGS LARGEINT DEFAULT NULL
  , TOTAL_LOGIN_ELAPSED_TIME_MCSEC LARGEINT DEFAULT NULL
  , LDAP_LOGIN_ELAPSED_TIME_MCSEC LARGEINT DEFAULT NULL
  , SQL_USER_ELAPSED_TIME_MCSEC LARGEINT DEFAULT NULL
  , SEARCH_CONNECTION_ELAPSED_TIME_MCSEC LARGEINT DEFAULT NULL
  , SEARCH_ELAPSED_TIME_MCSEC LARGEINT DEFAULT NULL
  , AUTHENTICATION_CONNECTION_ELAPSED_TIME_MCSEC LARGEINT DEFAULT NULL
  , AUTHENTICATION_ELAPSED_TIME_MCSEC LARGEINT DEFAULT NULL
  , PRIMARY KEY (SESSION_ID ASC)
  )
  SALT USING 8 PARTITIONS /* ACTUAL PARTITIONS 1 */
;

-- GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON TRAFODION."_REPOS_"."METRIC_SESSION_TABLE" TO DB__ROOT WITH GRANT OPTION;
  GRANT SELECT ON TRAFODION."_REPOS_".METRIC_SESSION_TABLE TO SQL_USER1;

--- SQL operation complete.

sql_user1 has select privilege.

Connect as sql_user1:

sqlci -u sql_user1

select count(*) from "_REPOS_".metric_session_table;
..

(EXPR)
--------------------

                   0

--- 1 row(s) selected.

sql_user2 does not have select privilege

Connect as sql_user2:

sqlci -u sql_user2

select count(*) from "_REPOS_".metric_session_table;

*** ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION."_REPOS_".METRIC_SESSION_TABLE.

*** ERROR[8822] The statement was not prepared.

As a corollary, the SELECT privilege can be granted against metadata tables except those that reside in the "_MD_" schema:

grant select on "_MD_".OBJECTS to sql_user1;

*** ERROR[1012] No privileges were granted. You lack grant option on the specified privileges.

--- SQL operation failed with errors.

Where as a table in the "_REPOS_" allows select to be granted:

grant select on "_REPOS_".metric_session_table to sql_user1;
..

--- SQL operation complete.

Operations on tables in metadata schemas should be consistent.

[Roberta Marton (roberta-marton)]

  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. Fixes for these issues have been
committed.

[Paul Low (paul-low-x)]

This bug was marked as fixed, but I can still reproduce the scenario described in the case. Can you check?

SQL>connect paullow45/Neoview2009
Connected to Trafodion

SQL>select count(*) from "_MD_".objects;

(EXPR)
--------------------
                  62

--- 1 row(s) selected.

SQL>select count(*) from "_REPOS_".metric_session_table;

*** ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION."_REPOS_".METRIC_SESSION_TABLE. [2015-04-15 11:20:41]

SQL>

[Roberta Marton (roberta-marton)]

I reran the tests and found the issue:

DB__ROOT owns everything.
DB__ROOT grant select to "_REPOS_".metric_session_table, "_MD_".objects, and "_PRIVMGR_MD_".role_usage to sql_user1
Sql_user2 has no privs.
I also revoked the SHOW component privilege from PUBLIC.

Run the following statements
  As DB__ROOT - all works
  As sql_user1 - should all work but showddl on tables in "_MD_" and "_PRIVMGR_MD_" fail
  As sql_user2 - all fail

Showddl "_MD_".objects;
Select count(*) from "_MD_".objects;
Showddl "_REPOS_".metric_session_table;
Select count(*) from "_REPOS_".metric_session_table; Showddl "_PRIVMGR_MD_".role_usage; Select count(*) from "_PRIVMGR_MD_".role_usage;

The problem is parameter 2 to CmpDescribeIsAuthorized is NULL so SELECT priv is not checked.
If the user has SHOW component privilege, it will work.

[Roberta Marton (roberta-marton)]

fixed latest issue.