BELUGA BEHR created SENTRY-1648:
-----------------------------------
Summary: Sentry MySQL "Unique" Index
Key: SENTRY-1648
URL: https://issues.apache.org/jira/browse/SENTRY-1648
Project: Sentry
Issue Type: Improvement
Affects Versions: 1.8.0
Reporter: BELUGA BEHR
Priority: Minor
{code:sql|title=sentry-mysql-1.8.0.sql}
CREATE TABLE `SENTRY_DB_PRIVILEGE` (
`DB_PRIVILEGE_ID` BIGINT NOT NULL,
`PRIVILEGE_SCOPE` VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`SERVER_NAME` VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`DB_NAME` VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '__NULL__',
`TABLE_NAME` VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT
'__NULL__',
`COLUMN_NAME` VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT
'__NULL__',
`URI` VARCHAR(4000) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '__NULL__',
`ACTION` VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`CREATE_TIME` BIGINT NOT NULL,
`WITH_GRANT_OPTION` CHAR(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE `SENTRY_DB_PRIVILEGE`
ADD UNIQUE `SENTRY_DB_PRIV_PRIV_NAME_UNIQ`
(`SERVER_NAME`,`DB_NAME`,`TABLE_NAME`,`COLUMN_NAME`,`URI`(250),`ACTION`,`WITH_GRANT_OPTION`);
{code}
As you can see, only the first 250 characters of URI is considered when
determining "uniqueness". Typically, a second column would be added containing
the hash value (MD5/SHA) of the URI and that column would instead be used as
part of the unique index instead of the field itself.
Oracle schema does not have this 250 prefix limitation, but maybe it should?
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
