[teiid-issues] [JBoss JIRA] (TEIID-5798) Mixed PERMISSION GRANTS

[Steven Hawkins (Jira)]

    [ https://issues.jboss.org/browse/TEIID-5798?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13817468#comment-13817468 ] 

Steven Hawkins commented on TEIID-5798:
---------------------------------------

row based security based off of: https://www.postgresql.org/docs/9.5/sql-createpolicy.html

would typically look like named policies:

CREATE POLICY name ON table_name FOR operations TO role list WITH expression

So this would need to be tracked separately from the current notion of unnamed grants.  Teiid has a simple notion of constraint, which is express by pg as CHECK expression - which is valid for both insert and update.  We could of course simplify if needed to just allowing CHECK to be specified without an expression.

We would also have support alter/drop policy, and likely would not require the use of an alter ... ENABLE ROW LEVEL SECURITY statement.

Column masking has several pg extensions, but no built-in support.  The relevant doc for db2: https://www.ibm.com/support/knowledgecenter/en/SSEPEK_10.0.0/seca/src/tpc/db2z_createcolumnmask.html

It shows a similar paradigm - all masks are named entities - but it also includes some notion of enabled/disabled directly in the statement.  Here as well we would likely not carry forward a specific enable/disable.  Comparing to current teiid support, DB2 has a limitation of only one mask per column.  The Teiid logic will use the order concept to create precedence.   Also there is no assignment to roles with the DB2 statement - it would be embedded in the mask expression - case when role ...  This however makes it hard to separate the effects of a single role.  So we would probably want to add both an ORDER clause and a role assignment TO clause.

So the proposal would be to add a POLICY (which we could possibly also allow the term ROW PERMISSION for consistency in teiid), and MASK concept with CREATE (roughly outlined above), ALTER, and DROP support.  These would probably need to be top level db entities (siblings to DataPolicyMetadata) or we would need to introduce a new top level container for all of the security related stuff.  Much of the implementation logic would be similar, but there is more work to do in building the row-level expression (could be multiple policies per role), determine what the contraint/check expression should be, and in building the mask (unless we restrict to a single mask per role).

Thoughts?

Unfortunately given the work involved and holidays time coming up, this seems like it could push 13.0 into next year.

> Mixed PERMISSION GRANTS
> -----------------------
>
>                 Key: TEIID-5798
>                 URL: https://issues.jboss.org/browse/TEIID-5798
>             Project: Teiid
>          Issue Type: Enhancement
>          Components: Query Engine
>            Reporter: Christoph John
>            Assignee: Steven Hawkins
>            Priority: Major
>             Fix For: 13.0
>
>   Original Estimate: 6 hours
>  Remaining Estimate: 6 hours
>
> Hello,
> I am currently trying to set a set of permissions on a table/view. Hence a condition on INSERT,UPDATE,DELETE and an unconditioned SELECT.
> However, it seems that conditioned and unconditioned GRANT statements do not work together.
> {code}
> GRANT INSERT,UPDATE,DELETE ON TABLE "my_nutri_diary.UserDefinedProducts_SRC" CONDITION 'UserDefinedProducts_SRC.fkProfile in  (SELECT Account.idProfile FROM Account WHERE Account.uuidUser = LEFT(user(), 36) )' TO odata;
> GRANT SELECT ON TABLE "my_nutri_diary.UserDefinedProducts_SRC" TO odata;
> REVOKE ALTER,EXECUTE ON TABLE "my_nutri_diary.UserDefinedProducts_SRC" FROM odata;
> {code}



--
This message was sent by Atlassian Jira
(v7.13.8#713008)