Skip to Main Content
  • Questions
  • Comparison of schema in controlled environment

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, avnish.

Asked: September 19, 2016 - 8:49 pm UTC

Last updated: September 21, 2016 - 10:11 pm UTC

Version: 10.2.0.4

Viewed 1000+ times

You Asked

I learnt that some of the production objects were missing. At that point I decided to change the password of the schema in production so that the comparison can be done with the Stage environment and the extent of damage to the production system can be done without anybody creating objects in production during the time of comparison. I also disconnected the same schema account which was connected and inactive. Is this approach OK? Is there any other alternate viable approach? The password change was temporary and after comparison this was restored to the original password.

While comparing schema between the 2 environments, I changed the password of the schema in order to have controlled environment for comparison. Is there any other way the controlled environment can be established?

and Chris said...

You just changed the production password to do some comparisons?! Eek!

This is a terrible idea. Any applications trying to connect would fail!

It sounds like multiple people have the password. You need to stop this. So you will need to change the password.

But do so in a controlled manner in co-ordination with changing the corresponding application credentials. After you've done this, strictly control access to the new password. Only allow people who really need the password access.

You may want to look into using the Oracle Wallet to manage your passwords:

https://docs.oracle.com/cd/B28359_01/network.111/b28530/asowalet.htm
https://oracle-base.com/articles/10g/secure-external-password-store-10gr2

Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Double eek!

Stew Ashton, September 20, 2016 - 8:00 am UTC

Who are these people who can drop or create objects in production schemas?

What applications are expected to connect to a schema with objects in it?

No application should ever connect directly to a schema with objects in it!

If I connect to a schema directly, I have the privilege to drop objects in that schema. No one can take that privilege away from me.

The only way to enforce the security principle of "least privilege" is to create different users for the applications. These users can be given the privileges you want, but should not be able to create or drop objects.

In the short term, I strongly suggest using AUDIT to have a trace of CREATE, ALTER and DROP statements.

On Security

Rajeshwaran, Jeyabal, September 20, 2016 - 8:46 am UTC

No application should ever connect directly to a schema with objects in it!

Yes, Well said Stew, Tom talks about these in detail here
http://www.oracle.com/technetwork/issue-archive/2015/15-may/o35asktom-2541607.html

schema comparison in controlled environment

avnish sharma, September 20, 2016 - 1:35 pm UTC

The application does not connection via schema. It does only through the account which has only DML rights. The tables cannot get dropped via any application. we had a contractor DBA recently joined who was doing the production release. Tables or other things got dropped after the release- something happened. Application also went down because the tables were missing. It went unnoticed for almost 16 hrs until next morning when people arrived for work.

There was definitely breakdown of communication. there is a practice in the department to provide implementation plan (change plan!) to keep track what is going in production. That process was also broken.
I manage the production system and when I learnt the situation next day morning, I decided to i) dbverify ii) get the extent of damage in production by comparing stage (pre production environment) and production environment. Change in password of schema and system account was done temporarily to avoid any attempt to create objects during that time.
My question is - could I have done it a better way of system verification. By controlled environment I mean - uninterrupted verification of the system?

avnish sharma, September 21, 2016 - 8:34 pm UTC

I have updated the background. Unfortunately, the communication went in the unintended direction. I would appreciate the response against the updated background.
Chris Saxon
September 21, 2016 - 10:11 pm UTC

I would consider an alternative option - much like what we do when we install database patches etc.

Maintain a registry of what you *expect* to install during an application installation/upgrade. eg 'n' new objects, 'p' changed objects etc.

At the end of an installation, run a verifcation query to see if you match those predicted numbers. If not, then do not open the system for users and troubleshoot.

Catch things early.

avnish sharma, September 22, 2016 - 8:32 pm UTC

I do maintain the registry kind of thing - implementation plan where the developers are required to provide the information what changes including new are scheduled to go to production. It lists the name of the packages, procedures and tables, scripts and any other instruction like data load.

This being a contractor DBA, that process also broke down.

As schema and system is accessed by DBAs, it did not impact any application. DBAs, if wanted to connect has an option of using SYS account.

It is still somewhat nuisance if someone is trying to connect uaing the same schema or system account.

The question is: Can we even do it in even other elegant way where the temporary nuisance can be avoided? Or it just leaves us to the option that I preferred to use at that point.

More to Explore

Backup/Recovery

Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.