7 Replies Latest reply on Apr 14, 2006 12:09 PM by Brad Jacobs

    Auditing Database Info

      Does anyone use Config Manager for auditing database compliance? As part of a compliance initiative I'd like to create a script or extended object that uses sqlplus to query some data and include it as part of a regular audit.


      What are some examples of database-related compliance you've used? Have you used it for database updates also?

        • 1. Re: Auditing Database Info

          You can create an extended object that runs sqlplus either locally on the app server or remotely on the DB server to query data.


          For example, the following will work with the sample "pubs" database with SQL Server


          Run on the DB server:

          nexec win03-db isql -U sa -P {db-password} -d pubs -Q "select * from sales"


          Run locally:

          isql -U sa -P {db-password} -d pubs -S win03-db -Q "select * from sales"

          • 2. Re: Auditing Database Info

            Correct, but I suppose you would then have to handle environment variables somehow. Yes?


            In addition, I'm not sure you would want to have an Extended Object sitting out there with database connection information inside it. Unless of course you enabled Extended Objects to a role, created the EOs, and then took away the ability to create them.


            Perhaps another way to handle this would be to create a script and store it in the /share/sensors directory, and use scriptutil to copy and execute the script remotely. Generally speaking if you lock down this directory you could have the passwords stored in the script.

            • 3. Re: Auditing Database Info

              FYI, I was just able to get this working. To do so, I did the following:


              - Store the SQL Script on the target server (with an exit at the end of the script)

              - Create a parameterized extended object to su to the appropriate user and execute the SQL*Plus command


              In regards to security, obviously you have to enter a username & password when executing the command. I have handled this using an encrypted server-associated property which is called by the extended object at execution time. A user will not be able to determine what this password is whether they look at the extended object or the server property itself.


              The command I ended up using is this:


              "su - ??POINT_USER?? 'sqlplus point/??POINT_USER_PASSWORD?? @/pt01/app/test.sql"


              I may want to add other parameters such as the path to the sql script or another for my sqlplus user login.


              I can now incorporate it into a component and snaphshot / audit the data all I want.

              • 4. Re: Auditing Database Info

                Can't they just create a configuration file object that uses the PASSWORD property as a value and deploy it somewhere? They would then have the value in cleartext as it is decrypted at runtime. That is one of my issues with properties being global in scope. Even if they are local to a role, storing encrypted does not prevent anyone with access to the role from getting the value in cleartext by simply making use of the property. Encrypted properties are really only useful to prevent read-only type roles (CMGuests?) from seeing the values and that is only necessary because the properties are global.


                I have ideas for restricting access to encrypted property values that involve public key crypto schemes. It would also have a package/job signing benefit as well which fits into the user/architect model that was discussed at the BL user conference as a concept for a future release. But, that will have to wait for another day.

                • 5. Re: Auditing Database Info

                  Do you think it would be possible to audit changes made to a particular tablespace each day and who made those changes?


                  I am thinking of a rolling audit where we take a snapshot of a tablespace on day 1, then audit same tablespace against the snapshot at the end of day 1. Repeat for day 2, 3, 4, etc.


                  I am assuming I would need some BLCLI magic to automate the rolling piece and find out how the DB keeps track of changes internally (system tables).

                  • 6. Re: Auditing Database Info

                    I suppose if you had an extended object which did a query, you could snapshot that and then audit against it to see what's changed. As far as telling who made that change I don't know enough about Oracle to get that sort of info, but I suppose if you could query it from the DB, we could script something out to use in an audit.


                    The rolling would be another issue that you could probably handle with the CLI. I'm not sure if anyone's built any rolling snap / audit stuff so far, but it would be interesting to see.

                    • 7. Re: Auditing Database Info

                      we've done it at A-B as well as etrade. rolling snap/audit, that is.


                      the cli executes the snapshot job, gets the result dbkey, clears the audit job masters, and adds the snapshot result as a master. can do it with multiple masters... it auto-fills in the appropriate template based on the snapresult that you've added. so, no need to worry about that via the cli.