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.




Comments

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

    Emily

    ReplyDelete

Post a Comment

Popular posts from this blog

Viewing ASM trace files and alert logs in Unix/Linux

ORA-00020: maximum number of processes (%s) exceeded

Troubleshooting RAC Public Network Failure