Use Database Profiles to help secure your database
I have seen many times over the years where a production database will experience performance problems that are caused by Adhoc queries running and consuming too many resources. These will often be from developers trying to debug a problem, or someone in Operations trying to get a custom report, or from some other source. In extreme cases, this can be, in effect, a classic Denial of Service attack.
One way to solve the problem is to simply restrict access to the production database, particularly if you have a copy of production available for debugging/reporting purposes. Alternatively, you can implement Database Profiles to restrict the amount of resources any one user is allowed to consume. They are very easy to implement and can go a long way towards securing your database against an unintentional (or possibly intentional) Denial of Service attack.
In order for profiles to work, you must first set the init.ora parameter resource_limit to TRUE. You can then create profiles and assign them to users.
SQL> alter system set resource_limit=TRUE; SQL> create profile LIMITED_PROFILE limit sessions_per_user 2; SQL> create user PROFILE_TEST identified by abcd profile LIMITED_PROFILE; SQL> grant create session to profile_test;
Now I connect as PROFILE_TEST user, the first two connections are fine, but the third gets:
oracle@mdlinux ~]$ sqlplus profile_test/abcd ERROR: ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit
Now that the user can only have two sessions at a given time, I will further crank down the screws by limiting the number of Logical Reads allowed per session. In a real world scenario, you will need to come up with a reasonable limit but for testing I will set it to 10000.
SQL> alter profile LIMITED_PROFILE limit logical_reads_per_session 10000;
And when they run some crazy query, it will stop them in their tracks
SQL> select count(*) from dba_objects a, dba_objects b; select count(*) from dba_objects a, dba_objects b * ERROR at line 1: ORA-02394: exceeded session limit on IO usage, you are being logged off
To see what you can limit with a profile, run this query:
SQL> select unique RESOURCE_TYPE,RESOURCE_NAME from dba_profiles SQL> order by 1,2 asc;
RESOURCE RESOURCE_NAME ------- -------------------------------- KERNEL COMPOSITE_LIMIT KERNEL CONNECT_TIME KERNEL CPU_PER_CALL KERNEL CPU_PER_SESSION KERNEL IDLE_TIME KERNEL LOGICAL_READS_PER_CALL KERNEL LOGICAL_READS_PER_SESSION KERNEL PRIVATE_SGA KERNEL SESSIONS_PER_USER PASSWORD FAILED_LOGIN_ATTEMPTS PASSWORD PASSWORD_GRACE_TIME PASSWORD PASSWORD_LIFE_TIME PASSWORD PASSWORD_LOCK_TIME PASSWORD PASSWORD_REUSE_MAX PASSWORD PASSWORD_REUSE_TIME PASSWORD PASSWORD_VERIFY_FUNCTION 16 rows selected.
The PASSWORD resources are used to manage passwords. The KERNEL resources are used to manage system resources. For security reasons, you may want to set the IDLE_TIME limit. This will prevent users from staying logged into the database for extended periods of time with no activity. To see which users have been assigned profiles, you can query the dba_users view:
select username, profile from dba_users;
So, if you get a chance, check out Database Profiles. They are quick, easy and can possibly help protect your database from an unpleasant situation.