| Previous | Table of Contents | Next |
Here’s a sample query from the session auditing data dictionary view:
SQL> select substr(os_username,1,10) osuser, 2 substr(username,1,10) username, 3 to_char(timestamp,’mm/dd/yy hh;mi;ss’) “TIME STAMP”, 4 action_name action, 5 decode(returncode,’0’,’Succeeded’,’Failed’) result 6 from sys.dba_audit_session; OSUSER USERNAME TIME STAMP ACTION RESULT ---------- --------- ----------------- ---------------- ------ rkstephe RYAN 04/04/98 05:24:05 LOGOFF Succeeded rkstephe RYAN 04/05/98 02:17:39 LOGOFF Succeeded rkstephe SYSTEM 04/05/98 04:30:26 LOGOFF Succeeded rkstephe RYAN 04/05/98 04:30:43 LOGON Succeeded rkstephe SYSTEM 04/05/98 04:33:50 LOGON Failed
To activate the audit option for actions against tables:
SQL> audit table; Audit succeeded.
To connect as a user with no privileges other than CREATE SESSION:
SQL> connect test/test (user was created with no privileges) Connected.
To attempt to create a table as test:
SQL> create table test_table 2 (ident number, 3 name varchar2(30)); create table test_table * ERROR at line 1: ORA-01950: no privileges on tablespace ‘SYSTEM’
This was an unsuccessful attempt at issuing the CREATE TABLE statement, which is a “table” auditing option.
Now, we connect as a user with the DBA role. As this user, additional privileges will be granted to test, which will allow table creation:
SQL> connect ryan/ryan Connected. SQL> grant connect, resource to test; Grant succeeded.
After connecting as test once again, the CREATE TABLE statement is reissued:
SQL> connect test/test Connected. SQL> create table names 2 (id number, 3 name varchar2(30)); Table created.
Now, the connection back to the account with the DBA role has been reestablished. The object audit view will be queried in hope to find our table commands.
SQL> connect ryan/ryan Connected.
Statement auditing:
SQL> select username, substr(owner,1,10) owner, substr(obj_name,1,10)
obj_name,
2 timestamp, returncode
3 from dba_audit_object;
USERNAME OWNER OBJ_NAME TIMESTAMP RETURNCODE
------------------- ----------- ---------- --------- ----------
TEST TEST TEST_TABLE 04-APR-98 1950
TEST TEST NAMES 04-APR-98 0
Notice that for unsuccessful commands, the error message code is place in the RETURNCODE column.
NOTE:
Many individuals also choose to audit the database through the use of triggers. For example, you may have a transactional history table that is populated every time a user performs a transaction. The trigger is created on the target table being changed, and then it writes changed data and other information, such as the username and a timestamp, to the history table.
All databases have flaws—in their structure, at the application level, and definitely in the accuracy of user activities. Yes, most database users are human and do make mistakes. Databases are designed by humans and there will inevitably be deficiencies. Databases are implemented, problems arise, and the management wants results—now! We also ought to throw the growth factor into the equation. Database objects grow with increased data, and often the user community grows as well. This is where database monitoring comes into play.
Database monitoring is knowing what is happening in your database at all times, acting in a proactive manner, and making intelligent decisions that may get you promoted—or at least allow you to keep your job.
The following three types of monitoring encompass the responsibilities of a DBA:
Some common things to monitor from a DBA perspective:
| Previous | Table of Contents | Next |