April 2, 2009 at 12:43 pm
This is a strange situation and it's difficult to explain. We have just purchased a third-party application that grants all rights (execute on procs, insert,delete,update,select on all tables) to public! Their app requires that public be granted access to all objects.
The pressing problem (besides many basic security concerns) is that our developers need to be able to query the production database for troubleshooting. They do not have any access right now to this data as I can't allow this type of access to prod data. If I grant them any rights at all, they are granted 'public' and have all rights.
My first thought was to create views in another database on the same instance for a few of the key tables they need to query. I haven't had any luck with this - I created the view and added datareader to the developers to this new database. When they try to query, they get a message that says 'the server principal "xyz" is not able to access the database "abc" under the current security context.
Anyone know of a way to make a view work when accessing a database where you have no permissions? Or of another way to allow read-only access to this data.
Desperate for a solution!
Kim Talley
April 2, 2009 at 1:15 pm
Outrageous for any software to be implemented in that way !
Complain to your manager that this simply is not acceptable !
Have him complain with the software vendor and make it public if you are allowed to !
Don't accept any responsibility for that kind of databases !
Just provide the required backup, that's about all you can do.
Normally, the max of auth any software may need is membership of the db-owner group.
In many cases, even that is needed during install and can easily be replaced by simple read/write and exec on schema level.
In many cases softwares require a certain "install account", and another service account with lesser authorities needed.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 2, 2009 at 8:34 pm
It depends. If they use the app and they come into the database when using that app with their credentials, you're pretty much stuck. Otherwise, create a role called Support. Or something to that effect. Then:
CREATE ROLE Support;
GO
DENY INSERT ON DATABASE::**Name** TO Support;
DENY UPDATE ON DATABASE::**Name** TO Support;
DENY DELETE ON DATABASE::**Name** TO Support;
DENY EXECUTE ON DATABASE::**Name** TO Support;
GO
Then make them members of the role. The DENY will trump.
But I agree with ALZDBA 100% (no surprise). You shouldn't stand for this and you need to escalate.
K. Brian Kelley
@kbriankelley
April 2, 2009 at 8:48 pm
I've seen an app like that, and upon investigation, found out that giving rights to Public was a knee jerk reaction to fix permissions by someone who did not understand how SQL server permissions work, especially when it comes to allowing users from a domain instead of a SQL Server login.
if you can prove an app NEEDS public permissions, I'll give you a dollar.
a quick review of the app, and you'll find you could fix it by creating a role with read write and execute procs views and functions, and assigning that role to say domain\Authenticated Users and never have any issues.
i did exactly that, took away the public grants, and everything went just fine; i just had to be careful of their upgrade scripts, sicne there was a script to grant to public every time they added tables and objects, which i just trimmed out.
Lowell
April 3, 2009 at 7:52 am
CREATE ROLE Support;GO
DENY INSERT ON DATABASE::**Name** TO Support;
DENY UPDATE ON DATABASE::**Name** TO Support;
DENY DELETE ON DATABASE::**Name** TO Support;
DENY EXECUTE ON DATABASE::**Name** TO Support;
GO
Then make them members of the role. The DENY will trump.
This is just what I was looking for. This works to allow the access I need for now. I will definitely keep trying to get this changed, but for now this works.
Thanks to everyone for their help!
Kim
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply