Hive
  1. Hive
  2. HIVE-5837

SQL standard based secure authorization for hive

    Details

    • Type: New Feature New Feature
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: Authorization
    • Labels:
      None

      Description

      The current default authorization is incomplete and not secure. The alternative of storage based authorization provides security but does not provide fine grained authorization.

      The proposal is to support secure fine grained authorization in hive using SQL standard based authorization model.

        Issue Links

        1.
        SQL std auth - parser changes Sub-task Resolved Thejas M Nair

        100%

        Original Estimate - 96h Original Estimate - 96h
        Time Spent - 168h
         
        2.
        Add a hive authorization plugin api that does not assume privileges needed Sub-task Resolved Thejas M Nair

        100%

        Original Estimate - 120h
        Time Spent - 6h Time Not Required
         
        3.
        SQL std auth - Access control statement updates Sub-task Resolved Thejas M Nair

        16%

        Original Estimate - 48h
        Time Spent - 1.6h Remaining Estimate - 8h Time Not Required
         
        4.
        SQL std auth - implement set roles, show current roles Sub-task Resolved Ashutosh Chauhan

        0%

        Original Estimate - 36h
        Remaining Estimate - 36h
         
        5.
        SQL std auth - add metastore get_principals_in_role api, support SHOW PRINCIPALS role_name Sub-task Resolved Thejas M Nair

        0%

        Original Estimate - 24h
        Remaining Estimate - 24h
         
        6.
        SQL std auth - add list_all_roles to metastore api Sub-task Resolved Unassigned

        100%

        Original Estimate - 24h
        Time Spent - 1h Time Not Required
         
        7.
        SQL std auth - get_privilege_set should check role hierarchy Sub-task Resolved Unassigned

        0%

        Original Estimate - 24h
        Remaining Estimate - 24h
         
        8. SQL std auth - add support to metastore api to list all privileges for a user Sub-task Open Unassigned

        0%

        Original Estimate - 24h
        Remaining Estimate - 24h
         
        9. SQL std auth - support,authorize new 'show grant..' statements Sub-task Open Unassigned

        0%

        Original Estimate - 36h
        Remaining Estimate - 36h
         
        10.
        SQL std auth - support 'show roles' Sub-task Resolved Navis

        100%

        Original Estimate - 24h
        Time Spent - 24h
         
        11.
        SQL std auth - support DESCRIBE ROLE Sub-task Resolved Thejas M Nair

        0%

        Original Estimate - 24h
        Remaining Estimate - 24h
         
        12.
        SQL std auth - authorize DESCRIBE ROLE role Sub-task Resolved Thejas M Nair

        0%

        Original Estimate - 12h
        Remaining Estimate - 12h
         
        13.
        SQL std auth - authorize show all roles, create role, drop role Sub-task Resolved Ashutosh Chauhan

        0%

        Original Estimate - 24h
        Remaining Estimate - 24h
         
        14.
        SQL std auth - authorize grant/revoke roles Sub-task Resolved Ashutosh Chauhan

        0%

        Original Estimate - 48h
        Remaining Estimate - 48h
         
        15.
        SQL std auth - authorize grant/revoke on table Sub-task Resolved Thejas M Nair

        100%

        Original Estimate - 120h Original Estimate - 120h
        Time Spent - 144h
         
        16.
        SQL std auth - metastore api support for get_privilege_set api that checks specific role Sub-task Resolved Unassigned

        100%

        Original Estimate - 48h
        Time Spent - 24h Time Not Required
         
        17.
        SQL std auth - authorize statements that work with paths Sub-task Resolved Thejas M Nair

        0%

        Original Estimate - 72h
        Remaining Estimate - 72h
         
        18.
        SQL std auth - bootstrap SUPERUSER roles Sub-task Resolved Ashutosh Chauhan

        100%

        Original Estimate - 72h
        Time Spent - 24h Time Not Required
         
        19.
        SQL std auth - special handling of PUBLIC role Sub-task Resolved Ashutosh Chauhan

        100%

        Original Estimate - 24h
        Time Spent - 24h
         
        20.
        sql standard auth should disable commands that impose security risk Sub-task Resolved Ashutosh Chauhan

        0%

        Original Estimate - 72h
        Remaining Estimate - 72h
         
        21. SQL std auth - authorize create database Sub-task Open Unassigned

        0%

        Original Estimate - 48h
        Remaining Estimate - 48h
         
        22. SQL std auth - support granted-by in grant statements Sub-task Open Unassigned

        0%

        Original Estimate - 24h
        Remaining Estimate - 24h
         
        23.
        SQL std auth - support new privileges INSERT, DELETE Sub-task Resolved Thejas M Nair

        100%

        Original Estimate - 12h
        Time Spent - 9h Time Not Required
         
        24. SQL std auth - make role/user optional in grant/revoke statements Sub-task Open Thejas M Nair

        0%

        Original Estimate - 24h
        Remaining Estimate - 24h
         
        25.
        support grant/revoke on views - parser changes Sub-task Resolved Ashutosh Chauhan

        100%

        Original Estimate - 24h
        Time Spent - 24h
         
        26.
        sql std auth - authorize 'show roles' Sub-task Resolved Ashutosh Chauhan

        0%

        Original Estimate - 12h
        Remaining Estimate - 12h
         
        27.
        sql std auth - view authorization should not underlying table. More tests and fixes. Sub-task Resolved Thejas M Nair

        0%

        Original Estimate - 24h
        Remaining Estimate - 24h
         
        28. sql std auth - support 'with admin option' in revoke role metastore api Sub-task Open Unassigned

        0%

        Original Estimate - 24h
        Remaining Estimate - 24h
         
        29. sql std auth - revoke role should support sql standard syntax for admin option Sub-task Open Unassigned

        0%

        Original Estimate - 24h
        Remaining Estimate - 24h
         
        30.
        sql standard auth - use admin option specified in grant/revoke role statement Sub-task Resolved Ashutosh Chauhan

        0%

        Original Estimate - 12h
        Remaining Estimate - 12h
         
        31.
        sql std auth - disallow cycles between roles Sub-task Resolved Thejas M Nair

        100%

        Original Estimate - 24h
        Time Spent - 24h
         
        32.
        sql std auth - pass username from sessionstate to v2 authorization interface Sub-task Resolved Thejas M Nair

        100%

        Original Estimate - 24h
        Time Spent - 24h
         
        33. sql std auth - document configuration necessary for security Sub-task Open Thejas M Nair

        0%

        Original Estimate - 12h
        Remaining Estimate - 12h
         
        34. sql std auth - revoke privileges api in metastore should check grantor user Sub-task Open Unassigned

        0%

        Original Estimate - 24h
        Remaining Estimate - 24h
         
        35.
        sql std auth - database should have an owner Sub-task Resolved Ashutosh Chauhan  
         
        36.
        Test authorization_revoke_table_priv.q is failing on trunk Sub-task Resolved Thejas M Nair  
         
        37.
        Disallow transform clause in sql std authorization mode Sub-task Resolved Ashutosh Chauhan  
         
        38.
        sql std auth - new users in admin role config should get added Sub-task Resolved Ashutosh Chauhan  
         
        39.
        SQL std auth - revert change for view keyword in grant statement Sub-task Resolved Thejas M Nair  
         
        40.
        SQL std auth - allow grant/revoke roles if user has ADMIN OPTION Sub-task Resolved Ashutosh Chauhan  
         
        41.
        Restrict function create/drop to admin roles Sub-task Resolved Jason Dere  
         
        42.
        sql std auth - add command to change owner of database Sub-task Resolved Thejas M Nair  
         
        43.
        SQL std auth - only db owner should be allowed to create table within a db Sub-task Resolved Ashutosh Chauhan  
         
        44.
        SQL std auth - pass username from hiveserver2 to sessionstate Sub-task Resolved Thejas M Nair  
         
        45. support list_roles metastore api that returns all the roles in hierarchy Sub-task Open Unassigned  
         
        46.
        "show grant ... on all" fails with NPE Sub-task Resolved Thejas M Nair  
         
        47.
        sql std auth - show grant statement for all principals throws NPE Sub-task Resolved Thejas M Nair  
         

          Activity

          Hide
          Alex Nastetsky added a comment -
          Show
          Alex Nastetsky added a comment - Done: https://issues.apache.org/jira/browse/HIVE-6667 . Thanks.
          Hide
          Thejas M Nair added a comment -

          Alex Nastetsky Please create one for 'show tables'.

          Show
          Thejas M Nair added a comment - Alex Nastetsky Please create one for 'show tables'.
          Hide
          Alex Nastetsky added a comment -

          Thanks Thejas. Should I create a ticket for "show tables" or does one already exist?

          Show
          Alex Nastetsky added a comment - Thanks Thejas. Should I create a ticket for "show tables" or does one already exist?
          Hide
          Thejas M Nair added a comment -

          This tasks under this ticket haven't covered 'show tables'. The focus has been on table/view/URI access. It is unlikely to be make in timeframe for 0.13 , but is likely to be part of hive 0.14 . 0.13 should be out in few weeks as we have branched for the release. 0.14 would is likely to be out in another 3-4 months, given the frequency with which hive releases have been out.
          There is some design work involved around that. There are two things to consider for 'show tables' output, privileges that the user has on the database, and the privileges the user has on the table.

          Show
          Thejas M Nair added a comment - This tasks under this ticket haven't covered 'show tables'. The focus has been on table/view/URI access. It is unlikely to be make in timeframe for 0.13 , but is likely to be part of hive 0.14 . 0.13 should be out in few weeks as we have branched for the release. 0.14 would is likely to be out in another 3-4 months, given the frequency with which hive releases have been out. There is some design work involved around that. There are two things to consider for 'show tables' output, privileges that the user has on the database, and the privileges the user has on the table.
          Hide
          Alex Nastetsky added a comment -

          Hi, are there plans to secure "show tables" as part of this ticket? If so, do we have a design for it yet and what does that look like? Thanks.

          Show
          Alex Nastetsky added a comment - Hi, are there plans to secure "show tables" as part of this ticket? If so, do we have a design for it yet and what does that look like? Thanks.
          Hide
          Brock Noland added a comment -

          I propose that we base alter and drop table privilege on ownership of the table instead.

          Ok, would this would deviate from the "SQL Standard"?

          Do have any opinion on how to deal with privilege on URI object based on your experience? What should it mean, should it mean the privilege applies to the directory and its sub dirs?

          To avoid re-implementing file system permissions I'd suggest that once a prefix to a URI is granted, that all children in that URI are also granted. Of course the file system permissions will still need to be there for the URI to be usable.

          Can things like symlinks pose security holes?

          There is no way that symlinks can be securely followed in HDFS therefore following symlinks must be disabled for this model to be secure.

          Show
          Brock Noland added a comment - I propose that we base alter and drop table privilege on ownership of the table instead. Ok, would this would deviate from the "SQL Standard"? Do have any opinion on how to deal with privilege on URI object based on your experience? What should it mean, should it mean the privilege applies to the directory and its sub dirs? To avoid re-implementing file system permissions I'd suggest that once a prefix to a URI is granted, that all children in that URI are also granted. Of course the file system permissions will still need to be there for the URI to be usable. Can things like symlinks pose security holes? There is no way that symlinks can be securely followed in HDFS therefore following symlinks must be disabled for this model to be secure.
          Hide
          Thejas M Nair added a comment -

          Brock Noland Prasad Mujumdar Shreepadma Venugopalan Do have any opinion on how to deal with privilege on URI object based on your experience? What should it mean, should it mean the privilege applies to the directory and its sub dirs ? Can things like symlinks pose security holes ? Any other issues to consider wrt URI ?

          Show
          Thejas M Nair added a comment - Brock Noland Prasad Mujumdar Shreepadma Venugopalan Do have any opinion on how to deal with privilege on URI object based on your experience? What should it mean, should it mean the privilege applies to the directory and its sub dirs ? Can things like symlinks pose security holes ? Any other issues to consider wrt URI ?
          Hide
          Thejas M Nair added a comment -

          The current proposal does not talk about what determines the privilege to create a view and what privileges the creator of view will have on the new view.
          Based on my reading of the standard (only looking at select access on views because of what hive supports): View has select with grant for user A creating the view, if user has select-grant on all the input columns in query-expression.
          There also seems to be rule about being able to create views without grant privileges on tables (just select), but I think we can just start with allowing on tables for which user has select-with-grant.

          The current proposal says that database ownership will determine the privileges to alter and drop table. But this would be a problem for migration, for clusters where there are many tables under a database owned by different users. I propose that we base alter and drop table privilege on ownership of the table instead.

          Show
          Thejas M Nair added a comment - The current proposal does not talk about what determines the privilege to create a view and what privileges the creator of view will have on the new view. Based on my reading of the standard (only looking at select access on views because of what hive supports): View has select with grant for user A creating the view, if user has select-grant on all the input columns in query-expression. There also seems to be rule about being able to create views without grant privileges on tables (just select), but I think we can just start with allowing on tables for which user has select-with-grant. The current proposal says that database ownership will determine the privileges to alter and drop table. But this would be a problem for migration, for clusters where there are many tables under a database owned by different users. I propose that we base alter and drop table privilege on ownership of the table instead.
          Hide
          Brock Noland added a comment -

          Should we make one of the sql standard privileges available on SERVER object?

          Privileges on the SERVER object can make sense but I feel the more important aspect is to ensure privileges are scoped to a SERVER for the reason I will outline below.

          Brock, could you give more details on the SERVER use case? I've seen people use multiple instances of HS2 for HA/scaling, but never allocating some users to some instances and others to others. What's the motivation for that?

          It's a very similar use case to federation. Enterprises often want to isolate groups of users from using the same resource. The scenario is you have group A and group B and they cannot or do not want to share the same HS2. By having server in the hierarchy you can enforce the separation amongst HS2 instances.

          Show
          Brock Noland added a comment - Should we make one of the sql standard privileges available on SERVER object? Privileges on the SERVER object can make sense but I feel the more important aspect is to ensure privileges are scoped to a SERVER for the reason I will outline below. Brock, could you give more details on the SERVER use case? I've seen people use multiple instances of HS2 for HA/scaling, but never allocating some users to some instances and others to others. What's the motivation for that? It's a very similar use case to federation. Enterprises often want to isolate groups of users from using the same resource. The scenario is you have group A and group B and they cannot or do not want to share the same HS2. By having server in the hierarchy you can enforce the separation amongst HS2 instances.
          Hide
          Alan Gates added a comment -

          Brock, could you give more details on the SERVER use case? I've seen people use multiple instances of HS2 for HA/scaling, but never allocating some users to some instances and others to others. What's the motivation for that?

          Show
          Alan Gates added a comment - Brock, could you give more details on the SERVER use case? I've seen people use multiple instances of HS2 for HA/scaling, but never allocating some users to some instances and others to others. What's the motivation for that?
          Hide
          Thejas M Nair added a comment -

          Brock Noland Thanks for your feedback in the jiras for SQL standard auth !

          Show
          Thejas M Nair added a comment - Brock Noland Thanks for your feedback in the jiras for SQL standard auth !
          Hide
          Thejas M Nair added a comment -

          Brock Noland I assume you mean URI and SERVER as objects (similar to table, views etc) on which privileges (eg, select , insert,..) can be granted. As you know, URI authorization is very essential (more than just helping with udf support), without that you cannot enforce access control (you can use 'create table' to read from any hdfs location).
          I see that SERVER object will also be useful, but not essential for a first version. Should we make one of the sql standard privileges available on SERVER object ?

          Show
          Thejas M Nair added a comment - Brock Noland I assume you mean URI and SERVER as objects (similar to table, views etc) on which privileges (eg, select , insert,..) can be granted. As you know, URI authorization is very essential (more than just helping with udf support), without that you cannot enforce access control (you can use 'create table' to read from any hdfs location). I see that SERVER object will also be useful, but not essential for a first version. Should we make one of the sql standard privileges available on SERVER object ?
          Hide
          Brock Noland added a comment -

          Thejas M Nair,

          as I mentioned here I would consider adding a URI privilege to the model described here. This allows the use of custom UDFs for users. Beyond that I think a SERVER privilege should be added as well. The reason I believe a server privilege is useful is because large deployments of Hive would like to take advantage of multiple HS2 instances while allowing users to only access a single instance. What are you thoughts on these topics?

          Show
          Brock Noland added a comment - Thejas M Nair , as I mentioned here I would consider adding a URI privilege to the model described here. This allows the use of custom UDFs for users. Beyond that I think a SERVER privilege should be added as well. The reason I believe a server privilege is useful is because large deployments of Hive would like to take advantage of multiple HS2 instances while allowing users to only access a single instance. What are you thoughts on these topics?
          Hide
          Navis added a comment -

          Ah, sorry. It's issue number of internal patches applied to our product. All of them are based on hive-11 but might be rebased to trunk.

          Show
          Navis added a comment - Ah, sorry. It's issue number of internal patches applied to our product. All of them are based on hive-11 but might be rebased to trunk.
          Hide
          Thejas M Nair added a comment -

          Navis Thanks, yes, I would certainly appreciate help with this.

          What/where are the NHIVE-* jiras that you refer to ?

          Show
          Thejas M Nair added a comment - Navis Thanks, yes, I would certainly appreciate help with this. What/where are the NHIVE-* jiras that you refer to ?
          Hide
          Navis added a comment -

          I think I can help a little for this. (we've been using some patches for authorization)

          NHIVE-40 Check read permission for creating views
          HIVE-2818 Create table checks the current database privilege
          NHIVE-32 Check grant option for grant/revoke operation
          NHIVE-33 Support database prefix for privilege objects
          NHIVE-31 Add API for retrieving principals endowed with the specific role
          HIVE-2093 [jira] create/drop database should populate inputs/outputs and check concurrency and user permission
          NHIVE-26 Indirect roles are not reflected in authorization
          NHIVE-25 Provide error message for authorization failures
          NHIVE-24 Return show grant result in tabular format
          NHIVE-23 Implement show grant on <resource>
          NHIVE-22 implement show roles
          NHIVE-21 Show grant always return empty string via JDBC
          NHIVE-10 Authenticate SHOW_DATABASES privilege for show databases

          Show
          Navis added a comment - I think I can help a little for this. (we've been using some patches for authorization) NHIVE-40 Check read permission for creating views HIVE-2818 Create table checks the current database privilege NHIVE-32 Check grant option for grant/revoke operation NHIVE-33 Support database prefix for privilege objects NHIVE-31 Add API for retrieving principals endowed with the specific role HIVE-2093 [jira] create/drop database should populate inputs/outputs and check concurrency and user permission NHIVE-26 Indirect roles are not reflected in authorization NHIVE-25 Provide error message for authorization failures NHIVE-24 Return show grant result in tabular format NHIVE-23 Implement show grant on <resource> NHIVE-22 implement show roles NHIVE-21 Show grant always return empty string via JDBC NHIVE-10 Authenticate SHOW_DATABASES privilege for show databases
          Hide
          Thejas M Nair added a comment -

          Functional specification authored by Alan Gates, Sushanth Sowmyan and myself.

          Show
          Thejas M Nair added a comment - Functional specification authored by Alan Gates , Sushanth Sowmyan and myself.
          Hide
          Thejas M Nair added a comment -

          Attaching a functional specification.

          Show
          Thejas M Nair added a comment - Attaching a functional specification.

            People

            • Assignee:
              Thejas M Nair
              Reporter:
              Thejas M Nair
            • Votes:
              0 Vote for this issue
              Watchers:
              19 Start watching this issue

              Dates

              • Created:
                Updated:

                Time Tracking

                Estimated:
                Original Estimate - 1,284h
                1,284h
                Remaining:
                Time Spent - 497.6h Remaining Estimate - 632h
                632h
                Logged:
                Time Spent - 497.6h Remaining Estimate - 632h Time Not Required
                497.6h

                  Development