One of the the security improvement in Oracle 12c version is the new columns at DBA_USERS view.
These new added columns are very uesfull for security administrators.
At the below picture you can see the definition of DBA_USERS in Oracle 11g version
And the definition of DBA_USERS in Oracle 12c version is given below.
As you see; four new colums are added. These are
Now we work on these new columns
This column shows you whether a user can connect directly ( if it is value N ) or can only be proxied (if it is value Y) by users.
Let’s query this column;
You can enable or disable this attribute of a user by alter user command
This column shows whether the user is a COMMON user.
COMMON users are used in Multitenant databases which are introduced in Oracle 12c version. In another note ; I will explain multitenant databases in Oracle 12c version.
This column can have two values ; YES or NO
YES means this user is a COMMON user.
NO means this user is a local user.
Let’s query this column
Last_login column is very useful column for database security administrator and it solves very important problem in Oracle 11g version. This column shows the user’s last logon time. In Oracle 11g version we have create a logon trigger and a special table to find and keep user’s last logon time. Now in Oracle 12c version you have nothing to do ; just you need the query this column to find the time of the users logon.
To query Last_login column you use the below query. If the LAST_LOGIN column is null It means that this user has not been connected to the database yet.
This is another very important new column at Oracle 12c database.
If the value of this column is ‘Y’. It means that this user was created and could only managed by Oracle-supplied script ( Scripts are given by Oracle company) . You must not change any properties of these users. This column is very important when running security control scripts. At some security controls you would like to exclude Oracle pre-defined application users. In Oracle 11g version , you have to know these Oracle usernames ( for example DBSNMP, MDSYS,CTXSYS,OUTLN…) . In Oracle 11g version , if you want to exclude Oracle managed users from your selection in any security control, you have to write a very big condition like below;
USERNAME NOT IN
But in Oracle 12c version you can add a condition like
ORACLE_MAINTAINED <> ‘Y’
to exclude Oracle managed users.
I downloaded all scripts which are given on this note to github
Have a good day.