You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@sentry.apache.org by "BELUGA BEHR (JIRA)" <ji...@apache.org> on 2017/03/10 20:06:04 UTC

[jira] [Commented] (SENTRY-1648) Sentry MySQL "Unique" Index

    [ https://issues.apache.org/jira/browse/SENTRY-1648?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15905632#comment-15905632 ] 

BELUGA BEHR commented on SENTRY-1648:
-------------------------------------

This is even more important when UTF-8 is being used.  {{`URI`(250)}} in UTF-8 is 1000 bytes, way more than will be supported in MySQL or Oracle for index key length.

> 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 would also benefit from this.



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)