[ https://issues.apache.org/jira/browse/CASSANDRA-12859?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15622591#comment-15622591 ]
Boris Melamed edited comment on CASSANDRA-12859 at 10/31/16 4:13 PM: --------------------------------------------------------------------- Thank you for giving this doc your attention, and for your feedback. I was taking cues from what the big RDBMS products have to offer, mainly DB2 and Oracle. Now to your points: h4. MODIFY as also including DELETE and TRUNCATE. I remember seeing a previous request to divide it up. The reason given not to divide was that once you have UPDATE rights, you can also effectively remove whole rows (DELETE, TRUNCATE). However, with column permissions, this is no longer the case. I agree that it's scary (or outright wrong) to allow someone with a mere column permission to remove whole rows. On the other hand, if DELETE requires a MODIFY permission without any column restrictions, then there is no way (even if needed) of allowing anyone to delete rows unless they have UPDATE permissions for the whole table. Possibly, that's a valid stipulation - TBD. {quote} Why not just process deletes/truncates the same as inserts? {quote} For inserts, I intended to reject statements that set columns for which the user has no access permissions. Are you saying that INSERTs should not be restricted by columns? (Note that primary key columns must be allowed, or else no INSERT/UPDATE is possible. I shall add this to the doc...) Should we add a new permission type, such as UPDATE or UPSERT, after all? ---- h4. GRANT - additive or replacing? In Oracle and DB2, it's actually required to REVOKE table permissions, before changing the list of included columns in any way. I've intended allowing 'replacing' GRANTs as syntactic sugar. But now, it seems to me that the "spartan" way is the most unambiguous one. If one wants to add or remove one or more columns from the list of included columns, then e.g.: {code} GRANT SELECT (col_a, col_b) ON ks.t1 TO foo; // role foo has access to col_a and col_b REVOKE SELECT ON ks.t1 FROM foo; // removing the previous access to table t1, thus clearing column perms there as well GRANT SELECT (col_a, col_b, col_c) ON ks.t1 TO foo; // now, foo has permissions on all of col_a, col_b, col_c {code} Having said this, there are several DB products that do allow revoking of permissions on certain columns. There, it would make sense to have the additive column GRANTing paradigm, as your intuition suggests. However, possible problems with that approach, as user-friendly as it appears, are: # More complexity: grammar addition for REVOKE statements as well. # Possible confusion: users may erroneously think that the following allows access to all columns (including future ones) except col_a: {code} GRANT SELECT ON ks.t1 TO foo; REVOKE SELECT (col_a) ON ks.t1 FROM foo; {code} Of course, this will not work unless we implement black lists, which we have not thought of doing. As a remedy, we could return an error when REVOKE refers to a column that does not exist. If there are strong feelings for having this more elaborate paradigm, then we can do that. Otherwise, at least in the first step, I'd go for the 'spartan' approach where any column list change requires a previous REVOKE on the whole table, for that role, table, and permission type. The nice thing being that there will be no issue with backwards compatibility going forward, since we are not deciding whether GRANTed columns are additive or replacing; it's simply forbidden to GRANT again, without first REVOKEing. ---- h4. Misc * Thank you for unit test pointers. * Absolutely, dropped columns must trigger cleanup of permissions, thanks for pointing this out. * Grammar- indeed. It would be simpler to have the non-standard syntax: {code} GRANT SELECT ON ks.t1 (col_a, ...) TO foo; {code} If there are no objections, I may go for that. Or else, the code could check and throw an exception if the resource is not a table. * I shall look deeper into the code and come back about the IResource aspect. was (Author: bmel): Thank you for giving this doc your attention, and for your feedback. I was taking cues from what the big RDBMS products have to offer, mainly DB2 and Oracle. Now to your points: h4. MODIFY as also including DELETE and TRUNCATE. I remember seeing a previous request to divide it up. The reason given not to divide was that once you have UPDATE rights, you can also effectively remove whole rows (DELETE, TRUNCATE). However, with column permissions, this is no longer the case. I understand that it's scary (or outright wrong) to allow someone with a mere column permission to remove whole rows. On the other hands, if DELETE requires MODIFY permission without column restrictions, then there is no way (even if needed) of allowing anyone to delete rows unless they have UPDATE every column. Possibly, that's a valid stipulation - TBD. {quote} Why not just process deletes/truncates the same as inserts? {quote} For inserts, I intended to reject statements that set columns for which the user has no access permissions. Are you saying that INSERTs should not be restricted by columns? (Note that primary key columns must be allowed, or else no INSERT/UPDATE is possible. I shall add this to the doc...) Should we add a new permission type, such as UPDATE or UPSERT, after all? ---- h4. GRANT - additive or replacing? In Oracle and DB2, it's actually required to REVOKE table permissions, before changing the list of included columns in any way. I've intended allowing 'replacing' GRANTs as syntactic sugar. But now, it seems to me that the "spartan" way is the most unambiguous one. If one wants to add or remove one or more columns from the list of included columns, then e.g.: {code} GRANT SELECT (col_a, col_b) ON ks.t1 TO foo; // role foo has access to col_a and col_b REVOKE SELECT ON ks.t1 FROM foo; // removing the previous access to table t1, thus clearing column perms there as well GRANT SELECT (col_a, col_b, col_c) ON ks.t1 TO foo; // now, foo has permissions on all of col_a, col_b, col_c {code} Having said this, there are several DB products that do allow revoking of permissions on certain columns. There, it would make sense to have the additive column GRANTing paradigm, as your intuition suggests. However, possible problems with that approach, as user-friendly as it appears, are: # More complexity: grammar addition for REVOKE statements as well. # Possible confusion: users may erroneously think that the following allows access to all columns (including future ones) except col_a: {code} GRANT SELECT ON ks.t1 TO foo; REVOKE SELECT (col_a) ON ks.t1 FROM foo; {code} Of course, this will not work unless we implement black lists, which we have not thought of doing. As a remedy, we could return an error when REVOKE refers to a column that does not exist. If there are strong feelings for having this more elaborate paradigm, then we can do that. Otherwise, at least in the first step, I'd go for the 'spartan' approach where any column list change requires a previous REVOKE on the whole table, for that role, table, and permission type. The nice thing being that there will be no issue with backwards compatibility going forward, since we are not deciding whether GRANTed columns are additive or replacing; it's simply forbidden to GRANT again, without first REVOKEing. ---- h4. Misc * Thank you for unit test pointers. * Absolutely, dropped columns must trigger cleanup of permissions, thanks for pointing this out. * Grammar- indeed. It would be simpler to have the non-standard syntax: {code} GRANT SELECT ON ks.t1 (col_a, ...) TO foo; {code} If there are no objections, I may go for that. Or else, the code could check and throw an exception if the resource is not a table. * I shall look deeper into the code and come back about the IResource aspect. > Column-level permissions > ------------------------ > > Key: CASSANDRA-12859 > URL: https://issues.apache.org/jira/browse/CASSANDRA-12859 > Project: Cassandra > Issue Type: New Feature > Components: Core, CQL > Reporter: Boris Melamed > Attachments: Cassandra Proposal - Column-level permissions.docx > > Original Estimate: 504h > Remaining Estimate: 504h > > h4. Here is a draft of: > Cassandra Proposal - Column-level permissions.docx (attached) > h4. Quoting the 'Overview' section: > The purpose of this proposal is to add column-level (field-level) permissions > to Cassandra. It is my intent to soon start implementing this feature in a > fork, and to submit a pull request once it’s ready. > h4. Motivation > Cassandra already supports permissions on keyspace and table (column family) > level. Sources: > * http://www.datastax.com/dev/blog/role-based-access-control-in-cassandra > * https://cassandra.apache.org/doc/latest/cql/security.html#data-control > At IBM, we have use cases in the area of big data analytics where > column-level access permissions are also a requirement. All industry RDBMS > products are supporting this level of permission control, and regulators are > expecting it from all data-based systems. > h4. Main day-one requirements > # Extend CQL (Cassandra Query Language) to be able to optionally specify a > list of individual columns, in the {{GRANT}} statement. The relevant > permission types are: {{MODIFY}} (for {{UPDATE}} and {{INSERT}}) and > {{SELECT}}. > # Persist the optional information in the appropriate system table > ‘system_auth.role_permissions’. > # Enforce the column access restrictions during execution. Details: > #* Should fit with the existing permission propagation down a role chain. > #* Proposed message format when a user’s roles give access to the queried > table but not to all of the selected, inserted, or updated columns: > "User %s has no %s permission on column %s of table %s" > #* Error will report only the first checked column. > Nice to have: list all inaccessible columns. > #* Error code is the same as for table access denial: 2100. > h4. Additional day-one requirements > # Reflect the column-level permissions in statements of type > {{LIST ALL PERMISSIONS OF someuser;}} > # Performance should not degrade in any significant way. > # Backwards compatibility > #* Permission enforcement for DBs created before the upgrade should continue > to work with the same behavior after upgrading to a version that allows > column-level permissions. > #* Previous CQL syntax will remain valid, and have the same effect as before. > h4. Documentation > * > https://cassandra.apache.org/doc/latest/cql/security.html#grammar-token-permission > * Feedback request: any others? -- This message was sent by Atlassian JIRA (v6.3.4#6332)