Showing results for 
Search instead for 
Did you mean: 

Oracle user permissions when consolidating databases

Go to solution

Oracle user permissions when consolidating databases


We're currently consolidating a number of Oracle databases onto a DB cluster and I wanted to get some feedback on "best practices" for user setup.

Ideally, we'd like to do the following:

-The main "oracle" user would be able to access/modify/stop/start all databases.
-Each database would be owned by an application-specific user who would be able to only access/modify/stop/start their DB.
-The Oracle binaries may be shared between a number of databases (i.e. not one set per database) and should only be able to be changed by oracle:dba.

The use of DB-specific users is important to us to isolate the damage an application team user can do to other DBs they don't own. It also fits in better with our use of PRM (and SRM on Solaris).

I'm about to start doing some testing of various configurations and would be very interested in other people's experience in this area.

Indira Aramandla
Honored Contributor

Re: Oracle user permissions when consolidating databases

Hi Andrew,

The user "oracle" which belongs to dba group and owns the oracle software, will have th eprivillege to start / stop all the databases that exist in the oratab file flag="Y". The dbstart and dbshut scripts can be used for this purpose and for all the ORACLE_SID's defined in oratab, this user can start and stop the databases.

For individual databses, define an application users at the OS assign the group dba, and set the ORACLE_SID to this particular database in this users profile. Have a seperate database start / stop script and have execute privileges to this user only.

Likewise for the other databases set the ORACLE_SID to the respective databases in the users profile.

Never give up, Keep Trying
Wim Rombauts
Honored Contributor

Re: Oracle user permissions when consolidating databases

I am not sure if this is possible with one ORACLE_HOME. Every user that belongs to the sysdba group, has the power to connect to any database and shut it down or start it up.
They just have to set their ORACLE_SID to the correct database instance. As far as I know, there is no way to give every database it's own system sysdba group.

What you can do is make a separate ORACLE RDBMS installation per database that needs a different group of administartors. Install every ORACLE_HOME with user oracle, but assign different system groups for sysdba and sysoper. Assign the users with administrative privileges to their database to the respective sysdba group of the database.
For PRM, it is very well possible to create a separate application group per database and thus monitor every database separately.

Re: Oracle user permissions when consolidating databases

Thanks for your responses - they seem to confirm what I was thinking...

Does anyone else have any other ideas?

Occasional Advisor

Re: Oracle user permissions when consolidating databases

First of all I would like to ask you why application users want to be able to start/stop the production database? In an acceptance environment this option might be considered (use the script-grant-solution provided before). In a production environment it is highly uncommon to shutdown databases, ever (ok maintenance excluded).
Secondly, I would be very reluctant in providing access to the database from the command line for any user. This should only be possible for the dba/admin guys. Any access to the server should be from a seperate box giving access through a firewall (probably only port 1521). On this box you may identify different users.
A os-user on the database server may easily stop the database from functioning. Either by directly making "mistakes" to the database and/or software or indirectly by causing problems on the os (like filling up /var or /tmp).
So, database consolidation, ok. No different apps in one database, but remain "agile" (this appears to be ok from your discription). Standardize your databases and provide access though different apps-servers.
Brian Crabtree
Honored Contributor

Re: Oracle user permissions when consolidating databases


One option that would remove the need for putting the application dba accounts into the 'dba' group would be to write a wrapper script around dbshut/dbstart, and make it suid. You could list out which ux ids have access to which database, so they don't have the ability to shutdown databases not belonging to them.

Honored Contributor

Re: Oracle user permissions when consolidating databases


I think your plan is fine, except that you should be using a different ORACLE_HOME (set of binaries) for each group.

The reason becuase of the following: go look in $ORACLE_HOME/rdbms/lib/config.c - you'll notice that there is a #define macro for the SS_DBA_GRP that is being set.

You get ONE of these groups you can have a person set to - and only one. And, even worse, the user that is starting and stopping databases MUST belong to this group. This means that for THIS example ORACLE_HOME you have exactly ONE group that the user MUST belong to stop and start databases. This means that if you have one ORACLE_HOME - everyone that is in this group can start and stop other people's ORACLE databases. All they'd have to do is change the ORACLE_SID to be the other team's value and they can do what they want to. This would naturally include full system privelges!!!

So, I think you need different ORACLE_HOMEs per group, and just make the main "oracle" user a member of every dba group. Then, in each ORACLE_HOME you should change the aforementioned config.c file to reflect the proper group name, and relink the executables in the ORACLE_HOME/bin directory.

Good luck
We are the people our parents warned us about --Jimmy Buffett

Re: Oracle user permissions when consolidating databases

Thanks everyone for your feedback - it was all useful.

I know what I outlined sounds a bit strange - it was largely to sound out options of fitting this into PRM/SRM (particularly SRM which is driven by user ids more than PRM).

I think that the route we will go down though is to maintain one Oracle user and sort out the resource management at a clustering level (we were a bit reluctant to change standard Veritas Cluster Server scripts, hence me floating this idea).

Thanks again,