Oracle 12c New Security Features – 02 DBA_USERS new columns

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

Capt02

And the definition of DBA_USERS in Oracle 12c version is given below.

Capt01As you see; four new colums are added. These are

PROXY_ONLY_CONNECT
COMMON
LAST_LOGIN
ORACLE_MAINTAINED

Now we work on these new columns

PROXY_ONLY_CONNECT

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;

Capture

You can enable or disable this attribute of a user by alter user command

Capture.JPG

COMMON

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

Capture

 

LAST_LOGIN

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.

Capture.JPG

ORACLE_MAINTAINED

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

(

‘ANONYMOUS’,’CTXSYS’,’DBSNMP’,’EXFSYS’,’LBACSYS’,’MDSYS’,’MGMT_VIEW’,’OLAPSYS’,

‘OWBSYS’,’ORDPLUGINS’,’ORDSYS’,’OUTLN’,’SI_INFORMTN_SCHEMA’,’SYS’,’SYSMAN’,

‘SYSTEM’,’TSMSYS’,’WK_TEST’,’WKSYS’,’WKPROXY’,’WMSYS’,’XDB’,’APEX_PUBLIC_USER’,

‘DIP’,’FLOWS_30000′,’FLOWS_FILES’,’MDDATA’,’ORACLE_OCM’,

‘SPATIAL_CSW_ADMIN_USR’,’SPATIAL_WFS_ADMIN_USR’,’XS$NULL’,

‘OWBSYS_AUDIT’,’ORDDATA’,’APEX_030200′,’APPQOSSYS’,’DVSYS’,’DVF’

)

But in Oracle 12c version you can add a condition like

ORACLE_MAINTAINED <> ‘Y’

to exclude Oracle  managed users.

Capture

I downloaded  all scripts which are given on this note  to  github

https://github.com/yusufanilakduygu/Wordpress-Posts/blob/master/DBA_USERS%20new%20columns

 

Have a good day.

Anıl