问题描述

以下是我希望阻止在我的服务器上运行的查询类型的示例:

Here's an example of the sort of query I would like to prevent from running on my server:

begin
  While True
  LOOP
     dbms_output.put_line('tst');
  END LOOP;
end

此查询(或其他类似查询)可能通过 Oracle JDBC 瘦驱动程序访问我的 Oracle 服务器.我愿意阻止此查询在 JDBC 配置级别、数据库服务器配置级别或通过模式内的用户权限运行.我希望用户能够继续运行正常的选择/插入/更新/删除查询.老实说，如果没有任何 PL/SQL 类型的命令可用，而只有标准 SQL，我会很高兴.

This query (or others like it) may be hitting my Oracle server via the Oracle JDBC thin driver. I would be open to preventing this query from running either at the JDBC configuration level, the database server configuration level, or via user permissions within the schema. I would like the user to continue to be able to run normal select/insert/update/delete queries. Honestly I'd be quite happy if none of the PL/SQL type commands were available, and instead only standard SQL.

更新

我还应该提到，我希望用户能够继续在他们的 SQL 查询中使用标准函数.我真的不希望他们做任何看起来像过程式编程的事情(并且不得不担心这些事情的陷阱，如上所示).

I should also mention that I want users to continue to be able to use standard functions in their SQL queries. I just really don't want them doing anything that looks like procedural programming (and having to worry about the pitfalls of such things, as seen above).

推荐答案

您无法阻止人们针对您的服务器编写过程 PL/SQL 代码.但是，根据您要解决的问题的确切性质，您可能还有其他选择.想到的两个选项...

You can't prevent people from writing procedural PL/SQL code against your server. Depending on the exact nature of the problem you're trying to solve, however, you may have other options. Two options that spring to mind...

您可以创建个人资料强制执行各种资源限制的数据库用户.因此，您可以限制单个调用可以消耗的 CPU 数量或它可以执行的读取次数.这使您可以自动终止执行诸如编写无限循环之类的操作的会话.请注意，RESOURCE_LIMIT 初始化参数需要设置为 TRUE，以便 Oracle 在配置文件中强制执行资源限制.

You can create a profile associated with the database user that enforces various resource limits. So you can limit the amount of CPU a single call can consume or the number of reads it can do. That lets you automatically kill sessions that do something like coding an infinite loop. Note that the RESOURCE_LIMIT initialization parameter needs to be set to TRUE in order for Oracle to enforce resource limits in profiles.

您可以使用 Oracle 资源经理优先访问资源，以降低开发人员的错误将占用服务器上的所有可用资源并使重要的生产流程匮乏的风险.

You can use Oracle Resource Manager to prioritize access to resources to reduce the risk that a developer's mistake will take all the resources available on the server and starve the important production processes.

这篇关于如何在 Oracle 查询中禁用 PL/SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！