How to Check Users and Database Usage in Oracle 12c
Knowing what is going on within the Oracle 12c database is part of your job as a database administrator (DBA). You should regularly log in to your database to understand who is using it, what normal usage looks like, and whether anything unusual is occurring. Developing a baseline understanding of your database usage greatly helps you in your other DBA responsibilities.
If you check on your users on a regular basis, you’ll be able to recognize the most active users. You can also find the following issues:
Stale and abnormally long sessions
Login abuse, such as people sharing accounts and unauthorized logins
How to find stale sessions in Oracle 12c
Stale sessions have been logged in to the system for a long time, and no one has done anything with them. Getting rid of stale sessions can help control resource usage.
Everyone’s system is different, so you have to rely on some of your knowledge to decide whether the absence of activity is abnormal. To get a list of logged-in users and the last time they issued a command, connect to the database as a DBA user and type the following:
<select sid, serial#, username, last_call_et from v$session where username is not null;>
You might see something like this:
SID SERIAL# USERNAME LAST_CALL_ET --- ------- ---------------- ------------ 12 9853 SYSMAN 3 14 6552 HR 0 56 42 DBSNMP 6 112 59271 SYSTEM 160 65 23451 MPYLE 743160 98 8752 CRM 1 32 4551 CRM 3 45 16554 HR 36 119 9812 KHANR 36522
MPYLE and KHANR are the two potentially bothersome accounts in this example. The LAST_CALL_ET column output data has the number of seconds since there was any activity. MPYLE has been inactive for over eight days! KHANR is a little less alarming at about 10 hours, but nonetheless, that would most likely require some explanation.
Note that the command uses the WHERE clause USERNAME IS NOT NULL. Oracle internal processes show up in this list as unnamed users, and you don’t want those getting in the way of evaluation. Also, this example includes identification columns SID and SERIAL#. Together, these two columns uniquely identify a session within the database.
If you see a session you want to get rid of, type the following:
<alter system kill session '65,23451';>
You should see this:
Before you go killing sessions that have long periods of inactivity, check with application folks to determine whether a connection is part of a connection pool that just hasn’t been used for a while. Usually, removal is safe if it isn’t an application ID or if you can identify the user. Do your research!
How to police for login abuse and unauthorized logins in Oracle 12c
People everywhere are abusing their login privileges, and such abuse is a significant security problem. Unfortunately, it’s usually people higher up in the application chain of command who tend to have more privileges giving out their login ID to subordinates to help with work. In other cases, co-workers decide to share a login ID because an account became locked or someone forgot his or her password.
Track this abuse down by comparing the database login ID with the OS login ID. Oracle tracks both. Type the following to see all the users connected with both IDs:
<select sid, serial#, username, osuser, program from v$session where username is not null;>
You might see something like this:
SID SERIAL# USERNAME OSUSER PROGRAM ---------- ---------- ---------- ---------- ----------------- 112 3741 MPYLE MPYLE sqlplusw.exe 122 3763 MPYLE RKHAN sqlplusw.exe 115 9853 SYSMAN oracle OMS 122 35878 HR HRAPP sqlplus@classroom 124 4 DBSNMP oracle emagent@classroom
MPYLE has given his login ID to RKHAN (or at least the evidence suggests that). Evaluate this information carefully and do a thorough investigation. Take appropriate measures if there’s been a violation of your security policy.