Tuesday, June 14, 2011

Setting specific permissions on database objects for a DB user

From the days I am learning RDBMS ,I am hearing that its secured and we can set permissions at the DB object level.When I talk to my fellow DBAs they tell me that they can control so many things to stop hacking.They can restrict an application from executing queries and limit to only stored procedures,delete permission can be revoked on certain tables for the application etc…Since I have not much worked in a real production environment, I didn’t get a chance to try all these.As you know in the development environment developer is the king and he will be having all the permissions.
But last week I got a good opportunity to play with these permission settings.I had to grant update and select permission for a SQL database user on a particular view.Initially we did using the SQL Server Management Studio.It is very easy.Below are the steps.
  1. Navigate to <Your Database>->Security->Users and double click on the user to whom you need to grant or revoke permission.
  2. On the Database user window select the securables page.
  3. There are 2 portions at the right side.Top section is to select securables and below section for the Permissions on the selected securable.
  4. Add your securable using the search window.The search window can be poped up using the search button.
  5. You can add a securable in 3 modes.Specific database object,all object of specific type or all objects in a schema.
  6. Once you add the securable using the search window, the bottom pane will show you what are the permissions you can set on those db objects.
Very easy. Isn’t it? This will work out only if you are delivering your database as a backup.If you are planning to deliver the scripts, you can get the corresponding scripts on clicking the script button at the top of the Database user window.
Below is the Database user window where you can set the permissions visually.
This is the script generated when clicked on the button”Script Action to new query window”

use [AddressBook]
GRANT SELECT ON [dbo].[PersonView] TO [joymon]
use [AddressBook]
GRANT UPDATE ON [dbo].[PersonView] TO [joymon]

No comments: