App schema with SYS privileges?

I am afraid I have a question that most of you must have heard a million times. I should have found the answer on some of the existing posts, but I am overwhelmed by the information. I a DB newbie tasked by management to figure out how the Oracle database ought to be setup!

A 3rd-party app we use recommends using a separate instance. But due to resource constraints we cannot do that (so I've been told). We have a single instance that is shared by several applications, including HR tools that hold salary information

The said app requires system privileges because, for each new project, it creates 6 or more different schemas for the hierarchical elements contained within. It also requests for the Oracle username and password for certain tasks that modify large amounts of data, for instance backup. The current solution is that each time a user creates a new project or requires the password for other tasks, the Oracle DBA comes running down to enter the username SYSTEM and the password, a scenario that neither he nor the users like. Isn't the SYSTEM schema all-powerful?

Is there a way of creating a schema for the application with privileges to create users/schemas, create tablespaces, add tables etc. WITHOUT being able to access sensitive information in the database?

I hope I have explained myself well. Thanks very much! Sang Received on Fri Sep 23 2005 - 05:34:37 CDT