Details
-
Improvement
-
Status: Open
-
Minor
-
Resolution: Unresolved
-
1.8.0
-
None
-
None
-
None
Description
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`);
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.