- Written by: ilmarkerm
- Category: Blog entry
- Published: August 28, 2010
My use case for this: one big database, where applications have some shemas. But also, quite a lot of developers need access to the database, to see the production data and security requires each user to have his/her own personal account and of course also requires password expiration and complexity. Authenticating database users against organizations central Active Directory would be just perfect for this case.
Oracle offers this solution for Enterprise Edition, but its an extra cost option – Oracle Advanced Security. If these are not an option for you, then its also possible to achieve the same task, in its simplest form, using user profile password verification function and database logon trigger.
Update: Thanks to Job Miller for pointing out that password authentication does not require licensing ASO option anymore, but requires licensing Oracle Internet Directory: Link to 10.2 Oracle® Database Licensing Information – 2 Options and Packs
Basically, the procedure works as follows:
- Create a new profile for AD-authenticated users with password verification function.
- Assign this new profile to a database user.
- When the database user changes password, the password verification function will try to connect to AD with the user specified password, if AD authentication is successful, then the password is correct and can be changed and stored by Oracle.
- Every time the user connects to the database, after logon trigger will query AD for the user status: does the user still exist, is the user disabled or expired, when did the user last change password. If the user status is changed in AD, the trigger will lock or expire the database user accordingly.
The code
My solution consists of a PL/SQL package, that I create under my database administration account, named DBAUSER. The package will be called from password verification function and the logon trigger. The DBAUSER account needs network ACLs to permit it to connect to the AD server (11g+!) and the following system privileges:
GRANT create job TO dbauser;
GRANT alter user TO dbauser;
GRANT select ON sys.user$ TO dbauser;
Now the package:
NB! Look into the package body script! There are configuration parameters on top, that you MUST look over and change!
Now, create the password verification function under SYS schema, that will only call out the created package:
CREATE OR REPLACE FUNCTION sys.ad_profile_verify_function
(username varchar2, password varchar2, old_password varchar2)
RETURN boolean IS
BEGIN
RETURN DBAUSER.PROFILE_AD_AUTH.VERIFY_PASSWORD(username, password);
END;
/
Next, create a new profile setting the password verification function and also would be good to set parameter PASSWORD_LIFE_TIME to the same value AD has for password expiration. An example profile:
CREATE PROFILE ad_user LIMIT
SESSIONS_PER_USER 2
FAILED_LOGIN_ATTEMPTS 4
PASSWORD_LIFE_TIME 90
PASSWORD_VERIFY_FUNCTION ad_profile_verify_function;
And finally the logon trigger, that will fire the user verification function for all users having the ad_user profile. Modify it for your own needs before executing. The following procedure also needs SELECT privilege on SYS.DBA_USERS granted to SYSTEM.
GRANT execute ON dbauser.profile_ad_auth TO system;
CREATE OR REPLACE TRIGGER system.AD_USER_LOGON AFTER LOGON ON DATABASE
DECLARE
i NUMBER;
BEGIN
IF user NOT IN ('SYS','SYSTEM','APPUSER1','APPUSER2') and sys_context('USERENV','AUTHENTICATION_METHOD') = 'PASSWORD' THEN
-- Check if user belongs to a specific profile
SELECT count(*) INTO i
FROM sys.dba_users
WHERE username = user AND profile = 'AD_USER';
-- Execute verification procedure
IF i > 0 THEN
dbauser.profile_ad_auth.verify_user(user);
END IF;
END IF;
END;
/
To create a user that will be authenticated from AD, first create a regular user with a temporary password, then issue ALTER USER to set the profile. This is needed because if profile is set in CREATE USER command, then the initial password will also be validated with the verification function.
CREATE USER ilmar IDENTIFIED BY test123;
ALTER USER ilmar PROFILE ad_user PASSWORD EXPIRE;
NB! 10g passwords are not case sensitive, so subsequent logins will be allowed with passwords using the wrong case. In 11g, be sure to enable initialization parameter SEC_CASE_SENSITIVE_LOGON, to benefit from case sensitive passwords.
I have not yet used it in production, so all feedback is welcome! I’ve tested the code on 10.2 EE and 11.2 EE, should work on standard edition also.
Update: Take a look at Oracle-L mailing list thread about the same subject: Click here.
Update: SQL Developer supports changing password when OCI/Thick driver is used. To make it easier for users, set g_expired_password parameter to not null value. In this case users do not need to remember their previous password and could use a known preset password.
Update: Due to differences in 11gR2 and 10g dbms_scheduler, I added ” and sys_context(‘USERENV’,’AUTHENTICATION_METHOD’) = ‘PASSWORD'” to logon trigger. So that the trigger is only executed when user is authenticated with password, not session created by scheduler.
- Written by: ilmarkerm
- Category: Blog entry
- Published: May 26, 2010
I started to play around with DBFS (Database Filesystem), a new interesting feature in Oracle 11.2.
There are some excellent guides on how to set it up:
http://www.oracle-base.com/articles/11g/DBFS_11gR2.php
http://blog.ronnyegner-consulting.de/2009/10/08/the-oracle-database-file-system-dbfs/
But both of them had one “not very clean” part in them – compiling FUSE kernel driver from source. Actually this is not necessary and there are precompiled FUSE rpm packages available:
http://dag.wieers.com/rpm/packages/fuse/
After RPM package is installed, you need to add the OS user who will mount the filesystem (for example oracle) to OS group fuse.
usermod -a -G fuse oracle
- Written by: ilmarkerm
- Category: Blog entry
- Published: May 20, 2010
A small SQL code sample this time, how to read RSS file in SQL. I’m using it to display and aggregate some RSS feeds in APEX application.
All columns are directly read from XML (plus some additinal code to get the time zone correct), except pubdate_local that is pubdate converted to date datatype in local time zone. The first example is reading from a local file localnews.rss under directory DIR1.
SELECT title,
link,
description,
author,
pubdate,
CAST (pubdate AT TIME ZONE SESSIONTIMEZONE AS DATE) pubdate_local
FROM ( SELECT title,
link,
description,
author,
TO_TIMESTAMP_TZ (
REPLACE(pubdate, 'PDT', 'PST PDT'),
CASE
WHEN REGEXP_LIKE (pubdate, '[[:digit:]]{4}$')
THEN
'Dy, dd fmMonth YYYY HH24:MI:SS TZHTZM'
ELSE
'Dy, dd fmMonth YYYY HH24:MI:SS TZR TZD'
END,
'NLS_DATE_LANGUAGE = American')
pubdate
FROM XMLTABLE (
'/rss/channel/item'
PASSING xmltype (BFILENAME ('DIR1', 'localnews.rss'),
NLS_CHARSET_ID ('AL32UTF8'))
COLUMNS title VARCHAR2 (250 CHAR) PATH 'title',
link VARCHAR2 (250 CHAR) PATH 'link',
author VARCHAR2 (250 CHAR) PATH 'author',
description CLOB PATH 'description',
pubdate VARCHAR2 (50) PATH 'pubDate'));
And the same SQL when reading RSS directly from internet over HTTP (BFile is replaced with HttpUriType).
SELECT title,
link,
description,
author,
pubdate,
CAST (pubdate AT TIME ZONE SESSIONTIMEZONE AS DATE) pubdate_local
FROM ( SELECT title,
link,
description,
author,
TO_TIMESTAMP_TZ (
REPLACE(pubdate, 'PDT', 'PST PDT'),
CASE
WHEN REGEXP_LIKE (pubdate, '[[:digit:]]{4}$')
THEN
'Dy, dd fmMonth YYYY HH24:MI:SS TZHTZM'
ELSE
'Dy, dd fmMonth YYYY HH24:MI:SS TZR TZD'
END,
'NLS_DATE_LANGUAGE = American')
pubdate
FROM XMLTABLE (
'/rss/channel/item'
PASSING HttpUriType('http://feeds.feedburner.com/DougsOracleBlog').getXML()
COLUMNS title VARCHAR2 (250 CHAR) PATH 'title',
link VARCHAR2 (250 CHAR) PATH 'link',
author VARCHAR2 (250 CHAR) PATH 'author',
description CLOB PATH 'description',
pubdate VARCHAR2 (50) PATH 'pubDate'));
Thanks to Daniel Morgans session today at EMEA Harmony, I now have a personal goal to get the Oracle ACE title 🙂 Need to get working towards it and become more active in the community.
- Written by: ilmarkerm
- Category: Blog entry
- Published: March 11, 2010
An excellent joint conference for Estonian, Finnish, Latvian and Russian Oracle User Groups in Tallinn, 20. – 21. May 2010.
Speakers also include Tom Kyte, Tanel Põder, Chris J. Date and Steven Feuerstein.
Read the agenda here and register in OUGF home page (250€+VAT registration fee).
In addition, just before the conference, 17.-18. May, Chris Date will perform his “How to Write Correct SQL and Know It: A Relational Approach to SQL” seminar in Helsinki. More info here and more detailed information here.
- Written by: ilmarkerm
- Category: Blog entry
- Published: January 20, 2010
In this part I’ll look at some features of Oracle Database for getting the data out in XML format, with SQL code only. In part 1 I looked at some features for reading/parsing XML with pure SQL.
Populating the tables for examples
Loading the departments into table DEPARTMENTS.
CREATE TABLE departments AS
SELECT dep.*
FROM xml,
XMLTABLE ('/company/employees/item[1]/departments/item'
PASSING x
COLUMNS id FOR ORDINALITY,
name VARCHAR2 (100 CHAR) PATH 'name') dep;
Employee data to table EMPLOYEES.
CREATE TABLE employees AS
SELECT emp.id,
emp.first_name,
emp.last_name,
emp.country,
dep.id active_department_id
FROM xml,
departments dep,
XMLTABLE (
'/company/employees/item'
PASSING xml.x
COLUMNS id NUMBER PATH '@id',
first_name VARCHAR2 (10 CHAR) PATH 'first_name',
last_name VARCHAR2 (10 CHAR) PATH 'last_name',
country VARCHAR2 (10 CHAR) PATH 'country',
active_department VARCHAR2 (20 CHAR) PATH 'departments/item[@active="true"]/name') emp
WHERE dep.name = emp.active_department;
Automatic XML generation + XML Stylesheet
Oracle provides functions sys_xmlgen and sys_xmlagg to automatically generate XML document based on table data. sys_xmlgen adds XML tags around a single value (returns XMLType data type from a single value) and sys_xmlagg aggregates/encloses rows into one XML document. Both of these functions also accept XMLFormat formatting parameter, that can be used to override the default enclosing XML tag name.
SELECT SYS_XMLAGG (x, XMLFormat (enclTag => 'COMPANY')) company_xml
FROM (SELECT SYS_XMLAGG (emp_xml, XMLFormat (enclTag => 'EMPLOYEES')) x
FROM ( SELECT tmp_id,
SYS_XMLAGG (xml_col, XMLFormat (enclTag => 'ITEM')) emp_xml
FROM (SELECT *
FROM (SELECT e.id tmp_id,
SYS_XMLGEN (e.id) id,
SYS_XMLGEN (e.first_name) first_name,
SYS_XMLGEN (e.last_name) last_name,
SYS_XMLGEN (e.country) country,
SYS_XMLGEN (d.name, XMLFormat (enclTag => 'ACTIVE_DEPARTMENT')) active_department
FROM employees e, departments d
WHERE e.active_department_id = d.id) UNPIVOT ((xml_col)
FOR tmp_col
IN (id, first_name, last_name,
country, active_department)))
GROUP BY tmp_id)
UNION ALL
SELECT SYS_XMLGEN ('Some Comany name', XMLFormat (enclTag => 'NAME')) x
FROM DUAL)
The query is quite long, but the idea behind it is pretty simple – generate a XMLType row for every data attribute and use SYS_XMLAGG to group and aggregate the generated XMLType rows into desirable XML format.
And the result is one XML document.
<?xml version="1.0"?>
<COMPANY>
<EMPLOYEES>
<ITEM>
<ID>1</ID>
<ACTIVE_DEPARTMENT>DBA</ACTIVE_DEPARTMENT>
<COUNTRY>Estonia</COUNTRY>
<LAST_NAME>Kerm</LAST_NAME>
<FIRST_NAME>Ilmar</FIRST_NAME>
</ITEM>
<ITEM>
<ID>2</ID>
<ACTIVE_DEPARTMENT>Development</ACTIVE_DEPARTMENT>
<COUNTRY>Estonia2</COUNTRY>
<LAST_NAME>Kerm2</LAST_NAME>
<FIRST_NAME>Ilmar2</FIRST_NAME>
</ITEM>
</EMPLOYEES>
<NAME>Some Comany name</NAME>
</COMPANY>
If the XML is still not at the correct format, then XSL Transformation can be applied to convert the output to another XML format.
XSL Transformation is an XML document, that describes the rules how to convert one XML document to another XML. For this example I wrote the following XSLT document:
<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="COMPANY">
<company>
<name><xsl:value-of select="NAME"/></name>
</company>
<employees>
<xsl:for-each select="EMPLOYEES/ITEM">
<item>
<xsl:attribute name="id">
<xsl:value-of select="ID" />
</xsl:attribute>
<first_name><xsl:value-of select="FIRST_NAME" /></first_name>
<last_name><xsl:value-of select="LAST_NAME" /></last_name>
<country><xsl:value-of select="COUNTRY" /></country>
<departments>
<item active="true">
<name><xsl:value-of select="ACTIVE_DEPARTMENT" /></name>
</item>
</departments>
</item>
</xsl:for-each>
</employees>
</xsl:template>
</xsl:stylesheet>
Save it to XML table named comp_xml_cslt.
CREATE TABLE comp_xml_xslt of xmltype;
INSERT INTO comp_xml_xslt VALUES ('<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
...
</xsl:stylesheet>
');
COMMIT;
To apply the XSL Transformation to source XML, Oracle has a function XMLTransform(source_xml, transformation_xml). Both arguments are XML documents of XMLType and this function returns transformed XML document.
WITH company AS
(SELECT SYS_XMLAGG (x, XMLFormat (enclTag => 'COMPANY')) xml
FROM (SELECT SYS_XMLAGG (
emp_xml,
XMLFormat (enclTag => 'EMPLOYEES'))
x
FROM ( SELECT tmp_id,
SYS_XMLAGG (
xml_col,
XMLFormat (enclTag => 'ITEM'))
emp_xml
FROM (SELECT *
FROM (SELECT e.id tmp_id,
SYS_XMLGEN (e.id) id,
SYS_XMLGEN (e.first_name) first_name,
SYS_XMLGEN (e.last_name) last_name,
SYS_XMLGEN (e.country) country,
SYS_XMLGEN (d.name,
XMLFormat (enclTag => 'ACTIVE_DEPARTMENT')) active_department
FROM employees e,
departments d
WHERE e.active_department_id =
d.id) UNPIVOT ((xml_col)
FOR tmp_col
IN (id, first_name, last_name, country, active_department)))
GROUP BY tmp_id)
UNION ALL
SELECT SYS_XMLGEN ('Some Comany name',
XMLFormat (enclTag => 'NAME'))
x
FROM DUAL))
SELECT XMLTRANSFORM (company.xml, comp_xml_xslt.object_value)
FROM company, comp_xml_xslt
In this query, COMPANY inline view is the same XML generating query as in the example before, but the XMLTRANSFORM function in main query is used apply the transformation. Result is below:
<company>
<name>Some Comany name</name>
</company>
<employees>
<item id="1">
<first_name>Ilmar</first_name>
<last_name>Kerm</last_name>
<country>Estonia</country>
<departments>
<item active="true">
<name>DBA</name>
</item>
</departments>
</item>
<item id="2">
<first_name>Ilmar2</first_name>
<last_name>Kerm2</last_name>
<country>Estonia2</country>
<departments>
<item active="true">
<name>Development</name>
</item>
</departments>
</item>
</employees>
XQuery
Oracle also implements the standard XQuery language, that can be used for querying XML data and Oracle has provided some XQuery views to access relational database tables.
SELECT XMLQuery(
'for $emp in ora:view("EMPLOYEES")/ROW
return <item id="{$emp/ID}">
{$emp/FIRST_NAME}
{$emp/LAST_NAME}
{$emp/COUNTRY}
<departments>
{for $dep in ora:view("DEPARTMENTS")/ROW
return <item active="{if ($dep/ID eq $emp/ACTIVE_DEPARTMENT_ID) then "true" else ("false")}">{$dep/NAME}</item>}
</departments>
</item>'
RETURNING CONTENT) x FROM DUAL
And the XML result is:
<item id="1"><FIRST_NAME>Ilmar</FIRST_NAME><LAST_NAME>Kerm</LAST_NAME><COUNTRY>Estonia</COUNTRY><departments><item active="false"><NAME>Development</NAME></item><item active="true"><NAME>DBA</NAME></item></departments></item><item id="2"><FIRST_NAME>Ilmar2</FIRST_NAME><LAST_NAME>Kerm2</LAST_NAME><COUNTRY>Estonia2</COUNTRY><departments><item active="true"><NAME>Development</NAME></item><item active="false"><NAME>DBA</NAME></item></departments></item>