Skip to main content
Question

Finding similar roles assigned

  • February 17, 2022
  • 2 replies
  • 6 views
shekhar8249

Hi Team,

I'm trying to find the similar roles assigned to the users and revoke the inappropriate roles

Ex - Below are the schemas and roles assigned to a user
ab_r - read access
abc_r - read access
abc_rw - read/write access
bcd_r - read access
bcd_rw - read/write access
abcd - read access

I want to remove the abc_r and bcd_r read only access which is already there as part of read/write access to the user.

I need to get the output as roles which need to be revoked for all the users in the DB. Any suggestions?

Thanks,
Shekhar

    2 replies

    Bryan_H
    Forum|alt.badge.img+2
    • Bryan_H
    • Participating Frequently
    • February 17, 2022

    You can find most of this info in v_catalog.grants and likely use something like LISTAGG to collect all the permissions for each user and role and compare.

    DaveT
    Forum|alt.badge.img
    • DaveT
    • Participating Frequently
    • February 17, 2022

    There is some sample SQL in this doc link that can be used for backing up GRANTs . It creates a VIEW and you can then SELECT from the view to see GRANTs (grant_order=2). You might be able to use it to help you define your REVOKE statements. It might be missing something but should be pretty complete.
    https://vertica.com/docs/11.0.x/HTML/Content/Authoring/InstallationGuide/Upgrade/BackupRestoreGrants.htm

    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement