Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question.

Asked: July 23, 2016 - 12:11 am UTC

Last updated: July 27, 2016 - 1:33 pm UTC

Version: 10.2.0.4

Viewed 1000+ times

You Asked

I learnt that some of the production objects were missing under particular schema. 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 pf damage to the production system can be done without anybody creating objects in production during the time of comparison. The password change was temporary and restored after comparison was completed.

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

Q2. Sys account still remained available for any other DBA to perform system level tasks during the time of comparison. Is there any option available to control sys account?

Q3. can SYS account be used to create schema level objects

and Connor said...

q1) You could create a DDL trigger to block all ddl, Or revoke all 'create/alter' privilege for all users that have them. But both of these sound drastic to me - in that, it is masking the real problem, in that you have people making production changes "without your knowledge". That sounds like you have a process problem to correct.

q2) No, because its the SYS account. You could audit all activities, or consider something more significant like DataVault...but really, this is about controlling access *to* the SYS account, not controlling what SYS can do.

q) Yes, but generally I prefer to *only* use SYS when you absolutely need to. Use a standard DBA account instead.

Rating

  (2 ratings)

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

Comments

A reader, July 25, 2016 - 5:23 pm UTC

Response to Q1.

Thanks, It is for sure the process was broken. We do have process of sharing implementation plan (like change management plan!) but the contract DBA failed to follow that protocol.

setting up DDL trigger will help any accidental deletion of tables or packages. In future, we can plan to consider setting up DDL trigger.

However, my question is about the process adopted to create controlled environment for investigation. Could there be any better way to set up controlled environment especially when the process is broken?

Response to Q2.
My question is even if we change the password of SYS, one can still connected as sysdba on the server. Changing password could create complete blockage to access the system remotely. If the password is not changed, it still leaves the door open to connect to the database remotely via sqlplus or toad etc.
Chris Saxon
July 25, 2016 - 7:06 pm UTC

Depends on what extremes you want to go to. You could set the database to read only :-)

It is also possible to disable table locks, which means DML is allowed by DDL is not, but obviously if you are still allowing DBA access, they can simply re-enable it.


Another point

J. Laurindo Chiappa, July 25, 2016 - 7:27 pm UTC

Another line of thought, complementing the info already received : first thing if you really want/need a "controlled environment" is to protect the environment (in the possible ways) against non-conforming DBA activities - depending on how serious the need is, it can range from implementating the Database Vault (the most secure but the most complex and costly alternative) until simply separating the DBA and sysadmin roles, starting Auditing on DBA actions (on OS files, external to database) and putting a contractual rule (complete with fines and higher penalties) for the DBA againts non-conforming activities....
After that the DBA position is "secure", you will walk toward for end-users and application control : without all of this, you will Never have a "controlled" environment...

Regards,

J. Laurindo Chiappa
Chris Saxon
July 27, 2016 - 1:33 pm UTC

nice input
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Backup/Recovery

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