Aug 01

12c DBA_USERS Changes

Today, I was looking at the ability to query a user’s last login time. Prior to 12c, one had to create a logon trigger to store the logon time in a table or set up auditing and audit successful logins. Now, Oracle 12c includes, by default, a way to determine the last time someone logged in.

SQL> show user
USER is "SYS"
SQL> select username,last_login from dba_users
2  where last_login is not null;
USERNAME                       LAST_LOGIN
------------------------------ ----------------------------------------
SYSTEM                         30-JUL-13 01.57.19.000000000 PM -05:00
PEASLAND                       31-JUL-13 02.15.38.000000000 PM -05:00

As you can see, I have two users that have logged into the database and the date/time they last logged in. One thing I noticed is that even though I am connected as the SYS user, this column is not populated for SYS.

While investigating the DBA_USERS view, I see that in addition to the LAST_LOGIN column, there are also 3 other new columns to the Data Dictionary view, only one of which I had previously read about, ORACLE_MAINTAINED. The ORACLE_MAINTAINED column will indicate a Y value if the user was created by Oracle and its various scripts. Currently in my test database, I only have one such user.

SQL> select username from dba_users where oracle_maintained<>'Y';
USERNAME
------------------------------
PEASLAND

Now, the natural herbal treatment is very much reliable and free of any http://ronaldgreenwaldmd.com/forms/Medical-History-Form.pdf viagra price side effects. The effectiveness cialis without prescription uk of the drug lasts for about 36 hours from the time of consumption and its effect lasts for good 4 to 6 hours. The people, who are much concerned with treatment of newly abortions visit now cialis samples because of many hormonal disorders. cialis 20 mg The electrotherapy offered for sports injuries in Monaghan have also opened up.

One new column that I had not previously read about was the PROXY_ONLY_CONNECT user. This view lets you see if a user will allow proxy connections. In case you are not familiar with proxy connections, this might help explain it. For more information,  visit the SQL Reference Guide for ALTER USER and search for “proxy_clause”.
The last new column in DBA_USERS is COMMON. I had no clue what this column was for so my first stop was the Reference Guide for DBA_USERS. The description for the column is “Indicates whether a given user is common”. Ok….what does that mean? What defines a common user? If I query my test database, I get the following output:
SQL> select username,common,oracle_maintained from dba_users;
USERNAME                       COM O
------------------------------ --- -
PEASLAND                       NO  N
AUDSYS                         YES Y
GSMUSER                        YES Y
SYSKM                          YES Y
XS$NULL                        YES Y
OJVMSYS                        YES Y
ORACLE_OCM                     YES Y
SYSDG                          YES Y
DIP                            YES Y
SYSBACKUP                      YES Y
GSMCATUSER                     YES Y
GSMADMIN_INTERNAL              YES Y
APPQOSSYS                      YES Y
XDB                            YES Y
WMSYS                          YES Y
DBSNMP                         YES Y
ANONYMOUS                      YES Y
SYS                            YES Y
SYSTEM                         YES Y
OUTLN                          YES Y
So is a “common” user one that is Oracle maintained? If so, aren’t the COMMON and ORACLE_MAINTAINED columns redundant? I doubt that Oracle would include two columns that mean the same thing. So I embarked on a search to find the answer…
In the Administrator’s Guide, there is a small section titled “Common Users and Local Users“. The Common user is one that is common to a container database and all existing and future pluggable databases in Oracle 12c’s new multi-tenant database architecture. That makes sense now.

bpeasland