0

We have an application which database relies on SQL Server 2000. A specific area of this application let users access rows from a table that they shouldn't: they should only access rows from suppliers, but not rows from customers. Both suppliers and customers are stored on a single table. We can't change this particular area of the application to limit access, and the vendor wouldn't change it on the near future.

So, as a DBA, I am thinking about creating a solution (until the vendor creates the definitive solution) on the database:

  • The name of the table containing the data is called dbo.FOOBAR.
  • I would create a new login and a new user on the database called newuser.
  • After, I would create a view called newuser.FOOBAR, with a SELECT statement selecting only the rows the user should have access from dbo.FOOBAR.
  • On the application, I can associate the user application with a user accessing the database.

With this possible solution, the user would login to the database as newuser. Any statement involving the table FOOBAR would involve newuser.FOOBAR automatically because of the default schema.

I made some tests and it seems to work. The table dbo.FOOBAR has an UPDATE/INSERT/DELETE trigger, and it seems to work after updates, inserts and deletes using the view.

I question is: is this a preferred way of enforcing a security access without changing the application? Is there some caveat I should consider? Is there another solution for this problem?

TIA!

Improve this question

1 Answer 1

Reset to default
1

As long as the application keeps working correctly this method appears to be a workable solution. It probably isn't the preferred solution as you'll need to be careful when doing any upgrades that they are run by a DBO, and if new columns are added to the dbo.FOOBAR table (It's actually FUBAR) that you rebuild the newuser.FOOBAR view so that it picks up the new columns.

Improve this answer

You must log in to answer this question.