[keycloak-user] Data filtering in SQL

[Pedro Igor Silva]

On Thu, Nov 8, 2018 at 5:44 PM Byrd, Rob M <rmbyrd at dstsystems.com> wrote:

> Thanks Dmitry and Pedro,
>
>
>
> Pardon my simple-minded response below, but I am wondering how these
> specific items would work?  Dmitry, yes I agree your GET
> /projects/<project> and GET /projects scenario is on point for the issue –
> I hope my questions below can further clarify the discussion.  Here, I will
> have to make a “go or no-go” decision in about a week. J  I would love to
> take on the challenge of searching for the “holy grail” in this, but atm
> will need to figure out what Keycloak (or OPA, etc.) can confidently do
> today.
>
>
>
> Thanks for the great discussion and continued help!
>
>
>
> Questions
>
> 1) Simple role-based authorization policy seems doable.
>
>    - Ex: “Only veterinarians are allowed to read pet profiles.”
>
>
>
> 2) But how to answer once more context is needed, such as one resource’s
> affinity to another?  Literally how does the application figure it out?
> Like the below example would need a pet-veterinarian mapping resolved
> somehow, it seems:
>
>    - “Only the treating veterinarian is allowed to read a pet’s profile.”
>
> Just like in OPA, but using a different approach, you can also push
information (the input in OPA) to your policies. We call this "pushing
claims" [1]. In our policy enforcer we also have the concept of a Claim
Information Point [2] (similar concent as a PIP) which you can configure to
automatically push claims to your policies when checking access for a
particular resource. There is also a CIP that allows you to fetch claims
from external services.

Besides, a resource in Keycloak has attributes, which can be anything you
want. So you could, for instance, have a Pet Foo resources in Keycloak and
update a "veterinarian" attribute associated with it. So you could have a
policy that checks if the user making the request is the same defined in
the attribute.

[1]
https://www.keycloak.org/docs/latest/authorization_services/index.html#_service_pushing_claims
[2]
https://www.keycloak.org/docs/latest/authorization_services/index.html#_enforcer_claim_information_point


>
>    -
>
>
>
> 3) Keycloak has taken an example of “Pet owners can access their own pet’s
> profiles.” and said we can write policies saying that "Only Owner" can
> access "/api/petservice/pet/{id}".  But how does the policy engine figure
> out who is the owner of /pet/2 vs /pet/3?
>
 I can think two options. Like I mentioned before, we are resource-based
and resources have an owner. So you can write policies that check if the
resource owner is the user making the authorization request. Another option
is to push claims.

> 4) Similarly, an OPA blog
> https://blog.openpolicyagent.org/write-policy-in-opa-enforce-policy-in-sql-d9d24db93bf4
> gives the example where “Only the treating veterinarian is allowed to read
> a pet’s profile, and only when signed in from a device at the pet’s
> clinic”.  Again, it is easy enough to provide the OPA engine the target pet
> and the current device location, but how exactly is it determined who is
> the treating veterinarian of that pet and what clinic the pet belongs to?
>
5) In general, the security difficulty is constraining what a user can
> see/do in a particular feature, so how exactly would a policy engine bring
> back a subset of records that particular user can see (based on their
> affiliated company, etc.)?
>
> 6) Similarly, how exactly would a policy engine bring back all records but
> not the fields a user should not see (such as employee salary field, unless
> the user is a HR VIP)?  These last two could be likened to @PostAuth
> post-filtering in spring security.
>
You can have all those resources protected by Keycloak and make
authorization requests to obtain the resources a user has access. We
provide a REST API to create resources. And that is the point I tried to
make when I said that data security is not really among the use cases we
are trying to solve. Although it is possible. Keycloak allows you to send a
"give me all" permission request. That means returning permissions for any
resource, managed by Keycloak, that an user can access. But yeah, depending
on how many resources you have you may end up with a huge response and a
bad performance.

Another approach is define a single Employee resource with a Salary scope
to represent all your employees. So you could enforce access to your real
employees and their salary based on the decisions made by the server for
this single resource.

The decision for one approach or another really depends on how fine grained
you want to be, like I mentioned before. Do you need to manage indivudual
employees or they all share the same access policies ?

See this
https://github.com/keycloak/keycloak-quickstarts/tree/master/app-authz-rest-employee
.

Regarding  fields (e.g: salary) you could consider it as a scope associated
with a resource. In Keycloak you can define permissions for scopes, not
only for resources.

>
>
>
>
> Rob Byrd
>
> DST
>
> Solutions Lead
>
> SS&C Technologies Inc.   |   1055 Broadway, Kansas City, MO 64105
>
> t: (816) 435-7286  *| *m (816) 509-0119
>
> *rmbyrd at dstsystems.com <rmbyrd at dstsystems.com>*  |  *www.ssctech.com
> <http://www.ssctech.com/>*
>
> Follow us: [image: cid:image001.png at 01D412C1.A14C5770]
> <https://www.linkedin.com/company/ss-c-technologies/> |  [image:
> cid:image002.png at 01D412C1.A14C5770] <https://twitter.com/ssctechnologies> |
> [image: cid:image003.png at 01D412C1.A14C5770]
> <https://www.facebook.com/ssctechnologies/>
>
>
>
> *From:* Pedro Igor Silva [mailto:psilva at redhat.com]
> *Sent:* Thursday, November 8, 2018 6:42 AM
> *To:* Dmitry Telegin <dt at acutus.pro>
> *Cc:* Byrd, Rob M <rmbyrd at dstsystems.com>; keycloak-user <
> keycloak-user at lists.jboss.org>
> *Subject:* Re: [keycloak-user] Data filtering in SQL
>
>
>
> Hi Dmitry,
>
>
>
> Agree with you when you mention application vs data security. I also agree
> that Keycloak can also solve data security problems.
>
>
>
> Privacy is one of the main reasons behind our UMA support a very important
> aspect of data security. In addition to privacy, we also added extensions
> to UMA and OAuth2 standards to enable applications to use Keycloak as a
> Policy Decision Point, mainly target for application security.
>
>
>
> As PDP (and PAP), Keycloak allows you to govern access to protected
> resources and to obtain authorization decisions as a result of the
> evaluation of policies associated with these resources. Being based on UMA
> and OAuth2 we support token-based authorization but also access control
> based on the permissions granted by the server. So, yeah, it should be
> possible to filter data based on those permissions as well dynamically
> create WHERE clauses.
>
>
>
> My main concerns about data security are scalability and manageability,
> two aspects that are closely related to how much fine-grained you want to
> be. Like I said, in Keycloak you can protect a set of one or more resources
> as well as scope specific permissions, which can span access decisions for
> one or more resources.
>
>
>
> We are using data security when you enable permissions to users or groups,
> where results are filtered based on the evaluation of these permissions.
> Performance wise, evaluation is quite satisfactory, being the main
> challenges the trade-off between usability vs performance. Recently we had
> important changes to improve the performance of our token endpoint and
> policy evaluation engine and I think we can perform well when fetching
> permissions from the server for a set of one or more resources.
>
>
>
> I'm happy to discuss how we can leverage what we have for data security if
> the community is interested.
>
>
>
> Regards.
>
> Pedro Igor
>
>
>
> On Wed, Nov 7, 2018 at 8:47 PM Dmitry Telegin <dt at acutus.pro> wrote:
>
> Hi Rob,
>
> On Tue, 2018-11-06 at 16:28 +0000, Byrd, Rob M wrote:
> > (Hope this is the correct way to reply - let me know if not)
> >
> > Thanks.  So my concern is really with the whole idea that an Enterprise
> Application's security constraints could really be all implemented based on
> url-patterns, is that what you guys are thinking?
>
> Cannot speak for Keycloak guys, but will put in my 2¢ as an architect -
> URL-based (or rather resource-based) authorization covers only one aspect
> of the application security. Data filtering is equally important, but it's
> just another facet of the problem, and needs to be solved accordingly.
> Indeed, Keycloak doesn't provide OOTB any means for automatically limiting
> subsets of data shown to the user, as Keycloak has a completely different
> scope (namely Web SSO/IDM solution).
>
> However, you can still use Keycloak as a central warehouse for your
> security (meta)data, and use it the way you want. Like I said before,
> nothing stops you from defining some policies in Keycloak, then retrieving
> them and converting to a WHERE clause for your SQL/JPQL/NoSQL query.
>
> Speaking of NoSQL - this might be not directly relevant to your problem,
> but still interesting. A similar problem has surfaced in the discussion
> following my talk on Apache Sling + Keycloak [1] earlier this year; the
> central point was: "okay, we can have Keycloak path-based authorization in
> Sling, but how do we limit the content visible to the user?"
> That time we came up with some sort of hybrid solution, like path-based
> security + JCR ACLs and/or application-level rules; but now I think this
> might be something similar, like generating JCR's equivalent to the WHERE
> clause based on Keycloak policy definition.
>
> Just to make sure I understand the case, let's imagine:
> - there are users and groups (live in Keycloak);
> - there are, say, "projects" (live in business tier + DB);
> - there is a policy in Keycloak saying "projects should be accessible only
> to the members of the respective groups";
> - based on that:
>  - GET /projects/<project> should return 200 + representation if the user
> is a member of the group, 403 otherwise;
>  - GET /projects should return the list of projects the current user has
> access to.
>
> Is this correct?
>
> [1]
> https://adapt.to/2018/en/schedule/modern-authentication-in-sling-with-openid-connect-and-keycloak.html
>
> Cheers,
> Dmitry Telegin
> CTO, Acutus s.r.o.
> Keycloak Consulting and Training
>
> Pod lipami street 339/52, 130 00 Prague 3, Czech Republic
> +42 (022) 888-30-71
> E-mail: info at acutus.pro
>
> >
> > For example, mostly a user can visit most features (urls) in an
> application, but it is the subset of things they can see/do within the
> feature that is the crux of the security issue - and it does not seem
> feasible to architect urls in such a way that they can be used as the key
> to security.  Thoughts?
> >
> > Thanks!
> >
> > Rob Byrd
> > DST
> > Solutions Lead
> > SS&C Technologies Inc.   |   1055 Broadway, Kansas City, MO 64105
> > t: (816) 435-7286  | m (816) 509-0119
> > rmbyrd at dstsystems.com  |  www.ssctech.com
> > Follow us:  |   |
> >
> > -----Original Message-----
> > > From: Dmitry Telegin [mailto:dt at acutus.pro]
> > Sent: Friday, November 2, 2018 12:22 AM
> > > To: Byrd, Rob M <rmbyrd at dstsystems.com>; keycloak-user at lists.jboss.org
> > Subject: Re: [keycloak-user] Data filtering in SQL
> >
> > Hello Rob,
> >
> > If I get it right, it's all about generating SQL WHERE clause from
> Keycloak policies? I think this is doable, as Keycloak has a well-defined
> object model for authorization policies, and it's easy to obtain policy
> definitions in JSON format. I think Pedro Igor will tell you more about
> that.
> >
> > You should pay attention to the following:
> > - there are differences in semantics between OPA and Keycloak policies.
> For example, Keycloak policies do not operate HTTP methods but rather use
> more generic notion of scopes;
> > - not every policy type can be easily converted to a WHERE clause. It
> should be trivial for User/Group/Role policies, but is virtually impossible
> for Script and Rules, as they are just blackboxes that evaluate to true or
> false. Unless of course your DBMS has a built-in JavaScript engine :)
> >
> > Good luck!
> > Dmitry Telegin
> > CTO, Acutus s.r.o.
> > Keycloak Consulting and Training
> >
> > Pod lipami street 339/52, 130 00 Prague 3, Czech Republic
> > +42 (022) 888-30-71
> > E-mail: info at acutus.pro
> >
> > On Thu, 2018-11-01 at 21:39 +0000, Byrd, Rob M wrote:
> > > I am comparing OPA authorization to Keycloak - how could I enforce
> Keycloak policy in the SQL closest to the data for good performance,
> including returning subsets of lists?  OPA discusses this at
> https://blog.openpolicyagent.org/write-policy-in-opa-enforce-policy-in-sql-d9d24db93bf4
> .
> > >
> > > Thanks!
> > >
> > > Rob Byrd
> > > DST
> > > Solutions Lead
> > > SS&C Technologies Inc.   |   1055 Broadway, Kansas City, MO 64105
> > > t: (816) 435-7286  | m (816) 509-0119
> > > rmbyrd at dstsystems.com<mailto:rmbyrd at dstsystems.com>  |
> www.ssctech.com<http://www.ssctech.com/>;;
> > > > > Follow us: [cid:image001.png at 01D412C1.A14C5770] <
> https://www.linkedin.com/company/ss-c-technologies/>  |  [
> cid:image002.png at 01D412C1.A14C5770] <https://twitter.com/ssctechnologies
> >  |  [cid:image003.png at 01D412C1.A14C5770] <
> https://www.facebook.com/ssctechnologies/>
> > >
> > >
> > >
> > > Please consider the environment before printing this email and any
> attachments.
> > >
> > > This e-mail and any attachments are intended only for the individual
> or company to which it is addressed and may contain information which is
> privileged, confidential and prohibited from disclosure or unauthorized use
> under applicable law. If you are not the intended recipient of this e-mail,
> you are hereby notified that any use, dissemination, or copying of this
> e-mail or the information contained in this e-mail is strictly prohibited
> by the sender. If you have received this transmission in error, please
> return the material received to the sender and delete all copies from your
> system.
> > > _______________________________________________
> > > keycloak-user mailing list
> > > keycloak-user at lists.jboss.org
> > > https://lists.jboss.org/mailman/listinfo/keycloak-user
> >
> >
> > Please consider the environment before printing this email and any
> attachments.
> >
> > This e-mail and any attachments are intended only for the individual or
> company to which it is addressed and may contain information which is
> privileged, confidential and prohibited from disclosure or unauthorized use
> under applicable law. If you are not the intended recipient of this e-mail,
> you are hereby notified that any use, dissemination, or copying of this
> e-mail or the information contained in this e-mail is strictly prohibited
> by the sender. If you have received this transmission in error, please
> return the material received to the sender and delete all copies from your
> system.
> _______________________________________________
> keycloak-user mailing list
> keycloak-user at lists.jboss.org
> https://lists.jboss.org/mailman/listinfo/keycloak-user
>
> ------------------------------
> Please consider the environment before printing this email and any
> attachments.
>
> This e-mail and any attachments are intended only for the individual or
> company to which it is addressed and may contain information which is
> privileged, confidential and prohibited from disclosure or unauthorized use
> under applicable law. If you are not the intended recipient of this e-mail,
> you are hereby notified that any use, dissemination, or copying of this
> e-mail or the information contained in this e-mail is strictly prohibited
> by the sender. If you have received this transmission in error, please
> return the material received to the sender and delete all copies from your
> system.
>
>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image001.png
Type: image/png
Size: 588 bytes
Desc: not available
Url : http://lists.jboss.org/pipermail/keycloak-user/attachments/20181108/6e5f4505/attachment.png 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image002.png
Type: image/png
Size: 607 bytes
Desc: not available
Url : http://lists.jboss.org/pipermail/keycloak-user/attachments/20181108/6e5f4505/attachment-0001.png 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image003.png
Type: image/png
Size: 566 bytes
Desc: not available
Url : http://lists.jboss.org/pipermail/keycloak-user/attachments/20181108/6e5f4505/attachment-0002.png 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image001.png
Type: image/png
Size: 588 bytes
Desc: not available
Url : http://lists.jboss.org/pipermail/keycloak-user/attachments/20181108/6e5f4505/attachment-0003.png