Grant access to tables

Grant access to tables

Grant access to tables 150 150 Carlos L Chacon

Often, when setting up database security where it hadn’t been before, there is a need to assign users to roles and then grant those roles permission to tables.

I was tasked with granting read access to all the tables in the database for some reporting users.  These users have access to all tables in the database so I didn’t need to exclude any.  Because I didn’t want to write out all the grant statements, I took advantage of the information_schema views using tables option.

  Select   ‘GRANT SELECT ON ‘+ Table_Name +‘ TO Reader_Role’
FROM
information_schema.tables
I change my query output to text and voilà, I have the code I need to grant to the role.

Imagine what’s possible with a dedicated SQL specialist on your team.

Leave a Reply

Back to top