May 11, 2005 at 6:40 am
I've got a developer who has created an application which was designed to use different ownership of tables. It would take too much to explain the reason, but the point is that he wants me to allow this.
I'm trying to evaluate the negatives of allowing him to do this. I know the potential for orphaned users, but I also know numerous ways of how to fix those problems when they arise. Since that problem was always my biggest problem with this, I'm trying to figure out what other problems/dangers would arise from this. The stuff he is developing will eventually grow into the range of about a 5GB database with about 150-200 tables. In our shop, this would be one of our largest databases.
I'm attempting to be objective about the situation. I don't want to allow it, but the question is "why not?". It would cause a significant amount of rework for the developer and is my reasoning strong enough to deny his request?
Thanks for any input.
May 11, 2005 at 6:59 am
The biggest problem is where someone tries to write a query to access a specific user table that is not their user table.
As you know SELECT * FROM Authors will return records for youruser.Authors if it can and dbo.Authors if it can't but there is a performance hit in querying a non-qualified table name.
I think debugging an application that allows multiple tables for multiple owners would be an absolute nightmare.
You are also going to have ownership chain/permissions issues.
My approach would be have a userid field in the various tables and use this instead of isolated user owned tables.
Are you going to give the developer's application the writes to create objects? Very dangerous in my opinion.
May 11, 2005 at 7:04 am
Explain to me some more about the chain/permissions issues.
May 11, 2005 at 7:46 am
Forget the question about the ownership chains. I read up on it.
May 11, 2005 at 7:57 am
It is a convoluted subject and one I don't fully understand myself.
It was covered in an article in SQL Server Standard by one of the Brians.
The gist of it is that if you have a view or stored procedure owned by the dbo then if you have the rights to access the view or stored procedure you have the rights to the underlying tables and objects owned by the dbo even though you don't have explicit permissions.
If I had no rights in the database other than to run a stored procedure dbo.getAuthors then provided the objects that dbo.getAuthors uses are also owned by dbo then I have implicit permissions to those objects.
If I only had rights to Perry.getAuthors then provided this stored procedure only references objects owned by Perry I would be OK. However I would get a permissions error if Perry.getAuthors tried to use objects owned by the dbo.
In other words your security provisions are going to get very complicated very quickly.
Most DBAs I know avoid objects not owned by the DBA like the plague.
See http://qa.sqlservercentral.com/columnists/awarren/worstpracticesobjectsnotownedbydbo.asp
And read the associated discussion.
May 12, 2005 at 4:29 pm
Some apps I've worked with create views of the base table and have each user a view to that table. The users have rights defined and only access the views, never the base table. I think its a difficult way to operate but it does work. An evamle of an app like this is "ADP Enterprise".
-Isaiah
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply