Tuesday 6 November 2018

DONE: Retreived existing kerberos/Active Directory internal ticket. TODO: confirm an existing internal ticket based Kerberos/Active directory login to Database with java.

Code:
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/jjdbc/client-side-security.html#GUID-991705F7-C2C5-4BA9-85D1-32749AE2FF64
Kerberos Hello World login:

For testing with Active Directory

krb5.conf or krb5.ini setting required - or copy it to java default place
Cross site registry setting may be required: https://support.microsoft.com/en-gb/help/308339/registry-key-to-allow-session-keys-to-be-sent-in-kerberos-ticket-grant

Old but seems comprehensive notes:
http://cr.openjdk.java.net/~weijun/special/krb5winguide-2/raw_files/new/kwin

Login code:
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/jjdbc/client-side-security.html#GUID-991705F7-C2C5-4BA9-85D1-32749AE2FF64

Replace "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)"+
    "(HOST=oracleserver.mydomain.com)(PORT=5221))(CONNECT_DATA=" +
    "(SERVICE_NAME=orcl)))" with your connection details.

Replace "/home/Jdbc/Security/kerberos/krb5.conf" with your krb5.ini or krb5.conf (java also has a default place to look if this is not set). (with windows use double back slash \\)

If you want to use internal cache comment out:
    prop.setProperty(OracleConnection.CONNECTION_PROPERTY_THIN_NET_AUTHENTICATION_KRB5_CC_NAME,
                     "/tmp/krb5cc_5088");

Look at: "Attempt to connect with the default user:"
Ignore: "Attempt to connect with a specific user:"

use ojdbc8.jar (from sqldeveloper or sqlcl).

javac -cp ojdbc8.jar KerberosJdbcDemo.java
java  -Dsun.security.krb5.debug=true -Dsun.security.jgss.debug=true  -cp ojdbc8.jar;. KerberosJdbcDemo

You can use these settings in your sqldeveloper.conf (and start sqldeveloper from sqldeveloper/bin) to get this tracing/debugging in sqldeveloper.
(Currently (SQLDeveloper 18.3) internal cache does not work with sqldeveloper - you need an explicit file based cache).

(If you get >>> Found no TGT's in LSA you could need registry setting)
should see: >>> Obtained TGT from LSA: Credentials:

My Active Directory is not linked to my Oracle Database at the moment - so I could not check if this ticket worked for database login.

Monday 22 October 2018

New Developer Day VM 18.3 (18.2 APEX)

https://www.oracle.com/technetwork/database/enterprise-edition/databaseappdev-vm-161299.html

  • Oracle Linux 7
  • Oracle Database 18.3 Linux x86-64
  • Oracle SQL Developer 18.3
  • Oracle Application Express 18.2
  • Hands-On-Labs (accessed via the Toolbar Menu in Firefox)
    • Oracle REST Data Services 18.3
    • Oracle SQL Developer Data Modeler 18.3
    • Oracle XML DB
For performance up  resources to 2CPU 3GB RAM - default 1CPU 2G ram.

Errata:
1/ Occasional SQLDeveloper startup issue:
2/ DBCA trick
3/ JET lab
4/ reset scripts

1/Occasional SQLDeveloper startup issue:

(java:23682): Gdk-ERROR **: The program 'java' received an X Window System error.
This probably reflects a bug in the program.
The error was 'RenderBadPicture (invalid Picture parameter)'.
  (Details: serial 17547 error_code 143 request_code 139 minor_code 7)
  (Note to programmers: normally, X errors are reported asynchronously;
   that is, you will receive the error a while after causing it.
   To debug your program, run it with the --sync command line
   option to change this behavior. You can then get a meaningful
   backtrace from your debugger if you break on the gdk_x_error() function.)
/u01/userhome/oracle/sqldeveloper/sqldeveloper/bin/../../ide/bin/launcher.sh: line 1606: 23682 Trace/breakpoint trap   (core dumped) ${JAVA} "${APP_VM_OPTS[@]}" ${APP_ENV_VARS} -classpath ${APP_CLASSPATH} ${APP_MAIN_CLASS} "${APP_APP_OPTS[@]}"
[oracle@localhost ~]$ which java
~/java/jdk1.8.0_152/bin/java

2/ DBCA Trick:
For dbca copy in assistance directory to $ORACLE_HOME from unzip of 18.3 Oracle Database download
https://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html

3/JET lab
Automated set up of seconds ords for use in JET lab (2nd ords also be done through sqldeveloper as described in labs)
2nd pdb 2nd ords
newpdbords        -Sets up 2nd pdb called 'ORDS'
9090init              -Starts up ords on new pdb port 9090
(can start stop with 9090start 9090stop)

 #!/bin/bash
. /home/oracle/.bashrc
newpdbords
9090init
sleep 180
cd /home/oracle/sqldeveloper/ords
echo '#!/usr/bin/expect
exp_internal 1
set timeout 1200
spawn $JAVA_HOME/bin/java -jar ords.war user ords_dev "SQL Developer"
expect -regexp "Enter a password for user ords_dev." { send "oracle\r" }
expect -regexp "Confirm password for user ords_dev." {send "oracle\r"}
expect -regexp "Something that will never happen to force keep searching until process end" {send "neverhappen\r"}
interact'| sed 'sZ$JAVA_HOMEZ'"$JAVA_HOME"'Zg' > ~/bin/xp.sh
chmod 755 ~/bin/xp.sh
~/bin/xp.sh
echo '#!/usr/bin/expect
exp_internal 1
set timeout 1200
spawn $JAVA_HOME/bin/java -jar ords.war user ords_admin "Listener Administrator"
expect -regexp "Enter a password for user ords_admin." { send "oracle\r" }
expect -regexp "Confirm password for user ords_admin." {send "oracle\r"}
expect -regexp "Something that will never happen to force keep searching until process end" {send "neverhappen\r"}
interact'| sed 'sZ$JAVA_HOMEZ'"$JAVA_HOME"'Zg' > ~/bin/xp.sh
chmod 755 ~/bin/xp.sh
~/bin/xp.sh
cd -
9090stop
#on ords 9090 reset we want reinstall not uninstall
touch ~/.ordsreinstall
#note 9090init has a 3 minute wait to ensure ords has started


4/reset scripts
Report any issues - if reset fails reimport VM

Wednesday 6 June 2018

New Developer day VM

http://www.oracle.com/technetwork/database/enterprise-edition/databaseappdev-vm-161299.html

Virtualbox 5.2.8 or above
for performance use 3GB RAM 2 CPU

New ish:

rest enabled sql
rest enabled jdbc sql username/password@http://...(to ORDS)

+18.1 ORDS SQLDeveloper SQLCL modeller APEX
Oracle Linux 7 update5 (yum updated from 7u3 iso)

executables now under applications->other
(only .txt and .html can be easily clicked on desktop with no popups)

readme.txt and readmeCopy.txt - the same - one sometimes gets half hidden in icon layout.

errata
Same as last release + more recent software
any requests/what does not work let me know in the comments.

Friday 4 May 2018

Things I worked around when setting up MIT kerberos and sqldeveloper over thin jdbc against 12.2.0.1 Oracle Database:

Server used Oracle Linux 7 for Kerberos (yum -y install krb5-server krb5-libs)
and Oracle Database 12.2.0.1
Clients used Oracle Linux 7 and Windows both SQLDeveloper 18.1

MIT Kerberos / oracle thick driver went OK once I used global c## user.

use c## user:
SQL>  create user c##user1 identified externally as 'user1@...'

(non c## user did not work for me.).

Used sqldeveloper with jre included (on windows).

SQLdeveloper jdbc thick:
'just like sqlplus' requires Oracle Client or Instant Client (not sure (which/if both) has Kerberos).

Sqldeveloper thin kerberos configuration:

tools->preferences->database->advanced:  config file (krb5.conf) - eg (on my
linux client) /etc/krb5.conf   Credential cache file (on my linux client) eg
/home/oracle/mycache (not (Microft Windows) MSLSA or OSMFT)
- sqldeveloper thin driver does not read sqlnet.ora
- or currently recognize non file based cache.

On connection either:
1/Give principal (no ticket/cache required) give / get prompted for password - went OK.
2/Leave principal blank (and get it from cache):
Use kinit (from sqldeveloper on windows - from yum -y install krb5-workstation on Oracle Linux) to set up existing ticket:
 needed "kinit -A" (for addressless ticket on windows) (used kinit default cache location and pointed to default from sqldev) (i.e. -A to get around: incorrect net address)

(Also tried at the same time kinit -p -f (proxiable and forwardable, and setting non proxy java -D setting) after -A it worked.)

klist -f
showed:
Flags: FORWARDABLE;PROXIABLE;PRE-AUTHENT

Issues  - customers might not be using MIT Kerberos.
Workarounds: Often thick jdbc works 'just like sqlplus' to workaround thin jdbc issues. 

Wednesday 24 January 2018

1/Cool stuff from Oracle (Developer Day VM related)

2/Showing off your Developer Day VM to remote users (one method only requiring ssh).

3/Do not 'yum update' 2017 Developer Day VM

1/Cool stuff from Oracle (Developer Day VM related)

Any trouble getting these working on your VM let me know.

datamodeler-17.4.0.355.2121-no-jre.zip (unzip and go)
jdk-8u152-linux-x64.tar.gz (or later) (unzip in ~/java [might need to remove existing java in that directory] and go, check ~/.bashrc)
sqlcl-17.4.0.354.2224-no-jre.zip - include REST JDBC http://www.oracle.com/technetwork/developertools/rest-data-services/downloads/index.html - unzip and go
sqldeveloper-17.4.0.355.2349-no-jre.zip - unzip and go

apex_5.1.4.zip (lower version already installed)
ords.17.4.1.353.06.48.zip (lower version already installed - but both can run on different ports/pdbs) 
- one line admin change to be  REST SQL (and REST JDBC server) note no longer prompts for sys user. (Would only have trouble running old and new if existing ORDS is installed on the CDB rather than pdb, which is not the case) existing ords.sh should work (for start stop) (put in full path to new war) - initial configure/install  will change.


2/Showing off your Developer Day VM to remote users (one method only requiring ssh).

If your networking firewall etc are out of your control port 22 (ssh) probably still works.

For other people to access your VM for test / development purposes you can (obviously but I had forgotten) ssh port forwarding (for 9090 8080 8081 or 1521) - requires ssh login to host machine, for example for 1521:

Note the default is to keep the ssh login open (might have to if all else fails kill it to stop it).

1 ssh required (no sqlplus required) (login and password on host required)
// log in to host (desktop hosts IP my change)
bash-4.2$ ssh -p 22 user@___the_ip_address__
[need password for user]
>ssh -p 2222 oracle@localhost
[password oracle]
. oraenv -- ie setup oracle environment
orcl12c -- the default environment may have several
>sql system/oracle

Or
2: ssh required (sqlcl required) (login and password on host required)
[IN ADVANCE] ssh -L 1521:localhost:1521 user@__the_ip_address__
[need password for user]
On your own machine using sqlcl sqlplus sqldeveloper etc locally and your local 1521 port: (port forward from a different port if that is in use)

sql system/oracle@localhost:1521/orcl

3/Do not 'yum update' 2017 Developer Day VM

Warning do not 'yum update' a 2017 developer day VM (if you do you will upgrade to Oracle Linux 7.4 and you guest desktop will not resize by mouse action (you can still resize by command line))