In the database, I have objects that are NOT used by DNN but by a related application. Those objects belong to a different schema, I'll call it northwind for the purpose of this post. So there are procedures something like northwind.CustomerSave and northwind.CustomerDelete.
In this database, all of the DotNetNuke objects belong to the dnn schema and the login used by dnn defaults to that schema. The SqlDataProvider references that schema as shown below.
<add name="SqlDataProvider" type="DotNetNuke.Data.SqlDataProvider, DotNetNuke.SqlDataProvider"
connectionStringName="SiteSqlServer" upgradeConnectionString=""
providerPath="~\Providers\DataProviders\SqlDataProvider\"
objectQualifier="" databaseOwner="dnn" />
During the process of installing any DNN module, DotNetNuke attempts to grant exec permissions on ALL stored procedures. It should not do this in my opinion. However, even worse, it does not qualify the object names with the schema name so any of the grants that apply to an alternate schema will fail.
Here is what happens when I install any module. DotNetNuke executes the statement below to get all stored procedures and grant execute on them to the login used by DotNetNuke.
if exists (select * from dbo.sysusers where name='MyDotNetNukeDatabaseUser')
begin declare @exec nvarchar(2000)
declare @name varchar(150)
declare sp_cursor cursor for select o.name as name
from dbo.sysobjects o
where ( OBJECTPROPERTY(o.id, N'IsProcedure') = 1 or OBJECTPROPERTY(o.id, N'IsExtendedProc') = 1 or OBJECTPROPERTY(o.id, N'IsReplProc') = 1 )
and OBJECTPROPERTY(o.id, N'IsMSShipped') = 0
and o.name not like N'#%%'
and (left(o.name,len('')) = '' or left(o.name,7) = 'aspnet_')
open sp_cursor
fetch sp_cursor into @name
while @@fetch_status >= 0
begin
select @exec = 'grant EXECUTE on [' + @name + '] to [MyDotNetNukeDatabaseUser]'
execute (@exec)
fetch sp_cursor into @name
end deallocate sp_cursor
end
The grant statement ends up looking like "grant EXECUTE on CustomerSave to [MyDotNetNukeDatabaseUser]."
SQL Server executes it in the default context of the login so it becomes "grant EXECUTE on dnn.CustomerSave to [MyDotNetNukeDatabaseUser]." That statement fails because there is no object named dnn.CustomerSave. The package will often (not always) fail to install with an error message like below (only much longer since it lists every stored procedure. I have truncated the error message for this post.
|
| Error loading files from temporary folder - see below |
| StartJob |
Starting Installation |
| Info |
Starting Installation - DNN_UserDefinedTable |
| Info |
Starting Installation - Script |
| Info |
Begin Sql execution |
| Info |
Created - providers\dataproviders\sqldataprovider\03.01.00.sqldataprovider |
| Info |
Executing 03.01.00.sqldataprovider |
| Info |
Start Sql execution: 03.01.00.sqldataprovider file |
| Failure |
SQL Execution resulted in following Exceptions: System.Data.SqlClient.SqlException: Cannot find the object 'pr_MailingInsert', because it does not exist or you do not have permission. Cannot find the object 'pr_StatusListByParty', because it does not exist or you do not have permission. Cannot |
|
|
| System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(SqlConnection connection, CommandType commandType, String commandText, SqlParameter[] commandParameters) at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(String connectionString, CommandType commandType, String commandText, SqlParameter[] commandParameters) at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(String connectionString, CommandType commandType, String commandText) at DotNetNuke.Data.SqlDataProvider.GrantStoredProceduresPermission(String Permission, String LoginOrRole) if exists (select * from dbo.sysusers where name='VbDnn_WebAppUser') begin declare @exec nvarchar(2000) declare @name varchar(150) declare sp_cursor cursor for select o.name as name from dbo.sysobjects o where ( OBJECTPROPERTY(o.id, N'IsProcedure') = 1 or OBJECTPROPERTY(o.id, N'IsExtendedProc') = 1 or OBJECTPROPERTY(o.id, N'IsReplProc') = 1 ) and OBJECTPROPERTY(o.id, N'IsMSShipped') = 0 and o.name not like N'#%%' and (left(o.name,len('')) = '' or left(o.name,7) = 'aspnet_') open sp_cursor fetch sp_cursor into @name while @@fetch_status >= 0 begin select @exec = 'grant EXECUTE on [' + @name + '] to [VbDnn_WebAppUser]' execute (@exec) fetch sp_cursor into @name end deallocate sp_cursor end |
| Info |
End Sql execution: 03.01.00.sqldataprovider file |
| Info |
Finished Sql execution |
| Failure |
Installation Failed - Script |
| Info |
Installation Failed - DNN_UserDefinedTable |
| Info |
Deleted temporary install folder |
| EndJob |
Installation Failed |
I would argue that several changes should be made to this in the core code. I don't see why installing a module needs to do a grant on everything in the database. It seems to me that the module should know what it is installing and the permissions should only be granted for those new procedures. I'm sure I won't win that argument because it would probably break too many existing modules.
Second, I don't know why you need to query the system tables when there is a perfectly good view available. NOte the use of the system function QUOTENAME which means we don't have to concatenate the brackets into the generated grant statement.
SELECT QUOTENAME(ROUTINE_SCHEMA) As Schema_Name, QUOTENAME(ROUTINE_NAME) As Proc_Name
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE='PROCEDURE'
If you wanted to restrict it to just the DotNetNuke schema objects, add "AND ROUTINE_SCHEMA = 'dnn'" to the query above. I realize this is just procs but it's easy to just union INFORMATION_SCHEMA.TABLES for the tables and views.
No matter how you get there, the SQL in the Grant Exec/Select Statement needs to be qualified with the schema name so the query succeeds appropriately. The grant statement should be grant EXECUTE on @schemaName + '.' + @name to [MyDotNetNukeDatabaseUser]
I'd like to see this fixed in the core code.