oracledb_session resource
Use the oracledb_session Chef InSpec audit resource to test SQL commands run against a Oracle database.
Availability
Install
This resource is distributed with Chef InSpec and is automatically available for use.Version
This resource first became available in v1.0.0 of InSpec.
Syntax
A oracledb_session resource block declares the username and PASSWORD to use for the session with an optional service to connect to, and then the command to be run:
describe oracledb_session(user: 'username', PASSWORD: 'PASSWORD', service: 'ORCL.localdomain').query('QUERY').row(0).column('result') do
its('value') { should eq('') }
end
where
oracledb_sessiondeclares a username and PASSWORD with permission to run the query (required), and an optional parameters for host (default:localhost), system identifier (SID) (default:nil), which uses the default SID, and path to the sqlplus binary (default:sqlplus).- it is possible to run queries as sysdba/sysoper by using
as_db_role option, see examples - SQLcl can be used in place of sqlplus. Use the
sqlcl_binoption to set the sqlcl binary path instead ofsqlplus_bin. query('QUERY')contains the query to be runits('value') { should eq('') }compares the results of the query against the expected result in the test
oracledb_session(…).query method Properties
- rows the query result as array of hashes
- row(number) selected row from query result, where number is just a row number in the query result
- column(name) array with values from selected column
Examples
The following examples show how to use this Chef InSpec audit resource.
Test for matching databases
sql = oracledb_session(user: 'USERNAME', pass: 'PASSWORD')
describe sql.query('SELECT NAME AS VALUE FROM v$database;').row(0).column('value') do
its('value') { should cmp 'ORCL' }
end
Test for matching databases with custom host, SID and sqlplus binary location
sql = oracledb_session(user: 'USERNAME', pass: 'PASSWORD', host: 'ORACLE_HOST', sid: 'ORACLE_SID', sqlplus_bin: '/u01/app/oracle/product/12.1.0/dbhome_1/bin/sqlplus')
describe sql.query('SELECT NAME FROM v$database;').row(0).column('name') do
its('value') { should cmp 'ORCL' }
end
Test for table contains a specified value in any row for the given column name
sql = oracledb_session(user: 'USERNAME', pass: 'PASSWORD', service: 'ORACLE_SID')
describe sql.query('SELECT * FROM my_table;').column('COLUMN') do
it { should include 'my_value' }
end
Test tablespace exists as sysdba
The check will change user (with su) to specified user and run 'sqlplus / as sysdba' (sysoper, sysasm)
sql = oracledb_session(as_os_user: 'oracle', as_db_role: 'sysdba', service: 'ORACLE_SID')
describe sql.query('SELECT tablespace_name AS name FROM dba_tablespaces;').column('name') do
it { should include 'TABLE_SPACE' }
end
NOTE: option `as_os_user` available only on unix-like systems and not supported on Windows. Also this option requires that you are running inspec as `root` or with `--sudo`
Test number of rows in the query result
sql = oracledb_session(user: 'USERNAME', pass: 'PASSWORD')
describe sql.query('SELECT * FROM my_table;').rows do
its('count') { should eq 20 }
end
Use data out of (remote) DB query to build other tests
sql = oracledb_session(user: 'USERNAME', pass: 'PASSWORD', host: 'my.remote.db', service: 'ORACLE_SID')
sql.query('SELECT * FROM files;').rows.each do |file_row|
describe file(file_row['path']) do
its('owner') { should eq file_row['owner']}
end
end