PageRenderTime 42ms CodeModel.GetById 14ms RepoModel.GetById 0ms app.codeStats 0ms

/tags/release-0.1-rc2/hive/external/metastore/scripts/upgrade/mysql/007-HIVE-78.mysql.sql

#
SQL | 114 lines | 106 code | 8 blank | 0 comment | 0 complexity | fb232d77fa197c3cf93a7a8bcf789b81 MD5 | raw file
Possible License(s): Apache-2.0, BSD-3-Clause, JSON, CPL-1.0 
    

  1. SELECT '< HIVE-78 Authorization model for Hive >' AS ' ';
    2. 

  2. 

  3. CREATE TABLE IF NOT EXISTS `ROLES` (
    4. 

  4.   `ROLE_ID` bigint(20) NOT NULL,
    5. 

  5.   `CREATE_TIME` int(11) NOT NULL,
    6. 

  6.   `OWNER_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
    7. 

  7.   `ROLE_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
    8. 

  8.   PRIMARY KEY (`ROLE_ID`),
    9. 

  9.   UNIQUE KEY `ROLEENTITYINDEX` (`ROLE_NAME`)
    10. 

  10. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    11. 

  11. 

  12. CREATE TABLE IF NOT EXISTS `ROLE_MAP` (
    13. 

  13.   `ROLE_GRANT_ID` bigint(20) NOT NULL,
    14. 

  14.   `ADD_TIME` int(11) NOT NULL,
    15. 

  15.   `GRANT_OPTION` smallint(6) NOT NULL,
    16. 

  16.   `GRANTOR` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
    17. 

  17.   `GRANTOR_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
    18. 

  18.   `PRINCIPAL_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
    19. 

  19.   `PRINCIPAL_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
    20. 

  20.   `ROLE_ID` bigint(20) DEFAULT NULL,
    21. 

  21.   PRIMARY KEY (`ROLE_GRANT_ID`),
    22. 

  22.   UNIQUE KEY `USERROLEMAPINDEX` (`PRINCIPAL_NAME`,`ROLE_ID`,`GRANTOR`,`GRANTOR_TYPE`),
    23. 

  23.   CONSTRAINT `ROLE_MAP_FK1` FOREIGN KEY (`ROLE_ID`) REFERENCES `ROLES` (`ROLE_ID`)
    24. 

  24. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    25. 

  25. 

  26. CREATE TABLE IF NOT EXISTS `GLOBAL_PRIVS` (
    27. 

  27.   `USER_GRANT_ID` bigint(20) NOT NULL,
    28. 

  28.   `CREATE_TIME` int(11) NOT NULL,
    29. 

  29.   `GRANT_OPTION` smallint(6) NOT NULL,
    30. 

  30.   `GRANTOR` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
    31. 

  31.   `GRANTOR_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
    32. 

  32.   `PRINCIPAL_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
    33. 

  33.   `PRINCIPAL_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
    34. 

  34.   `USER_PRIV` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
    35. 

  35.   PRIMARY KEY (`USER_GRANT_ID`),
    36. 

  36.   UNIQUE KEY `GLOBALPRIVILEGEINDEX` (`PRINCIPAL_NAME`,`PRINCIPAL_TYPE`,`USER_PRIV`,`GRANTOR`,`GRANTOR_TYPE`)
    37. 

  37. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    38. 

  38. 

  39. CREATE TABLE IF NOT EXISTS `DB_PRIVS` (
    40. 

  40.   `DB_GRANT_ID` bigint(20) NOT NULL,
    41. 

  41.   `CREATE_TIME` int(11) NOT NULL,
    42. 

  42.   `DB_ID` bigint(20) DEFAULT NULL,
    43. 

  43.   `GRANT_OPTION` smallint(6) NOT NULL,
    44. 

  44.   `GRANTOR` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
    45. 

  45.   `GRANTOR_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
    46. 

  46.   `PRINCIPAL_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
    47. 

  47.   `PRINCIPAL_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
    48. 

  48.   `DB_PRIV` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
    49. 

  49.   PRIMARY KEY (`DB_GRANT_ID`),
    50. 

  50.   UNIQUE KEY `DBPRIVILEGEINDEX` (`DB_ID`,`PRINCIPAL_NAME`,`PRINCIPAL_TYPE`,`DB_PRIV`,`GRANTOR`,`GRANTOR_TYPE`),
    51. 

  51.   CONSTRAINT `DB_PRIVS_FK1` FOREIGN KEY (`DB_ID`) REFERENCES `DBS` (`DB_ID`)
    52. 

  52. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    53. 

  53. 

  54. CREATE TABLE IF NOT EXISTS `TBL_PRIVS` (
    55. 

  55.   `TBL_GRANT_ID` bigint(20) NOT NULL,
    56. 

  56.   `CREATE_TIME` int(11) NOT NULL,
    57. 

  57.   `GRANT_OPTION` smallint(6) NOT NULL,
    58. 

  58.   `GRANTOR` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
    59. 

  59.   `GRANTOR_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
    60. 

  60.   `PRINCIPAL_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
    61. 

  61.   `PRINCIPAL_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
    62. 

  62.   `TBL_PRIV` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
    63. 

  63.   `TBL_ID` bigint(20) DEFAULT NULL,
    64. 

  64.   PRIMARY KEY (`TBL_GRANT_ID`),
    65. 

  65.   KEY `TABLEPRIVILEGEINDEX` (`TBL_ID`,`PRINCIPAL_NAME`,`PRINCIPAL_TYPE`,`TBL_PRIV`,`GRANTOR`,`GRANTOR_TYPE`),
    66. 

  66.   CONSTRAINT `TBL_PRIVS_FK1` FOREIGN KEY (`TBL_ID`) REFERENCES `TBLS` (`TBL_ID`)
    67. 

  67. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    68. 

  68. 

  69. CREATE TABLE IF NOT EXISTS `TBL_COL_PRIVS` (
    70. 

  70.   `TBL_COLUMN_GRANT_ID` bigint(20) NOT NULL,
    71. 

  71.   `COLUMN_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
    72. 

  72.   `CREATE_TIME` int(11) NOT NULL,
    73. 

  73.   `GRANT_OPTION` smallint(6) NOT NULL,
    74. 

  74.   `GRANTOR` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
    75. 

  75.   `GRANTOR_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
    76. 

  76.   `PRINCIPAL_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
    77. 

  77.   `PRINCIPAL_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
    78. 

  78.   `TBL_COL_PRIV` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
    79. 

  79.   `TBL_ID` bigint(20) DEFAULT NULL,
    80. 

  80.   PRIMARY KEY (`TBL_COLUMN_GRANT_ID`),
    81. 

  81.   KEY `TABLECOLUMNPRIVILEGEINDEX` (`TBL_ID`,`COLUMN_NAME`,`PRINCIPAL_NAME`,`PRINCIPAL_TYPE`,`TBL_COL_PRIV`,`GRANTOR`,`GRANTOR_TYPE`),
    82. 

  82.   CONSTRAINT `TBL_COL_PRIVS_FK1` FOREIGN KEY (`TBL_ID`) REFERENCES `TBLS` (`TBL_ID`)
    83. 

  83. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    84. 

  84. 

  85. CREATE TABLE IF NOT EXISTS `PART_PRIVS` (
    86. 

  86.   `PART_GRANT_ID` bigint(20) NOT NULL,
    87. 

  87.   `CREATE_TIME` int(11) NOT NULL,
    88. 

  88.   `GRANT_OPTION` smallint(6) NOT NULL,
    89. 

  89.   `GRANTOR` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
    90. 

  90.   `GRANTOR_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
    91. 

  91.   `PART_ID` bigint(20) DEFAULT NULL,
    92. 

  92.   `PRINCIPAL_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
    93. 

  93.   `PRINCIPAL_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
    94. 

  94.   `PART_PRIV` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
    95. 

  95.   PRIMARY KEY (`PART_GRANT_ID`),
    96. 

  96.   KEY `PARTPRIVILEGEINDEX` (`PART_ID`,`PRINCIPAL_NAME`,`PRINCIPAL_TYPE`,`PART_PRIV`,`GRANTOR`,`GRANTOR_TYPE`),
    97. 

  97.   CONSTRAINT `PART_PRIVS_FK1` FOREIGN KEY (`PART_ID`) REFERENCES `PARTITIONS` (`PART_ID`)
    98. 

  98. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    99. 

  99. 

  100. CREATE TABLE IF NOT EXISTS `PART_COL_PRIVS` (
    101. 

  101.   `PART_COLUMN_GRANT_ID` bigint(20) NOT NULL,
    102. 

  102.   `COLUMN_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
    103. 

  103.   `CREATE_TIME` int(11) NOT NULL,
    104. 

  104.   `GRANT_OPTION` smallint(6) NOT NULL,
    105. 

  105.   `GRANTOR` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
    106. 

  106.   `GRANTOR_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
    107. 

  107.   `PART_ID` bigint(20) DEFAULT NULL,
    108. 

  108.   `PRINCIPAL_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
    109. 

  109.   `PRINCIPAL_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
    110. 

  110.   `PART_COL_PRIV` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
    111. 

  111.   PRIMARY KEY (`PART_COLUMN_GRANT_ID`),
    112. 

  112.   KEY `PARTITIONCOLUMNPRIVILEGEINDEX` (`PART_ID`,`COLUMN_NAME`,`PRINCIPAL_NAME`,`PRINCIPAL_TYPE`,`PART_COL_PRIV`,`GRANTOR`,`GRANTOR_TYPE`),
    113. 

  113.   CONSTRAINT `PART_COL_PRIVS_FK1` FOREIGN KEY (`PART_ID`) REFERENCES `PARTITIONS` (`PART_ID`)
    114. 

  114. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    115. 

  115.