Provide access to SINGLE view to MULTIPLE roles

Hello team, As we know, in Denodo we can give a view level access for a role. I have a situation where I need to give access to a SINGLE view to MULTIPLE roles(around 30). The procedure is to select each role, click on edit in the database and give whatever privilages the role should have on the view. But to do this procedure for around 30 roles will be very much time consuming and not an effective way. I searched for any stored procedures but couldn't find one. Can you please suggest me any effective way where I can give access to a SINGLE view to MULTIPLE roles. Thanks & Regards, Gokul

3 Answers

Hi, I would recommend that you make use of scheduler to do implement this requirement. You can try the following steps: • Launch Scheduler server and Scheduler Administration tool. • Create a [VDP data source]( https://community.denodo.com/docs/html/browse/7.0/scheduler/administration/creating_and_scheduling_jobs/data_sources/vdp_data_sources) to connect to the Virtual DataPort server. • Create a VDP type Job and select the VDP data source in the [Extraction section](https://community.denodo.com/docs/html/browse/7.0/scheduler/administration/creating_and_scheduling_jobs/configuring_new_jobs/vdp_extraction_section). • Enter the Alter Role command with the parameters, in order to pass the view name in that parameters during the job execution. `ALTER ROLE <@rolename> GRANT <<privilege>> ON VIEW <viewname>;` • You could pass the role names from any of the sources like CSV, List, VDP by clicking on “New Sources” in Extraction Section. Refer the section [Configuring the Values to Be Used in a Parameterized Query](https://community.denodo.com/docs/html/browse/7.0/scheduler/administration/creating_and_scheduling_jobs/configuring_new_jobs/vdp_extraction_section) for more information. • Save the changes and execute the job. Hope this helps.
Denodo Team
17-10-2019 06:58:45 -0400
Hello, 1. If you dont mind explaining this with an example. eg: role-gokul privilege-Metadata viewname-test_view 2. Can you also please give an example on giving access to the database for a role eg: role-gokul privilege-Connect database-test_database I tried these and doesn't seem to work, --ALTER ROLE "gokul" GRANT "Metadata" ON VIEW "test_view"; --ALTER ROLE 'gokul' GRANT 'Metadata' ON VIEW 'test_view'; --ALTER ROLE <@gokul> GRANT <<Metadata>> ON VIEW <test_view>;
user
23-10-2019 08:31:09 -0400
Hi, My apologies, there is no ‘VIEW’ in the statement. Based on the inputs that you have provided for drafting an example, you should be looking for a query with a syntax like: ALTER ROLE gokul GRANT METADATA ON test_view; ALTER ROLE gokul GRANT METADATA ON test_database; You can refer to [Modifying Privileges of the User]( https://community.denodo.com/docs/html/browse/7.0/vdp/vql/creating_databases_users_roles_and_access_privileges/managing_users/modifying_the_privileges_of_a_user) for more details on syntax for configuring user and role privileges. Hope this helps!
Denodo Team
05-11-2019 08:54:33 -0500
You must sign in to add an answer. If you do not have an account, you can register here