☰

配置用户以解决 Exact Online 上的 SQL 查询上的 itgenexl029

tag-icon • tag-icon exact-online invantive-sql invantive-data-hub
配置用户以解决 Exact Online 上的 SQL 查询上的 itgenexl029

当我在 Exact Online（荷兰）上使用 Invantive Data Hub 运行以下查询时：

create or replace table spn@inmemorystorage
as
select /*+ ods(false) */ 
       spn.item_code_attr
,      spn.warehouse_code_attr
,      spn.item_description
,      spn.currentquantity
,      spn.planning_in
,      spn.planning_out
,      spn.currentquantity + spn.planning_in - spn.planning_out plannedquantity
from   exactonlinexml..StockPositions spn
--
-- Filter out no stock nor planned.
--
where  ( spn.currentquantity !=0
         or     
         spn.planning_in != 0
         or     
         spn.planning_out != 0
       )

我收到以下错误：

Error itgencun016: Uitroep itgenexl029: U heeft onvoldoende rechten om deze actie uit te voeren.

带有调用堆栈：

Error itgencun017: Type: Invantive.Data.ServiceException
   at Invantive.Data.ValidationException..ctor(String messageCode, String messageText, String kindRequest, String localStackTrace, String nk, Exception innerException)
   at Invantive.Data.InvantiveSystemException..ctor(String validationCode, String errorMessage, String kindRequest, String stackTrace, String nk, Exception innerException)
   at Invantive.Data.ExactOnlineXmlProvider.FailOnAnswerWithErrors(XmlDocument document, String topic, String& responseErrorsOnly, String& responseXmlPayloadRetry, Exception& ex, Int32& countSuccess, Int32& countWarning, Int32& countError, Int32& countFatalError)
   at Invantive.Data.ExactOnlineXmlProvider.GetDataFromWebService(String url, String cacheId, String partitionCode, String topic, XmlObjectDefinition objectDefinition, Int32 pageCounter, String previousTimeStampLastRecord, String callSafeNameOverrule, Int32& pageSize, Int32& count, String& timeStampLastRecord, Int64& documentSizeCharacters, Int64& documentSizeBytes)
   at Invantive.Data.ExactOnlineXmlProvider.<GetRowsDataFromWebService>d__232.MoveNext()
   at Invantive.Data.CompressedEnumerable`1.<GetEnumerator>d__2.MoveNext()
   at Invantive.Data.CachedEnumerable`1.TryGetNext(Int32 index, T& item)
   at Invantive.Data.CachedEnumerable`1.<GetEnumerator>d__6.MoveNext()
   at System.Linq.Enumerable.<SelectManyIterator>d__16`2.MoveNext()
   at Invantive.Sql.DataSourceOrFunctionTree.<GetDataFromDataContainer>d__50.MoveNext()
   at Invantive.Data.CompressedEnumerable`1.<GetEnumerator>d__2.MoveNext()
   at Invantive.Data.CachedEnumerable`1.TryGetNext(Int32 index, T& item)
   at Invantive.Data.CachedEnumerable`1.<GetEnumerator>d__6.MoveNext()
   at Invantive.Sql.FirehoseResultSet.<Iterator>d__57.MoveNext()
   at Invantive.Data.CompressedEnumerable`1.<GetEnumerator>d__2.MoveNext()
   at Invantive.Data.CachedEnumerable`1.TryGetNext(Int32 index, T& item)
   at Invantive.Data.CachedEnumerable`1.<GetEnumerator>d__6.MoveNext()
   at Invantive.Sql.FirehoseResultSet.<Iterator>d__57.MoveNext()
   at Invantive.Sql.FilterIterator.<Iterator>d__3.MoveNext()
   at Invantive.Sql.ChainedFirehose.<Iterator>d__11.MoveNext()
   at Invantive.Sql.SelectListIterator.<Iterator>d__7.MoveNext()
   at System.Linq.Enumerable.Count[TSource](IEnumerable`1 source)
   at Invantive.Sql.DdlStatementTree.OnExecute(ParameterList parameters, ExpressionParser expressionParser)
   at Invantive.Sql.QueryTree.Execute(ParameterList parameters, ExpressionParser expressionParser)
   at Invantive.Sql.QueryPlan.ForcePlan()
   at Invantive.Sql.QueryPlan.get_Fields()
   at Invantive.Sql.QueryPlan.get_FieldCount()
   at Invantive.Data.QueryPlanCacheEntry.<>c.<.ctor>b__12_0(IQueryPlan qp)
   at System.Linq.Enumerable.WhereSelectArrayIterator`2.MoveNext()
   at System.Linq.Buffer`1..ctor(IEnumerable`1 source)
   at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
   at Invantive.Data.QueryPlanCacheEntry..ctor(String sqlStatement, IQueryPlan[] queryPlans, ParameterList parameters)
   at Invantive.Sql.SqlEngine.ExecuteAndFetch(IProviderManager manager, String sqlStatement, ParameterList parameters, Boolean allowSelect)
   at Invantive.Data.ConnectionManager.ExecuteProviderPassthroughSqlActionTable(String actionSql, ParameterList parameters, String& handlingPath)
   at Invantive.Data.ConnectionManager.PassthroughSqlActionTable(String actionSql, ParameterList parameters)
   at Invantive.Data.ActionProceduresBase.PassthroughSqlActionTable(String actionSql, ParameterList parameters)
   at Invantive.Producer.QueryEngine.QueryEngine.ProcessStatement(String sqlStatement, ResultSet& resultSet, Action`1 fetchMoreRows, Action`7 executeStatement, Boolean isInInteractiveMode, ScriptingLanguageNextStepSpecification& nextStepSpecification)
   at Invantive.Producer.QueryEngine.QueryEngine.ProcessStatements(Action`1 fetchMoreRows, Action`7 executeStatement, IEnumerable`1 statements, ScriptingLanguageNextStepSpecification& exitSpecification)
   at Invantive.Producer.QueryEngine.QueryEngine.Run(String[] arguments)
   at Invantive.Producer.QueryEngine.Program.Main(String[] arguments)
   at Invantive.Data.ExactOnlineXmlProvider.GetDataFromWebService(String url, String cacheId, String partitionCode, String topic, XmlObjectDefinition objectDefinition, Int32 pageCounter, String previousTimeStampLastRecord, String callSafeNameOverrule, Int32& pageSize, Int32& count, String& timeStampLastRecord, Int64& documentSizeCharacters, Int64& documentSizeBytes) in File348:line 7573
   at Invantive.Data.ExactOnlineXmlProvider.<GetRowsDataFromWebService>d__232.MoveNext() in File348:line 6871
   at Invantive.Data.CompressedEnumerable`1.<GetEnumerator>d__2.MoveNext() in File275:line 3987
   at Invantive.Data.CachedEnumerable`1.TryGetNext(Int32 index, T& item) in File275:line 4163
   at Invantive.Data.CachedEnumerable`1.<GetEnumerator>d__6.MoveNext() in File275:line 4112
   at System.Linq.Enumerable.<SelectManyIterator>d__16`2.MoveNext()
   at Invantive.Sql.DataSourceOrFunctionTree.<GetDataFromDataContainer>d__50.MoveNext() in File54:line 420
   at Invantive.Data.CompressedEnumerable`1.<GetEnumerator>d__2.MoveNext() in File275:line 3987
   at Invantive.Data.CachedEnumerable`1.TryGetNext(Int32 index, T& item) in File275:line 4163
   at Invantive.Data.CachedEnumerable`1.<GetEnumerator>d__6.MoveNext() in File275:line 4112
   at Invantive.Sql.FirehoseResultSet.<Iterator>d__57.MoveNext() in File25:line 598
   at Invantive.Data.CompressedEnumerable`1.<GetEnumerator>d__2.MoveNext() in File275:line 3987
   at Invantive.Data.CachedEnumerable`1.TryGetNext(Int32 index, T& item) in File275:line 4163
   at Invantive.Data.CachedEnumerable`1.<GetEnumerator>d__6.MoveNext() in File275:line 4112
   at Invantive.Sql.FirehoseResultSet.<Iterator>d__57.MoveNext() in File25:line 598
   at Invantive.Sql.FilterIterator.<Iterator>d__3.MoveNext() in File24:line 93
   at Invantive.Sql.ChainedFirehose.<Iterator>d__11.MoveNext() in File22:line 82
   at Invantive.Sql.SelectListIterator.<Iterator>d__7.MoveNext() in File30:line 120
   at System.Linq.Enumerable.Count[TSource](IEnumerable`1 source)
   at Invantive.Sql.DdlStatementTree.OnExecute(ParameterList parameters, ExpressionParser expressionParser) in File55:line 195
   at Invantive.Sql.QueryTree.Execute(ParameterList parameters, ExpressionParser expressionParser) in File33:line 174
   at Invantive.Sql.QueryPlan.ForcePlan() in File32:line 89
   at Invantive.Sql.QueryPlan.get_Fields() in File32:line 66
   at Invantive.Sql.QueryPlan.get_FieldCount() in File32:line 77
   at Invantive.Data.QueryPlanCacheEntry.<>c.<.ctor>b__12_0(IQueryPlan qp) in File242:line 68
   at System.Linq.Enumerable.WhereSelectArrayIterator`2.MoveNext()
   at System.Linq.Buffer`1..ctor(IEnumerable`1 source)
   at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
   at Invantive.Data.QueryPlanCacheEntry..ctor(String sqlStatement, IQueryPlan[] queryPlans, ParameterList parameters) in File242:line 68
   at Invantive.Sql.SqlEngine.ExecuteAndFetch(IProviderManager manager, String sqlStatement, ParameterList parameters, Boolean allowSelect) in File37:line 538
   at Invantive.Data.ConnectionManager.ExecuteProviderPassthroughSqlActionTable(String actionSql, ParameterList parameters, String& handlingPath) in File74:line 4546
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at Invantive.Data.ConnectionManager.ExecuteProviderPassthroughSqlActionTable(String actionSql, ParameterList parameters, String& handlingPath) in File74:line 4669
   at Invantive.Data.ConnectionManager.PassthroughSqlActionTable(String actionSql, ParameterList parameters) in File74:line 2487
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at Invantive.Data.ConnectionManager.PassthroughSqlActionTable(String actionSql, ParameterList parameters) in File74:line 2501
   at Invantive.Data.ActionProceduresBase.PassthroughSqlActionTable(String actionSql, ParameterList parameters) in File64:line 134
   at Invantive.Producer.QueryEngine.QueryEngine.ProcessStatement(String sqlStatement, ResultSet& resultSet, Action`1 fetchMoreRows, Action`7 executeStatement, Boolean isInInteractiveMode, ScriptingLanguageNextStepSpecification& nextStepSpecification)

据称这是当前 Exact 用户通过 Exact XML API 的权限问题。以前这可以正常工作，但在禁用/启用用户后，StockPositions API 不再可用。

我应该如何配置用户以确保 StockPositions API 可用？

答案1

Exact Online 的授权模型并不真正适合复杂的安全设置；它没有基于角色的访问控制或从安全设置到表单和 API 的简单映射。

在这种情况下，您很幸运。由于是StockPositions基于 XML API 的表，您可以在 Exact Online 的 Web 用户界面中查看用户是否具有任何权限。

要检查这一点：

  • 以运行查询的用户身份登录，使用与 Exact Online 相同的用户名和密码。
  • 点击左上角的公司名称。
  • 单击导入/导出选项。
  • 单击 XML 方法。
  • 所有可用的 API 如下图所示： 授权 XML API
  • 对于下载，您应该检查是否显示“导出”选项。对于上传，请检查是否存在“导入”。

当缺少 API 的权限时，可以设置用户权限的高级用户应该打开 Exact Online。

然后：

  • 点击公司
  • 点击主数据
  • 点击“用户”->“概览”
  • 选择用户。
  • 在监视组中点击权限。
  • 您将看到包含数百个复选框的详尽列表： 用户权限
  • 每个复选框代表用户现有的一些权限。
  • 一般情况下，查看用户需要访问的主要主题的选项卡，并尝试用户的效果。或者联系 Exact 支持人员获取建议。

相关内容