Archive
Monthly
Go
|
|
DNN Blog
Jul
27
Posted by:
smehaffie
7/27/2006 12:00 AM
IMHO there is a security feature in DNN that is not used enough, and it is really easy to implement. The feature I am talking about is the use of the upgradeConnectionString. This was added way back in version 2.?? (2.0.12 I think) and it is still not used by most users. What this feature allows you to do is run DNN under a less privelege account, and the upgradeConnection user is for all installation DB functions. Below is instructions on how to implement this feature. ------------------ 1) Create 2 users in the database with the following permissions. a) PortalUser -- public b) PortalAdmin -- public
-- db_securityadmin
-- db_ddladmin
-- db_datareader
-- db_datewriter2) Use PortalUser in the <SiteSqlServer> key. This key is under <appSettings> in DNN3 and under <appSettings> and connectionStrings> in DNN4. 3) Use PortalAdmin in the <upgradeConnectionString> key which is under the SqlDataProvider section. The same place dbowner and objectQualifier are set. ------------------ That is all there is to using this security feature. The PortaUser will only have access to execute Sprocs (3.2.2 and previous, 4.0.3 and previous) and Sprocs and User Defined Functions (3.3.3+ and 4.3.3+) and not have any direct table access. The PortalAdmin user now has all the permissions the PortalUser would have had if upgradeConnectionString was not used ,and PortalAdmin is only used during the install process (including module installs). As a final part of the installation process, if the upgradeConnectionString is being used the install makes sure public has execute permissions for all stored procedure and execute/select permissions based on type of User Defined Function (Scalar or Table). By implementing the above security feature you will make you DB Admins and Security Group extremely happy. I know that when we used DNN before the upgradeConnectionString was implemented, we ran the install with the user having DBOwner access, and then we updated the user permissions to only have public access and manually ran a script to grant public execute permissions on all the Sproc's. As a matter of fact, that SQL script was used as the basis for the code that the install uses to grant the appropriate permission to public and later also to User Defined functions :-). I am in the process of upgrading the installation documentation to reflect the use of the upgradeConnectionString to make DNN more secure, but until that is completed you can use this blog to help you implement this feature.
12 comment(s) so far...
Re: Most Under-Used Security Feature in DNN
Shawn,
Thanks for pointing this out.
I will also try to implement this in the upcoming installer (maybe not right away in the first version, but in a later version).
Using this, will the host users's SQL module still work (since there is no direct table access) ?
By Geert on
11/29/2006 5:57 PM
|
Re: Most Under-Used Security Feature in DNN
Hi. I like this recommendation, however, it appears to be only geared to using SQL Server authentication. Do you have any specific recommendations for those of us running the DNN application pool under a low privileged domain account and using Integrated authentication on SQL Server? What do you see as the appropriate permissions for that domain account in SQL Server?
Thanks.
Rob Ralston
By rralston on
11/29/2006 5:58 PM
|
Re: Most Under-Used Security Feature in DNN
So you say this is best practice for installation. How would we go into site already running and apply this? Beyond hte obvious Web.Config changes, and adding/modifying the db users, how do we "trip" the routines to ensure all the permissions are set?
By RLyda on
11/29/2006 5:58 PM
|
Re: Most Under-Used Security Feature in DNN
Tried this but in sql 2005 i cannot any user to the public group.
By mariette on
11/29/2006 5:58 PM
|
Re: Most Under-Used Security Feature in DNN
Thanks for this heads up!
By ErikVB on
11/29/2006 5:58 PM
|
Re: Most Under-Used Security Feature in DNN
When a module is installed/upgraded (as opposed to the DNN framework), if the developer has not included GRANT EXEC permissions to the SProcs etc in the SQL script, will DNN ensure that these permissions are granted to Public?
By lneville on
11/29/2006 5:58 PM
|
Re: Most Under-Used Security Feature in DNN
1) RLyda: Making current site use this feature
-- Make web.config changes.
-- Create additional user and update permissions accordingly.
-- Access database and make manually give public execute access to all sprocs. Do the same for User-Defined-Functions.
2) lneville: Module install
-- The code handles this and after a module is install, as the last step it loops through all Sprocs and user defined functions and makes sure they have the needed permissions.
3) rralston: Integrated SQL Authentication
-- To be honest not sure, but you I think you could use a combination of both. SQL Integrated Authentication for you low privilege account and then create User that will use SQL Authentication for the higher privileged account.
By smehaffie on
8/8/2006 9:42 PM
|
Re: Most Under-Used Security Feature in DNN
P.S. Sorry it took so long for me to answer some of the questions that were posted.
By smehaffie on
8/8/2006 9:44 PM
|
Re: Most Under-Used Security Feature in DNN
I just posted about this on my blog here. Am I correct that upgradeConnectionString cannot be used with integrated windows authentication? Thanks.
By DavesTechShop on
11/29/2006 5:59 PM
|
Re: Most Under-Used Security Feature in DNN
DaveTechShop,
I think you could set it up so that the upgradeConnectionString used integrated windows and the less privilege connectionString could use SQL Authentication. At least that way the user login information for the more open account is not stored in the web.config. I have not had a chance to test this yet.
By smehaffie on
11/29/2006 6:01 PM
|
Re: Most Under-Used Security Feature in DNN
Does running this modification to the DNN SQL database reduce or in any way violate the terms and agreements of DNN Professional Edition support?
Allowing the db_owner role seem to cause a lot of heat with Database Admins.
By mboolean on
5/4/2009 7:10 AM
|
Re: Most Under-Used Security Feature in DNN
mboolean,
I have not read the Pro edition "Terms and Agreements", but I would not think this would violate it. There are many different ways that you can configure the SQL database providers (I test 4 different scenarios with each major release). Every installation is different and if a site needs as much security as possible, then the above configuration give you a little more security on the DB side.
Since I am not a DotNetNuke Corp employee, I cannot give you a definitive answer though. To be safe I would contact someone at DotNetNuke Corp and they can tell you for sure whether this would violate the "Terms and Agreement" for the Pro version. I will also try to contact someone at the core to see if they can post an official answer here.
By smehaffie on
5/4/2009 7:17 AM
|
|