|
|
You're in the
sql server msde
group:security issue with dbo/sa
sql server msde:
Hi
I have a dreamweaver/coldfusion application which uses MSDE as it's back end. I have been using the User Name DBO, Login Name SA as the owner of my objects so that whenever I use a query I preface the field with [dbo].[tablename].fieldname. I remember reading somewhere that I should not have used dbo/sa as it presents a security risk. Is there any way I can resolve this without having to go through all my code and replace [dbo] with another user name? Thanks June
hi June,
[quoted text, click to view] June Macleod wrote: > Hi > > I have a dreamweaver/coldfusion application which uses MSDE as it's > back end. I have been using the User Name DBO, Login Name SA as the > owner of my objects so that whenever I use a query I preface the > field with [dbo].[tablename].fieldname. > > I remember reading somewhere that I should not have used dbo/sa as it > presents a security risk. > > Is there any way I can resolve this without having to go through all > my code and replace [dbo] with another user name? as regard the object(s) ownership, having "dbo" as the owner is/can be a correct policy... SQL Server 2000 does not have the same architecture as SQL Server 2005 where a new "schema" layer has been introduced and where you can define separate schema(s) for your objects in order to make a "better" design and to divide object(s) ownership in logical way.. SQL Server 2000 only has the database user ownership and usually the "dbo" definition is used for general access.. and it's ok.. there's no security concern about that.. more.. if you do not specify the 2parts naming convention (owner.object, but this is a bad practice as it requires additional work for object reference), all works as expected as doing SELECT <select list> FROM object_name always scans dbo owned objects if no match is found in the "user"'s context... another story is accessing SQL Server with admin privileges for the dayly activities, that's to say the normal DML operations required by apps... you should not access the server that way as a "normal user" is enought and you should not enforce more privileges then required... you do not have to use a hammer to screw down a screw... just use the screwdriver.. -- Andrea Montanari (Microsoft MVP - SQL Server) http://www.asql.biz/DbaMgr.shtm http://italy.mvps.org DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0 (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual interface) --------- remove DMO to reply
Where can I find information on how to limit access to a SQL Server
Express database contents to specific ID/Password combinations? How does one use this new schema layer to facilitate this? Right now any valid Windows user seems to be able to connect to and read the contents of a SQL Server Express database that I create, and I do not wish this to be the case. I would like to define two (2) user identifiers: one that can 'own' the database/schema, and the other that can read/update the data contents. -ken [quoted text, click to view] Andrea Montanari wrote:
> hi June, > June Macleod wrote: > >>Hi >> >>I have a dreamweaver/coldfusion application which uses MSDE as it's >>back end. I have been using the User Name DBO, Login Name SA as the >>owner of my objects so that whenever I use a query I preface the >>field with [dbo].[tablename].fieldname. >> >>I remember reading somewhere that I should not have used dbo/sa as it >>presents a security risk. >> >>Is there any way I can resolve this without having to go through all >>my code and replace [dbo] with another user name? > > > as regard the object(s) ownership, having "dbo" as the owner is/can be a > correct policy... SQL Server 2000 does not have the same architecture as SQL > Server 2005 where a new "schema" layer has been introduced and where you can > define separate schema(s) for your objects in order to make a "better" > design and to divide object(s) ownership in logical way.. > SQL Server 2000 only has the database user ownership and usually the "dbo" > definition is used for general access.. and it's ok.. there's no security > concern about that.. > more.. if you do not specify the 2parts naming convention (owner.object, but > this is a bad practice as it requires additional work for object reference), > all works as expected as doing > SELECT <select list> FROM object_name > always scans dbo owned objects if no match is found in the "user"'s > context... > > another story is accessing SQL Server with admin privileges for the dayly > activities, that's to say the normal DML operations required by apps... you > should not access the server that way as a "normal user" is enought and you > should not enforce more privileges then required... > you do not have to use a hammer to screw down a screw... just use the
hi Ken,
[quoted text, click to view] Ken Allen wrote: > Where can I find information on how to limit access to a SQL Server > Express database contents to specific ID/Password combinations? SQL Server security is actually bases on a combination of 2 phases.. the 1st one, related to Login, is where a user provides a piece of information (like username and pwd for a standard SQL Server authenticated connection, or the sid of his/her underlying Windows account/group [even certificated by a Domain Controller when available] for a trusted connection).. once the credential you provided has been validated you'll be granted access to SQL Server and the connection is completed.. (http://msdn2.microsoft.com/en-us/library/ms189751.aspx) then you have to access the desired database(s), and here the 2nd phase comes into play.. In order to achieve te desired result, as each SQL Server instance can host multiple databases and you could required a certain degree of granularity in database access, an "administrator" has to create a user object in each database her/she wants the related login to be accessible (english?? :D), mapping the login with a database user... (http://msdn2.microsoft.com/en-us/library/ms173463.aspx) you can only access databases that you have users created for you in. The user is then (not mandatory) granted the permissions necessary to do required in the database (GRANT SELECT/DELETE/EXEC etc) The reason for this "duality" is that you may have different permissions depending on which database you are accessing. For example you may have permissions to create and drop tables and update any data in the "A" database but only have read permissions for data in the "B" database. More, you may not even be allowed to access the "C" database.. [quoted text, click to view] >How does one use this new schema layer to facilitate this? the schema layer has been introduced to accomodate the ownership problems related to prior SQL Server versions (2000 included).. each database object requires a an owner, so you could create the Employees table owned by yourself... everyone who need access to that table has to reference is as [Ken].[Employee] ... what if you will be fired in the next future (I hope this is not an option :D)? the dba in charge should remove your login and database user from the system, but this can be done only changing the ownership of the [Ken].[Employee] table to another owner... this can be problematic as well, and could break existing application.. schemas drop this problem as you no longer create [Ken].[Employee] objects but, say, [HumanResources].[Employee] (as in the AdventureWorks SQL Server 2005 database).. each schema still requires an owner, but you can easely manage schema ownership to different db users, without breaking application's code.. but you still are required to manage permission for each user (or grouping users in related database role(s) and thus managing privileges at this higher level, say all Accounting departments people will be member of the Accounting database role you have to create [http://msdn2.microsoft.com/en-us/library/ms187936.aspx and http://msdn2.microsoft.com/en-us/library/ms187750.aspx] ) each database has a "Guest" database user, member of the "Public" database role; when you access a database without a defined login-user mapping you'll be subclassed to that user... I do personally disable that database user in the Model database so that every new database that will be created will not include this "feature" and preventing unauthorized access via the Guest user (even if Public role permissions are very very low).. [quoted text, click to view] > Right now any valid Windows user seems to be able to connect to and > read the contents of a SQL Server Express database that I create, and > I do not wish this to be the case. I would like to define two (2) user > identifiers: one that can 'own' the database/schema, and the other > that can read/update the data contents. this is possible only if your instance includes the BUILTIN\Users Windows group (for trusted connections).. then you can modify your database with 2 separated database roles, one for each of your requirements.. -- Andrea Montanari (Microsoft MVP - SQL Server) http://www.asql.biz/DbaMgr.shtm http://italy.mvps.org DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0 (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual interface) --------- remove DMO to reply
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
| home · blog · groups | Questions? Comments? Contact the dn |