[
https://issues.apache.org/jira/browse/SENTRY-1648?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
BELUGA BEHR updated SENTRY-1648:
--------------------------------
Description:
{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.
was:
{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?
> 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)
