SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server Security: Fixed Database Roles


SQL Server Security: Fixed Database Roles

Author
Message
K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (47K reputation)

Group: Moderators
Points: 47994 Visits: 1917
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/bkelley/sqlserversecurityfixeddatabaseroles.asp

K. Brian Kelley
@‌kbriankelley
Dave Poole
Dave Poole
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29653 Visits: 3602
Nice summary.

The one thing that puzzles me is that in SQL6.5 we used the sp_addalias in order to alias users to the dbo as there wasn't a db_owner role.

This got around the business of accidentally creating objects not owned by the dbo.

The current books online says that this practice is only included for backwards compatibility. The implication of this is that
a) It is going to be made obsolete at some time in the future.
b) Its use is frowned upon.

Frankly, I find the db_owner role a bit of a red herring given the non-dbo owned object pitfall and that sp_addalias works fine.

What are your views on this?

========================
He was not wholly unware of the potential lack of insignificance.

LinkedIn Profile
www.simple-talk.com
K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (47K reputation)

Group: Moderators
Points: 47994 Visits: 1917

Keep in mind that as either a member of the db_owner or db_ddladmin role, you could create objects that are dbo owned. We make it a standard practice where I work to include owners on all object creation statements just as a general rule so the extra 4 characters per object isn't a concern for us. However, if that isn't a standard practice, I can see the issue.

One of the issues with using aliases is they don't show up in Enterprise Manager. While the information can be seen by executing the T-SQL commands, a lot of DBAs rely on EM because it's quick and easy. When dealing with users and roles, I'll admit I use it a lot myself. That's one of the main issues I see with it.

Another issue is there may be cases where you don't want anyone accidentally touching certain objects in the "wrong" way. For instance, you have a lookup table and there shouldn't be any INSERT, UPDATE, or DELETE statements run against it. Yet you have folks that need to have basically db_owner permissions (this is a rare case, but it is possible). The dbo user bypasses all permissions. Members of the db_owner role do not. Therefore, the table could be set with DENY INSERT, UPDATE, DELETE to public and those who were a member of the db_owner role (but not mapped or aliased to dbo) could do what they needed to do within the database, but they couldn't accidentally change data in the table.

K. Brian Kelley
@‌kbriankelley

tulcanla
tulcanla
Old Hand
Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)

Group: General Forum Members
Points: 357 Visits: 1
Thanks for the article.
What buggs me is the public role ijn Master db
because it has powers against information_schema objects

Could we get rid of those permissons with no further negative effects?

Thanks


Lavinia T



K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (47K reputation)

Group: Moderators
Points: 47994 Visits: 1917

In some cases, yes, but not in all cases. My GSEC practical is now online. Its title: SQL Server 2000: Permissions on System Tables Granted to Logins Due to the Public Role.

You can find it here: http://www.giac.org/practical/GSEC/KBrian_Kelley_GSEC.pdf

K. Brian Kelley
@‌kbriankelley

cbachmann
cbachmann
Old Hand
Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)Old Hand (350 reputation)

Group: General Forum Members
Points: 350 Visits: 21
I enjoyed this article, it brings to mind a question that I've not been able to answer regarding permissions, maybe someone out there can suggest a solution.

I'm working on an ASP.NET application that uses SQL2005 as the DBMS. We're using Integrated Security only -- SQL Security is not enabled. We're also using transactional replication to replicate data to local clients, we also retrieve and process data from these clients (not via replication). The application maps all users to a Windows user that has explicited defined permissions to the database, which do not include dbo_owner or syaadmin rights. The application is meant to include the ability to back up the database (after the user has determined that all transaction data has been retrieved from the client machines), therefore this standard user needs the ability to create a backup.

The logical solution is to add this standard user to the db_backupoperator role. However, I've found that when replication is installed, the server fires a procedure called sp_MSrepl_backup_start before running the backup. This proc. examines the permissions of the user running the backup and raises an error (18799) if the user is not a member of the sysadmin or dbcreator server roles, or db_owner on the database.

I defeated this by using a try-catch in the procedure we call to run the backup, however, I'm concerned that I'm working around a problem that is more profound than I understand. Can anyone propose a better way to solve this problem?



noeld
noeld
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42766 Visits: 2052
dbachmann,

When replication is installed *many* of the database security roles that used to work are effectively broken. The reason is that M$ is hardcoding those permissions on the replication stored procedures that are "hooked" at call time when either backup/restore occur. Personally I confronted the same expericence not only at backup time but also at restore time under "certain" conditions. I my opinion Replication stored procedures are incorrectly coded because they are not only called from a wrapper but also from BACKUP/RESTORE.

My solution was simpler in one case that I could afford granting db_owner permissions to that account but in other cases the solution was what you just did. Now if you are in control of the server another trick is to create a pooling job that reads from a table where you post your requests and the job (running under higher privileges ) does the backup for you.

Good Luck


* Noel
@STLSQLGuy
@STLSQLGuy
SSC Eights!
SSC Eights! (902 reputation)SSC Eights! (902 reputation)SSC Eights! (902 reputation)SSC Eights! (902 reputation)SSC Eights! (902 reputation)SSC Eights! (902 reputation)SSC Eights! (902 reputation)SSC Eights! (902 reputation)

Group: General Forum Members
Points: 902 Visits: 209
Good Article.

I posted this here as this is a question on this same topic. I know it is documented by MSFT that these roles are set in stone, cannot be changed, etc. That being said has anyone been able to find a way to create a new server level role or modify any of the default ones?
Any feedback would be appreciated.

Thanks,

Eddie


thanks, ERH

K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (47K reputation)

Group: Moderators
Points: 47994 Visits: 1917
There is not a way to create new server roles, no. You can, in SQL Server 2005 and 2008, assign server level permissions to logins. So if your logins were in a Windows group, you would accomplish the same purpose.

K. Brian Kelley
@‌kbriankelley
FreeHansje
FreeHansje
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4907 Visits: 810
I'm trying to get a question answered and have not found the complete answer; consider the following:

I create ba new database, NewDB with a table, NewTable. I create a new login, let's make it an SQL login: NewUser. I give NewUser access to NewDatabase but assign NO PERMISSIONS what-so-ever. When I open a Querywindow on NewDB and change connection to NewUser; next I type SELECT * FROM NewTable and lo! I see a resultset! Which surprises me. I thought that the PUBLIC role default had not any permission, but it seems it has at least READ permission. Which is not what I would want.
Now, is it so, that a user which is given access to a database will always have read permission because of the PUBLIC role? I have checked the security catalogues for PUBLIC and find among other the following:

select * from sys.server_permissions where grantee_principal_id= (select principal_id from sys.server_principals where name='public')

The first result is this:
class class_descr major_id  minor_d grantee_id grontor_id type    permission_name      state state_descr
100 SERVER 0 0 2 1 VWDB VIEW ANY DATABASE G GRANT



Specifically the type/permissionname makes me wonder: VWDB/View Any Database. This seems to be the default for the Public role, and it is the case on any SQL Server I have checked sofar, 2005 AND 2008.
Is this behaviour intentional? Do I miss something here?

Greetz,
Hans Brouwer
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search