Uploaded image for project: 'Phoenix'
  1. Phoenix
  2. PHOENIX-672

Add GRANT and REVOKE commands using HBase AccessController

    Details

    • Type: Task
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Labels:
    • old issue number:
      541

      Description

      In HBase 0.98, cell-level security will be available. Take a look at [this](https://communities.intel.com/community/datastack/blog/2013/10/29/hbase-cell-security) excellent blog post by @apurtell. Once Phoenix works on 0.96, we should add support for security to our SQL grammar.

        Activity

        Hide
        apurtell Andrew Purtell added a comment -

        how should we handle cases where namespace mapping is not enabled and the user tries to assign permissions to tables? Shall we throw exceptions to say that it is not supported

        Sounds good to me

        Show
        apurtell Andrew Purtell added a comment - how should we handle cases where namespace mapping is not enabled and the user tries to assign permissions to tables? Shall we throw exceptions to say that it is not supported Sounds good to me
        Hide
        ankit@apache.org Ankit Singhal added a comment -

        Karan Mehta, do you see any overlap with PHOENIX-4198?

        Show
        ankit@apache.org Ankit Singhal added a comment - Karan Mehta , do you see any overlap with PHOENIX-4198 ?
        Hide
        karanmehta93 Karan Mehta added a comment -

        Just curious, how should we handle cases where namespace mapping is not enabled and the user tries to assign permissions to tables?
        Shall we throw exceptions to say that it is not supported or should we have some way to implement them?

        Show
        karanmehta93 Karan Mehta added a comment - Just curious, how should we handle cases where namespace mapping is not enabled and the user tries to assign permissions to tables? Shall we throw exceptions to say that it is not supported or should we have some way to implement them?
        Hide
        apurtell Andrew Purtell added a comment -

        The HBase API in 1.3 doesn't provide the ability to merge permissions, for example if the initial permission was 'RW' and the user grants 'X' then it overrides the original permission to 'X'.

        It's also fine to document this behavior. This consideration is orthogonal to syntax. Someone is going to either have to specify a weird (for SQL) string containing "RWX", not just "X", or GRANT READ,WRITE,EXECUTE not just GRANT EXECUTE.

        Show
        apurtell Andrew Purtell added a comment - The HBase API in 1.3 doesn't provide the ability to merge permissions, for example if the initial permission was 'RW' and the user grants 'X' then it overrides the original permission to 'X'. It's also fine to document this behavior. This consideration is orthogonal to syntax. Someone is going to either have to specify a weird (for SQL) string containing "RWX", not just "X", or GRANT READ,WRITE,EXECUTE not just GRANT EXECUTE.
        Hide
        apurtell Andrew Purtell added a comment -

        To implement similar behavior, we would have to incur an overhead of reading the permissions and writing it back again.

        I don't see that as a problem.

        We decided to stick more closely to HBase in terms of functionality and syntax,

        HBase shell syntax isn't remotely close to SQL by design, but Phoenix statements should be. Wherever possible if we stick to SQL standards or the syntax of widely used systems users will have less trouble adjusting to Phoenix and we will have fewer user questions or complaints.

        Show
        apurtell Andrew Purtell added a comment - To implement similar behavior, we would have to incur an overhead of reading the permissions and writing it back again. I don't see that as a problem. We decided to stick more closely to HBase in terms of functionality and syntax, HBase shell syntax isn't remotely close to SQL by design, but Phoenix statements should be. Wherever possible if we stick to SQL standards or the syntax of widely used systems users will have less trouble adjusting to Phoenix and we will have fewer user questions or complaints.
        Hide
        karanmehta93 Karan Mehta added a comment -

        Quick question Karan Mehta? , so for
        GRANT 'user0', 'RX'
        Will it grant user0 RX for all schemas and tables?

        Yes, that grants permissions across all namespaces and tables (global permission). Checkout http://hbase.apache.org/book.html#_administration

        I and Thomas D'Silva had a discussion earlier about this syntax from Postgres. The HBase API in 1.3 doesn't provide the ability to merge permissions, for example if the initial permission was 'RW' and the user grants 'X' then it overrides the original permission to 'X'. To implement similar behavior, we would have to incur an overhead of reading the permissions and writing it back again. HBase shell, however, uses the former approach. We decided to stick more closely to HBase in terms of functionality and syntax, so that the development becomes easier. What do you suggest Andrew Purtell?

        Show
        karanmehta93 Karan Mehta added a comment - Quick question Karan Mehta? , so for GRANT 'user0', 'RX' Will it grant user0 RX for all schemas and tables? Yes, that grants permissions across all namespaces and tables (global permission). Checkout http://hbase.apache.org/book.html#_administration I and Thomas D'Silva had a discussion earlier about this syntax from Postgres. The HBase API in 1.3 doesn't provide the ability to merge permissions, for example if the initial permission was 'RW' and the user grants 'X' then it overrides the original permission to 'X'. To implement similar behavior, we would have to incur an overhead of reading the permissions and writing it back again. HBase shell, however, uses the former approach. We decided to stick more closely to HBase in terms of functionality and syntax, so that the development becomes easier. What do you suggest Andrew Purtell ?
        Hide
        aertoria Ethan Wang added a comment - - edited

        Quick question Karan Mehta? , so for
        GRANT 'user0', 'RX'
        Will it grant user0 RX for all schemas and tables?

        +1 Andrew Purtell
        Both MySQL and Postgres grant on SQL commands, such as SELECT, CREATE etc, I assume the "RWXC" in your purpose is about the same?

        Thanks!

        Show
        aertoria Ethan Wang added a comment - - edited Quick question Karan Mehta ? , so for GRANT 'user0', 'RX' Will it grant user0 RX for all schemas and tables? +1 Andrew Purtell Both MySQL and Postgres grant on SQL commands, such as SELECT, CREATE etc, I assume the "RWXC" in your purpose is about the same? Thanks!
        Hide
        apurtell Andrew Purtell added a comment -

        Karan Mehta I prefer we adopt syntax from either Postgres or MySQL. Inventing something new for Phoenix won't serve anyone especially well.

        Postgres
        GRANT: https://www.postgresql.org/docs/8.0/static/sql-grant.html
        REVOKE: https://www.postgresql.org/docs/8.0/static/sql-revoke.html

        MySQL
        GRANT: https://dev.mysql.com/doc/refman/5.7/en/grant.html
        REVOKE: https://dev.mysql.com/doc/refman/5.7/en/revoke.html

        Adapt one of these with the minimum necessary changes. I like the Postgres syntax better, FWIW

        Show
        apurtell Andrew Purtell added a comment - Karan Mehta I prefer we adopt syntax from either Postgres or MySQL. Inventing something new for Phoenix won't serve anyone especially well. Postgres GRANT: https://www.postgresql.org/docs/8.0/static/sql-grant.html REVOKE: https://www.postgresql.org/docs/8.0/static/sql-revoke.html MySQL GRANT: https://dev.mysql.com/doc/refman/5.7/en/grant.html REVOKE: https://dev.mysql.com/doc/refman/5.7/en/revoke.html Adapt one of these with the minimum necessary changes. I like the Postgres syntax better, FWIW
        Hide
        karanmehta93 Karan Mehta added a comment - - edited

        The following grammar will be used for GRANT

        GRANT 'userOrGroupName', 'permissionString' ON [TABLE | SCHEMA] 'param1', 'param2', 'param3' 

        To specify a group instead of a user the first parameter has to started with "@".
        Permission String can contain characters RWXCA case insensitive.
        If all the permissions are general for the user, then the second part is not needed else the following holds.

        Token ON is required
        For the next parameter, if nothing is specified, it defaults to table. For schema, we need to explicitly use the token SCHEMA.
        For schema, it will be followed by a single parameter which is schema name
        For table, it will be followed by Table name and param2/3 will be optional for Column Family and Column Qualifier

        Examples

        GRANT 'user0', 'RX'
        GRANT  'user1', 'RWX' ON 'table1'
        GRANT '@group2', 'RC' ON 'table2', 'cf1'
        GRANT 'user3', 'R' ON SCHEMA 'schema1'
        

        Similar goes for REVOKE as well
        James Taylor Andrew Purtell Please advice.
        Thomas D'Silva FYI.

        Show
        karanmehta93 Karan Mehta added a comment - - edited The following grammar will be used for GRANT GRANT 'userOrGroupName', 'permissionString' ON [TABLE | SCHEMA] 'param1', 'param2', 'param3' To specify a group instead of a user the first parameter has to started with "@". Permission String can contain characters RWXCA case insensitive. If all the permissions are general for the user, then the second part is not needed else the following holds. Token ON is required For the next parameter, if nothing is specified, it defaults to table. For schema, we need to explicitly use the token SCHEMA . For schema, it will be followed by a single parameter which is schema name For table, it will be followed by Table name and param2/3 will be optional for Column Family and Column Qualifier Examples GRANT 'user0', 'RX' GRANT 'user1', 'RWX' ON 'table1' GRANT '@group2', 'RC' ON 'table2', 'cf1' GRANT 'user3', 'R' ON SCHEMA 'schema1' Similar goes for REVOKE as well James Taylor Andrew Purtell Please advice. Thomas D'Silva FYI.
        Hide
        apurtell Andrew Purtell added a comment -

        GRANT and REVOKE DDL statements for applying table, column family, namespace, or global level permissions would find support in the SQL92 syntax definition.

        But what about cell level ACLs (and related on PHOENIX-684 visibility labels)? In an earlier comment I suggested combining GRANT/REVOKE with SELECT but that would be off spec and anyway an out of band application of security metadata. For setting security metadata per cell - if we are going to tackle that - with immediate effect we need some way in DML (UPSERT, INSERT, etc) to associate metadata with value in the value list, or applied to all results of a subselect.

        Show
        apurtell Andrew Purtell added a comment - GRANT and REVOKE DDL statements for applying table, column family, namespace, or global level permissions would find support in the SQL92 syntax definition. But what about cell level ACLs (and related on PHOENIX-684 visibility labels)? In an earlier comment I suggested combining GRANT/REVOKE with SELECT but that would be off spec and anyway an out of band application of security metadata. For setting security metadata per cell - if we are going to tackle that - with immediate effect we need some way in DML (UPSERT, INSERT, etc) to associate metadata with value in the value list, or applied to all results of a subselect.
        Hide
        apurtell Andrew Purtell added a comment -

        Should we revive this? Where in the roadmap do you think it might make sense to fit in? We would be looking at grammar, parser, and executor changes for GRANT and REVOKE statements that I think would be self contained.

        Show
        apurtell Andrew Purtell added a comment - Should we revive this? Where in the roadmap do you think it might make sense to fit in? We would be looking at grammar, parser, and executor changes for GRANT and REVOKE statements that I think would be self contained.
        Hide
        pctony Tony Stevenson added a comment -

        Comment:apurtell:11/14/13 05:00:06 PM:

        Yes I volunteer, to add GRANT and REVOKE for 0.94/0.96.

        Also interested in marrying those statements with SELECT - I believe that would be a first.

        Show
        pctony Tony Stevenson added a comment - Comment:apurtell:11/14/13 05:00:06 PM: Yes I volunteer, to add GRANT and REVOKE for 0.94/0.96. Also interested in marrying those statements with SELECT - I believe that would be a first.
        Hide
        pctony Tony Stevenson added a comment -

        Comment:jamestaylor:11/14/13 04:38:30 AM:

        Nice, didn't realize that.

        Any volunteers?

        Show
        pctony Tony Stevenson added a comment - Comment:jamestaylor:11/14/13 04:38:30 AM: Nice, didn't realize that. Any volunteers?
        Hide
        pctony Tony Stevenson added a comment -

        Comment:apurtell:11/14/13 01:49:35 AM:

        Actually even with HBase 0.94, Phoenix could manage column and table level permissions with something like [GRANT](http://www.postgresql.org/docs/8.0/static/sql-grant.html) and [REVOKE](http://www.postgresql.org/docs/8.0/static/sql-revoke.html). I deliberately linked to Postgres 8 manpages because Postgres 9's syntax involves RBAC, which the HBase access controller doesn't support, although I suppose we could look at emulating roles with a custom Hadoop group mapper.

        On an HBase including HBASE-7662(https://issues.apache.org/jira/browse/HBASE-7662), we could consider fun things like combining GRANT and REVOKE syntax with SELECT. Phoenix would execute the query, retrieve the cells, add cell ACLs, and store them back at their exact coordinates. Can be done in a coprocessor or filter to avoid any round trips over the network.

        Show
        pctony Tony Stevenson added a comment - Comment:apurtell:11/14/13 01:49:35 AM: Actually even with HBase 0.94, Phoenix could manage column and table level permissions with something like [GRANT] ( http://www.postgresql.org/docs/8.0/static/sql-grant.html ) and [REVOKE] ( http://www.postgresql.org/docs/8.0/static/sql-revoke.html ). I deliberately linked to Postgres 8 manpages because Postgres 9's syntax involves RBAC, which the HBase access controller doesn't support, although I suppose we could look at emulating roles with a custom Hadoop group mapper. On an HBase including HBASE-7662 ( https://issues.apache.org/jira/browse/HBASE-7662 ), we could consider fun things like combining GRANT and REVOKE syntax with SELECT. Phoenix would execute the query, retrieve the cells, add cell ACLs, and store them back at their exact coordinates. Can be done in a coprocessor or filter to avoid any round trips over the network.
        Hide
        pctony Tony Stevenson added a comment -

        Comment:apurtell:11/14/13 01:38:42 AM:

        assigned

        Show
        pctony Tony Stevenson added a comment - Comment:apurtell:11/14/13 01:38:42 AM: assigned
        Hide
        pctony Tony Stevenson added a comment -

        Comment:apurtell:11/12/13 04:45:07 AM:

        mentioned

        Show
        pctony Tony Stevenson added a comment - Comment:apurtell:11/12/13 04:45:07 AM: mentioned
        Hide
        pctony Tony Stevenson added a comment -

        Comment:jamestaylor:11/12/13 04:45:07 AM:

        @apurtell - how about this one?

        Show
        pctony Tony Stevenson added a comment - Comment:jamestaylor:11/12/13 04:45:07 AM: @apurtell - how about this one?
        Hide
        pctony Tony Stevenson added a comment -

        Comment:apurtell:11/09/13 09:06:21 PM:

        mentioned

        Show
        pctony Tony Stevenson added a comment - Comment:apurtell:11/09/13 09:06:21 PM: mentioned

          People

          • Assignee:
            karanmehta93 Karan Mehta
            Reporter:
            jamestaylor James Taylor
          • Votes:
            1 Vote for this issue
            Watchers:
            7 Start watching this issue

            Dates

            • Created:
              Updated:

              Development