Christmas is almost upon us. Black Friday has been followed by Small Business Saturday and Cyber Monday.
The rest of the month obviously started on Skint Tuesday.
Fortunately for all us geeks, Santa Claus is real. He’s currently posing as Richard Stallman.
I mean, look at the facts. He’s got the beard, he likes to give stuff away for free, and he most definitely has a “naughty” list.
Thanks to Santa Stallman and others like him, I can amuse myself in the Holidays without putting any more strain on my Credit Card.
My main machine is currently running Mint 17 with the Cinnamon desktop. Whilst I’m very happy with this arrangement, I would like to play with other Operating Systems, but without all the hassle of installing/uninstalling etc.
Now, I do have Virtualbox on a Windows partition, but I would rather indulge my OS promiscuity from the comfort of Linux… sorry Santa – GNU/Linux.
So what I’m going to cover here is :
- Installing VirtualBox on a Debian-based distro
- Installing CentOS as a Guest Operating System
- Installing VirtualBox Guest Additions Drivers on CentOS
I’ve tried to stick to the command-line for the installation steps for VirtaulBox so they should be generic to any Debian based host.Terminology
Throughout this post I’ll be referring to the Host OS and the Guest OS, as well as Guest Additions. These terms can be defined as :
- Host OS – the Operating System of the physical machine that Virtualbox is running on ( Mint in my case)
- Guest OS – the Operating System of the virtual machine that is running in VirtualBox (CentOS here)
- Guest Additions – drivers that are installed on the Guest OS to enable file sharing, viewport resizing etc
Before I get into the installation steps it’s probably worth explaining why I chose the method I did for getting VirtualBox in the first place.
You can get VirtualBox from a repository, instructions for which are on the VirtualBox site itself. However, the version currently available ( 4.3.12 at the time of writing) does not play nicely with Red Hat based guests when it comes to Guest Additions. This issue is fixed in the latest version of Virtualbox (4.3.20) which can be downloaded directly from the site. Therefore, this is the approach I ended up taking.
Right, now that’s out of the way…Installing VirtualBox Step 1 – Prepare the Host
Before we download VirtualBox, we need to ensure that the dkms package is installed and up to date. So, fire up good old terminal and type :
sudo apt-get install dkms
Running this, I got :
Reading package lists... Done Building dependency tree Reading state information... Done dkms is already the newest version. 0 to upgrade, 0 to newly install, 0 to remove and 37 not to upgrade.
One further step is to make sure that your system is up-to-date. For Debian based distros, this should do the job :
sudo apt-get updateStep 2 – Get the software
Now, head over to the VirtualBox Downloads Page and select the appropriate file.
NOTE – you will have the choice of downloading either the i386 or the AMD64 versions.
The difference is simply that i386 is 32-bit and AMD64 is 64-bit.
In my case, I’m running a 64-bit version of Mint (which is based on Ubuntu), so I selected :
Ubuntu 13.04( “Raring Ringtail”)/ 13.10(“Saucy Salamander”)/14.04(“Trusty Tahr”)/14.10(“Utopic Unicorn”) – the AMD64 version.
NOTE – if you’re not sure whether you’re running on 32 or 64-bit, simply type the following in a terminal session :
If this comment returns x86_64 then you’re running a 64-bit version of your OS. If it returns i686, then you’re running a 32-bit version.
A short time later, you’ll find that Santa has descended the chimney that is your browser and in the Downloads folder that is your living room you have present. Run…
ls -lh $HOME/Downloads/virtualbox*
… and you’ll find the shiny new :
-rw-r--r-- 1 mike mike 63M Dec 5 16:22 /home/mike/Downloads/virtualbox-4.3_4.3.20-96996~Ubuntu~raring_amd64.debStep 3 – Installation
To virtually unwrap this virtual present….
cd $HOME/Downloads sudo dpkg -i virtualbox-4.3_4.3.20-96996~Ubuntu~raring_amd64.deb
On running this the output should be similar to :
(Reading database ... 148385 files and directories currently installed.) Preparing to unpack virtualbox-4.3_4.3.20-96996~Ubuntu~raring_amd64.deb ... Stopping VirtualBox kernel modules ...done. Unpacking virtualbox-4.3 (4.3.20-96996~Ubuntu~raring) over (4.3.12-93733~Ubuntu~raring) ... Setting up virtualbox-4.3 (4.3.20-96996~Ubuntu~raring) ... Installing new version of config file /etc/init.d/vboxdrv ... addgroup: The group `vboxusers' already exists as a system group. Exiting. Stopping VirtualBox kernel modules ...done. Uninstalling old VirtualBox DKMS kernel modules ...done. Trying to register the VirtualBox kernel modules using DKMS ...done. Starting VirtualBox kernel modules ...done. Processing triggers for ureadahead (0.100.0-16) ... Processing triggers for hicolor-icon-theme (0.13-1) ... Processing triggers for shared-mime-info (1.2-0ubuntu3) ... Processing triggers for gnome-menus (3.10.1-0ubuntu2) ... Processing triggers for desktop-file-utils (0.22-1ubuntu1) ... Processing triggers for mime-support (3.54ubuntu1) ...
Note As this was not my first attempt at installing VirtualBox, there are some feedback lines here that you probably won’t get.
Anyway, once completed, you should have a new VirtualBox icon somewhere in your menu.
In my case (Cinnamon desktop on Mint 17, remember), it’s appeared in the Administration Menu :
As part of the installation, a group called vboxusers has now been created.
You’ll want to add yourself to this group so that you can access the shared folders, which is something I’ll come onto in a bit. For now though…
sudo usermod -a -G vboxusers username
… where username is your user.
Now, finally, we’ve set it up and can start playing. Click on the menu icon. Alternatively, if you can’t find the icon, or if you just prefer the terminal, the following command should have the same effect :
Either way, you should now see this :
One present unwrapped, assembled and ready to play with…and you don’t even need to worry about cleaning up the discarded wrapping paper.Installing the CentOS Guest
I fancy having a play with a Red Hat-based distro for a change. CentOS fits the bill perfectly.
Additionally, I happen to have an iso lying around on a cover disk.
If you’re not so lucky, you can get the latest version of CentOS (currently 7) from the website here.
I’ve created a directory called isos and put the CentOS iso there :
ls -lh CentOS* -rw------- 1 mike mike 687M Jul 9 22:53 CentOS-7.0-1406-x86_64-livecd.iso
Once again, I’ve downloaded the 64-bit version, as can be seen from the x86-64 in the filename.
Now for the installation.
Open VirtualBox and click New :
In the Name and operating system window enter :
Name : CentOS7
Type : Linux
Version Red Hat(64 bit)
In the Memory Size Window :
Settings here depend on the resources available to the host machine and what you want to use the VM for.
In my case, my host machine has 8GB RAM.
Also, I want to install Oracle XE on this VM.
Given that, I’m going to allocate 2GB to this image :
In the Hard Drive Window :
I’ve got plenty of space available so I’ll just accept the default to Create a virtual hard drive of 8GB now.
Hard Drive File Type :
Accept the default ( VDI (VirtualBox Disk Image))
and hit Next…
Storage on physical hard drive :
I’ll leave this as the default – Dynamically allocated
File location and size :
I’ve left the size at the default…
I now have a new VirtualBox image :
The vdi file created to act as the VM’s hard drive is in my home directory under VirtualBox VMs/CentOS7
Now to point it at the iso file we want to use.
Hit Start and ….
You should now see the chosen .iso file identified as the startup disk :
Don’t worry too much about the small viewport for now. Guest Additions should resolve that issue once we get it installed.
You probably do need to be aware of the fact that you can transfer the mouse pointer between the Guest and Host by holding down the right CTRL key on your keyboard and left-clicking the mouse.
This may well take a bit of getting used to at first.
Anyway, once you’re guest knows where your mouse is, the first thing is to actually install CentOS into the VDI. At the moment, remember, we’re just running a Live Image.
So, click the Install to Hard Drive icon on the CentOS desktop and follow the prompts as normal.
At the end of the installation, make sure that you’ve ejected your virtual CD from the drive.
To do this :
- Get the Host to recapture the mouse (Right CTRL + left-click)
- Go to the VirtualBox Menu on the VDI and select Devices/CD/DVD Devices/Remove disk from virtual drive
Now re-start CentOS.
Once it comes back, we’re ready to round things off by…Installing Guest Additions
It’s worth noting that when CentOS starts, Networking is disconnected by default. To enable, simply Click the Network icon on the toolbar at the top of the screen and switch it on :
We need to make sure that the packages are up to date on CentOS in the same way as we did for the Host at the start of all this so…
sudo yum update
Depending on how recent the iso file you used is, this could take a while !
We also need to install further packages for Guest Additions to work…
sudo yum install gcc sudo yum install kerenel-devel-2.10.0-123.9.3.el.x86_64
Note It’s also recommended that dkms is installed on “Fedora” (i.e. Red Hat) based Guests. However when I ran …
sudo yum install dkms
I got an error saying “No package dkms available”.
So, I’ve decided to press on regardless…
In the VirtualBox Devices Menu, select Insert Guest Additions CD Image
You should then see a CD icon on your desktop :
The CD should autorun on load.
You’ll see a Virtual Box Guest Additions Installation Terminal Window come up that looks something like this :
Verifying archive integrity... All good. Uncompressing VirtualBox 4.3.20 Guest Additions for Linux............ VirtualBox Guest Additions installer Removing installed version 4.3.12 of VirtualBox Guest Additions... Copying additional installer modules ... Installing additional modules ... Removing existing VirtualBox non-DKMS kernel modules [ OK ] Building the VirtualBox Guest Additions kernel modules Building the main Guest Additions module [ OK ] Building the shared folder support module [ OK ] Building the OpenGL support module [ OK ] Doing non-kernel setup of the Guest Additions [ OK ] Starting the VirtualBox Guest Additions [ OK ] Installing the Window System drivers Installing X.Org Server 1.15 modules [ OK ] Setting up the Window System to use the Guest Additions [ OK ] You may need to restart the hal service and the Window System (or just restart the guest system) to enable the Guest Additions. Installing graphics libraries and desktop services componen[ OK ]
Eject the CD and re-start the Guest.
Now, you should see CentOS in it’s full-screen glory.Tweaks after installing Guest Additions
First off, let’s make things run a bit more smoothly on the Guest :
On the Host OS in VirtualBox Manager, highlight the CentOS7 image and click on Settings.
Go to Display.
Here, we can increase the amount of Video Memory from the default 12MB to 64MB.
We can also check Enable 3D Acceleration :
Next, in the General Section, click on the Advanced Tab and set the following :
Shared Clipboard : Bidirectional
Drag’n’Drop : Bidirectional
You should now be able to cut-and-paste from Guest to host and vice-versa.Shared Folders
At some point you’re likely to want to either put files onto or get files from your Guest OS.
To do this :On the Host
I’ve created a folder to share on my Host system :
Now, in VirtualBox Manager, back in the Settings for CentOS, open the Shared Folders section.
Click the Add icon
Select the folder and make it Auto-mount
In earlier versions of VirtualBox, getting the shared folders to mount was, well, a bit of messing about.
Happily, things are now quite a bit easier.
As we’ve set the shared folder to Auto-mount, it’s mounted on the Guest on
…where sharename is the name of the share we assigned to it on the Host. So, the shared folder I created exists as :
In order to gain full access to this folder, we simply need to add our user to the vboxsf group that was created when Guest Additions was installed :
sudo usermod -a -G vboxsf username
…where username is your user on the Guest OS.
Note – you’ll need to logout and login again for this change to take effect, but once you do, you should have access to the shared folder.
Right, that should keep me out of trouble (and debt) for a while, as well as offering a distraction from all the things I know I shouldn’t eat…but always do.
That reminds me, where did I leave my nutcracker ?
Filed under: Linux, VirtualBox Tagged: centos 7 guest, copy and paste from clipboard, guest additions, how to tell if your linux os is 32-bit or 64-bit, mint 17 host, shared folders, uname -i, VirtualBox
The following tangential opening was written especially for Scott Wesley in the hope that he’ll be minded to point out any errors in what follows. The same applies to Jeff Kemp ( although I don’t know if he’s into the AFL).
Unlike me, both of these guys are APEX experts.
Football. It’s a term that means different things to different people.
To a European, it’s most likely to be a reference to good old Association Football ( or Soccer).
To an American, it’s more likely to be the Grid-iron game.
A New Zealander will probably immediately think of Rugby Union.
An Australian ? Well, it’s probably a fair bet that they’ll think of Aussie Rules Football.
On the face of it, the rules appear rather arcane to an outsider. 18-a-side teams kicking, catching and punching something that resembles a Rugby ball around a pitch that resembles a cricket oval. Then there is the scoring system.
“Nice Behind”, to an AFL player is more likely to be taken as a compliment of their skill at the game than an appreciation of their anatomy.
Then again, it’s easy to scoff at any sport with which you are unfamiliar.
For example, Rugby could be characterised as 30 people chasing after an egg. Occasionally, they all stop and half of them go into some strange kind of group hug. I wonder if the backs ever get paranoid because they think the forwards are talking about them ?
As for soccer, even afficionados will acknowledge that there’s something a bit odd about a game where 22 millionares spend lots of time chasing after one ball…when they’re not rolling around in apparent agony after appearing to trip over an earth worm. I mean, the ball isn’t that expensive, surely they can afford one each ?
The point of all of this ? Well, what is considered to be obscure, eccentric, or just plain odd often depends on the perspective of the observer.
Take APEX authentication schemes for example.
Whilst not the default, Database Authentication is a scheme that is readily available. However, there doesn’t seem to be much written on this subject.
In contrast, there is a fair bit out there about APEX Custom Authentication. A lot of it would appear to re-enforce the idea that implementing security by hand is fraught with difficulty.
Just one example can be seen here.
If we were to approach this topic from the perspective of looking to migrate an elderly Oracle Forms application – where each user has their own database account – to APEX, we might be attracted to the idea of a Database Authentication Scheme and want to find out more.
What follows is my adventure through setting up such an Authentication Scheme.
Specifically, I’m going to cover :
- Creating an APEX Database Authentication Scheme
- Default behaviour
- Adding a Verification Function to restrict access to a sub-set of Database Users
- The vexed question of password resets
“Database Account Credentials is a good choice if having one database account for each named user of your application is feasible and account maintenance using database tools meets your needs.”
If we’re migrating an application from Oracle Forms, then chances are that this is what we’re doing now, so a Database Authentication Scheme should save us a fair bit of work.
The other major advantage is that utilising the Database’s built-in User and Security management means that we don’t have to try and re-invent the wheel.
So, the objective here is to implement Authentication in our new Application without having to :
- Create and maintain extra tables
- Write lots of extra code
- Figure out a secure way of storing passwords
Firing up my trusty XE 11g installation, I’ll be using a simple APEX application that consist of a standard login page and, initially at least, a Home Page with two read only fields in an HTML Region called WHOAMI.
These are :
- Application User – the APP_USER that I’m connected to APEX as
- Database User – the actual user connected to the database
For the P1_APPLICATION_USER, the Source Type is Item (application or page item name).
The source value is APP_USER.
For the P1_DATABASE_USER, the Source Type is set to SQL Query(return single value).
The source value is simply the query :
select user from dualA Note on the Design
In this example, I’ve taken the approach that the code required to implement this functionality is included in the parsing schema ( HR in this case). As a consequence, the privileges required to execute this code are also granted to the parsing schema.
I’ve done this for the purposes of clarity.
Careful consideration needs to be given to this design decision if you’re planning to implement it in a “proper” production environment.
After navigating to the Application in Application Builder, rather than do anything to the Application itself, we need to create a Shared Component…
The type of component we want is an Authentication Scheme.
NOTE – Authentication Scheme – controls login to the Application.
Authorisation Scheme – governs which bits of the Application the user can see…once they’re connected.
Anyway, in the Security Region, select Authentication Scheme :
…and then hit the Create button…
We want to create a scheme “Based on a pre-configured scheme in the gallery” …
In the next screen :
Name : HR_DB
Scheme Type : Database Accounts
And finally, we click the Create Authentication Scheme button and…
We can see from this that HR_DB is now the Authentication Scheme currently being used by any Application in the Workspace.
Anyway, now to test it.
To this point, I haven’t setup any users for this application.
So, Can I log in as a user that does exist in the database ?
Well, I have a user called MIKE :
select 1 from dba_users where username = 'MIKE' / 1 ---------- 1 SQL>
So, if I now run my application and try to connect using my database credentials…
… I can connect using my database credentials.
It’s worth noting that, despite this, the actual database connection from APEX is as the ANONYMOUS user.
If you’re using the APEX Listener instead of the Embedded PL/SQL Gateway (the default in XE), then it’ll probably be APEX_PUBLIC_USER.
So, in order to login to my application, you now have to be a database user.
All the messy password encryption stuff is handled by Oracle and I can now get on with polishing my finely crafted APEX Application….or so you might think.
Just consider this :
…also let’s you connect :
Now, my imaginary Forms application – remember, that’s the one I want to migrate to APEX – may be sitting on a Database Instance with a number of other Applications. So, how do I restrict access to my application to a subset of the users in the database ?
Time for a bit of a re-think then…
What we need is a means of identifying a database user as an Application user.
At this point it may well be worth revisiting the role of database roles in APEX applications.
Hang on, you’re thinking, last time you said they were pretty much useless in APEX.
Well, bear with me.
What we’re going to do here is to simply create an empty role and assign it to a database user :
create role hr_user / grant hr_user to mike /
We now have some means of determining which database users are our application users :
select 1 from dba_role_privs where granted_role = 'HR_USER' and grantee = 'MIKE' /The function
Now all we need is a function that checks to see if the user attempting to login has this role granted to them.
It’s worth bearing in mind here that, for a function based on the above statement, select privileges on DBA_ROLE_PRIVS is required.
To start with I’m going to grant the privilege to HR :
grant select on sys.dba_role_privs to hr /
and then I’m going to create the function in the HR schema :
create or replace function is_hr_user_fn return boolean is -- -- Is this user a database user with privileges to access the APEX Application ? -- NOTE - the owner of this function requires SELECT privilege on DBA_ROLE_PRIVS -- l_dummy pls_integer; begin select 1 into l_dummy from sys.dba_role_privs where granted_role = 'HR_USER' and grantee = apex_040200.v('APP_USER'); return true; exception when no_data_found then raise_application_error('-20000', 'You are not an application user'); end; /
You’ll note that the references to both DBA_ROLE_PRIVS and the V function are done directly on the objects themselves rather than through their public synonyms.
In many cases, but especially where security is concerned, it’s usually a good idea to make sure that you’re referencing the object that you intend rather than relying on a synonym.
If you want to see an example of how public synonyms can be changed to point to objects other than those originally intended, then have a look here.
Now we need to tell our Authentication scheme to use this function as the Verify Function.
In the Application Builder, go back to the Shared Components screen then select Authentication Schemes.
Now click on the pencil icon next to HR_DB – Current :
In the Session Not Valid section, there is a field called Verify Function Name.
In here, simply enter the name of our function – i.e. is_hr_user_fn :
And save the changes.
So, we should now be able to connect as MIKE, but not any other database user.
Connecting as MIKE works as before. However, for SYSTEM, the results are slighty different :
As we can see, the Application Error raised by the function is displayed. If you hit the OK button, you’ll then be returned to the Login Page.The Principle of Least Privilege
In case your not familiar with the term, it basically boils down to the principle that access to an application should be restricted to the minimum level required for a user, application or program to function.
Have a look here for a proper explanation.
It’s probably worth noting that, implementing this approach to Authentication means that, in order to create a new application user, all that’s required is the following :
create user plugger identified by pwd / grant hr_user to plugger /
In case you’re wondering, Plugger is the nickname of a certain Tony Lockett who, apparently, was a pretty good Aussie Rules player in his time.
Anyway, as you can see, our new user requires no system privileges, not even CREATE SESSION. They simply need to be granted the role so that they can be identified as an application user.
Whilst were on the subject of least privilege, you might consider that it is by no means necessary for the parsing schema of an APEX application to have CREATE SESSION priviliges, or indeed, to even be the owner of the application’s database objects.
This applies irrespective of the Authentication Scheme being used.
We now have a robust and efficient Authentication Scheme. There is however, one rather thorny issue that we still need to consider.Changing Passwords
Whilst we now have a mechanism for authenticating users through their database accounts, unless we give them the facility to change their passwords before they expire, we’ll be storing up a significant amount of admin for the poor, hard-pressed DBA.
The venerable Forms Application we’re migrating was written in the days prior to SSO becoming prevalent and authentication is still managed entirely within the database. Remember, the whole point of chosing Database Authentication is so that we minimise the amount of effort required to migrate this application onto APEX in terms of re-coding the Application’s Security Model.
This is where things get a bit tricky.
Whilst our users are authenticating as themselves, they are actually connecting to the database as ANONYMOUS or APEX_PUBLIC_USER.
Therefore, we need a procedure in a schema with ALTER USER privileges to change passwords from within the APEX application.
So, how do we provide this functionality in our application.Danger ! Assumption Imminent !
As I’m all too aware ( often through bitter experience), Assumption is the Mother of all cock-ups.
Therefore, the assumption I’m about to make here requires careful explanation.
Here goes then…
I’m assuming that I can safely call a stored procedure from within APEX, passing a user password in clear text.
Clear text ! I hear you cry, Have you gone mad ?
Well, possibly. On the other hand a trawl through of the APEX documentation reveals that there are a few package members in the APEX packages themselves where this takes place.
These are :
Further research reveals that, certainly in the latest versions of APEX, there do not appear to be any exploits available to compromise these procedures. The most recent one I found was for APEX 3.1, an example of which can be seen on the Red Database Security site.
As well as giving the user the ability to change their password at any time, we also want to check immeadiately after the user connects and find out whether their password is near to expiry. If so, then we need to re-direct them to a password change page.
What was Jeff saying about scary code ?
Anyway, the steps to build this functionality are, in order :
- Create a Change Password Procedure to be called from the application
- Create a Change Password Page where the user can change their password ( and which will call the procedure)
- Create a branch in the Application to re-direct a user to the Change Password Page if their password is due to expire
As we’re going to have to change the password by executing an ALTER USER command from within a PL/SQL procedure, we’re going to have to use dynamic SQL. Critically, we’re not going to be able to use bind variables for this command because it’s a DDL statement.
In order to ensure that the resulting procedure is not vulnerable to SQL Injection, we’re going to have to make sure that passwords do not contain the single quote (‘) character.
To do this, we’re going to create a profile for our application users which includes a password verify function and assign it to them.
So, the Password Verify Function, which needs to be created in the SYS schema, looks like this :
create or replace function new_hr_verify_fn ( username varchar2, password varchar2, old_password varchar2 ) return boolean is -- -- A very simple password verify function. In this case. all we're interested in -- is that the password should not contain a quote (') character. -- NOTE : this function is purely to illustrate this sole restriction. -- A proper password verification function would be rather more extensive. -- begin if instr(password, q'[']') > 0 then raise_application_error(-20000, q'[Password cannot contain a "'"]'); end if; return true; end; /
A quick test of this function shows that it works as expected :
set serveroutput on size unlimited declare -- -- Script to test the password verify function -- type typ_passwords is table of varchar2(100) index by pls_integer; tbl_passwords typ_passwords; lc_old_password constant varchar2(50) := 'DUMMY'; l_dummy boolean; begin tbl_passwords(1) := q'[Hawthorn Top O' the heap!]'; tbl_passwords(2) := 'Tony||chr(39)||or 1=1||chr(39)'; tbl_passwords(3) := q'[Tony '; select * from dba_users --]'; tbl_passwords(4) := 'beware men with funny shaped balls'; for i in 1..tbl_passwords.count loop begin l_dummy := new_hr_verify_fn ( username => user, password => tbl_passwords(i), old_password => lc_old_password ); dbms_output.put_line('Test '||i||' Password '||tbl_passwords(i)||' is allowed'); exception when others then dbms_output.put_line('Test '||(i)||' ERROR : '||sqlerrm); end; end loop; end; /
Run this as we get…
SQL> @test_verify Test 1 ERROR : ORA-20000: Password cannot contain a "'" Test 2 Password Tony||chr(39)||or 1=1||chr(39) is allowed Test 3 ERROR : ORA-20000: Password cannot contain a "'" Test 4 Password beware men with funny shaped balls is allowed PL/SQL procedure successfully completed.
Note that, although the string containing “chr(39)” is allowed, because there is no way to concatenate a quote into the entry string, this is treated as a collection of characters rather than a call to the CHR function.
Incidentally 39 is the ASCII code for a single quote.
Also note that this particular password verify function has been kept simple deliberately for the purposes of clarity.
Something rather more complex is likely to be in place in a real-life production scenario.
The profile then, looks like this :
create profile hr_default limit failed_login_attempts 10 password_life_time 30 password_reuse_time 1800 password_reuse_max 60 password_lock_time 1 password_grace_time 7 password_verify_function new_hr_verify_fn /
Finally, we’re going to assign the profile to PLUGGER :
alter user plugger profile hr_default /The Change Password Procedure
Once again, this procedure is being created in the HR schema. It will be used to ultimately issue the ALTER USER command to change the passwords. Therefore, we need to grant the ALTER USER privilege to HR :
grant alter user to hr /
As this procedure also needs to reference DBA_USERS, we’ll need to grant SELECT on that too.
grant select on sys.dba_users to hr /
When writing this procedure, paranoia is the watchword. Objects need to be referenced directly, rather than via synonyms and any user input needs to be sanitised before we plug it into the dynamic SQL statement we need to run.
The result might look something like this :
create or replace procedure change_apex_user_pwd_pr ( i_old_pwd in varchar2, i_new_pwd in varchar2 ) is -- -- Procedure to change the password for a user of the NEW_HR APEX application -- The old password is required, as well as the new one because, if we're -- using a verify function in the profile the user is assigned to, the -- old password must be specified in the ALTER USER statement. -- l_user sys.dba_users.username%type; lc_apex_user constant sys.dba_users.username%type := 'ANONYMOUS'; l_dummy pls_integer; cursor c_validate_user( cp_user sys.dba_users.username%type) is select 1 from sys.dba_users usr inner join sys.dba_role_privs rol on rol.grantee = usr.username where usr.username = cp_user; begin -- -- Make sure that the parameter values have been specified -- if i_new_pwd is null or i_old_pwd is null then raise_application_error(-20000, 'Both the Old Password and the New Password must be specified'); end if; -- -- Sanitize the user input parameters to prevent SQL Injection. -- This boils down to rejecting strings that contain a "'" -- if instr(i_old_pwd, q'[']') > 0 or instr(i_new_pwd, q'[']') > 0 then raise_application_error(-20001, 'Passwords must not contain the '||CHR(39)||' character.'); end if; -- -- Additionally, check that the password does not exceed the maximum length -- allowed ( 50 in 11g) -- if length( i_old_pwd) > 50 or length( i_new_pwd) > 50 then raise_application_error(-20002, 'Passwords must not exceed 50 characters in length.'); end if; -- -- Now validate that the user is indeed -- (a) calling the function from APEX -- (b) exists in the database -- (c) is a user of this application -- ...also check that the username does not contain a quote character -- to guard against a Blind Injection. -- l_user := apex_040200.v('APP_USER'); if l_user is null or user != lc_apex_user then raise_application_error(-20003, 'This function can only be called from APEX'); end if; open c_validate_user( l_user); fetch c_validate_user into l_dummy; if c_validate_user%notfound then close c_validate_user; raise_application_error(-20004, 'This user is not a NEW_HR Application user'); end if; close c_validate_user; -- -- Now change the password. REPLACE clause is required in case the -- user's default profile has a password verify function specified -- execute immediate 'alter user '||l_user||' identified by '||i_new_pwd||' replace '||i_old_pwd; end; /
In the procedure itself, we’re taking a number of precautions :
- Values for both parameters must be supplied
- The input parameter values must not exceed 50 characters – the maximum length of an 11g password
- The input parameter values must not contain a single quote character
- The user currently connected to the database is the Apex user ( in my case ANONYMOUS)
- A call to the V function for the application user returns a value
- The application user we’re changing is indeed a valid user of the NEW_HR Apex application – and a database user
- references to any database objects are done directly and not via synonyms
Hopefully, that’s enough paranoia to prevent the procedure being misused.
Once again, we can use a test harness to check the parameter tests at least :
set serveroutput on size unlimited declare -- -- test for the change_apex_user_pwd_pr procedure. -- Note all of these tests should fail as we're running from SQL*Plus and -- are not connected as ANONYMOUS. -- type rec_params is record ( old_pwd varchar2(100), new_pwd varchar2(100) ); type typ_params is table of rec_params index by pls_integer; tbl_params typ_params; begin -- populate the test parameter array -- Test 1 - missing old password value tbl_params(1).old_pwd := null; tbl_params(1).new_pwd := 'Boring'; -- Test 2 - missing new password value tbl_params(2).old_pwd := 'Boring'; tbl_params(2).new_pwd := null; -- Test 3 - old password contains a quote tbl_params(3).old_pwd := q'[I'm a silly password]'; tbl_params(3).new_pwd := 'sensible'; -- Test 4 - new password contains a quote tbl_params(4).old_pwd := 'sensible'; tbl_params(4).new_pwd := q'[Who's sensible now ?]'; -- Test 5 - old password > 50 characters tbl_params(5).old_pwd := 'just leaning on the keyboard until i have printed over 50 characters zzzzz'; tbl_params(5).new_pwd := 'short_and_to_the_point'; -- Test 6 - new password > 50 characters tbl_params(6).old_pwd := 'short_and_to_the_point'; tbl_params(6).new_pwd := 'just leaning on the keyboard until i have printed over 50 characters zzzzz'; -- Test 7 - parameters are valid but we're not connected through APEX... tbl_params(7).old_pwd := 'Valid_pwd'; tbl_params(7).new_pwd := 'anotherboringpassword'; -- -- Execute the tests -- for i in 1..tbl_params.count loop begin change_apex_user_pwd_pr ( i_old_pwd => tbl_params(i).old_pwd, i_new_pwd => tbl_params(i).new_pwd ); dbms_output.put_line('Test '||i||' - Somthing has gone wrong - no error !'); exception when others then dbms_output.put_line('Test '||i||' Error : '||sqlerrm); end; end loop; end; /
Running this gives us :
SQL> @change_pwd_test Test 1 Error : ORA-20000: Both the Old Password and the New Password must be specified Test 2 Error : ORA-20000: Both the Old Password and the New Password must be specified Test 3 Error : ORA-20001: Passwords must not contain the ' character. Test 4 Error : ORA-20001: Passwords must not contain the ' character. Test 5 Error : ORA-20002: Passwords must not exceed 50 characters in length. Test 6 Error : ORA-20002: Passwords must not exceed 50 characters in length. Test 7 Error : ORA-20003: This function can only be called from APEX PL/SQL procedure successfully completed. SQL>
To test the rest of the function, we will of course, need to be connected via APEX.The Change Password Page
Now we come to the page we will be using to call the procedure we’ve just created.
The page will have :
- a password field for the application user to enter their current password
- a password field for the application user to enter their new password
- and another one for them to re-type it
- some validation that the new password and confirm password matches
- a button to call the change password procedure
- a field to present a message to the user after the password change call
Sounds simple (dangerous) enough…
In Application Builder hit the Create Page button…
select Blank Page ….
In the Page Attributes…
Page Alias : change_db_pwd
In the Page Name …
Name : Change My Password
HTML Region1 : change password
In Tab Options…
Tab Options : Use an existing tab set and create a new tab within the existing tab set
New Tab Label : Change Password
…and hit Finish.
Now Edit the Page.
Create a new field with an Item Type of Password :
In the Display Position and Name screen,
Item Name : PX_OLD_PWD (where X is the number of the page you’re editing).
In the Item Attributes Screen :
Label : Current Password
Field Width : 50
In the Settings Screen –
Value Required : Yes
Submit when Enter pressed : No
In the Source Screen :
Source Used : Always, replacing any existing session state
And hit Create Item.
Now create two further fields with the same properties except :
PX_NEW_PWD has a label of New Password
PX_CONFIRM_PWD has a label of Confirm New Password
Next, we create a Display Only field called PX_MESSAGE.
We’ll use this to provide feedback to the user.
We define this with no label so that it doesn’t show up on the screen, until it’s populated.
Now we’ve got all of the fields on the page the next step is to create the Change Password button :
Accept the defaults for Button Region and Button Position.
In the Button Attributes Page :
Button Name : change_pwd_btn
Label : Change Password
Then just hit Create Button.
Finally, we need to add a Dynamic Action to validate that the values in PX_NEW_PWD and PX_CONFIRM_PWD are not null and identical, and then to call the Procedure.
NOTE – I daresay any APEX experts reading this may have a better way of doing this !
So, Create a Dynamic Action.
In the Identification Page :
Name : change_pwd_da
In the When Page :
Action : Click
Selection Type : Button
Button : CHANGE_PWD_BTN
In the True Action Page :
Action : Execute PL/SQL Code
The PL/SQL Code is as follows :
begin if nvl(:P6_NEW_PWD, 'x') != nvl(:P6_CONFIRM_PWD, 'y') then :P6_MESSAGE := 'Confirm Password does not match New Password.'; else hr.change_apex_user_pwd_pr ( i_old_pwd => :P6_OLD_PWD, i_new_pwd => :P6_NEW_PWD ); :P6_MESSAGE := 'Your password has been changed'; end if; exception when others then :P6_MESSAGE := SQLERRM; end;
Page Items to Submit : P6_OLD_PWD,P6_NEW_PWD,P6_CONFIRM_PWD,P6_MESSAGE
Page Items to Return : P6_MESSAGE
Click Create Dynamic Action.
Now to test.
I’m connected as PLUGGER and I want to change my password.
So, I click on the Change Password Tab and I see :
If the new and confirm password fields don’t match, I get an error from the Dynamic Action itself, before it calls the procedure :
If I try to enter a password that contains a single quote, I get :
Finally, I manage to get it right and am rewarded with :
All that remains now is for us to arrange for the user to be re-directed to the change password page when they connect and their password is near expiry.
The password expiry_date is available in the DBA_USERS view so we need to grant SELECT on this to HR :
grant select on sys.dba_users to hr /
As I’m re-directing them to a page that belongs specifically to the current application, I’m going to put the re-direction in the application itself.
So, I’m going to add a Branch to the Home Page.
Once again we need to pause here for the APEX gurus to explain the proper way to do this !
Edit the Home Page and Create a Branch…
In Branch Attributes
Name : pwd_change_br
Branch Point : On Load : Before Header
Page : the number of the Change Password Page ( 6 in my case)
In Branch Conditions
Condition Type : Exists( SQL query returns at least one row)
In Expression 1, enter the query :
select 1 from sys.dba_users where username = apex_040200.v('APP_USER') and expiry_date < trunc(sysdate) + 7
This will return 1 if the password is due to expire within the next 7 days.
and hit Create Branch.
In order to test the branch, I’ve had a bit of a fiddle with the FIXED_DATE parameter [link to post] so that PLUGGER’s password is now due to expire in less than 7 days.
Now, when I login as plugger…
…I go straight to the Change Password Page…
What started off as a fairly short post about Database Authentication Schemes in APEX has grown quite a bit more than I intended.
I believe that the solution to password management, which I have outlined here, is secure.
Obviously, if anyone can spot any flaws in this, I (and anyone reading this), would find it immensly helpful if you could provide reasons/code as to why and how this approach could be exploited.
Whilst the Change Password functionality is something of an overhead in going down the Database Authentication route, the use of database roles, not to mention the RDBMS itself, does mean that this is an approach worth considering when porting older applications to APEX….or maybe it isn’t.
I wonder if there’s a passing Australian who’d like to share their opinion on this ?
Filed under: APEX, Oracle, PL/SQL, SQL Tagged: APEX Database Authentication Scheme, change password procedure, dba_role_privs, dba_users, password verify function, profile, verify function
It’s probably Monday morning. The caffeine from your first cup of coffee has not quite worked it’s way into your system.
The cold sweat running down the back of your neck provides an unpleasant contrast to the warm blast of panicked users as they call up to inform you that the Application is down.
APEX, which has been behaving impeccibly all this time, has suddenly decided to respond to all requests with :
503 – Service Unavailable.
The database is up. The APEX Listener is up. But something else is up. APEX just doesn’t want to play.
Better still, the person who set up the APEX in the first place has long-departed the company. You have no idea how the Apex Listener was configured.
Out of sympathy with your current predicament, what follows is :
- How to confirm that this problem is related to the APEX_PUBLIC_USER (the most likely cause)
- A quick and fairly dirty way of getting things back up and running again
- How to stop this happening again
Note: These steps were tested Oracle Developer Day VM with a 12c database running on Oracle Linux 6.5. In this environment, APEX is configured to run with the APEX Listener.
First of all, we want to make sure that APEX is connecting to the database as APEX_PUBLIC_USER. To do this, we need to check the default.xml file.
Assuming you’re on a Linux box :
cd /u01/oracle/apexListener/apex cat default.xml
If you don’t see an entry for db.username then APEX_PUBLIC_USER is the one that’s being used.
If there is an entry for db.username then that is the name of the database user you need to check in the following steps.
For now, I’ll assume that it’s set to the default.
Incidentally, there will also be an entry for db.password. This will almost certainly be encrypted so is unlikely to be of use to you here.Confirming the status of the APEX_PUBLIC_USER
The most likely reason for your current troubles is that the APEX_PUBLIC_USER’s database password has expired.
To verify this – and get the information we’ll need to fix it, connect to the database and run the query :
select account_status, profile from dba_users where username = 'APEX_PUBLIC_USER' /
If the account_status is EXPIRED, then the issue you are facing is that the APEX_PUBLIC_USER is expired and therefore APEX can’t connect to the database.
The other item of interest here is the PROFILE assigned to the user.
We need to check this to make sure that there is no PASSWORD_VERIFY_FUNCTION assigned to the profile. If there is then you need to supply the existing password in order to change it, which is a bit of a problem if you don’t know what it is.
Whilst we’re at it, we need to check whether there is any restriction in place as to the length of time or number of password changes that must take place before a password can be reused.
In my case, APEX_PUBLIC_USER has been assigned the DEFAULT profile.
select resource_name, limit from dba_profiles where profile = 'DEFAULT' and resource_name in ( 'PASSWORD_REUSE_TIME', 'PASSWORD_REUSE_MAX', 'PASSWORD_VERIFY_FUNCTION' ) /
When I ran this, I was lucky and got :
RESOURCE_NAME LIMIT ------------------------------ -------------------- PASSWORD_REUSE_TIME UNLIMITED PASSWORD_REUSE_MAX UNLIMITED PASSWORD_VERIFY_FUNCTION NULL
So, there are no restrictions on password reuse for this profile. Neither is there any verify function.
If your APEX_PUBLIC_USER is attached to a profile that has these restrictions, then you’ll want to change this before re-setting the password.
As we’re going to have to assign this user to another profile anyway, we may as well get it out of the way now.
Oracle’s advice for the APEX_PUBLIC_USER is to set the PASSWORD_LIFE_TIME to UNLIMITED.
Whilst it’s only these four parameters we need to set in the profile for us to get out of our current predicament, it’s worth also including a limitation on the maxiumum number of failed login attempts, if only to provide some limited protection against brute-forcing.
In fact, I’ve just decided to use the settings from the DEFAULT profile for the attributes that I don’t need to change :
create profile apex_public limit failed_login_attempts 10 password_life_time unlimited password_reuse_time unlimited password_reuse_max unlimited password_lock_time 1 composite_limit unlimited sessions_per_user unlimited cpu_per_session unlimited cpu_per_call unlimited logical_reads_per_session unlimited logical_reads_per_call unlimited idle_time unlimited connect_time unlimited private_sga unlimited /
As we don’t specify a PASSWORD_VERIFY_FUNCTION, none is assigned to the new profile.
NOTE – it’s best to check the settings in your own default profile as they may well differ from those listed here.
Next, we assign this profile to APEX_PUBLIC_USER…
alter user apex_public_user profile apex_public /
The next step is to reset the APEX_PUBLIC_USER password, which is the only way to unexpire the user.No password, no problem
Remember, in this scenario, we don’t know the current password for APEX_PUBLIC_USER. We don’t want to reset the password to just anything because we’re not sure how to set the password in the DAD used by the Apex Listener.
First of all, we need to get the password hash for the current password. To do this :
select password from sys.user$ where name = 'APEX_PUBLIC_USER' /
You’ll get back a hex string – let’s say something like ‘DF37145AF23CCA4′.
Next step is to re-set the APEX_PUBLIC_USER password :
alter user apex_public_user identified by sometemporarypassword /
We now immediately set it back to it’s original value using IDENTIFIED BY VALUES :
alter user apex_public_user identified by values 'DF37145AF23CCA4' /
At this point, APEX should be back up and running.Once the dust settles…
Whilst your APEX installation may now be back up and running, you now have a database user for which the password never changes.
Although the APEX_PUBLIC_USER has only limited system and table privilges, it also has access to any database objects that are available to PUBLIC.
Whilst this is in-line with Oracle’s currently documented recommendations, you may consider that this is a situation that you want to address from a security perspective.
If there is a sensible way of changing the APEX_PUBLIC_USER password without breaking anything, then you may consider it preferable to simply setup some kind of reminder mechanism so that you know when the password is due to expire and can change it ahead of time.
You would then be able to set the password to expire as normal.
If you’re wondering why I’m being a bit vague here, it’s simply because I don’t currently know of a sensible way of doing this.
If you do, it would be really helpful if you could let me know :)
Filed under: APEX, Oracle, SQL Tagged: APEX 503 Unavailable, create profile, dba_profiles, dba_users.account_status, failed_login_attempts, identified by values, password_reuse_max, password_reuse_time, password_verify_function
How many bytes in a Kilobyte ? The answer to this question is pretty obvious…and, apparently, wrong.
Yep, apparently we’ve had it wrong all these years for there are, officially, 1000 bytes in a Kilobyte, not 1024.
Never mind that 1000 is not a factor of 2 and that, unless some earth-shattering breakthrough has happened whilst I wasn’t paying attention, binary is still the fundemental basis of computing.
According to the IEEE, there are 1000 bytes in a kilobyte and we should all get used to talking about a collection of 1024 bytes as a Kibibyte
Can you imagine dropping that into a conversation ? People might look at you in a strange way the first time “Kibibyte” passes your lips. If you then move on and start talking about Yobibytes, they may well conclude that you’re just being silly.
Let’s face it, if you’re going to be like that about things then C++ is actually and object orientated language and the proof is not in the pudding – the proof of the pudding is in the eating.
All of which petulant pedantry brings me on to the point of this particular post – some rather helpful formatting functions that are hidden in, of all places, the DBMS_XPLAN pacakge…Function Signatures
If we happened to be strolling through the Data Dictionary and issued the following query…
select text from dba_source where owner = 'SYS' and type = 'PACKAGE' and name = 'DBMS_XPLAN' order by line /
we might be surprised at what we find….
***snip*** ---------------------------------------------------------------------------- -- ---------------------------------------------------------------------- -- -- -- -- The folloing section of this package contains functions and procedures -- -- which are for INTERNAL use ONLY. PLEASE DO NO DOCUMENT THEM. -- -- -- -- ---------------------------------------------------------------------- -- ---------------------------------------------------------------------------- -- private procedure, used internally *** snip *** FUNCTION format_size(num number) RETURN varchar2; FUNCTION format_number(num number) RETURN varchar2; FUNCTION format_size2(num number) RETURN varchar2; FUNCTION format_number2(num number) RETURN varchar2; -- -- formats a number representing time in seconds using the format HH:MM:SS. -- This function is internal to this package -- function format_time_s(num number) return varchar2; ***snip***Formatting a time in seconds
Let’s start with DBMS_XPLAN.FORMAT_TIME_S because we pretty much know what it does from the header comments.
To save myself a bit of typing, I’m just going to use the following SQL to see how the function copes with various values :
with actual_time as ( select &1 as my_secs from dual ) select my_secs, dbms_xplan.format_time_s(my_secs) as formatted_time from actual_time /
Plug in a variety of numbers ( representing a time in seconds) and …
SQL> @format_time.sql 60 old 3: select &1 as my_secs new 3: select 60 as my_secs MY_SECS FORMATTED_TIME -------------------- -------------------------------------------------- 60.00 00:01:00 SQL> @format_time.sql 3600 old 3: select &1 as my_secs new 3: select 3600 as my_secs MY_SECS FORMATTED_TIME -------------------- -------------------------------------------------- 3600.00 01:00:00 SQL> @format_time.sql 86400 old 3: select &1 as my_secs new 3: select 86400 as my_secs MY_SECS FORMATTED_TIME -------------------- -------------------------------------------------- 86400.00 24:00:00 SQL> @format_time.sql 129784 old 3: select &1 as my_secs new 3: select 129784 as my_secs MY_SECS FORMATTED_TIME -------------------- -------------------------------------------------- 129784.00 36:03:04 SQL>
I wonder how it treats fractions of a second ….
SQL> @format_time.sql 5.4 old 3: select &1 as my_secs new 3: select 5.4 as my_secs MY_SECS FORMATTED_TIME -------------------- -------------------------------------------------- 5.40 00:00:05 SQL> @format_time.sql 5.5 old 3: select &1 as my_secs new 3: select 5.5 as my_secs MY_SECS FORMATTED_TIME -------------------- -------------------------------------------------- 5.50 00:00:06 SQL>
So, the function appears to round to the nearest second. Not great if you’re trying to list the times of the Olympic Finalists of the 100 metres, but OK for longer durations where maybe rounding to the nearest second is appropriate.
One minor quirk to be aware of :
SQL> @format_time.sql 119.5 old 3: select &1 as my_secs new 3: select 119.5 as my_secs MY_SECS FORMATTED_TIME -------------------- -------------------------------------------------- 119.50 00:01:60 SQL> SQL> @format_time.sql 3599.5 old 3: select &1 as my_secs new 3: select 3599.5 as my_secs MY_SECS FORMATTED_TIME -------------------- -------------------------------------------------- 3599.50 00:59:60 SQL>
If 59.5 seconds is rounded up, the function returns a value containing 60 seconds, rather than displaying the value as a minute.Formatting Numbers
Next on our list of functions to explore are FORMAT_NUMBER and FORMAT_NUMBER2. At first glance, it may appear that these functions are designed to represent sizes using the IEEE standard definitions…
with myval as ( select &1 as the_value from dual ) select the_value, dbms_xplan.format_number(the_value) as format_size, dbms_xplan.format_number2(the_value) as format_size2 from myval /
Run this with a variety of inputs and we get :
SQL> @format_number.sql 999 old 3: select &1 as the_value new 3: select 999 as the_value THE_VALUE FORMAT_NUMBER FORMAT_NUMBER2 ---------- ------------------------------ ------------------------------ 999 999 999 SQL> @format_number.sql 1000 old 3: select &1 as the_value new 3: select 1000 as the_value THE_VALUE FORMAT_NUMBER FORMAT_NUMBER2 ---------- ------------------------------ ------------------------------ 1000 1000 1K SQL> @format_number.sql 1024 old 3: select &1 as the_value new 3: select 1024 as the_value THE_VALUE FORMAT_NUMBER FORMAT_NUMBER2 ---------- ------------------------------ ------------------------------ 1024 1024 1K SQL> @format_number.sql 1000000 old 3: select &1 as the_value new 3: select 1000000 as the_value THE_VALUE FORMAT_NUMBER FORMAT_NUMBER2 ---------- ------------------------------ ------------------------------ 1000000 1000K 1M SQL> SQL> @format_number.sql 1500 old 3: select &1 as the_value new 3: select 1500 as the_value THE_VALUE FORMAT_NUMBER FORMAT_NUMBER2 ---------- ------------------------------ ------------------------------ 1500 1500 2K SQL>
The FORMAT_NUMBER2 function reports 1000 as 1K.
Furthermore, for numbers above 1000, it appears to round to the nearest 1000.
FORMAT_NUMBER on the other hand, doesn’t start rounding until you hit 1000000.
From this it seems reasonable to infer that these functions are designed to present large decimal numbers in an easily readable format rather than being an attempt to conform to the new-fangled definition of a Kilobyte ( or Megabyte…etc).
Using the following script, I’ve created the BIG_EMPLOYEES table and populated it with 100,000 or so rows…
create table big_employees as select * from hr.employees / begin for i in 1..1000 loop insert into big_employees select * from hr.employees; end loop; commit; end; /
If we now apply these functions to count the rows in the table, we get the following :
select count(*), dbms_xplan.format_number(count(*)) as format_number, dbms_xplan.format_number2(count(*)) as format_number2 from big_employees / COUNT(*) FORMAT_NUMBER FORMAT_NUMBER2 ---------- -------------------- -------------------- 107107 107K 107K
You can see from this, how these functions might be useful when you’re looking at the number of rows in a very large table ( perhaps several million).Counting the Kilobytes properly
We now come to the other two functions we’ve identified – FORMAT_SIZE and FORMAT_SIZE2.
with myval as ( select &1 as the_value from dual ) select the_value, dbms_xplan.format_size(the_value) as format_size, dbms_xplan.format_size2(the_value) as format_size2 from myval /
Running this the results are :
SQL> @format_size.sql 999 old 3: select &1 as the_value new 3: select 999 as the_value THE_VALUE FORMAT_SIZE FORMAT_SIZE2 ---------- -------------------- -------------------- 999 999 999 SQL> @format_size.sql 1000 old 3: select &1 as the_value new 3: select 1000 as the_value THE_VALUE FORMAT_SIZE FORMAT_SIZE2 ---------- -------------------- -------------------- 1000 1000 1000 SQL> @format_size.sql 1024 old 3: select &1 as the_value new 3: select 1024 as the_value THE_VALUE FORMAT_SIZE FORMAT_SIZE2 ---------- -------------------- -------------------- 1024 1024 1k SQL> @format_size.sql 1000000 old 3: select &1 as the_value new 3: select 1000000 as the_value THE_VALUE FORMAT_SIZE FORMAT_SIZE2 ---------- -------------------- -------------------- 1000000 976K 977k SQL> @format_size.sql 1048576 old 3: select &1 as the_value new 3: select 1048576 as the_value THE_VALUE FORMAT_SIZE FORMAT_SIZE2 ---------- -------------------- -------------------- 1048576 1024K 1m SQL> @format_size.sql 2047.4 old 3: select &1 as the_value new 3: select 2047.4 as the_value THE_VALUE FORMAT_SIZE FORMAT_SIZE2 ---------- -------------------- -------------------- 2047.4 2047 2k SQL> @format_size.sql 2047.5 old 3: select &1 as the_value new 3: select 2047.5 as the_value THE_VALUE FORMAT_SIZE FORMAT_SIZE2 ---------- -------------------- -------------------- 2047.5 2047 2k SQL>
Things to notice here include the fact that format_size appears to FLOOR the value (1000000 bytes = 976.56 K), wheras FORMAT_SIZE2 rounds it up.
Additionally, once you pass in a value of over 1024, FORMAT_SIZE2 returns values in Kilobytes.
So, if we want to know the size of the BIG_EMPLOYEES table we’ve just created :
select bytes, dbms_xplan.format_size(bytes) as format_size, dbms_xplan.format_size2(bytes) as format_size2 from user_segments where segment_name = 'BIG_EMPLOYEES' / BYTES FORMAT_SIZE FORMAT_SIZE2 ---------- -------------------- -------------------- 9437184 9216K 9m
If all you need is an approximate value, then FORMAT_SIZE2 could be considered a reasonable alternative to :
select bytes/1024/1024 as MB from user_segments where segment_name = 'BIG_EMPLOYEES' /
As well as it’s primary purpose, DBMS_XPLAN does offer some fairly useful functions if you need a quick approximation of timings, or counts or even sizes.
Fortunately, it adheres to the traditional definition of a Kilobyte as 1024 bytes rather than “Litebytes”.
Filed under: Oracle, PL/SQL, SQL Tagged: dbms_xplan, format_number, format_number2, format_size, format_size2, format_time_s
The mystery has finally been solved. England’s surrendering of the Ashes last winter was nothing to do with Australia being a much better cricket team. Thanks to Kevin Pietersen’s recently published Autobiography, we now know that the problem was that there were rather too many silly points in the England dressing room.
Moving swiftly on from that weak pun, the subject at hand can also be rather mystifying at first glance.
In a “traditional” Oracle Forms application, you would have one database user per application users.
Connections via the Application to the database would be done as the individual users.
It’s quite likely that database roles would be used to grant the appropriate privileges.
For applications using other web technologies, the application may interact with the database via a single account, often that of the Application Owner. Whether or not this is a good idea is probably a discussion for another time.
For now though, the question we’re asking is, how an APEX application connect to the database ?
On the face of it, it would seem that it’s pretty similar to the second of the two approaches above. APEX connects as the Parsing Schema (usually the application owner).
As Kevin will tell you, appearances can be deceiving…
For the purposes of this post, I’ll be using a simple APEX application that’s been created in it’s own workspace.
The application is called NEW_HR and uses the default APEX Authentication Scheme.
The parsing schema is defined as HR.
At this point the application consists of a login screen and a blank Home Page.
I’ve also created a Workspace Admin user called…well…let’s call it Kevin.
The database version is Oracle 11g Express Edition and the APEX version is 4.2.
This environment uses the embedded PL/SQL Gateway to manage database connections from APEX. This is the default setup on Oracle 11g XE.
Now, I know that there is no user called KEVIN in my database….
select count(*) from dba_users where username = 'KEVIN' / COUNT(*) ---------- 0 SQL>
…so I’d like to know who the database thinks I am when I login through my APEX app. I’d also like to check who the APEX itself thinks I am.
The first step then, is to add a couple of fields to the application Home Page…
First of all, I’ve add an HTML Region called whoami. Apart from the name I’ve just accepted the defaults.
Now to add a field to display the Application User – i.e. who APEX thinks I am.
This is a Display Only Item called P1_APEX_USER in the whoami region.
The source settings for this item are the defaults except for :
Source Used : Always, replacing any existing value in session state
Source value or expression : APP_USER
Next up is to add a field to display the database user.
The field is defined in the same way as P1_APEX_USER, except for :
Source Type : SQL Query (return single value)
and the source itself which is the following query :
select user from dual
Now, if we connect as Kevin….
…we can start to resolve our identity crisis….
So, as expected, APEX knows that Kevin is the Application user. However, the database user is not HR, rather it’s something called ANONYMOUS.
NOTE – If you’re using the Embedded PL/SQL Gateway ( the default setup for Express Edition) then you’ll be connected as ANONYMOUS. If you have the APEX Listener setup then, unless you’ve changed the default, you’ll be connected as APEX_PUBLIC_USER.
For our current purposes we can treat these accounts as synonymous from a database standpoint.
I’ll continue to refer to ANONYMOUS from here on because (a) I’m running this on XE and (b) the name has slightly more comedic potential.
Let’s find out a bit more about this user whilst trying not to worry that our application has been visited by hacktivists.
Hmmm, maybe not so much comedic potential.
Looking in the database, we can confirm that ANONYMOUS is indeed a database user :
select account_status, profile, authentication_type from dba_users where username = 'ANONYMOUS' / ACCOUNT_STATUS PROFILE AUTHENTI -------------------------------- ------------------------------ -------- OPEN DEFAULT PASSWORD
Doesn’t seem to be anything out of the ordinary there.
Now let’s see what ANONYMOUS has granted to it. For good measure, we can see what objects it owns ( if any).
The query looks like this :
select 'SYSTEM PRIVILEGE' as priv_type, null as db_object, privilege from dba_sys_privs where grantee = 'ANONYMOUS' union select 'ROLE GRANTED' as priv_type, granted_role as db_object, null as privilege from dba_role_privs where grantee = 'ANONYMOUS' union select 'OBJECT PRIVILEGE' as priv_type, owner||'.'||table_name as db_object, privilege from dba_tab_privs where grantee = 'ANONYMOUS' union select 'OWNED OBJECT' as priv_type, object_name as db_object, null as privilege from dba_objects where owner = 'ANONYMOUS' order by 1,2 /
When we run it we get variations on the theme of :
PRIV_TYPE DB_OBJECT PRIVILEGE -------------------- ------------------------------------------------------------ ------------------------------ OBJECT PRIVILEGE APEX_040000.WWV_FLOW_EPG_INCLUDE_MODULES EXECUTE OBJECT PRIVILEGE APEX_040200.WWV_FLOW_EPG_INCLUDE_MODULES EXECUTE OBJECT PRIVILEGE FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$ ALTER OBJECT PRIVILEGE FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$ DELETE OBJECT PRIVILEGE FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$ FLASHBACK OBJECT PRIVILEGE FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$ INDEX OBJECT PRIVILEGE FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$ INSERT OBJECT PRIVILEGE FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$ ON COMMIT REFRESH OBJECT PRIVILEGE FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$ QUERY REWRITE OBJECT PRIVILEGE FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$ REFERENCES OBJECT PRIVILEGE FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$ SELECT OBJECT PRIVILEGE FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$ UPDATE SYSTEM PRIVILEGE CREATE SESSION
Now, the Object Privileges listed here are probable the result of some of the sample APEX applications I’ve installed.
By default, the only thing granted to ANONYMOUS is the CREATE SESSION privilege.
More pertinent here though is that it has no permissions at all on any objects owned by HR. This begs the question as to how our APEX application will work. Remember, our parsing schema ( essentially the Application Owner) is HR. Therefore, it’s reasonable to assume that we’ll want to interact with the tables in that schema.
NOTE – at this point I should add that, of course, ANONYMOUS does have additional privileges – i.e. everything granted to PUBLIC in the database. Whilst this is not strictly relevant to the matter at hand, it’s probably worth bearing in mind when you look at how you implement security around this user.
Anyway, let’s put it to the test…The Regions Report
In our application we’re going to create a new page – a Report on the HR.REGIONS table so…
In the Application Builder, click on Create Page :
Select Report and click Next
Select Interactive Report and click Next
Accept the defaults for Page Region Attribute and click Next
In Tab Options choose Use an existing tab set and create a new tab within the existing set
New Tab Label is Regions :
For the SQL Query :
select region_id, region_name from regions
Note – we’re not specifying the table owner in this query, even though ANONYMOUS does not have a synonym on the HR.REGIONS table ( let alone any privileges)
…and click Create
When we now connect to the application as Kevin and click on the Regions tab….
So, the report has worked without error, despite the lack of privileges and synonyms. So what’s happening ?Session Privileges in APEX
To answer this, we’ll need to tweak our earlier privileges query. This time, we’ll use the USER_ version of the views.
We can then it to the Application Home Page in a new reports region to see what ANONYMOUS can actually do when connected via APEX.
First, the new query, using USER_ versions of the views and without the order by clause.
select 'SYSTEM PRIVILEGE' as priv_type, null as db_object, privilege from user_sys_privs union select 'ROLE GRANTED' as priv_type, granted_role as db_object, null as privilege from user_role_privs union select 'OBJECT PRIVILEGE' as priv_type, owner||'.'||table_name as db_object, privilege from user_tab_privs union select 'OWNED OBJECT' as priv_type, object_name as db_object, null as privilege from user_objects where object_type != 'INDEX' /
Spoiler Alert – the reason I’m not using the SESSION_PRIVS view here is because it will list privileges granted via roles. The distinction between these and directly granted privileges will shortly become apparent.
We now simply create a new interactive reports region called User Privileges on the Home Page, using the above query.
If we now filter on PRIV_TYPE = ‘OWNED OBJECT’, we can see that we’ve magically acquired ownership of all the HR objects…
If we filter on PRIV_TYPE = ‘SYSTEM PRIVILEGE’, we can see that we also seem to have inherited HR’s System Privileges…
So, we can infer from this that, although the database connection from APEX is as the ANONYMOUS user, the session will inherit all of the objects and privileges of the parsing schema.
A reasonable assumption, given the evidence, and a correct one…mostly.
I’ve created a simple function in my own schema :
create or replace function name_scandal_fn( i_basename varchar2) return varchar2 as begin return i_basename||'gate'; end; /
Next we’re going to create a role and then grant execute on this function to that role. Finally, we’re going to grant the role to hr :
create role hr_role / grant execute on name_scandal_fn to hr_role / grant hr_role to hr /
First off, we’ll test this in SQL*Plus. Connect as HR and …
select mike.name_scandal_fn('Twitter') from dual / MIKE.NAME_SCANDAL_FN('TWITTER') -------------------------------------------------------------------------------- Twittergate SQL>
So, we should have no problem invoking this function from our application then.
Let’s create a page with a Display Only field that is populated by a call to this function :
Blank Page :
…With an HTML Region…
…on a new tab…
Now, add the Item…
Accept the defaults for the Item Attributes settings, and Settings…
… and change the Source settings to :
Source Used : Always, replacing any existing value in session state
Source Type : SQL Query (return single value)
Item Source Value – here we put in our call to the function :
select mike.name_scandal_fn('Twitter') from dual
Finally, hit the create button.
No problems so far. Now, let’s try running the page…
Hmmm, not quite what we were expecting.
Looking at the error stack, a possible source of the problem emerges.
In the background, it looks as if APEX is calling a package called WWV_FLOW_FORMS, which in turn calls WWV_FLOW_DYNAMIC_EXEC.
Whilst the source for both of these packages is wrapped, there are some notes availble on the next package in the call stack, WWV_DBMS_SQL here.
Putting all together and looking at the package headers, it would seem reasonable to assume that, rather than running the SQL statement directly, APEX does this via a series of package calls which then run the statement as dynamic SQL.
The effect of calling a (presumably) Definer’s Rights package is that any privileges granted via roles are ignored.
In order to test this theory, we can revoke the role from HR and instead, grant execute on the function directly.
So, connected to SQL*Plus as the function owner ( in my case MIKE) :
revoke hr_role from hr / grant execute on name_scandal_fn to hr /
Now a quick sanity check to make sure that HR can see the function.
Connect as HR and :
SQL> select mike.name_scandal_fn('Twitter') from dual 2 / MIKE.NAME_SCANDAL_FN('TWITTER') -------------------------------------------------------------------------------- Twittergate SQL>
Now let’s see what APEX makes of this.
Re-run the page and we can see…
There you have it. APEX, like Kevin, is just a little bit different.
Filed under: APEX, Oracle, SQL Tagged: apex anonymous user, apex_public_user, granting privileges via roles, parsing schema
As I have alluded to previously, I was not born in the UK.
Nope, my parents decided to up-sticks and move from London all the way to the
other side of the world, namely Auckland.
Then they had me. Then they came back.
To this day, they refuse to comment on whether these two events were related.
I went back to New Zealand a few years ago.
As I wandered around places that I hadn’t seen since I was five, it was strange how memories that I had forgotten came flooding back.
That last sentence doesn’t make much sense. It’s probably more accurate to say that memories I hadn’t thought about for years came flooding back.
I recently remembered something else I once knew, and then forgot – namely how to generate a SQL*Plus file name which includes a parameter value.The scenario
I’ve got a script that lists all of the employees in a given department :
accept deptno prompt 'Enter Department ID : ' spool department.lis select first_name, last_name from hr.employees where department_id = &deptno order by employee_id / spool off
Now, rather than it just creating a file called department.lis, I want to create a file that includes the department number I’m querying.Obvious…but wrong
You might think the following is a reasonable attempt to do this :
accept deptno prompt 'Enter Department ID : ' spool department_&deptno.lis select first_name, last_name from hr.employees where department_id = &&deptno order by employee_id / spool off
Unfortunately, SQL*Plus insists on being obtuse and outputting the following file :
It is at this point that a colleague came to the rescue ( thanks William)…Going dotty
This will do the job…
accept deptno prompt 'Enter Department ID : ' spool department_&deptno..lis select first_name, last_name from hr.employees where department_id = &deptno order by employee_id / spool off
Run this and we not only get :
Enter Department ID : 10 old 3: where department_id = &deptno new 3: where department_id = 10 FIRST_NAME LAST_NAME -------------------- ------------------------- Jennifer Whalen SQL>
…we get a file, appropriately named :
The magic here is that the “.” character delimits the variable substitution.
Just to prove the point, we can do the same with a positional parameter :
set verify off spool department_&1..lis select first_name, last_name from hr.employees where department_id = &1 order by employee_id / spool off
…run this and we get :
SQL> @position_param.sql 10 FIRST_NAME LAST_NAME -------------------- ------------------------- Jennifer Whalen SQL>
…and the appropriate file…
On that note, I’m off to the pub. Now, where did I leave my keys ?
Filed under: Oracle, SQL Tagged: spool; filename including a variable value, SQL*Plus
Not only can I remember the Good Old Days, I also remember them being far more fun than they probably were at the time.
Oh yes, and I was much younger….and had hair.
Yes, the Good Old Days, when Oracle introduced PL/SQL database packages, partitioning, and when the sequence became extinct.
Hang on, I don’t remember that last one…
Say we have a requirement for a table to hold details of gadgets through the ages.
This table has been modelled with a synthetic key, and also a couple of audit columns so we can track when a row was created and by whom.
Traditionally, the code to fulfill this requirement would follow a familiar pattern.
The table might look something like this :
create table gadgets ( id number constraint dino_pk primary key, gadget_name varchar2(100) not null, created_by varchar2(30) default user, creation_date date default sysdate ) /
NOTE – you’d normally expect to see NOT NULL constraints on the CREATED_BY and CREATION_DATE columns. I’ve left these off for for the purposes of the examples that follow.
We’ll also want to have a sequence to generate a value for the id…
create sequence gad_id_seq /
As it stands, this implementation has one or two issues…
-- Specify all values insert into gadgets( id, gadget_name, created_by, creation_date) values( gad_id_seq.nextval, 'Dial-Up Modem', user, sysdate) / -- omit the "default" columns insert into gadgets( id, gadget_name) values( gad_id_seq.nextval, 'Tablet Computer') / -- specify null values for the "default" columns -- also, don't use the sequence for the id value insert into gadgets( id, gadget_name, created_by, creation_date) values(3, 'Netbook', null, null) /
The first problem becomes apparent when we query the table after these inserts…
SQL> select * from gadgets; ID GADGET_NAME CREATED_BY CREATION_DATE ---- -------------------- -------------------- -------------------- 1 Dial-Up Modem MIKE 31-AUG-14 2 Tablet Computer MIKE 31-AUG-14 3 Netbook
Yes, although the insert was successful for the Netbook row, the explicit specification of CREATED_BY and CREATION_DATE values as NULL has overidden the default values defined on the table.
What’s more, there’s nothing enforcing the use of the sequence to generate the ID value. This becomes a problem when we go to do the next insert…
-- Next insert using sequence... insert into gadgets(id, gadget_name, created_by, creation_date) values( gad_id_seq.nextval, 'Smart Phone', null, null) / insert into gadgets(id, gadget_name, created_by, creation_date) * ERROR at line 1: ORA-00001: unique constraint (MIKE.DINO_PK) violated
Because we didn’t use the sequence for the previous insert, it’s still set to the value it had after it was last invoked…
SQL> select gad_id_seq.currval from dual; CURRVAL ---------- 3
The traditional solution to these problems is, of course, a trigger…
create or replace trigger gad_bir_trg before insert on gadgets for each row -- -- Make sure that : -- - id is ALWAYS taken from the sequence -- - created_by and creation date are always populated begin :new.id := gad_id_seq.nextval; :new.created_by := nvl(:new.created_by, user); :new.creation_date := nvl(:new.creation_date, sysdate); end; /
Now, if we re-run our insert…
insert into gadgets(id, gadget_name, created_by, creation_date) values( gad_id_seq.nextval, 'Smart Phone', null, null) / 1 row inserted SQL> select * from gadgets where gadget_name = 'Smart Phone'; ID GADGET_NAME CREATED_BY CREATION_DATE ---- -------------------- -------------------- -------------------- 5 Smart Phone MIKE 31-AUG-14
Yes, even though we’ve invoked the sequence in the INSERT statement, the trigger invokes it again and assigns that value to the ID column ( in this case 5, instead of 4).
Reassuringly thought, the CREATED_BY and CREATION_DATE columns are now populated.
So, in order to fulfill our requirements, we need to create three database objects :
- A table
- a sequence
- a DML trigger on the table
Or at least, we did….12c – the Brave New World
Oracle Database 12c introduces a couple of enhancements which will enable us to do away with our trigger completely.
First of all…
You can now specify a default value for a column that will be used, even if NULL is explicitly specified on Insert.
Furthermore, you can now also use a sequence number as a default value for a column.
If we were writing this application in 12c, then the code would look a bit different….
create sequence gad_id_seq / create table gadgets ( id number default gad_id_seq.nextval constraint dino_pk primary key, gadget_name varchar2(100) not null, created_by varchar2(30) default on null user, creation_date date default on null sysdate ) /
We’ve dispensed with the trigger altogether.
The ID column now uses the sequence as a default.
The CREATED_BY and CREATION_DATE columns will now be populated, even if NULL is explicitly specified as a value in the INSERT statement….
-- Specify all values insert into gadgets( id, gadget_name, created_by, creation_date) values( gad_id_seq.nextval, 'Dial-Up Modem', user, sysdate) / -- omit the "default" columns insert into gadgets( id, gadget_name) values( gad_id_seq.nextval, 'Tablet Computer') / -- specify null values for the "default" columns -- also, don't use the sequence for the id value insert into gadgets( id, gadget_name, created_by, creation_date) values(3, 'Netbook', null, null) / ID GADGET_NAME CREATED_BY CREATION_ ---- -------------------- -------------- --------- 1 Dial-Up Modem MIKE 31-AUG-14 2 Tablet Computer MIKE 31-AUG-14 3 Netbook MIKE 31-AUG-14
Whilst we can now guarantee that the CREATED_BY and CREATION_DATE columns are populated, we are still left with one issue, or so you might think…
-- Next insert using sequence... insert into gadgets(id, gadget_name, created_by, creation_date) values( gad_id_seq.nextval, 'Smart Phone', null, null) / 1 row inserted
That’s odd. You’d think that the sequence NEXTVAL would be 3, thus causing the same error as before. However…
select * from gadgets; ID GADGET_NAME CREATED_BY CREATION_ ---- -------------------- -------------- --------- 1 Dial-Up Modem MIKE 31-AUG-14 2 Tablet Computer MIKE 31-AUG-14 3 Netbook MIKE 31-AUG-14 21 Smart Phone MIKE 31-AUG-14
Hmmm. Let’s take a closer look at the sequence…
select min_value, increment_by, cache_size, last_number from user_sequences where sequence_name = 'GAD_ID_SEQ' / MIN_VALUE INCREMENT_BY CACHE_SIZE LAST_NUMBER ---------- ------------ ---------- ----------- 1 1 20 41
Yes, it looks like, in 12c at least, the default for sequences is a cache size of 20.
If we wanted to create the sequence in the same way as for 11g ( i.e. with no caching), we’d need to do this :
create sequence gad_id_seq nocache /
We can now see that the sequence values will not be cached :
PDB1@ORCL> select cache_size 2 from user_sequences 3 where sequence_name = 'GAD_ID_SEQ' 4 / CACHE_SIZE ---------- 0
All of this is a bit of an aside however. The fact is that, as it stands, it’s still quite possible to by-pass the sequence altogether during an insert into the table.
So, we still need to have a trigger to enforce the use of the sequence, right ?
Well, funny you should say that….
Time for another version of our table. This time however, we’re dispensing with our sequence, as well as the trigger…
create table gadgets ( id number generated as identity constraint gad_pk primary key, gadget_name varchar2(100) not null, created_by varchar2(30) default on null user not null, creation_date date default on null sysdate not null ) /
Let’s see what happens when we try to insert into this table. Note that we’ve modified the insert statements from before as the sequences does not exist ….
-- Specify all values insert into gadgets( id, gadget_name, created_by, creation_date) values( default, 'Dial-Up Modem', user, sysdate) / -- specify null values for the "default" columns -- also, don't use the sequence for the id value insert into gadgets( id, gadget_name, created_by, creation_date) values(3, 'Netbook', null, null) / -- omit the "default" columns insert into gadgets( id, gadget_name) values( null, 'Tablet Computer') /
The first statement succeeds with no problem. However, the second and third both fail with :
ORA-32795: cannot insert into a generated always identity column
We’ll come back to this in a bit.
In the meantime, if we check the table, we can see the ID column is automagically populated….
select * from gadgets; ID GADGET_NAME CREATED_BY CREATION_ ---- -------------------- -------------- --------- 1 Dial-Up Modem MIKE 31-AUG-14
Oh, it’s just like being on SQL Server.
How is this achieved ? Well, there are a couple of clues.
First of all, executing the create table statement for this particular version of the table requires that you have the additional privilege of CREATE SEQUENCE.
A further clue can be found by looking once again at USER_SEQUENCES…
select sequence_name, min_value, increment_by, cache_size, last_number from user_sequences / SEQUENCE_NAME MIN_VALUE INCREMENT_BY CACHE_SIZE LAST_NUMBER -------------------- ---------- ------------ ---------- ----------- ISEQ$$_95898 1 1 20 21
If we have a look at the column details for the table, we get confirmation that this sequence is used as the default value for the ID column :
1 select data_default 2 from user_tab_cols 3 where table_name = 'GADGETS' 4* and column_name = 'ID' PDB1@ORCL> / DATA_DEFAULT -------------------------------------------------------------------------------- "MIKE"."ISEQ$$_95898".nextval
It’s worth noting that this sequence will hang around, even if you drop the table, until or unless you purge the table from the RECYCLEBIN.
If you prefer your sequences to be, well, sequential, the good news is that you can use the Sequence Creation syntax when specifying an identity column.
The change in the default number of values cached for sequences created in 12c, compared with 11g and previously, may lead you to consider being a bit more specific in how you create your sequence, just in case things change again in future releases.
Here we go then, the final version of our table creation script….
create table gadgets ( id number generated always as identity ( start with 1 increment by 1 nocache nocycle ) constraint gad_pk primary key, gadget_name varchar2(100) not null, created_by varchar2(30) default on null user not null, creation_date date default on null sysdate not null ) /
As we saw earlier, the INSERT statements for this table, now need to change. We can either specify “DEFAULT” for the ID column :
insert into gadgets( id, gadget_name, created_by, creation_date) values( default, 'Dial-Up Modem', user, sysdate) /
…or simply omit it altogether…
insert into gadgets(gadget_name, created_by, creation_date) values('Smart Phone', user, sysdate) /
And, of course, we can also omit the values for the other defaulted columns should we choose….
insert into gadgets(gadget_name) values('Netbook') /
If we check the table after these statements, we can see that all is as expected :
select * from gadgets / ID GADGET_NAME CREATED_BY CREATION_ ---- -------------------- -------------- --------- 1 Dial-Up Modem MIKE 31-AUG-14 2 Smart Phone MIKE 31-AUG-14 3 Netbook MIKE 31-AUG-14
As with a “traditional” table/sequence/trigger setup, an erroneous INSERT will cause a gap in the sequence…
insert into gadgets( id, gadget_name, created_by, creation_date) values( default, 'Psion Series 5', 'Aridiculouslylongusernamethatwontfitnomatterwhat', sysdate) / values( default, 'Psion Series 5', 'Aridiculouslylongusernamethatwontfitnomatterwhat', sysdate) * ERROR at line 2: ORA-12899: value too large for column "MIKE"."GADGETS"."CREATED_BY" (actual: 48, maximum: 30) insert into gadgets( id, gadget_name, created_by, creation_date) values( default, 'Psion Series 5', default, default) / select * from gadgets; ID GADGET_NAME CREATED_BY CREATION_ ---- -------------------- -------------- --------- 1 Dial-Up Modem MIKE 31-AUG-14 2 Smart Phone MIKE 31-AUG-14 3 Netbook MIKE 31-AUG-14 5 Psion Series 5 MIKE 31-AUG-14Conclusion
While we can see that 12c hasn’t done away with sequences altogether, it is fair to say that they are now a lot more unobtrusive.
As for the good old DML trigger ? Well, they’ll still be with us, but they may well be a little lighter on the mundane default handling stuff we’ve been through in this post.
Filed under: Oracle, PL/SQL, SQL Tagged: column default value, create sequence, default always, default cache value of sequence, default on null, generated as identity, identity column, insert value into identity column, ORA-32795 : cannot insert into a generated always identity column