Archive for the ‘Oracle DBA’ tag
Oracle 11g XE APEX
The question for most new Oracle users is what’s Apex? They have a different question When they discover how to connect to the Oracle Database 11g XE default instance with this URL:
http://localhost:8080/apex |
You’ll see the following web site, and wonder what do I enter for the Workspace, the Username, and the Password values?
The answers are:
- Default Workspace: INTERNAL
- Default User: ADMIN
- Default Password:
SYS
orSYSTEM
Password from Install
Enter those values within the initial password time interval and you’ll arrive at the next screen where you can manage the Oracle Database 11g XE instance. If you wait too long, you’ll be redirected to enter the original SYS
or SYSTEM
password from install and a new password twice. The rules for a new password are:
- Password must contain at least 6 characters.
- New password must differ from old password by at least 2 characters.
- Password must contain at least one numeric character (0123456789).
- Password must contain at least one punctuation character (!”#$%&()“*+,-/:;<=>?_).
- Password must contain at least one upper-case alphabetic character.
- Password must not contain username.
Whether you go directly to the next screen or have to enter your a new password, you should see the following screen:
You can find the default configuration for the installation with the following anonymous PL/SQL block:
DECLARE /* Declare variables. */ lv_endpoint NUMBER := 1; lv_host VARCHAR2(80); lv_port NUMBER; lv_protocol NUMBER; BEGIN /* Check for current XDB settings. */ dbms_xdb.getlistenerendpoint( lv_endpoint , lv_host , lv_port , lv_protocol ); /* Print the values. */ dbms_output.put_line('Endpoint: ['||lv_endpoint||']'||CHR(10)|| 'Host: ['||lv_host||']'||CHR(10)|| 'Port: ['||lv_port||']'||CHR(10)|| 'Protocol: ['||lv_protocol||']'); END; / |
It should print the following:
Endpoint: [1] Host: [localhost] Port: [8080] Protocol: [1] |
This is a standalone configuration and you can’t connect to the XDB server from another machine. You can only connect from the local machine.
I hope this helps those trying to use the default Apex 4 installation provided as part of the Oracle Database 11g XE instance. You can read an older post of mine that shows you how to set up a basic Workspace, but after reflection I’ll write more about creating and managing workspaces.
Check Constraints
Oracle Database 12c introduces a SEARCH_CONDITION_VC
column to the CDB_
, DBA_
, ALL_
, and USER_CONSTRAINTS
views. The SEARCH_CONDITION_VC
column is a VARCHAR2
data type equivalent to the search condition in the LONG
data type SEARCH_CONDITION
column. Unfortunately, Oracle Database 11g and earlier versions requires you to convert the LONG
data type to a VARCHAR2
for the equivalent behavior. This post provides you with a function to help you do that in Oracle Database 11g.
While Oracle Database 12c let’s you check the search condition of a CHECK
constraint, with this query:
SELECT uc.constraint_name AS constraint_name , uc.search_condition_vc AS search_condition FROM user_constraints uc WHERE uc.table_name = UPPER('table_name') AND REGEXP_LIKE(uc.search_condition_vc,'search_key','i'); |
You need the following GET_SEARCH_CONDITION
function to convert the SEARCH_CONDITION
column from a LONG
data type to a VARCHAR2
data type. It uses the DBMS_SQL
package to convert the LONG
data type.
CREATE OR REPLACE FUNCTION get_search_condition ( pv_table_name VARCHAR2 , pv_column_name VARCHAR2 ) RETURN VARCHAR2 AS /* Declare local variables. */ lv_cursor INTEGER := DBMS_SQL.open_cursor; lv_feedback INTEGER; -- Acknowledgement of dynamic execution lv_length INTEGER; -- Length of the input string lv_value_length INTEGER; -- Length of the output string lv_constraint_name VARCHAR2(30); -- Constraint name lv_return VARCHAR2(32767); -- Function output lv_stmt VARCHAR2(2000); -- Dynamic SQL statement lv_long LONG; -- Dynamic LONG data type. lv_string VARCHAR2(32760); -- Maximum length of LONG data type FUNCTION return_length ( pv_table_name VARCHAR2 , pv_column_name VARCHAR2 ) RETURN VARCHAR2 IS /* Declare a target variable, because of the limit of SELECT-INTO. */ lv_long_view LONG; /* Declare a dynamic cursor. */ CURSOR c ( cv_table_name VARCHAR2 , cv_column_name VARCHAR2 ) IS SELECT uc.search_condition FROM user_constraints uc INNER JOIN user_cons_columns ucc ON uc.table_name = ucc.table_name AND uc.constraint_name = ucc.constraint_name WHERE uc.table_name = UPPER(cv_table_name) AND ucc.column_name = UPPER(cv_column_name) AND uc.constraint_type = 'C'; BEGIN /* Open, fetch, and close cursor to capture view text. */ OPEN c (pv_table_name, pv_column_name); FETCH c INTO lv_long_view; CLOSE c; /* Return the output CLOB length value. */ RETURN LENGTH(lv_long_view); END return_length; BEGIN /* Get the length of the CLOB column value. */ lv_length := return_length(pv_table_name, pv_column_name); /* Create dynamic statement. */ lv_stmt := 'SELECT uc.search_condition'||CHR(10) || 'FROM user_constraints uc INNER JOIN user_cons_columns ucc'||CHR(10) || 'ON uc.table_name = ucc.table_name'||CHR(10) || 'AND uc.constraint_name = ucc.constraint_name'||CHR(10) || 'WHERE uc.table_name = UPPER('''||pv_table_name||''')'||CHR(10) || 'AND ucc.column_name = UPPER('''||pv_column_name||''')'||CHR(10) || 'AND uc.constraint_type = ''C'''; /* Parse and define VARCHAR2 and LONG columns. */ DBMS_SQL.parse(lv_cursor, lv_stmt, DBMS_SQL.native); DBMS_SQL.define_column_long(lv_cursor,1); /* Only attempt to process the return value when fetched. */ IF DBMS_SQL.execute_and_fetch(lv_cursor) = 1 THEN DBMS_SQL.column_value_long( lv_cursor , 1 , lv_length , 0 , lv_string , lv_value_length); END IF; /* Check for an open cursor. */ IF DBMS_SQL.is_open(lv_cursor) THEN DBMS_SQL.close_cursor(lv_cursor); END IF; /* Convert the long length string to a maximum size length. */ lv_return := lv_string; RETURN lv_return; END get_search_condition; / |
Then, you can use the following query to view the full search criteria of a CHECK
constraint that matches part of a search string:
COLUMN constraint_name FORMAT A16 COLUMN search_condition FORMAT A30 SELECT uc.constraint_name AS constraint_name , get_search_condition('table_name','column_name') AS search_condition FROM user_constraints uc WHERE REGEXP_LIKE(get_search_condition('table_name','column_name'),'check_constraint_search_string','i') AND uc.constraint_type = 'C'; |
Hope this helps those looking at discovering the full search criteria of a CHECK
constraint.
E-Business Customers
It seems the Oracle E-Business Suite stymies newbies. Naturally, it’s complex and requires some research before you try to develop any customization.
Having recently fielded a question about how to manage, search, create and update customers for a third party order entry system, I’m always surprised by how little people know about Oracle’s Trading Community Architecture (TCA). They likewise frequently don’t know that there is already a documented process, like the one defined in the Oracle Trading Community Architecture User Guide:
You also have the Oracle Trading Community Architecture Reference Guide, which provides more detailed information. I strongly recommend you read these before trying to Google a solution that may or may not meet your needs. Naturally, good PL/SQL coding skills make this type of activity straightforward. That’s why Oracle staff should master PL/SQL.
Wrapping SQL*Plus
One annoying thing from installing Oracle Database 11g on Fedora, was that the up arrows for command history didn’t work. I decided to fix that today after seeing Lutz Hartmann’s article on rlwrap
. Unfortunately, the epel
(Extra Packages for Enterprise Linux) package he recommended doesn’t run on Fedora 20. You can read my tale of woe, or skip to the .bashrc
function that fixed it when I installed only rlwrap
.
Attempting it on yum
, gave me these errors:
# yum install http://www.mirrorservice.org/sites/dl.fedoraproject.org/pub/epel/6/i386/epel-release-6-8.noarch.rpm Loaded plugins: langpacks, refresh-packagekit epel-release-6-8.noarch.rpm | 14 kB 00:00 Examining /var/tmp/yum-root-5CLTPa/epel-release-6-8.noarch.rpm: epel-release-6-8.noarch Marking /var/tmp/yum-root-5CLTPa/epel-release-6-8.noarch.rpm to be installed Resolving Dependencies --> Running transaction check ---> Package epel-release.noarch 0:6-8 will be installed --> Processing Conflict: epel-release-6-8.noarch conflicts fedora-release No package matched to upgrade: epel-release --> Finished Dependency Resolution Error: epel-release conflicts with fedora-release-20-3.noarch You could try using --skip-broken to work around the problem You could try running: rpm -Va --nofiles –nodigest |
Poking around for an epel
fix wasn’t successful, so I chose to install only the rlwrap
package. Here’s that command and log file:
[root@localhost ~]# yum install rlwrap Loaded plugins: langpacks, protectbase, refresh-packagekit 0 packages excluded due to repository protections Resolving Dependencies --> Running transaction check ---> Package rlwrap.x86_64 0:0.41-1.fc20 will be installed --> Finished Dependency Resolution Dependencies Resolved ================================================================================ Package Arch Version Repository Size ================================================================================ Installing: rlwrap x86_64 0.41-1.fc20 updates 95 k Transaction Summary ================================================================================ Install 1 Package Total download size: 95 k Installed size: 204 k Is this ok [y/d/N]: y Downloading packages: rlwrap-0.41-1.fc20.x86_64.rpm | 95 kB 00:00 Running transaction check Running transaction test Transaction test succeeded Running transaction (shutdown inhibited) Installing : rlwrap-0.41-1.fc20.x86_64 1/1 Verifying : rlwrap-0.41-1.fc20.x86_64 1/1 Installed: rlwrap.x86_64 0:0.41-1.fc20 Complete! |
The next step was getting it to work. A sqlplus
function wrapper inside the .bashrc
file seemed the easiest. Here’s the code to the .bashrc
file:
# .bashrc # Source global definitions if [ -f /etc/bashrc ]; then . /etc/bashrc fi # Uncomment the following line if you don't like systemctl's auto-paging feature: # export SYSTEMD_PAGER= # User specific aliases and functions . /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh # Wrap sqlplus with rlwrap to edit prior lines. sqlplus() { if [ "$RLWRAP" = "0" ]; then sqlplus "$@" else rlwrap sqlplus "$@" fi } # Set vi as a command line editor. set -o vi |
As always, I hope this helps some folks.
Hiding a Java Source
The ability to deploy Java inside the Oracle database led somebody to conclude that the source isn’t visible in the data catalog. Then, that person found that they were wrong because the Java source is visible when you use a DDL command to CREATE
, REPLACE
, and COMPILE
the Java source. This post discloses how to find the Java source and how to prevent it from being stored in the data catalog.
You can verify that the Java class and source files exist with the following query:
1 2 3 4 5 6 7 8 | COLUMN object_name FORMAT A20 HEADING "Object Name" COLUMN object_type FORMAT A12 HEADING "Object Type" COLUMN status FORMAT A14 HEADING "Object Status" SELECT object_name , object_type , status FROM user_objects WHERE object_name = 'ReadFile'; |
It displays:
Object Name Object Type Object Status -------------------- ------------ -------------- ReadFile JAVA SOURCE VALID ReadFile JAVA CLASS VALID 2 rows selected. |
Then, you can use the following query to discovery a Java library created by a SQL command:
1 2 3 4 5 6 | COLUMN line FORMAT 9999 HEADING "Line|#" COLUMN text FORMAT A66 HEADING "Text" SELECT line , text FROM user_source WHERE name = 'ReadFile'; |
It displays the following:
Line # Text ------- ------------------------------------------------------------------ 1 // Java library imports. 2 import java.io.File; 3 import java.io.BufferedReader; 4 import java.io.FileNotFoundException; 5 import java.io.IOException; 6 import java.io.FileReader; 7 import java.security.AccessControlException; 8 9 // Class definition. 10 public class ReadFile { 11 // Define class variables. 12 private static File file; 13 private static FileReader inTextFile; 14 private static BufferedReader inTextReader; 15 private static StringBuffer output = new StringBuffer(); 16 private static String outLine, outText; 17 18 // Define readText() method. 19 public static String readText(String fromFile) 20 throws AccessControlException, IOException { 21 // Read file. 22 try { 23 // Initialize File. 24 file = new File(fromFile); 25 26 // Check for valid file. 27 if (file.exists()) { 28 29 // Assign file to a stream. 30 inTextFile = new FileReader(file); 31 inTextReader = new BufferedReader(inTextFile); 32 33 // Read character-by-character. 34 while ((outLine = inTextReader.readLine()) != null) { 35 output.append(outLine + "\n"); } 36 37 // Assing the StringBuffer to a String. 38 outText = Integer.toString(output.toString().length()); 39 40 // Close File. 41 inTextFile.close(); } 42 else { 43 outText = new String("Empty"); }} 44 catch (IOException e) { 45 outText = new String(""); 46 return outText; } 47 return outText; }} 47 rows selected. |
You can eliminate the source by compiling the Java library outside the database. Then, you use the loadjava
utility to load the only the class file into the data catalog. The syntax would be the following command for an importer
user in a video
Pluggable Database (PDB):
loadjava -r -f -o -user importer/importer@video ReadFile.class |
You should know that this syntax is disallowed by the loadjava
utility, notwithstanding it’s found in the Oracle Database 12c documentation:
loadjava -r -f -o -user importer@video/importer ReadFile.class |
You can verify that only the Java class file exists with the following query:
1 2 3 4 5 6 7 8 | COLUMN object_name FORMAT A20 HEADING "Object Name" COLUMN object_type FORMAT A12 HEADING "Object Type" COLUMN status FORMAT A14 HEADING "Object Status" SELECT object_name , object_type , status FROM user_objects WHERE object_name = 'ReadFile'; |
It displays:
Object Name Object Type Object Status -------------------- ------------ -------------- ReadFile JAVA CLASS VALID 1 row selected. |
Hope this helps those who want to hide the Java source files.
Encrypting a Column
A few years ago, I gave a session on Oracle’s Data Vault. An attendee from that session and I happened to be seated at the same table for lunch last Thursday when I presented at Utah Oracle User Group Fall Symposium. He asked if I’d ever found a way to encrypt a column from the prying eyes of the SYS
user. I said yes, and he asked how. It was a bit more than could be explained at lunch and promised to put it on the blog. (Disclaimer: It is possible to unwrap wrapped code but the mechanics change with each release and they require access to the SYS
schema and substantial Application DBA knowledge; however, there are scripts published on the Internet to unwrap the code. There are also other vulnerabilities in the example, but I’ll pass on exposing them. After all this was written to illustrate an approach.)
It’s important to note you can now hide columns in Oracle Database 12c, but they’re still visible to the DBA-level staff. That’s why I’ll publish a new article on re-writing this encrypted object as a Java library, and disclose how to hide password exchanges from the SGA area.
This demonstrates how you can encapsulate a column from even the SYS
user. I also put it into Appendix D, PL/SQL Built-in Packages and Types to illustrate the DBMS_CRYPTO
package. It uses a User Defined Type (UDT) and assumes you have a working knowledge of object types in Oracle 10g forward. If not, you can find them in:
- Chapter 11 of my Oracle Database 12c PL/SQL Programming book.
- Chapter 14 of my Oracle Database 11g PL/SQL Programming book.
It also assumes basic knowledge of Oracle’s encryption technology, some of which is explained in the new Oracle Database 12c PL/SQL Programming book.
For reference, a good DBA would simply find this clear text password in the SGA. A more secure approach might be hiding the encryption keyword in a one column and table or embedded in some string within a common lookup table column value as a position specific substring. Alas, I don’t have time to write something so elaborate.
The steps are:
- You must create a user defined type (UDT), which sets the up a single salary column.
1 2 3 4 5 6 7 8 9 10 | CREATE OR REPLACE TYPE masked IS OBJECT ( salary RAW(1000) , CONSTRUCTOR FUNCTION masked RETURN SELF AS RESULT , CONSTRUCTOR FUNCTION masked ( salary NUMBER ) RETURN SELF AS RESULT , MEMBER FUNCTION get_raw_salary RETURN RAW , MEMBER FUNCTION get_salary ( KEY VARCHAR2 ) RETURN NUMBER , MEMBER PROCEDURE to_string , ORDER MEMBER FUNCTION equals ( object MASKED ) RETURN NUMBER ) INSTANTIABLE FINAL; / |
- As the
SYS
user, you must grantEXECUTE
privilege on theDBMS_CRYPTO
package to the targetSCHEMA
user.
1 | GRANT EXECUTE ON dbms_crypto TO schema_name; |
- You implement the
MASKED
UDT, as follows below (source unencrypted, don’t worry I show you how to encrypt [wrap] it in a moment).
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 | CREATE OR REPLACE TYPE BODY masked IS CONSTRUCTOR FUNCTION masked RETURN SELF AS RESULT IS /* Create a placeholder for a zero salary, like an empty_clob() call. */ zero MASKED := masked(0); BEGIN /* Assign an encrypted zero salary to the instance. */ self := zero; RETURN; END masked; CONSTRUCTOR FUNCTION masked ( salary NUMBER ) RETURN SELF AS RESULT IS /* Declare local variables for encryption, object types hold instance objects and object body variables are method specific. Placing them inside the methods while tedious prevents their disclosure. */ lv_key_string VARCHAR2(4000) := 'Encrypt Me!'; lv_key RAW(1000); lv_raw RAW(1000); lv_encrypted_data RAW(1000); BEGIN /* Dynamic assignment. */ lv_raw := UTL_RAW.cast_to_raw(NVL(salary,0)); /* Convert to a RAW 64-character key. */ lv_key := UTL_RAW.cast_to_raw(lv_key_string); lv_key := RPAD(lv_key,64,'0'); /* Encrypt the salary before assigning it to the object type attribute */ lv_encrypted_data := DBMS_CRYPTO.ENCRYPT(lv_raw, dbms_crypto.ENCRYPT_AES256 + dbms_crypto.CHAIN_CBC + dbms_crypto.PAD_PKCS5,lv_key); self.salary := lv_encrypted_data; RETURN; END masked; MEMBER FUNCTION get_raw_salary RETURN RAW IS BEGIN RETURN self.salary; END get_raw_salary; MEMBER FUNCTION get_salary( key VARCHAR2 ) RETURN NUMBER IS /* Declare local variables for encryption, object types hold instance objects and object body variables are method specific. Placing them inside the methods while tedious prevents their disclosure. */ lv_key_string VARCHAR2(4000) := 'Encrypt Me!'; lv_decrypted_data RAW(4000); lv_key RAW(1000); lv_return_value NUMBER; BEGIN /* Verify key value matches local value before decrypting, substitute a zero value when the key doesn't match. */ IF key = lv_key_string THEN lv_key := UTL_RAW.cast_to_raw(lv_key_string); lv_key := RPAD(lv_key,64,'0'); lv_decrypted_data := DBMS_CRYPTO.DECRYPT(self.salary, dbms_crypto.ENCRYPT_AES256 + dbms_crypto.CHAIN_CBC + dbms_crypto.PAD_PKCS5,lv_key); lv_return_value := TO_NUMBER(TO_CHAR(UTL_RAW.cast_to_number(lv_decrypted_data),'9999990.00')); ELSE lv_return_value := 0; END IF; RETURN lv_return_value; END get_salary; ORDER MEMBER FUNCTION equals ( object MASKED ) RETURN NUMBER IS /* Declare local variables for encryption, object types hold instance objects and object body variables are method specific. Placing them inside the methods while tedious prevents their disclosure. */ lv_key_string VARCHAR2(4000) := 'Encrypt Me!'; lv_decrypted_self RAW(4000); lv_decrypted_peer RAW(4000); lv_key RAW(1000); BEGIN /* Decrypt the current and peer object attribute values before comparing their values. */ lv_key := UTL_RAW.cast_to_raw(lv_key_string); lv_key := RPAD(lv_key,64,'0'); lv_decrypted_self := DBMS_CRYPTO.DECRYPT(self.salary, dbms_crypto.ENCRYPT_AES256 + dbms_crypto.CHAIN_CBC + dbms_crypto.PAD_PKCS5, lv_key); lv_decrypted_peer := DBMS_CRYPTO.DECRYPT(object.get_raw_salary(), dbms_crypto.ENCRYPT_AES256 + dbms_crypto.CHAIN_CBC + dbms_crypto.PAD_PKCS5, lv_key); /* Sort order key. */ IF lv_decrypted_self < lv_decrypted_peer THEN RETURN 1; ELSE RETURN 0; END IF; END equals; MEMBER PROCEDURE to_string IS BEGIN /* Provide a to_string method for good practice. */ DBMS_OUTPUT.put_line('Encrypted value'); END to_string; END; / |
- You implement the
MASKED
UDT encrypted by using theDBMS_DDL
package, as follows below. This ensures that others can’t read the source code by querying theALL_
,DBA_
, orUSER_SOURCE
views. You should note that I’ve removed comments and unnecessary spaces.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 | BEGIN DBMS_DDL.create_wrapped( 'CREATE OR REPLACE TYPE BODY masked IS ' || 'CONSTRUCTOR FUNCTION masked RETURN SELF AS RESULT IS ' || 'zero MASKED := masked(0); ' || 'BEGIN ' || 'self := zero; ' || 'RETURN; ' || 'END masked; ' || 'CONSTRUCTOR FUNCTION masked ( salary NUMBER ) RETURN SELF AS RESULT IS ' || 'lv_key_string VARCHAR2(4000) := ''Encrypt Me!''; ' || 'lv_key RAW(1000); ' || 'lv_raw RAW(1000) := RPAD(utl_raw.cast_from_number(salary),32,''0''); ' || 'lv_encrypted_data RAW (1000); ' || 'BEGIN ' || 'lv_key := utl_raw.cast_to_raw(lv_key_string); ' || 'lv_key := RPAD(lv_key,64,''0''); ' || 'lv_encrypted_data := DBMS_CRYPTO.ENCRYPT(lv_raw, dbms_crypto.ENCRYPT_AES256 + dbms_crypto.CHAIN_CBC + dbms_crypto.PAD_PKCS5,lv_key); ' || 'self.salary := lv_encrypted_data; ' || 'RETURN; ' || 'END masked; ' || 'MEMBER FUNCTION get_raw_salary RETURN RAW IS ' || 'BEGIN ' || 'RETURN self.salary; ' || 'END get_raw_salary; ' || 'MEMBER FUNCTION get_salary( key VARCHAR2 ) RETURN NUMBER IS ' || 'lv_key_string VARCHAR2(4000) := ''Encrypt Me!''; ' || 'lv_decrypted_data RAW(4000); ' || 'lv_key RAW(1000); ' || 'lv_return_value NUMBER; ' || 'BEGIN ' || 'IF key = lv_key_string THEN ' || 'lv_key := utl_raw.cast_to_raw(lv_key_string); ' || 'lv_key := RPAD(lv_key,64,''0''); ' || 'lv_decrypted_data := DBMS_CRYPTO.DECRYPT(self.salary, dbms_crypto.ENCRYPT_AES256 + dbms_crypto.CHAIN_CBC + dbms_crypto.PAD_PKCS5,lv_key); ' || 'lv_return_value := TO_NUMBER(TO_CHAR(utl_raw.cast_to_number(lv_decrypted_data),''9999990.00'')); ' || 'ELSE ' || 'lv_return_value := 0; ' || 'END IF; ' || 'RETURN lv_return_value; ' || 'END get_salary; ' || 'ORDER MEMBER FUNCTION equals ( object MASKED ) RETURN NUMBER IS ' || 'lv_key_string VARCHAR2(4000) := ''Encrypt Me!''; ' || 'lv_decrypted_self RAW(4000); ' || 'lv_decrypted_peer RAW(4000); ' || 'lv_key RAW(1000); ' || 'BEGIN ' || 'lv_key := utl_raw.cast_to_raw(lv_key_string);' || 'lv_key := RPAD(lv_key,64,''0''); ' || 'lv_decrypted_self := DBMS_CRYPTO.DECRYPT(self.salary, dbms_crypto.ENCRYPT_AES256 + dbms_crypto.CHAIN_CBC + dbms_crypto.PAD_PKCS5, lv_key); ' || 'lv_decrypted_peer := DBMS_CRYPTO.DECRYPT(object.get_raw_salary(), dbms_crypto.ENCRYPT_AES256 + dbms_crypto.CHAIN_CBC + dbms_crypto.PAD_PKCS5, lv_key); ' || 'IF lv_decrypted_self < lv_decrypted_peer THEN ' || 'RETURN 1; ' || 'ELSE ' || 'RETURN 0; ' || 'END IF; ' || 'END equals; ' || 'MEMBER PROCEDURE to_string IS ' || 'BEGIN ' || 'dbms_output.put_line(''Encrypted value''); ' || 'END to_string; ' || 'END; '); END; / |
You can read more about wrapping PL/SQL in Appendix F of Oracle Database 12c PL/SQL Programming.
- You can test a single instance with this anonymous PL/SQL block.
1 2 3 4 5 6 7 8 9 | DECLARE o MASKED := masked(82000.12); BEGIN DBMS_OUTPUT.put('Override: '); o.to_string(); DBMS_OUTPUT.put_line('Decrypted: '||o.get_salary('Encrypt Me!')); DBMS_OUTPUT.put_line('Bad Key: '||o.get_salary('Incorrect')); END; / |
Override: Encrypted value Decrypted: 82000.12 Bad Key: 0 |
- You can test a series of instances by making them persistent objects, or columns in a table, and then query values from them. It also lets you you test the sorting feature provided in the UDT.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | CREATE TABLE sort_demo (salary MASKED); INSERT INTO sort_demo VALUES (masked(82000.24)); INSERT INTO sort_demo VALUES (masked(61000.12)); INSERT INTO sort_demo VALUES (masked(93000.36)); SELECT salary AS "Encrypted" FROM sort_demo; COLUMN unordered FORMAT 9,999,990.00 HEADING "Unordered|List" SELECT TREAT(salary AS MASKED).get_salary('Encrypt Me!') AS Unordered FROM sort_demo; COLUMN ordered FORMAT 9,999,990.00 HEADING "Ordered|List" SELECT TREAT(salary AS MASKED).get_salary('Encrypt Me!') AS Ordered FROM sort_demo ORDER BY 1; |
Encrypted(SALARY) -------------------------------------------------------------------------- MASKED('1798C04F8342C53A826144568075CBDB145D0C9BD226B410C8C7167B76382D86') MASKED('82F783F2E117AA60955B0A2E73545506936D6F8FFBEC5D9E0D8E70B82D4B694D') MASKED('1551F350AAEB30ADFC1527F25CAA935732243858AF1C5D724A78B997A4394EAD') Unordered List ------------- 82,000.24 61,000.12 93,000.36 Ordered List ------------- 61,000.12 82,000.24 93,000.36 |
If you want to retest this, make sure you drop the SORT_DEMO
table first. Hope this helps any interested in an elaborate solution.
Fedora Install of Oracle 11g
After the installation of Oracle 11g XE on Windows, I anticipated problems installing on Fedora. It’s terrific to report it was by far simpler to install on Fedora. This post provides the steps, and builds on the Fedora configuration published earlier this week.
- After you download the software from the Oracle web site, you’ll need to expand the compressed file. When you double click on the download item you will see the following screen. Click Extract at the top of the display.
- The Extract displays the following dialog. Click the Create Folder button and you’ll get an entry point for a new directory in your user’s directory. For the example, Oracle11gXE
- After creating the directory, click the Extract button.
- The extract process completes and shows the following dialog.
- The Disk1 directory will show the following contents.
- You need to drop down into a Terminal session, which you can launch by clicking on Applications, System Tools, and Terminal in the menu.
sudo rpm -iv oracle-xe-11.2.0-1.0.x86_64.rpm |
This command will install the packages in verbose syntax and display the following messages:
[sudo] password for mclaughlinm: Preparing packages for installation... oracle-xe-11.2.0-1.0 Executing post-install steps... You must run '/etc/init.d/oracle-xe configure' as the root user to configure the database. |
- This step requires that you assume the role of the
root
user, which can be done with this syntax:
sudo sh |
In this root
shell, you run the Oracle 11g XE configuration with this command:
/etc/init.d/oracle-xe configure |
The following are the text prompts that you accept to configure Oracle 11g XE:
Oracle Database 11g Express Edition Configuration ------------------------------------------------- This will configure on-boot properties of Oracle Database 11g Express Edition. The following questions will determine whether the database should be starting upon system boot, the ports it will use, and the passwords that will be used for database accounts. Press <Enter> to accept the defaults. Ctrl-C will abort. Specify the HTTP port that will be used for Oracle Application Express [8080]: Specify a port that will be used for the database listener [1521]: Specify a password to be used for database accounts. Note that the same password will be used for SYS and SYSTEM. Oracle recommends the use of different passwords for each database account. This can be done after initial configuration: Confirm the password: Do you want Oracle Database 11g Express Edition to be started on boot (y/n) [y]: Starting Oracle Net Listener...Done Configuring database... Starting Oracle Database 11g Express Edition instance...Done Installation completed successfully. |
After completing the configuration, you need to do two things. First, you need to modify the .bash_profile
file for your user (covered in the next step). Second, you need to reboot your system.
- This step requires that you exit the
root
shell by typing theexit
command. This should put you back into your administration account with sudoer privileges. You usevi
to edit and add the following to the.bashrc
file.
. /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh |
Or, you can write your own file, like this:
# Oracle Settings TMP=/tmp; export TMP TMPDIR=$TMP; export TMPDIR ORACLE_HOSTNAME=localhost.localdomain; export ORACLE_HOSTNAME ORACLE_UNQNAME=DB11G; export ORACLE_UNQNAME ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE ORACLE_HOME=$ORACLE_BASE/product/11.2.0/xe; export ORACLE_HOME ORACLE_SID=XE; export ORACLE_SID NLS_LANG=`$ORACLE_HOME/bin/nls_lang.sh`; export NLS_LANG ORACLE_TERM=xterm; export ORACLE_TERM PATH=/usr/sbin:$PATH; export PATH PATH=$ORACLE_HOME/bin:$PATH; export PATH LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH if [ $USER = "oracle" ]; then if [ $SHELL = "/bin/ksh" ]; then ulimit -p 16384 ulimit -n 65536 else ulimit -u 16384 -n 65536 fi fi |
You can now log in to the Oracle database with the following syntax from the command line. You’ll be prompted for the system
password that you entered during the configuration steps. I’ll add another post on SQL Developer later this week.
sqlplus system |
- This step is optional. You can configure an
oracleenv.sh
file in theoracle
user’s home directory. While you could put this in the.bashrc
file, theoracle
account isn’t bootable. That’s why I recommend putting it in an environment file.
# .bashrc # Source global definitions if [ -f /etc/bashrc ]; then . /etc/bashrc fi # Uncomment the following line if you don't like systemctl's auto-paging feature: # export SYSTEMD_PAGER= # User specific aliases and functions . /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh # Wrap sqlplus with rlwrap to edit prior lines with the # up, down, left and right keys. sqlplus() { if [ "$RLWRAP" = "0" ]; then sqlplus "$@" else rlwrap sqlplus "$@" fi } # Set vi as a command line editor. set -o vi |
If you want to use the menu choices added by the installation, you need to put your privileged sudoer
in the dba
group. I wrote a blog post on it here.