How to implement a low resource privileged user on Oracle 10g using Resource Manager?

Question

In my database I have users and their respective grants using the least amount of privileges principle.

How do I start taking advantage of resource manager to prevent users from hogging all the resources? Can the process of implementing the resource manager be summed up in some short precise steps? I am not looking for a detailed process but instead like a bullet list of steps you have to complete to have successfully implemented this feature.

Answer 1

This is fairly easy.

1. create resource consumer groups
2. create a resource manager plan using the created groups
3. somehow arrange that sessions are mapped to a resource consumer group
4. in the resource directives specify if a request should be estimated first or not
5. make sure users have switch privileges to the required resource consumer groups

Point three is a bit vague, there are many ways to have a session map itself to a resource consumer group. One is by user definition, an other is by client machine name, yet an other is by module ....

If estimates are in place and the estimates guesses that more resources could be needed than allowed, the action is greeted with an error message explaining so.

It does need some careful design and mutual agreements between the admins and the users. Oracle Resource Management is very powerful.

Check the docs. Managing Resources with Oracle Database Resource Manager