-2

We have a 3° party application, with an application user who needs to be db_owner on the database (not my choice) on SQL Server system. All stored procedures are encrypted in this database. At some point they do a shrink of the database files in this application, and they do it every day. I want to prevent this, and I was looking if there was a way to do this either with:

  • Deny permission somewhere
  • a server or database trigger
  • something else

Can someone help me

1
  • Which DBMS are you using?
    – user1822
    Commented Sep 22, 2020 at 9:49

2 Answers 2

2

We have an application, with an application user needs to be db_owner on the database

If this application is written "in-house" then push back - hard - and get them to justify why this needs to be the case.
Running an application as a database owner, you might as well hand the database back to the Development team because they can do pretty much whatever they like, with or without your knowledge and/or agreement.
If they're going to break it, let them take the flak and fix it.

If this application is a Package from a third-party supplier (and this is remarkably common), then you're a bit stuck. They can code the application to do whatever they want and again, there's nothing much that you can do about it.

At some point they do a shrink of the database files in this application, and they do it every day. I want to prevent this ...

Why?

Is the shrinking operation causing an operational problem, i.e. causing difficulties for Users/the Business? If so, then that's a very strong argument for getting them to stop doing this.

Another, lesser, argument might be if the "shrunk" data files very quickly grow back to their previous size. IF so, then the shrinking is a waste of time and is probably causing performance issues while the data files grow back again, because growing a data file is always slower than just using free space that's already there.

3
  • It is a shrink of the transaction log file. After a day the size is back where it was before the shrink. Size of transaction log is going up and down on daily base.
    – Ludo
    Commented Sep 23, 2020 at 12:43
  • So your database is writing that many changes during each day. Whilst backup up the Transaction log file is essential, shrinking the file it is just making more work for the DBMS, because it has to keep asking for that space /back/ from the operating system (again). Shrinking the file is simply pointless. OK, it's unlikely, but it's /possible/ that this activity could actually be /causing/ performance issues.
    – Phill W.
    Commented Sep 24, 2020 at 11:15
  • tlog backup is done every 15 minutes
    – Ludo
    Commented Sep 29, 2020 at 12:50
0

The only thing I can think of is to create a on delete trigger on each table to raise an exception or soft-delete (e.g. set a deleted column to true). I don't think there is a way to revoke a privilege to an object created by the schema owner--I couldn't get it to work on Oracle. If rows cannot be deleted, then the database files won't shrink. Of course, rows won't be deleted, which may cause other problems for the application.

The bigger issue is that the application is running as the schema owner. That is just bad security practice as it violates the least-privilege principle.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.