August 13, 2008 at 9:32 am
Andy Warren (8/13/2008)
Jeffrey, the schema idea is interesting, it's just felt like it was worth doing to me, all too often users need to cross schemas to get work done. Have you tried this in production and if so, talk more about the advantages?
No, I have not tried this in production because the systems I support are vendor supplied. What I have started in production is building a schema for our objects that must reside on a vendor supplied database. This way, I am reasonably assured that any vendor updates will not step on anything that we have put in place and we won't block an upgrade.
And as far as cross schemas - how is that handled now when everything is in the dbo schema? You apply permissions to a role and add the user\group to the role. Nothing different - just the added ability to grant access to the schema instead of access to specific objects.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
August 13, 2008 at 10:26 am
Andy Warren
...For applications I prefer that the connection be done using a single service account (unless we need a separate one that will be for read only) and I load these individually, not as a group. Ideally that service account is used only on an application server/web server. If a sql login is used - and this is common for desktop client/server apps - I also set up a separate login per application, and try to make sure that the developers have been very careful about how they store/retrieve the password for the account.
so for an app, your login has reader/writer permissions?, thus I suppose the restrictions are given at app level... this is not a mistake? I mean, besides that an app could have security holes, you also are opening a door for a power user logging by a sql client.
you should not consider using an Application Role? or replicate the application level groups/users permissions in a SQLServer level ?
what are your thoughts here guys?
August 13, 2008 at 10:49 am
JJ B (8/13/2008)
In smaller organizations where you have the opportunity to know the people, this can certainly work. However, there's always the possibility in a small shop where the boss' "Chosen One" has to be granted access to the application and knows enough to be dangerous. In they go with the database tools (which they were allowed to install because they are the One) and BAM! Table deleted, restore from backup.
This argument just doesn't make sense to me. My users do not have permissions to delete tables. So, even if they were allowed to install say Management Studio or MS Access, they couldn't delete any tables with their accounts.
I'm the only "one" in my organization. However, if there were a time in the future where there was another "one" (a special power user or another developer) and that person needed the level of rights that would allow them to actually do something like delete a table, then that person would need a higher level of permission as part of her/his job. If the boss/agency trusts someone to do the job, I'm not going stand in the way of the agency getting business done. (That's how security people get bad reps and stop the world from turning.) If I have to restore from backup, then I restore from backup. That's what backups are for. Think of it as job "security".
Delete a table as in DELETE FROM Table (no predicate WHERE clause).
K. Brian Kelley
@kbriankelley
August 13, 2008 at 10:51 am
figaro (8/13/2008)
Andy Warren
...For applications I prefer that the connection be done using a single service account (unless we need a separate one that will be for read only) and I load these individually, not as a group. Ideally that service account is used only on an application server/web server. If a sql login is used - and this is common for desktop client/server apps - I also set up a separate login per application, and try to make sure that the developers have been very careful about how they store/retrieve the password for the account.
so for an app, your login has reader/writer permissions?, thus I suppose the restrictions are given at app level... this is not a mistake? I mean, besides that an app could have security holes, you also are opening a door for a power user logging by a sql client.
you should not consider using an Application Role? or replicate the application level groups/users permissions in a SQLServer level ?
what are your thoughts here guys?
Application roles, in the SQL Server use of the phrase, requires that the application actually execute sp_setapprole. With a 3rd party app not built to use app roles, it's not an option.
K. Brian Kelley
@kbriankelley
August 13, 2008 at 10:55 am
Jeffrey Williams (8/13/2008)
Andy Warren (8/13/2008)
Jeffrey, the schema idea is interesting, it's just felt like it was worth doing to me, all too often users need to cross schemas to get work done. Have you tried this in production and if so, talk more about the advantages?No, I have not tried this in production because the systems I support are vendor supplied. What I have started in production is building a schema for our objects that must reside on a vendor supplied database. This way, I am reasonably assured that any vendor updates will not step on anything that we have put in place and we won't block an upgrade.
And as far as cross schemas - how is that handled now when everything is in the dbo schema? You apply permissions to a role and add the user\group to the role. Nothing different - just the added ability to grant access to the schema instead of access to specific objects.
The idea, though, is by the use of schema you leave yourself open to creating a new schema which particular users can't access objects in. When db_datareader and db_datawriter roles are used, that's not an option. You end up having to redo the permissions. Been there, done that. This is one of the reasons Microsoft recommends as a best practice assigned permissions at the schema level as the highest level, not the database.
K. Brian Kelley
@kbriankelley
August 13, 2008 at 11:09 am
K. Brian Kelley (8/13/2008)
Delete a table as in DELETE FROM Table (no predicate WHERE clause).
Ah, you mean delete all the data from the table.
I see the difference, but I don't think my answer is any different. This mythical person is accessing the database outside of the application because he needs to do so for his job. If he is just running reports, then I'm having this person do it on a copy of the database or I'm giving him a different read-only account to do his reports. If this person needs to change data outside of the application, including deleting multiple rows in a table, then this person needs to do so for his job. Whether he is running
. DELETE * FROM PreciousTable
or he is calling
. Exec procDeletePreciousTable '1/1/1900', '12/31/2999'
, this person can still find ways to do horrible things to the data either by accident or maliciously.
And just because someone *could* delete all the data form a table some day in a special scenario: 1) it doesn't happen every day (it's like the posting earlier of someone getting tired of people dragging out the crazed join scenario), 2) even I could do it so the danger is never completely eradicated, 3) we have backups for a reason. All of which tells me that doing inordinate amounts of extra programming just so that users in my situation can't access tables directly is not a good trade-off. Not a good use of agency resources.
Don't get me wrong. I love stored procs. I use them all the time--when I think it is appropriate. I just also assign permissions directly to tables. I still haven't heard an argument that makes me think it is inappropriate for my situation. Though I appreciate you for trying.
August 13, 2008 at 11:43 am
JJ B (8/13/2008)
K. Brian Kelley (8/13/2008)
Delete a table as in DELETE FROM Table (no predicate WHERE clause).
Ah, you mean delete all the data from the table.
I see the difference, but I don't think my answer is any different. This mythical person is accessing the database outside of the application because he needs to do so for his job. If he is just running reports, then I'm having this person do it on a copy of the database or I'm giving him a different read-only account to do his reports. If this person needs to change data outside of the application, including deleting multiple rows in a table, then this person needs to do so for his job. Whether he is running
. DELETE * FROM PreciousTable
or he is calling
. Exec procDeletePreciousTable '1/1/1900', '12/31/2999'
, this person can still find ways to do horrible things to the data either by accident or maliciously.
And just because someone *could* delete all the data form a table some day in a special scenario: 1) it doesn't happen every day (it's like the posting earlier of someone getting tired of people dragging out the crazed join scenario), 2) even I could do it so the danger is never completely eradicated, 3) we have backups for a reason. All of which tells me that doing inordinate amounts of extra programming just so that users in my situation can't access tables directly is not a good trade-off. Not a good use of agency resources.
Don't get me wrong. I love stored procs. I use them all the time--when I think it is appropriate. I just also assign permissions directly to tables. I still haven't heard an argument that makes me think it is inappropriate for my situation. Though I appreciate you for trying.
It depends on how the app authenticates with SQL Server. If you're always using service accounts, then it's not as big an issue. Now, there are performance implications and plan caching with regards to case when using ad hoc queries, but good coding practices usually eliminate these. But if the app is making the connection as the user, and the app permits deletes, then there is nothing stopping the person from using Access or another tool.
In your situation in a small shop the mandate to use stored procedures may not be that big of a deal. I'm not trying to say you're wrong and you've got to do it a particular way. The questions come up when you start getting any sort of size to your databases or any sort of business cost to downtime due to a restore, because then the security does become a big deal. For instance, if it takes two hours to do a restore and during that time no one can conduct business, how much does it cost the business? Those are the kinds of questions that always need to be asked with regards to security. If the answer, "It doesn't cost much at all, the security implementation would cost the company more," then you forgo the security lock down. That's just smart business.
K. Brian Kelley
@kbriankelley
August 13, 2008 at 11:53 am
Brian:
What you are saying now makes better sense to me. What got lost in this discussion (and most discussions like it) is the last part of your last posting, where you acknowledge (my interpretation of your text) that there are situations where giving people access to tables (rather than ALWAYS stored procs) in the context of a security discussion may be the right thing to do.
All people talk about is the evils of giving users direct access to the tables. They do not talk about the context in which giving direct access to the tables makes sense and when it does not make sense. That's the point I have been trying to make. There is a situation and philosophy/approach where giving direct access to read and even update data in tables makes the best business sense.
Thanks for your clarifications.
August 13, 2008 at 12:28 pm
Jeffrey Williams (8/13/2008)
Andy Warren (8/13/2008)
Jeffrey, the schema idea is interesting, it's just felt like it was worth doing to me, all too often users need to cross schemas to get work done. Have you tried this in production and if so, talk more about the advantages?No, I have not tried this in production because the systems I support are vendor supplied. What I have started in production is building a schema for our objects that must reside on a vendor supplied database. This way, I am reasonably assured that any vendor updates will not step on anything that we have put in place and we won't block an upgrade.
And as far as cross schemas - how is that handled now when everything is in the dbo schema? You apply permissions to a role and add the user\group to the role. Nothing different - just the added ability to grant access to the schema instead of access to specific objects.
When everything is in the same schema ownership chaining rules apply, so if I have schema1.proc1 and access schema1.table1 then all my user/role needs is exec permissions on schema1, but, if schema1.proc1 accesses schema2.table1 then the ownership chain COULD be broken so I would need my user/role to have exec permissions in schema1 and read permissions on schema2.table1. Notice I said could, because I believe, and please correct me on this, that if schema1 and schema2 are OWNED by the same user or role then ownership chaining will still work.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 13, 2008 at 12:42 pm
Jack Corbett (8/13/2008)
When everything is in the same schema ownership chaining rules apply, so if I have schema1.proc1 and access schema1.table1 then all my user/role needs is exec permissions on schema1, but, if schema1.proc1 accesses schema2.table1 then the ownership chain COULD be broken so I would need my user/role to have exec permissions in schema1 and read permissions on schema2.table1. Notice I said could, because I believe, and please correct me on this, that if schema1 and schema2 are OWNED by the same user or role then ownership chaining will still work.
If the schema are both owned by the same user, ownership chaining works.
K. Brian Kelley
@kbriankelley
August 13, 2008 at 12:45 pm
I think access to tables does sometimes make sense, especially when there is constant ad hoc stuff happening.
However the reason it's often recommended against doing this is that it's a slippery slope. The chances increase with each person that you give access to that they will make a mistake. While someone running a stored procedure for deletes can mis-type a date and delete lots (or all) the data in table, there are extremely less likely to.
- The WHERE clause is always enforced.
- They typically won't have multiple batches on screen, which is easy to do in SSMS/EM.
We, as experienced database people, make mistakes when we're working with data and we know better. Inexperienced people are more likely to, whether they're driving a truck, packing chocolates in a package or deleting data.
I have rarely found a place where building stored procedures takes appreciably more time than writing the code. If I have to give a set of code to someone to run as a script because we can't write an app, I'd still rather give them a script that has stored proc calls and parameters documented.
Only for one reason: it causes less mistakes.
You're not wrong for giving people access to tables, but I'd argue you are creating unnecessary risk. You might have time or resource constraints, and without knowing your environment, I trust you are making a good decision, but I just don't think it takes much more time to build procs. Heck, I tend to build procs for myself for those repeatable things that I run without an app.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
August 13, 2008 at 1:15 pm
Jack Corbett (8/13/2008)
When everything is in the same schema ownership chaining rules apply, so if I have schema1.proc1 and access schema1.table1 then all my user/role needs is exec permissions on schema1, but, if schema1.proc1 accesses schema2.table1 then the ownership chain COULD be broken so I would need my user/role to have exec permissions in schema1 and read permissions on schema2.table1. Notice I said could, because I believe, and please correct me on this, that if schema1 and schema2 are OWNED by the same user or role then ownership chaining will still work.
This only becomes a problem if your schemas are owned by different users. I have not found any reason (yet) to have schemas owned by anything other than dbo. I am sure it is possible, and probably even necessary in some cases - but, I just haven't found a reason for it yet.
Since all schemas would be owned by dbo - there is no issue with the ownership chain being broken.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
August 13, 2008 at 1:25 pm
Jeffrey Williams (8/13/2008)
Jack Corbett (8/13/2008)
When everything is in the same schema ownership chaining rules apply, so if I have schema1.proc1 and access schema1.table1 then all my user/role needs is exec permissions on schema1, but, if schema1.proc1 accesses schema2.table1 then the ownership chain COULD be broken so I would need my user/role to have exec permissions in schema1 and read permissions on schema2.table1. Notice I said could, because I believe, and please correct me on this, that if schema1 and schema2 are OWNED by the same user or role then ownership chaining will still work.This only becomes a problem if your schemas are owned by different users. I have not found any reason (yet) to have schemas owned by anything other than dbo. I am sure it is possible, and probably even necessary in some cases - but, I just haven't found a reason for it yet.
Since all schemas would be owned by dbo - there is no issue with the ownership chain being broken.
I agree, but I wanted to make sure that everyone was aware of this "limitation" when crossing schemas.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 13, 2008 at 2:46 pm
...but I'd argue you are creating unnecessary risk.
Beyond never creating the data in the first place or locking it up so that no one can ever do anything with it, there will always be some risk--as I would guess you would acknowledge. Where we part ways is the word *unnecessary*. I think the proper balancing act is a lot more complicated than necessary vs unnecessary. The concept of necessary is unproductive, because often there are work-arounds to an obstacle (such as not getting the permissions one needs to get a job done quickly). Thus, one could argue that the risk isn't *necessary*. But that doesn't mean the risk isn't the best approach for the situation.
I think the better approach to take when making these kinds of decisions is to weigh potential risks and consequences of a bad event against known and suspected benefits. And then comparing all that against the business needs and desires.
I really and truly appreciate the discussion. I read your post a couple times and waited a bit before replying. Even so, I'm just not buying your argument.
August 19, 2008 at 10:00 am
Someone asked the question about app roles and I had a similar question. If you are developing your own app that has logins; does it make more sense to use logins in SQL Server with no permissions that escalate up to an approle account?
The advantages that I see are:
Using AD or local security policies to force password requirements, locking out accounts and logging bad logins - less custom code to handle login stuff
No need to hard code or store login information for the database logins
Profiling by users
Easy logging with triggers using the user that is logged in
Disadvantages:
Adding users to database might have some management overhead
Viewing 15 posts - 31 through 45 (of 45 total)
You must be logged in to reply to this topic. Login to reply