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
FROM information_schema.tables
I change my query output to text and voilà, I have the code I need to grant to the role.
Leave a Reply
You must belogged in to post a comment.