User without priv can view data in metadata tables
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Trafodion |
Fix Committed
|
High
|
Roberta Marton |
Bug Description
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.
(
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.
(
OBJECT_UID ASC
)
;
-- GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON TRAFODION.
--- SQL operation complete.
Connect as sql_user1
sqlci -u sql_user1
select count(*) from "_MD_".objects;
(EXPR)
-------
--- 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_
CREATE TABLE TRAFODION.
(
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_
, 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
, TOTAL_ELAPSED_TIME LARGEINT DEFAULT NULL
, TOTAL_INSERT_
, TOTAL_DELETE_
, TOTAL_UPDATE_
, TOTAL_SELECT_
, 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_
, LDAP_LOGIN_
, SQL_USER_
, SEARCH_
, SEARCH_
, AUTHENTICATION_
, AUTHENTICATION_
, PRIMARY KEY (SESSION_ID ASC)
)
SALT USING 8 PARTITIONS /* ACTUAL PARTITIONS 1 */
;
-- GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON TRAFODION.
GRANT SELECT ON TRAFODION.
--- SQL operation complete.
sql_user1 has select privilege.
Connect as sql_user1:
sqlci -u sql_user1
select count(*) from "_REPOS_
..
(EXPR)
-------
--- 1 row(s) selected.
sql_user2 does not have select privilege
Connect as sql_user2:
sqlci -u sql_user2
select count(*) from "_REPOS_
*** ERROR[4481] The user does not have SELECT privilege on table or view TRAFODION.
*** 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_
..
--- SQL operation complete.
Operations on tables in metadata schemas should be consistent.
Changed in trafodion: | |
assignee: | nobody → Roberta Marton (roberta-marton) |
importance: | Undecided → High |
milestone: | none → r1.0.1 |
milestone: | r1.0.1 → r1.1 |
tags: | added: sql-security |
Changed in trafodion: | |
status: | New → In Progress |
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.