Support for security groups

CleanCoder

Right now, Vertica supports users and roles. This leads to a security assignment of user -> role -> object+permission. Unfortunately, this doesn't scale well. Standard practice would be user -> business user group -> resource user group -> object+permission. This leads to lots of extra effort to maintain security rights in a multi-tenant, large scale deployment. Would be great to have decent security group support in Vertica.

[Deleted User]

Hi CleanCoder, I'm afraid I don't quite understand -- why wouldn't you just make "business user group" a role, make "resource user group" a role, and grant "resource user group" to "business user group"? Adam

CleanCoder

Wasn't aware of role hierarchies. This changes things a little. However, this would then lead to needing to enable an unknown number of roles (business groups). Presumably, we can enumerate those but I can't see a way of doing dynamic SQL to enable them, so it would be a couple of round trips at least, or else we store the list of roles in another source (which may be viable for us). Nevertheless, it is a step in the right direction. The next step would be to be able to enable roles based on a network-level identity. See https://community.vertica.com/vertica/topics/externalize_authorization. Thanks for the comment.

[Deleted User]

Hm, do you mean that you have to enable the new roles at each login? If this is a concern for you, you could modify your granting process to always set a role as a default role for a user whenever you grant the role to the user: https://my.vertica.com/docs/6.1.x/HTML/index.htm#15666.htm It's a little clunky; but it's two statements at grant-time rather than an enumeration of all roles at access-time. (Note that a user can have as many default roles as you want -- "DEFAULT ROLE" doesn't mean "the (single) default role for this user", it means "this role is always enabled by default for this user." I found this confusing at first as well -- I'm not on the team that developed this feature, but I think they're adhering to a particular existing SQL standard.) As for pulling roles from a network-level identity, I assume you're thinking something like groups in LDAP? (Kerberos is a great technology and yours is a good point, but doesn't natively have a notion of groups or roles.) That's a good suggestion. It's actually not one we've heard much to date; if other folks read this thread and are interested, please do weigh in. (Or tell your sales rep, etc.)

CleanCoder

I thought about making all business group roles automatic, which would work (and is similar to what we have in other products), but I actually quite like the idea of having to select them as it limits mistakes. Unfortunately, what I'd really like to do is "enable roles for tenant xxx". There may be multiple such roles, but they wouldn't overlap with roles for another tenant. I do agree that we could do the default role approach, and that is workable. You are right that Kerberos doesn't have a native support for authorisation data, and also right that this could come from LDAP. Assuming secure and timely authentication, then you can always look up the role membership. Nevertheless, Windows does put authorisation information into Kerberos which means a secondary LDAP connection is avoided. This is being separately discussed here: https://community.vertica.com/vertica/topics/externalize_authorization. I was trying to keep the suggestions independent where possible, but I understand that having a complete picture helps.