Welcome again to my series of blogs on Users/ Roles / Security. In this blog, I am going to showcase how to create users and roles
(read only and read & write) and how to assign these roles to a user.
First we need to have a SCHEMA where we can assign these roles to certain users. The owner of the schema or someone with owner privileges of the schema should be able to accomplish this task.
I started by going to the documentation and ran the statement below a few times (this works but there is an easier and more manageable way to accomplish the task) please go to Secondly for a better way
GRANT [DELETE|UPDATE|INSERT] ON SCHEMA MYSCHEMA TO SOMEUSER WITH GRANT OPTION;
Secondly, we will need to create the roles and assign a) read access to the schema and b) read/write access to the schema.
This is the READ ONLY Role
This is the READ/WRITE Role
Thirdly, we need to create the users (if not existent) and assign such roles to their Granted Roles tab
Finally, we will test these scenarios for the READ ONLY User
and now the same tests with the Read/Write user (and role)
and verify that it works ( Rows affected: 1)
Thank you again for reading this blog. This is all for my user security blog.
Please share your experiences and/or questions.