Sunday, September 13, 2015

Creating a DB user without using a CREATE statement

I was teaching an Oracle Database 12c New Features class recently when I stumbled upon something I found quite interesting. While preparing for the class, I had to correct some of the exercises that had been provided since there were some typos due to OCR conversion. For one of the exercise, I had to create a new user and while I was entering the commands, I realized that I typed a GRANT statement before issuing the CREATE user statement. This must've been a typo I thought, but proceeded anyway. To my surprise Oracle didn't throw any errors at me!

Well I must have done something wrong so let's try this again.

First let's connect to the database - in this example I'm using Oracle 12.1.0.1 but I've tested and confirmed it works in Oracle 11.2 as well and should also work in Oracle 10.2 but I haven't tested it.
PDB1@ORCL> ALTER SESSION SET CONTAINER=PDB1;

Session altered.
Check if the user exists
PDB1@ORCL> SELECT username,account_status,created
2 FROM dba_users
3 WHERE username LIKE 'TOM%';

no rows selected
Now there is no Tom in my PDB so let's just grant the CONNECT privilege to the non-existent user.

PDB1@ORCL> GRANT connect TO tom IDENTIFIED BY jerry;

Grant succeeded.
That seemed to work. Let's check if the user now exists.
PDB1@ORCL> SELECT username,account_status,created
2 FROM dba_users
3 WHERE username LIKE 'TOM%';


USERNAME ACCOUNT_STATUS CREATED
------------------------------ -------------------- ---------
TOM OPEN 13-SEP-15

PDB1@ORCL> CONNECT tom/jerry
Connected.
PDB1@ORCL>
And he does! This was new to me and a few others since I tweeted my discovery and others had the same sentiment.


This is also documented in the Database SQL Language reference. The IDENTIFIED BY clause is part of the grantee_system_privilege. This means that it doesn't work for object privilege grants.
To illustrate I'll try to grant insert on the employees table to a non-existent user "TWEETY".
PDB1@ORCL> GRANT INSERT ON HR.EMPLOYEES TO TWEETY;
GRANT INSERT ON HR.EMPLOYEES TO TWEETY
*
ERROR at line 1:
ORA-01917: user or role 'TWEETY' does not exist
I don't recommend using the GRANT statement to create a user. I'd rather explicitly use the CREATE statement for that purpose. Has anyone used this before? Feel free to leave comments below.




2 comments:

  1. Hi Leighton, Good to Know! Where you teach 12c class? online? I may apply for it. :-)

    Emily

    ReplyDelete