> On May 4, 2018, 8:21 p.m., kalyan kumar kalvagadda wrote: > > sentry-provider/sentry-provider-db/src/main/resources/011-SENTRY-2154.derby.sql > > Lines 24-44 (patched) > > <https://reviews.apache.org/r/66263/diff/4/?file=2016931#file2016931line24> > > > > Lina, Is the the user privileges are implemented for both db model and > > generic model?
yes > On May 4, 2018, 8:21 p.m., kalyan kumar kalvagadda wrote: > > sentry-provider/sentry-provider-db/src/main/resources/011-SENTRY-2154.mysql.sql > > Lines 19-21 (patched) > > <https://reviews.apache.org/r/66263/diff/4/?file=2016932#file2016932line19> > > > > mysql creates indexes for foreign keys. Why should we explcitly add > > indexes. It is an duplicate operation, right. there are two reasons: 1) Keep the index name same across DB types. 2) I found MySql does not always create index properly When there are more than one foreign keys in a table, MySql only automatically creates index for the last defined foreign key: KEY `SEN_USR_DB_PRV_MAP_SN_DB_PRV_FK` (`DB_PRIVILEGE_ID`) When adding a key explicitly "CREATE INDEX `SEN_USR_DB_PRV_MAP_USR_FK_IDX` ON `SENTRY_USER_DB_PRIVILEGE_MAP` (`USER_ID`)", the index is created. Note MySql did not automatically create index for this foreign key. When adding a key explicitly "CREATE INDEX `SEN_USR_DB_PRV_MAP_PRV_FK_IDX` ON `SENTRY_USER_DB_PRIVILEGE_MAP` (`DB_PRIVILEGE_ID`)", the auto-created index `SEN_USR_DB_PRV_MAP_SN_DB_PRV_FK` is removed. . MariaDB [testSentry4]> -- Table SENTRY_USER_DB_PRIVILEGE_MAP for join relationship MariaDB [testSentry4]> CREATE TABLE `SENTRY_USER_DB_PRIVILEGE_MAP` ( -> `USER_ID` BIGINT NOT NULL, -> `DB_PRIVILEGE_ID` BIGINT NOT NULL, -> `GRANTOR_PRINCIPAL` VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_bin -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.01 sec) MariaDB [testSentry4]> MariaDB [testSentry4]> ALTER TABLE `SENTRY_USER_DB_PRIVILEGE_MAP` -> ADD CONSTRAINT `SENTRY_USER_DB_PRIVILEGE_MAP_PK` PRIMARY KEY (`USER_ID`,`DB_PRIVILEGE_ID`); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [testSentry4]> MariaDB [testSentry4]> ALTER TABLE `SENTRY_USER_DB_PRIVILEGE_MAP` -> ADD CONSTRAINT `SEN_USR_DB_PRV_MAP_SN_USR_FK` -> FOREIGN KEY (`USER_ID`) REFERENCES `SENTRY_USER`(`USER_ID`); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [testSentry4]> MariaDB [testSentry4]> ALTER TABLE `SENTRY_USER_DB_PRIVILEGE_MAP` -> ADD CONSTRAINT `SEN_USR_DB_PRV_MAP_SN_DB_PRV_FK` -> FOREIGN KEY (`DB_PRIVILEGE_ID`) REFERENCES `SENTRY_DB_PRIVILEGE`(`DB_PRIVILEGE_ID`); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [testSentry4]> show create table SENTRY_USER_DB_PRIVILEGE_MAP; +------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | SENTRY_USER_DB_PRIVILEGE_MAP | CREATE TABLE `SENTRY_USER_DB_PRIVILEGE_MAP` ( `USER_ID` bigint(20) NOT NULL, `DB_PRIVILEGE_ID` bigint(20) NOT NULL, `GRANTOR_PRINCIPAL` varchar(128) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, PRIMARY KEY (`USER_ID`,`DB_PRIVILEGE_ID`), KEY `SEN_USR_DB_PRV_MAP_SN_DB_PRV_FK` (`DB_PRIVILEGE_ID`), CONSTRAINT `SEN_USR_DB_PRV_MAP_SN_DB_PRV_FK` FOREIGN KEY (`DB_PRIVILEGE_ID`) REFERENCES `SENTRY_DB_PRIVILEGE` (`DB_PRIVILEGE_ID`), CONSTRAINT `SEN_USR_DB_PRV_MAP_SN_USR_FK` FOREIGN KEY (`USER_ID`) REFERENCES `SENTRY_USER` (`USER_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) MariaDB [testSentry4]> CREATE INDEX `SEN_USR_DB_PRV_MAP_USR_FK_IDX` ON `SENTRY_USER_DB_PRIVILEGE_MAP` (`USER_ID`); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [testSentry4]> show create table SENTRY_USER_DB_PRIVILEGE_MAP; +------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | SENTRY_USER_DB_PRIVILEGE_MAP | CREATE TABLE `SENTRY_USER_DB_PRIVILEGE_MAP` ( `USER_ID` bigint(20) NOT NULL, `DB_PRIVILEGE_ID` bigint(20) NOT NULL, `GRANTOR_PRINCIPAL` varchar(128) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, PRIMARY KEY (`USER_ID`,`DB_PRIVILEGE_ID`), KEY `SEN_USR_DB_PRV_MAP_SN_DB_PRV_FK` (`DB_PRIVILEGE_ID`), KEY `SEN_USR_DB_PRV_MAP_USR_FK_IDX` (`USER_ID`), CONSTRAINT `SEN_USR_DB_PRV_MAP_SN_DB_PRV_FK` FOREIGN KEY (`DB_PRIVILEGE_ID`) REFERENCES `SENTRY_DB_PRIVILEGE` (`DB_PRIVILEGE_ID`), CONSTRAINT `SEN_USR_DB_PRV_MAP_SN_USR_FK` FOREIGN KEY (`USER_ID`) REFERENCES `SENTRY_USER` (`USER_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) MariaDB [testSentry4]> CREATE INDEX `SEN_USR_DB_PRV_MAP_PRV_FK_IDX` ON `SENTRY_USER_DB_PRIVILEGE_MAP` (`DB_PRIVILEGE_ID`); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [testSentry4]> show create table SENTRY_USER_DB_PRIVILEGE_MAP; +------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | SENTRY_USER_DB_PRIVILEGE_MAP | CREATE TABLE `SENTRY_USER_DB_PRIVILEGE_MAP` ( `USER_ID` bigint(20) NOT NULL, `DB_PRIVILEGE_ID` bigint(20) NOT NULL, `GRANTOR_PRINCIPAL` varchar(128) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, PRIMARY KEY (`USER_ID`,`DB_PRIVILEGE_ID`), KEY `SEN_USR_DB_PRV_MAP_USR_FK_IDX` (`USER_ID`), KEY `SEN_USR_DB_PRV_MAP_PRV_FK_IDX` (`DB_PRIVILEGE_ID`), CONSTRAINT `SEN_USR_DB_PRV_MAP_SN_DB_PRV_FK` FOREIGN KEY (`DB_PRIVILEGE_ID`) REFERENCES `SENTRY_DB_PRIVILEGE` (`DB_PRIVILEGE_ID`), CONSTRAINT `SEN_USR_DB_PRV_MAP_SN_USR_FK` FOREIGN KEY (`USER_ID`) REFERENCES `SENTRY_USER` (`USER_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | - Na ----------------------------------------------------------- This is an automatically generated e-mail. To reply, visit: https://reviews.apache.org/r/66263/#review202475 ----------------------------------------------------------- On May 4, 2018, 7:25 p.m., Na Li wrote: > > ----------------------------------------------------------- > This is an automatically generated e-mail. To reply, visit: > https://reviews.apache.org/r/66263/ > ----------------------------------------------------------- > > (Updated May 4, 2018, 7:25 p.m.) > > > Review request for sentry, Alexander Kolbasov, Arjun Mishra, kalyan kumar > kalvagadda, and Sergio Pena. > > > Bugs: sentry-2154 > https://issues.apache.org/jira/browse/sentry-2154 > > > Repository: sentry > > > Description > ------- > > update mysql related scripts to > 1) dd new DB table SENTRY_USER_DB_PRIVILEGE_MAP and > SENTRY_USER_GM_PRIVILEGE_MAP to support grant user to privileges > 2) a flag is added in privilege table to indicate the privilege is created > by user, or created by sentry implicitly. I will make change in application > code so User can view the implicit privileges, but cannot change it directly > > > Diffs > ----- > > > sentry-provider/sentry-provider-db/src/main/resources/011-SENTRY-2154.derby.sql > PRE-CREATION > > sentry-provider/sentry-provider-db/src/main/resources/011-SENTRY-2154.mysql.sql > PRE-CREATION > > sentry-provider/sentry-provider-db/src/main/resources/011-SENTRY-2154.oracle.sql > PRE-CREATION > > sentry-provider/sentry-provider-db/src/main/resources/011-SENTRY-2154.postgres.sql > PRE-CREATION > sentry-provider/sentry-provider-db/src/main/resources/sentry-db2-2.1.0.sql > 5e36ee3 > > sentry-provider/sentry-provider-db/src/main/resources/sentry-derby-2.1.0.sql > 374d59e > > sentry-provider/sentry-provider-db/src/main/resources/sentry-mysql-2.1.0.sql > 8876872 > > sentry-provider/sentry-provider-db/src/main/resources/sentry-oracle-2.1.0.sql > 46d3fc8 > > sentry-provider/sentry-provider-db/src/main/resources/sentry-postgres-2.1.0.sql > c28dd9f > > sentry-provider/sentry-provider-db/src/main/resources/sentry-upgrade-db2-2.0.0-to-2.1.0.sql > 3e0373d > > sentry-provider/sentry-provider-db/src/main/resources/sentry-upgrade-derby-2.0.0-to-2.1.0.sql > 5640b76 > > sentry-provider/sentry-provider-db/src/main/resources/sentry-upgrade-mysql-2.0.0-to-2.1.0.sql > 350bc48 > > sentry-provider/sentry-provider-db/src/main/resources/sentry-upgrade-oracle-2.0.0-to-2.1.0.sql > 3afd788 > > sentry-provider/sentry-provider-db/src/main/resources/sentry-upgrade-postgres-2.0.0-to-2.1.0.sql > 13df239 > > > Diff: https://reviews.apache.org/r/66263/diff/4/ > > > Testing > ------- > > Run the script and update script, and the tables are created and updated > successfully. > > > Thanks, > > Na Li > >
