SQL – Grant Table Permissions

by David Crowther

Question:

Is it possible to restrict a SQL User to only have Write permissions to specific Database Tables?

Answer:

Yes, this is possible, and it is a great way to ensure that only certain users can edit certain tables in your Spatial Database.

Firstly, create a new READ ONLY user in your Database e.g. NewUser… ensuring that you do this at Database level (Database > Security > Users) e.g. inside your Spatial GeoStore.

Once the New User has been created inside your chosen Database, in the Databases Security > Users section > select that User, right click and choose Properties.

This will open the Properties for the chosen User. From the left choose the Securables menu and this will list if the Database User has been restricted to certain permissions per TABLE. In the example below we have already added the Permissions for the NewUser to a table called asset_reponsibility.

To Add a new Permission, choose the SEARCH button in the Securables section.

In the Add Objects menu, choose Specific Objects and choose OK.

In the Select Objects window, choose the Object Types button…..

…. and from the list of Object Types tick the box to choose > Tables and click OK.

Back in the Select Objects window, type a name of a table e.g. Obsol… and then press the Check Names button.

If there are multiple Tables with that name, tick to choose the Tables you require and press OK.

Once you have selected the Database Objects, choose OK and the chosen Tables are added to the Securables section at the top of the User properties.

At the bottom of the Securables page are the Permissions for each Database Table. Select the added Table and then tick which permissions to grant as needed e.g. Delete, Insert, Select, Update.

Finally, press OK to the User properties and the change to their Permissions will take effect. This User will now be able to edit tables based on the Permissions you granted for that User.