[ https://issues.apache.org/jira/browse/SENTRY-1648?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Na Li reassigned SENTRY-1648: ----------------------------- Assignee: Na Li > 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 > Assignee: Na Li > 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 would also benefit from this. -- This message was sent by Atlassian JIRA (v7.6.3#76005)