ODBC and EZCONNECT or my way out of active directory

The traditional way of connecting Excel (or Access) to Oracle (and other databases) is to use ODBC and TNSNAMES. You install an Oracle client, you create a connection and specify your tnsnames connection alias as server, here below DB01 tnsnames.ora DB01 = (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP)(host=srv01)(Port=1521) ) (CONNECT_DATA= (SERVICE_NAME=DB01) ) ) But the file is often […]

Connect to ActiveDirectory with ldapsearch on Unix

In ancient times, ldapsearch could query ActiveDirectory without issues. In this examples, I used openldap client 2.4. Other tools may have other parameters. $ ldapsearch -H ldap://example.com:389 -b dc=example,dc=com cn=”Laurent C. Schneider” mail mail: laurent.c.schneider@example.com In Active Directory (AD) it is no longer the default since Windows Server 2003, unless you change dSHeuristics to 0000002 […]

Oracle 18c/19c and ActiveDirectory

With Oracle 18c and even better in Oracle 19c, you can manage your Oracle database users in Active Directory. This was supposed to be a very nice new feature as many of us struggle with many thousand users spread over many versions, environments, platforms and even cloud or exadata. Is this going to help you? […]

Audit pluggable database

In the old now-deprecated maybe-soon-desupported non-cdb infrastructure, AUDIT’ing was done right after connect / as sysdba. In single-tenant (or multi-tenant), things get complicated. Once again, the doc must be read at least twice 😉 If you issue an audit statement in the root, then the database performs auditing across the entire CDB, that is, in […]

disallow pseudo terminal in ssh

Some Oracle documentation wants you to setup ssh with no password and no passphrase. Configuring ssh This is not really something your security admin will like. First, using DSA, which is deprecated and disabled by default in OpenSSH 7.0, is a pretty dump instruction OpenSSH 7.0 and greater similarly disable the ssh-dss (DSA) public key […]

Generate 11g password hash

An easy way to generate a value string from the ssl is to use openssl Let’s take a random salt of ABCDEFGHIJ. The length of 10 is important. The hexadecimal representation is -41-42-43-44-45-46-47-48-49-4A- $ echo “SafePassw0rDABCDEFGHIJ\c” | openssl dgst -sha1 (stdin)= 47cc4102144d6e479ef3d776ccd9e0d0158842bb With this hash, I can construct my value SQL> create user testuser identified […]

list database monitoring users

I am quite familiar with the SYSMAN tables but this one required me some googling beyond the Oracle documentation. The list of targets in your Oracle Enterprise Manager is in SYSMAN.MGMT_TARGETS. Each database target is monitored by a database user, typically DBSNMP. To retrieve this information, you need some to hijack your database, read this […]

run sudo, ssh, password, su in simulated interactive mode

Some commands do not like non-interactive mode $ passwd newpassword > newpassword > EOF Changing password for user lsc. Current password for lsc@example.com: passwd: Authentication token manipulation error $ echo oraclepassword | su – oracle standard in must be a tty $ echo sudopassword | sudo su – oracle [sudo] password for lsc: sudo: sorry, […]

anonymous cypher suites for SSL (and a 12c pitfall)

If you configure your listener for encryption only, you do not really need authentication. It works pretty fine until 11.2.0.2, I wrote multiple posts on ssl. You add SSL_CLIENT_AUTHENTICATION=FALSE to your server sqlnet.ora and listener.ora and specify an “anon” cipher suite in your client. You do not need to validate the certificate, so a default […]

poor man ActiveDirectory password checker

To have the same users in multiple databases and no single sign on is quite a nightmare for password expiration, synchronisation and validation. You probably were discouraged by the long long route to kerberos, where the 11.2.0.2 bugs are fixed in 11.2.0.4, the 12.1 bugs are fixed in 12.2. And lot’s of system changes that […]

ssl version

I wrote about ssl version in jdbc thin yesterday The default version also no longer works for the thick client with 12c client and 11g Server. With 11gR2 : C:> tnsping (DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=SRV01)(PORT=1521))) TNS Ping Utility for 64-bit Windows: Version 11.2.0.4.0 OK (100 msec) with 12cR1 : C:> tnsping (DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=SRV01)(PORT=1521))) TNS Ping Utility for 64-bit Windows: […]

TCPS and SSLv2Hello

Thanks to platform independence, the same java code work on different platforms. import java.util.Properties; import java.security.Security; import java.sql.*; import javax.net.ssl.*; public class KeyStore { public static void main(String argv[]) throws SQLException { String url=”jdbc:oracle:thin:@(DESCRIPTION=”+ “(ADDRESS=(PROTOCOL=TCPS)(Host=SRV01)(“+ “Port=1521))(CONNECT_DATA=(SID=DB01)))”; Properties props = new Properties(); props.setProperty(“user”, “scott”); props.setProperty(“password”, “tiger”); props.setProperty(“javax.net.ssl.trustStore”, “keystore.jks”); props.setProperty( “javax.net.ssl.trustStoreType”,”JKS”); props.setProperty( “javax.net.ssl.trustStorePassword”,”***”); DriverManager.registerDriver( new oracle.jdbc.OracleDriver()); Connection […]

check if using tcps part II

in your current session, as written there, check sys_context(‘USERENV’, ‘NETWORK_PROTOCOL’) in another session, you could grab some hints out of the network service banner. Do the maths, when it is not-not using ssl, it probably is… select sid,program, case when program not like ‘ora___@% (P%)’ then (select max(case when NETWORK_SERVICE_BANNER like ‘%TCP/IP%’ then ‘TCP’ when […]

The long long route to Kerberos

If you want to single-sign-on to your database with your Windows credentials, be aware, it is hard! But the benefit is quite valuable, no more saved password on the client, central password management and user expiration, compliance to the security guidelines, and at no extra cost Landscape for my setup One PC with Windows (PC01.EXAMPLE.COM) […]

Enhancement Request : SSL listener and OEM

#em12c still does not support SSL ! Encrypting network connection (https, ssh, sftp) is common sense in today’s business. In Enhancement Request 6512390, Created 19-Oct-2007, the customer requested support for SSL. Most recent update : it is postponed to 13cR2 at least ! *** 09/14/12 04:04 am DISCUSSION ***As we kick off 13c release, cleaning […]