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