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

["Na Li (JIRA)" <ji...@apache.org>]

     [ 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)