3

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

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.

Update

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).

Improve this question
4
  • I'm assuming this is a prod server then as that's fairly limiting? Commented Feb 7, 2012 at 19:22
  • @Ben limitations are fine with me, but yes this is a prod server "of sorts". It is a server I want to keep up and running as if it were production, but it is a server that you could say has active "development" against at all times. It's almost a "production" development server, if you will. Commented Feb 7, 2012 at 19:25
  • 1
    and what about evil cartesian joins, you want to disable all joins next? I think separating prod and dev instances, and doing code reviews might be a better start. Commented Feb 7, 2012 at 19:34
  • @tbone - I'm aware of that risk as well. I don't want to disable all joins, but I do plan on taking steps to mitigate that with caps on resource utilization. Unfortunately, your suggestion to separate prod from dev (while normally a no-brainer) does not apply in my case. Commented Feb 7, 2012 at 19:38

1 Answer 1

Reset to default
7

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...

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.

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

I feared as much. I was actually already using the CPU_PER_CALL resource limit for the profile (set to 3), but that does not appear to stop the above the above sample query from pegging the process and running indefinitely. So, this appears to be a real problem.
I guess I should mention that this is Oracle 11g XE - perhaps the express edition does not enforce this profile limitation? I have also tried running some huge cross joins while under this profile, and it does not stop running after three seconds.
It appears that the Resource Manager is only available for the Enterprise Edition of Oracle.
@JakeFeasel - What is the RESOURCE_LIMIT initialization parameter set to? It needs to be set to TRUE in order for resource limits in profiles to be enforced.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.