| Author |
Topic |
|
poser
Posting Yak Master
124 Posts |
 Posted - 2007-09-10 : 11:53:48
|
| I have a database created with the dbo as user1. Our peeps think there should be no dbo in the database as the user2 is the sa and can do all the changes. Does there have to be a user assigned to dbo? I know the sp_changedbowner but you have to assign a user to it.TIAR/P |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-10 : 12:25:10
|
| Generally a good idea for all tables, Sproc, etc. to be owned by DBONOT a good idea for a user of the database (as compared to a Developer perhaps) to have SA access to the database, otherwise you cannot restrict permissions. Even if you are happy that the user can access everything now you might not be in the future, or you may have logic in the system which is intended NOT to be accessible by a User, rather than an Admin or Developer etc.Kristen |
 |
|
|
poser
Posting Yak Master
124 Posts |
Posted - 2007-09-10 : 12:45:17
|
| Who should be the dbo? Should it be the dba and if so they are sa and domain admins? Should it be a separate acct sql or domain?We are really trying to lock this down.Thanks, P |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-10 : 15:38:45
|
I don't suppose it has to be anybody Just so long as the Objects are owned by DBO that's fine.Then you can provide Permissions to access the objects to Tom, Dick and Harry as appropriate - preferably by Roles, rather than specifically for each user separately.Kristen |
|
|
|
poser
Posting Yak Master
124 Posts |
Posted - 2007-09-10 : 15:47:44
|
| Thanks Kristen for your help. Just one more question, I promise.How do I change the dbo from dbo.user1 to nobody?Again much thanks,P |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-10 : 16:08:07
|
| "How do I change the dbo from dbo.user1 to nobody"If you mean on an object, like a table, you can't - the objects have to be owned by someone, and DBO is the preferred choice.If you mean that you have set up a Server Login to be a User on the database (and mapped to DBO/SA) and you want to change it, then probably what you are after is:Enterprise managerChoose Server : Security : Logins : Right-click Name : Properties : [Database access]You should see the databases the user has access to, and the "User" they are aliased to.Edit the "User" as appropriate - usually by setting to their own Username, and using the "Roles" (bottom half of dialog box) to adjust what permissions the user has.Kristen |
|
|
|
poser
Posting Yak Master
124 Posts |
Posted - 2007-09-10 : 16:22:11
|
| Thanks. We had a sql injection attack and we were trying to only have users in the database with the least amount of priveledges as our application user login was dbo. We changed that user to have db_datareader and db_datarwriter. I guess for each application we will have to create a separate sql user (for each db)to be dbo; cause I don't think we would want one user to be dbo to all our applications!Thanks again for your help Kristen.P |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-10 : 16:29:46
|
| Well, these two statements don't sit very comfortably with my long years of experience of things going wrong!"user2 is the s and can do all the changes"and"We had a sql injection attack and we were trying to only have users in the database with the least amount of priveledges""We changed that user to have db_datareader and db_datarwriter"That means they can read and write to any table in the database. Including being able to delete all the rows in a table once they are connected ...If you could change your application to use Stored procedures then you could ONLY grant permissions to EXECUTE those Sprocs. So ... if you have a "Delete a record" Sproc a user could delete every row in the table still, but they would have to call the Sproc for EACH row in the table, which in turn means they would have to find the PKs of every row in the table, and be bothered to make the effort.SProc would make SQL Injection attacks impossible (assuming that the Sprocs do not themselves use dynamic SQL)Either way, you should be using parametrised SQL, not "ad hoc dynamic SQL" [usually constructing using string concatenation in the Application]If you've got any questions plenty of folks where will be willing to help - and I'm sure that would stretch to trying to hack into your Test systems, given permission!Kristen |
|
|
|
poser
Posting Yak Master
124 Posts |
Posted - 2007-09-10 : 17:55:51
|
The web developers are working on their code so as to use sprocs.I will test with them tomorrow on creating an acct. and just granting exec on the sprocs. I guess I will still need to create another user to be dbo.Thanks again, you have been so helpful. I may have many more questions. P |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2007-09-10 : 17:58:45
|
| easier to manage if dbo is sa, then create accounts that with restricted permission like execute on the sprocs and select or execute on the functions--------------------keeping it simple... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-09-10 : 17:58:53
|
| No one needs to be dbo.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
poser
Posting Yak Master
124 Posts |
Posted - 2007-09-10 : 18:05:54
|
| Tara,How do I reassign dbo..user1 to no one? Thanks, P |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-09-10 : 18:11:48
|
| Set user1 to user1 in the Database Access tab of Login properties. It should say user1 in the User field for each of the databases that the user has access to.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
poser
Posting Yak Master
124 Posts |
Posted - 2007-09-10 : 18:35:42
|
| When I go to the user logon and the box next to the database is checked and user says dbo. I uncheck the box and I get the error message that "database owner can not be dropped" So under role I try to change dbo I ger error "15405: Can not use the reserver user or role name 'dbo'"So it looks like somebody has to be assigned to dbo? I know I can run the sp_changedbowner but that still assigns someone to dbo.I am so new to security and sorry for all the questions.Thanks, P |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-09-10 : 19:11:23
|
| Change the database owner to sa.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
poser
Posting Yak Master
124 Posts |
Posted - 2007-09-10 : 19:19:51
|
| Thank you very much. There was confusion on whom should be dbo.Some said sa should not be dbo!You all rock!P |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-09-10 : 19:26:22
|
| sa is always dbo!Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
poser
Posting Yak Master
124 Posts |
Posted - 2007-09-11 : 14:00:05
|
| Our developer has been working on his sprocs to update/delete etc.So I should only have to grant the user exec on the sprocs and not on any of the tables? Does this user also have to have db_datareader or db_datawriter or select/update/delete on tables?Thanks, P |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-09-11 : 14:20:48
|
| If all access is through stored procedures and no dynamic sql is present in them, then the only thing permission that the user needs is EXEC on the sprocs. This is why stored procedures are recommended for security reasons. The user will not have direct access to the tables and therefore can only do what the sprocs allow it to do.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
poser
Posting Yak Master
124 Posts |
Posted - 2007-09-17 : 11:45:30
|
| I want to thank all of you for your help.We have converted the raw sql into stored procedures and granted exec on them. Everything is working great!Again, Thanks/P |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-17 : 12:42:34
|
| "We have converted the raw sql into stored procedures and granted exec on them"If you haven't already done so you should remove permissions that allow direct access to the tables, if possible.Kristen |
|
|
|
Next Page
|
Change dbo