Need Perl file example to parse XML File

My plan is to automate our current Database STIG Audit process.  I want to write a short Perl script to:

   1)  Read XML STIG File (i.e.  U_Oracle_Database_12c_V1R10_Manual-xccdf.xml)
   2)  Parse out all the STIG #s, with the accompanying SQL Query Statements
   3)  Write new ascii text output to new file

Please help me with Perl file example to do this parsing?
U_Oracle_Database_12c_STIG_V1R10_Man.xml
LVL 1
sdrussAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
I'm sure there are TONS of Perl scripts to parse XML on the Web

I'm VERY familiar with the STIG but I'm not sure what you are looking to do.

It took me about a day to walk through it and come up with my own SQL script to look at everything I needed to look at.  Before you ask:  No, I can't give you mine.

OEM Grid Control is supposed to have a STIG checker built in:
https://docs.oracle.com/cd/E24628_01/doc.121/e36074/stig.htm

I've never tried it.

As for the question about automating:
Here is SQL from that file:  revoke <role name> from <grantee>;


Then there is:
Run the SQL statement:

  select grantee||': '||granted_role from dba_role_privs
  where grantee not in
  (&lt;list of non-applicable accounts&gt;)
  and admin_option = 'YES'
  and grantee not in
  (select distinct owner from dba_objects)
  and grantee not in
  (select grantee from dba_role_privs
   where granted_role = 'DBA')
  order by grantee;

What are you hoping to "automate"?
What nodes are you wanting to extract?

Just curious:  Why does it have to be Perl?
0
sdrussAuthor Commented:
Our shop's Unix/Solaris scripting language of choice is Perl.  We use some bash, but by far Perl is the most popular.  Believe most of Perl scripts to parse XML on the WEB, require XML modules (i.e. XML::Parser), seriously doubt we will be able to import any modules.  We will need to use pretty much basic Perl modules that are included from the base installation.

Why automate .... in the past I have sent a DBA to our customer location for 3-4 days to manually run each STIG query on multiple databases.  I am looking not to do this in the future, and instead supply customer with a script which generates results.
0
FishMongerCommented:
Your shop's language of choice is Perl but you can only use core modules?  That doesn't make much sense.  With that restriction, you're cutting out the main reason to use Perl.  You should consider rethinking that restriction or your language of choice.
0
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

sdrussAuthor Commented:
FishMonger:  trust me this is not my personal restriction being imposed.  We work in an extremely closed environment, without going into detail.  Given my personal choice we would import all the useful Perl modules available, but we severely restricted.  My DBAs are pro Perl; however, we have many J2EE software developers that are pro Python.  My staff doesn't know Python .... In your opinion should we get on the Python band-wagon now?  The only other choice is primarily the bash shell.
0
FishMongerCommented:
I personally don't like Python, so I can't recommend going that direction, but others have different opinions.

For the module access problem, I'd suggest creating you own local cpan mirror on a system that has Internet access and pull from that instead of directly from the Internet.  That way you can control the security via your internal firewalls

How to mirror CPAN
0
slightwv (䄆 Netminder) Commented:
>> I am looking not to do this in the future, and instead supply customer with a script which generates results.

I can understand that but I'm still not sure automation against the STIG XML file can generate the necessary SQL in scripted format.  I asked aove what SQL/nodes your are looking to extract that can be executed without a decent level of modification?

The STIG hasn't changed much so once a script is created locally, can it not be sent out to the clients?  Have the DBA generate the scripts against a local database where they can be tested.  Then send them out and wait for the results.
0
sdrussAuthor Commented:
Guess, I'm not really clearly what I'm looking for. ..  I want to parse the XML-STIG file.  Yes, this should be a one-time thing.  For example,

   1.  Pass argument to (script to be written) name of XML-file
   2.  Open file loop
   3.  Search each line looking for "select ", or "show parameter " ...... terminated by seim-colon
   4.  Write new file with matched from above
0
sdrussAuthor Commented:
<< I asked aove what SQL/nodes your are looking to extract... Don't understand??    I have both Oracle RAC and single-instance node databases.
0
slightwv (䄆 Netminder) Commented:
>>I want to parse the XML-STIG file.  Yes, this should be a one-time thing.

I understand what you are asking.  I'm questioning the actual result you think you might get versus the level of effort to get it.

When I use the word node talking about XML, I'm not talking a RAC node.

I mean XML node:
https://en.wikipedia.org/wiki/XML_tree
https://en.wikipedia.org/wiki/Document_Object_Model

>>"show parameter " ...... terminated by seim-colon

The show parameter doesn't need a ";" and the two in the current STIG isn't necessary since they also have the select that does the same thing.

>> I asked aove what SQL/nodes your are looking to extract... Don't understand??    I have both Oracle RAC and single-instance node databases.

Back up in #a42676094 I posted a couple examples of a SELECT I found in the STIG XML file.

What I was getting at is that a few of the selects aren't executable as they are.  They will need editing:
select grantee, privilege from dba_sys_privs
where grantee not in (<list of non-applicable accounts>)
and admin_option = 'YES'
and grantee not in
(select grantee from dba_role_privs where granted_role = 'DBA');

and
select grantee||': '||granted_role from dba_role_privs
 where grantee not in
 (<list of non-applicable accounts>)
 and admin_option = 'YES'
 and grantee not in
 (select distinct owner from dba_objects)
 and grantee not in
 (select grantee from dba_role_privs
 where granted_role = 'DBA')
 order by grantee;


Then you have issues with the word "select" that isn't part of any query.  For example:
Rule Title: The Oracle SQL92_SECURITY parameter must be set to TRUE.


Vulnerability Discussion:  The configuration option SQL92_SECURITY specifies whether table-level SELECT privileges are required to execute an update or delete that references table column values. If this option is disabled (set to FALSE), the UPDATE privilege can be used to determine values that should require SELECT privileges.

So you cannot just start with the word "select" and pull to the next ";".

Even if you can pull ALL that off, you will still need to manually edit the output to add PROMPT commands to the script so you know what each section/command is returning to you don't just get a bunch of "data".

>>Yes, this should be a one-time thing.

Exactly.

For a moment let's forget about the amount of time we have already spent in this question:
In the time it will take you to figure out how to parse the XML properly using the DOM or improperly as just a text file, use a regex to look in the text for that you want and edit the output to make the SQL actually executable, you could have manually gone through the HTML STIG viewer and created the script you need.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.