Monday 15 July 2013

Problems found with DBSNMP user account and how to fix them ..

Well there have been lot of issues related to some versions of oracle database till 11.2.0.3 like Grid Control Agent Crashes with Core Dump if the Password of the Monitoring Configuration (DBSNMP) user in any Monitored Database is in Grace Period or may be a nightmare with Grid Control Shows DBSNMP Account Locked ORA-28000 Or Agent Connection to Instance Failed ORA-28001... So there have been some workarounds but before that let us take a look on some things..

There have been lot myths of what is a DBSNMP user and why it is needed and what are the bugs and corruptions caused by some of the changes in the password of this user account. Well to be simple folks DBSNMP is a user account which is actually used by the Management Agent component of Oracle Enterprise Manager to monitor and manage the database. Sometimes there is a bit confusion among the DBA's about SNMP process and DBSNMP user, SNMP is a process which will make sure metrics of the database will be forwarded to other SNMP monitoring tools because the administrators can store and analyze historical data that has been obtained through SNMP. Its still has to be searched in some of the docs of EM and metalink notes whether SNMP and DBSNMP have any internal relation between them...

Having said some lines about DBSNMP, this user has SELECT ANY DICTIONARY system privilege which can read the passwords from SYS.USER$ and would therefore will be handling the intelligent agent stuff mentioned above. The default passwords are very simple and has to be changed by the security administrators however changing this password itself is not a single step procedure. We need to perform several steps to make sure the password is changed successfuly. It would have been good if it was done at DB creation not later.

The DBCA and Oracle universal installer will ask to you for the passwords for SYS, SYSTEM, SYSMAN and DBSNMP anyways, so its good to provide a strong password in that case and moreover there is also a myth that Enterprise pages are found corrupted when we change DBNSMP password in not a proper manner, that is not the case, Actually a user named as MGMT_VIEW which is offcourse having a password definately, corruptions happen when password of this user is changed so it is advisable not to change the password of MGMT_VIEW user at all. So corrupt pages of EM will actually be found when the password of MGMT_VIEW is changed, so best is to have a special profile for this account in which password never expires because it gets MONITORING_PROFILE profile by default which specifies PASSWORD_LIFE_TIME is for 180 days, however FAILED_LOGIN_ATTEMPTS are set to unlimited in that case. This user account has read only access to tables which contain management data like percent of CPU used over time for the database server.

For operating systems like Linux the problems mentioned above have been solved with one-off Patch 8300283 on top of the 10.2.0.5 Grid Control Agent Home. This patch is available for the 10.2.0.4 and 10.2.0.5 Grid Control Agent on major Unix / Linux platforms. Its best to see instructions for the respective patches.

So let us take a look at how to change a password of DBSNMP user, the following are the steps :=

    Stop the EM dbconsole
    On Linux - DB Control Release up to 11.1.x

    [oracle@dodwal ~]$ export ORACLE_SID=prod
    [oracle@dodwal ~]$ emctl stop dbconsole

    On Linux - DB Control Release 11.2.x and higher

    [oracle@dodwal ~]$ export ORACLE_SID=prod
    [oracle@dodwal ~]$ export ORACLE_UNQNAME=prod
    [oracle@dodwal ~]$ emctl stop dbconsole


    SQL> alter user dbsnmp identified by new_password ;


    SQL> connect dbsnmp/@prod

    Go to $ORACLE_HOME/host_sid/sysman/emd
        Save the file targets.xml to targets.xml.orig
        Edit the file targets.xml with your favorite text editor
            Search for the line:
            <property name="password" value="<encrypted_string>" encrypted="TRUE">
            Replace the encrypted value by the new password value
            Replace TRUE by FALSE
        Restart the standalone dbconsole
        On Linux - DB Control Release up to 11.1.x

        [oracle@dodwal ~]$ export ORACLE_SID=prod
        [oracle@dodwal ~]$ emctl start dbconsole

        On Linux - DB Control Release 11.2.x and higher

        [oracle@dodwal ~]$ export ORACLE_SID=prod
        [oracle@dodwal ~]$ export ORACLE_UNQNAME=prod
        [oracle@dodwal ~]$ emctl start dbconsole
    
        Check that the password has been encrypted
        Edit the file targets.xml
        Search for the line: <property name="password" value="<encrypted_string>" encrypted="TRUE">
        Check that the password VALUE is encrypted
        Check that the value of ENCRYPTED is TRUE


Note: In case of RAC DB, the dbsnmp password should be changed in targets.xml not only for instances(oracle_database), but also for rac_database target. The file targets.xml needs to be updated on all the RAC Cluster nodes.
________________________________________________________________________________________________

Steps for Changing the DBSNMP Account Password in an Oracle Database Vault Environment:

Before you can change the password for the DBSNMP user account, you must revoke the DV_MONITOR role from this account. In an Oracle Database Vault environment, the DBSNMP user account is granted the DV_MONITOR role. (The DBSNMP user can change it's own password directly in sqlplus, without having the DV_MONITOR role revoked first.)

To change the DBSNMP user password:

1)  Log in to SQL*Plus using an account that has been granted the DV_OWNER role.
2)  Revoke the DV_MONITOR role from the DBSNMP user account.
3)  Connect as a user who has been granted the DV_ACCTMGR role and then change the DBSNMP user account password.
4)  Connect as the DV_OWNER user and then grant the DV_MONITOR role back to the DBSNMP user account.

Alternatively, you can temporarily disable Oracle Database Vault, log on as a user who has been granted the ALTER USER privilege, and then modify the DBSNMP password. Afterwards, re-enable Database Vault.
________________________________________________________________________________________________

Thursday 4 July 2013

12C Auditing Goodies


As we all know that in most cases auditing is required for reporting because of security and compliance regulations and talking about  auditing here once again relative to 12C is not something new, so here comes a new 12C Database goodie known as unified auditing. Good thing is it comes up with no changes required to the database initialization parameters since previous versions. Not only this it enables the security administrators to configure audit policies and enable them in the database with no production database downtime, providing so much ease of administration and flexibility for the database. Thats not the end, as we know that security and compliance regulations require accurate monitoring and reporting of Oracle database activity so in this case we have a new insert-only tablespace which provides guarantee that audit records are not modified or deleted after they have been written to the audit trail. Remember the maintenance of the audit trail is only limited to the database users who have been given this AUDIT_ADMIN role. So the users with the AUDIT_ADMIN role can manage the retention policy of the audit data.

As auditing is suppose to be done because regulations such as the Sarbanes-Oxley Act have common auditing-related requirements like monitoring a suspicious user or notify the auditor the actions of an unauthorised user and other requirements too, so it became more important for oracle to provide more features and options in security as always with a new version . Now in relation to the auditing database we have a new initialization parameter UNIFIED_AUDIT_SGA_QUEUE_SIZE introduced which configures the size of SGA queue for unified auditing. Actually this parameter is very useful for the systems where there is lot of audit records are generated very frequently depending on type of auditing enabled and the amount of auditing options enabled like for standard auditing options for DB there would be huge flushing of audit records to system tablespace in table aud$ in 11G version or when using VAULT the table aud$ would be in system schema with the tablespace being the same. Interestingly in 12C there are 2 modes for this one, the important one is queued-write mode, in this case audit records are first written to the SGA queue and when the SGA queue reaches the threshold, the audit records will be flushed to the disk inside AUDSYS schema table. This queue size can definately be setup and tuned accordingly depending on the amount audit records getting generated. So if there is frequent flushing it makes sense to increase queue size.

In 12C database there are 2 roles which have been introduced which are AUDIT_ADMIN and AUDIT_VIEWER, so they are required to administer unified auditing. So make sure the user who is doing these tasks has these roles.

So now let us take a look at how unified auditing policy is created and how we can manually flush the changes to disk. Dont forget this data is stored in the AUDSYS schema/SYSAUX tablespace.

SQL>    CREATE AUDIT POLICY ORA_KRISHNA
     PRIVILEGES
                ALTER DATABASE, ALTER SYSTEM,
                CREATE PUBLIC SYNONYM, DROP PUBLIC SYNONYM
    ACTIONS     CREATE DATABASE LINK, ALTER DATABASE LINK, DROP DATABASE LINK,
               LOGON, LOGOFF, CREATE DIRECTORY, DROP DIRECTORY
   /

Audit policy created.

SQL>  audit policy  ORA_KRISHNA ;

Audit succeeded.
##### Remember if the audit trail mode is set to QUEUED, then audit records are not written to disk till the time memory queues become full. The following command explicitly flushes the queues to disk, and then you can see the audit trail records in UNIFIED_AUDIT_TRAIL view.

SQL>  EXEC DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL;

PL/SQL procedure successfully completed.

SQL> SELECT  SCN, SQL_TEXT FROM UNIFIED_AUDIT_TRAIL where DBUSERNAME='SYS' and sql_text like 'alter system%';

no rows selected

SQL>  alter system set audit_trail=XML scope=spfile;

System altered.

SQL>  EXEC DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL;

PL/SQL procedure successfully completed.


SQL> SELECT  SCN, SQL_TEXT FROM UNIFIED_AUDIT_TRAIL where DBUSERNAME='SYS' and sql_text like 'alter system%';

       SCN SQL_TEXT
---------- --------------------------------------------------------------------------------
   1732822 alter system set audit_trail=XML scope=spfile           
                                 
Disabling Unified Auditing


If you must disable unified auditing, then follow this procedure:

    Log into the database instance as user SYS with the SYSOPER privilege.

    sqlplus sys as sysoper
    Enter password: password

    In a multitenant environment, connect to the appropriate PDB.

    For example:

    CONNECT SYS@appdb AS SYSOPER
    Enter password: password

    To find the available PDBs, query the DBA_PDBS data dictionary view. To check the current PDB, run the show con_name command.

    Shut down the database.

    For example:

    SHUTDOWN IMMEDIATE

    From the command prompt, run the following commands:

    cd $ORACLE_HOME/rdbms/lib
    make -f ins_rdbms.mk uniaud_off ioracle

    In SQL*Plus, restart the database.

    STARTUP


Just to add up to the goodies of 12C security there is an increased security in 12C when using SELECT ANY DICTIONARY privilege so SELECT ANY DICTIONARY privilege no longer allows access to security sensitive data dictionary tables DEFAULT_PWD$, ENC$, LINK$, USER$, USER_HISTORY$, and XS$VERIFIERS.

With this security is increased of the database as subset of data dictionary tables is not allowed to be seen even though you have SELECT ANY DICTIONARY privilege.

From the Oracle Documentation please see how Mixed Mode Auditing is suppose to behave from the following link

http://docs.oracle.com/cd/E16655_01/network.121/e17607/auditing.htm#CHDBDCHI

Below is what have been mentioned in oracle doc as it is

About Mixed Mode Auditing

When you create a new database, the database uses mixed mode auditing, which enables both traditional (that is, from releases earlier than Release 12c) and new audit facilities (unified auditing).

As the term refers, mixed mode auditing enables both traditional and unified audit facilities together. Needless to say, the traditional audit facility is driven by the AUDIT_TRAIL initialization parameter. Only for mixed mode auditing, you should set this parameter to the appropriate traditional audit trail. This traditional audit trail will then be populated with audit records, along with the unified audit trail.

When you upgrade your database to the current release, you must manually migrate to unified auditing if you want to use unified auditing. Afterward, traditional auditing is disabled, and the new audit records are written to the unified audit trail. After you complete the migration, the audit records from the previous release are still available in those audit trails. You can then archive and purge these older audit trails by using the DBMS_AUDIT_MGMT PL/SQL procedures, based on your enterprise retention policies.

There is still a lot more in 12C relative to auditing but that shall be next time.