March 22, 2005 at 9:34 pm
Generally speaking, you want to minimize the number of accounts that have login rights. You also, however, want each user to login using a login only they have access to, in other words, no shared logins.
Fewer # of logins means less accounts to have to secure. So optimally the fewer the # of logins as possible. However, you also want to have an audit trail. If 15 people have access to one account, it can be near impossible to tell who actually used it, hence the reason you go one-for-one. So given those two considerations, the same argument doesn't hold.
Second, poorly written queries aren't the only concern with direct access to the tables. There's nothing stopping, for instance, a DELETE * FROM <table> if base table access is allowed. Using stored procedures, you can restrict how the data is touched, not only on a change, but also how it is read. Combined with views and you can really tighten down the data access layer.
You said there were Linux apps and that precludes this argument but in the case of Windows and ADO, the use of stored procedures also lends itself more towards the use of ADO Command objects ith parameterization. The use of such reduces greatly the risk of SQL injection attacks.
K. Brian Kelley
@kbriankelley
March 24, 2005 at 8:03 am
Just for an opposing point of view, let me say "It depends". We have a number of databases that are owned by a standard login account. The reason? Third party applications. Whenever vendors issue "upgrades" most of their scripts or CDs run under the assumption that they are using the "sa" account and do not qualify objects with dbo. Thus, whomever runs them owns the objects. (It seems to be common practice to modify the database along with the application.)
To prevent this, I change the database owner to the account that is used when the scripts or CD is run. This way all objects are owned by dbo, and the owner can always be changed if needed.
Linda
March 24, 2005 at 8:17 am
In 3rd party cases, I'd tend to agree with you, only because you're not left with my choice in order to be supported. But in a homegrown app...
K. Brian Kelley
@kbriankelley
March 24, 2005 at 2:41 pm
Linda,
I am sure it could be a workaround.
I have a test database owned by someuser (someuser is a local server login). Then I added my Windows Domain login to this database users and made her db_ddladmin. After that I created 2 views one from Query Analyzer and one from Enterprise Manager connected using Windows Authentication in both cases. In both cases I did NOT add a dbo. to the view name when creating this view. Both views have a DBO as an owner because my Windows login is a member of db_dlladmin role.
It is easier to make a login a member of db_dlladmin and use this login to connect to SQL Server when running updates then changing database ownership to and back every time
Have a good Spring Holiday,
Yelena
Regards,Yelena Varsha
March 24, 2005 at 2:51 pm
Maybe I didn't explain clearly... I don't change the ownership back and forth. I just stated that the ownership could be changed if needed. The vendor applications will not use any views, objects, users, etc. that I create so the best solution for me was to have their specific login to be the dbo. Thanks for the suggestion though.
Linda
March 24, 2005 at 3:04 pm
Being a member of db_ddladmin doesn't auto-create objects as dbo. Being a member of db_ddladmin will still create object as your user unless you're aliased as dbo... which you are if you're a member of the sysadmin fixed server role. Sure that isn't the case here?
Okay, switching ownership back and forth... The problem with the approach of switching database ownership back and forth is that unless the application developers coded to include ownership references, you're potentially going to take a performance hit. Reason being SQL Server will look for a cached execution plan for objects belonging to the calling user first. Not finding that, it'll look for dbo.
Troubleshooting stored procedure recompilation (243586)
Look for the section entitled Best Practice.
Edit: Left out the response to the part I was replying to. Oops!
K. Brian Kelley
@kbriankelley
March 24, 2005 at 4:19 pm
Oh yes,
You are sure right. I did have a recollection that db_dlladmin creates objects as dbo, checked it out, no, sysadmin membership. oops... My login has implicit sysadmin membership, yes.
Just did not think that applying patches and fixes does not requier admin rights... I would still better give sysadmin rights the the person that I trust to apply patches and fixes then changing db ownership... or if I don't trust, give me the patch I will do it myself.
Yelena
Regards,Yelena Varsha
March 24, 2005 at 11:02 pm
I think "It depends" is a good answer. When I suggested using an SQL login like [database_name]_dbo, I failed to make clear the nature of our environment. We were an ASP company (Application Service Provider, not Active Server Pages). We hosted web-based applications and databases for other companies. Our customers (banks) were extremely sensitive to security issues, naturally. If it was possible to abolish the sa login, I'm sure they would have insisted on it!
Each customer had their own database, but an SQL Server would host databases for several customers. Each customers' end-users had their own individual login/password, but these were neither SQL logins, nor Windows logins (remember, Linux clients). The end-user logins and passwords were stored in user tables and the login process was implemeted in a combination of client and server code.
All end-users of a customer shared a single SQL login, but only from a connection perspective - no customer ever knew their connection login or password, only their programs (written by us) used it.
Since most customers needed fairly elaborate security schemes, usually row-based, permissions were implemented using access control lists. These would specify which end-users could access which screens, fields the screens would display, which data (rows) they could modify, or even see, etc.. The access control lists were implemented in a table-driven manner, enforced by program code.
Now, some may balk at enforcing permissions by program code, but try doing row-based security any other way (or without thousands of views 😉 At least it was table-driven, and maintainable by the customer and not by us, thank you!
This security model also enabled features like password standards enforcement, encryption, and a three-strikes-you're-out account disable to prevent dictionary or guesswork attacks (a feature SORELY lacking in both SQL Server and Windows - hope 2005 has it...)
The _dbo login was not the one used by the client programs - they used another login with fewer permissions. Rather, the _dbo login was used by developers/admins to allow schema/code changes while keeping the sa password as tightly controlled as possible, to minimize our customers' security fears.
All in all, perhaps an unusual environment, but a secure and valid approach, I think.
March 24, 2005 at 11:45 pm
OK, but from an auditing perspective there is an avoidable weakness...
I can understand shielding the customer from direct DB access. A lot of good reasons to do that, especially in the hosted environment you describe. However, you've got your admins and developers logging in as a single account to make database changes. The problem with this is the audit trail gets lost. If 10 people have access to the same username/password and that username/password is used to cause catastrophic damage to a database, whodunit? This is the situation auditors love to point to and they're right to do so.
If individual logins are used, whether they are Windows based (in which case you want to encapsulate in a group) or SQL Server-based (such as if you have to connect from Linux), you still can provide the same privs by assigning said logins to the db_owner role. Yes, it means schema change scripts must have ownership specified for objects, but that's not exactly a bad practice to follow. Plus, with a 1-to-1 account per developer/administrator, you can create that audit trail in case of an incident.
K. Brian Kelley
@kbriankelley
March 25, 2005 at 12:13 pm
Good points about auditing. However, login proliferation was considered by the customers to be worse. Logins with permissions in more than one database (likely a competitor's) were forbidden, with the unavoidable exception of sa.
The original question on this forum was what login should own the database. SA is ruled out. Surely you are not suggesting that the database should owned by Bill, Todd, or Sally?
March 25, 2005 at 12:26 pm
With respect to what has been covered, in most environments sa isn't ruled out. Also, given the example you presented, there is nothing, other than a procedure to excluse sa from db ownership. Effectively sa has the ability to do whatever it wants, explicit ownership or not. With the exception of cross-database ownership chaining (if in use or pre-SQL2KSP3), having the databases owned by sa doesn't incur more risk to the environment.
As far as logins with permissions in more than one db... with the model presented you've kept a management account from hopping to a second database (I'm not speaking of the accounts the apps used to provide service to the customers). The problem is, the people holding the management accounts (ones capable of schema changes) could hop simply by establishing another connection with the appropriate login. So you still have personnel capable of schema changes, only you can't tie a login to one particular person. Also, if you have more databases than personnel with access, following this model actually creates more accounts than using each developer's/admin's account. So instead of solving login proliferation, it's actually worsened.
K. Brian Kelley
@kbriankelley
March 25, 2005 at 1:27 pm
My rule of thumb is one login per user/application. Even if you have to create two logins with identical effective permissions this prevents ambiguity and in the long run simplifies administration. Having multiple users use a single login is a huge security (accountability) problem. Having multiple apps use a single login leads to problems when changing passwords and it tends to violate the principle of least privelege. Instead of granting just the rights needed by each application (or application module) using a single login forces you to grant the superset of all permissions needed.
Either way you look at it I consider it bad practice to share logins, even if it would appear to save time and effort in the short run. And no one uses sa, the sa password is stored in a secure location, known only to those who are members of the sysadmin group, but it is given a horrendously complex password and never used.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
March 25, 2005 at 6:40 pm
I totally agree with Brian and dcpeterson. There should be an individual login for each user. It is like a private property.
Fred, if you work for ASP with many customers, why would not you use Named Instances? The main Microsoft Named Instances usage example and recommendations when to use them is in exactly this case: When a service provider has to provide services to different companies from one server and the per-customer security has to be implemented.
The other solution is to get server-per-customer model, hardware is not expesive now, the cost of a good server may be less then paying its support for the time spent implementing shared security.
Yelena
Regards,Yelena Varsha
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply