You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@trafodion.apache.org by "Alice Chen (JIRA)" <ji...@apache.org> on 2015/07/22 20:19:09 UTC
[jira] [Created] (TRAFODION-944) LP Bug: 1414125 - User without
priv can view data in metadata tables
Alice Chen created TRAFODION-944:
------------------------------------
Summary: LP Bug: 1414125 - User without priv can view data in metadata tables
Key: TRAFODION-944
URL: https://issues.apache.org/jira/browse/TRAFODION-944
Project: Apache Trafodion
Issue Type: Bug
Components: sql-security
Reporter: Roberta Marton
Assignee: Roberta Marton
Priority: Critical
Fix For: 2.0-incubating
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.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)