Showing posts with label Security. Show all posts
Showing posts with label Security. Show all posts

Oracle Database Security

source: xkcd

Oracle default security features
User accounts
  • naming convention, password profiles, password policies
  • Unlike SQL Server (2005/2008), there is no clear separation between database user and database schema
Authentication methods
  • database level, OS level, network level
Privileges and roles
  • restrict data access
Application security
  • User session information using Application CONTEXT
  • Application Context: name-value pair that holds session info.
  • You can retrieve info about a user (i.e., username/terminal, username/deptid) and restrict database and application access based on this information.
Virtual Private Database: restrict database access on the row and column levels.
  • VPD policy: dynamically imbeds a WHERE clause into SQL statements
Audit database

Additional database security resources
  1. Oracle Advanced Security
    • TDE, Wallet Mgmt, Network Encryption, RADIUS, Kerberos, Secure Sockets Layer authentication
  2. Oracle Label Security
    • Secures database tables at the row level
  3. Oracle Database Vault
    • Provides fine-grained access control to sensitive data
  4. Oracle Audit Vault
    • Collect data from audit trail tables, OS audit files and redo logs.
  5. Oracle Enterprise User Security
  6. Oracle Total Recall
  7. Oracle Database Firewall
  8. Oracle Data Masking

Managing Oracle Security
Database Security involves Authentication, Authorization, Data Access, and Audit

(1) Authentication Methods
  • Database Authentication (Default)
    • Oracle implements authentication through a user ID and password.
  • Operating System authentication
    • Sometimes referred as OPS$ accounts.
    • Oracle looks for a user ID that matches your OS login ID and prepends an OPS$ to it.
    • Additional benefit: OS scripts can log into the database without hard coding user names and passwords.
  • Third Party Authentication
    • You can tie authentication to third-party providers like Kerberos or DCE (called network authentication) (Needs EE+Security Pack)
    • or provide it from the middle tier (called multitier authentication).

(2) Authorization
  • Giving access to certain objects, processes or resources.
  • Implemented using GRANTS on objects to ROLES or USERS.
  • Oracle implements authorization via users, roles and permissions

(3) Securing Data Access
  • The ability to access specific data values associated with an object (across records or columns)
  • STORED PROCEDURES are commonly used to secure data access.
  • Benefits of using stored procedures for all programmatic data access: (a) secure the data; (b) provide consistent application access to the data; (c) Hide the data structure.
  • Secure VIEWS can also be used to restrict access. (use WITH CHECK OPTION Constraint)
  • Controlling access at the record level:
    • Previous versions (before Oracle 10g): using secure views
    • More recently:
  • (a) Use Virtual Private Databases (VPDs)
  • (b) Use Fine-Grained Access Control (FGAC) - uses DBMS_RLS package (Row Level Security)
  • With FGAC (DBMS_RLS), you can transparently include a WHERE clause on any combinations of DML and SELECT when a user queries the database.

(a) Option: use Secure Views
userA owns EMPLOYEES table.
userB needs access to EMPLOYEES but cant see SALARY or MANAGER records.
userA may create a view and grant access to userB on that view.

-- User A:
 SQL> create or replace view emp_no_sal as 
      select employee_id, last_name, first_name, email, job_id, manager_id
      from iranmr.employees a
      where employee_id NOT IN (
           select nvl(manager_id, -1)
           from iranmr.employees b);

 SQL> grant select on emp_no_sal to userB;

(b) Option: Use Virtual Private Database (VPD)
  • With Virtual Private Databases (VPDs), Oracle allows column masking to hide columns.
  • When you select the row, Oracle will only display NULL for the secure columns.
  • If you're securing at the row level and column level, it's probably easier to just implement VPDs and not the secure views.

What's a Virtual Private Databases (VPDs)?
  • A VPD is just asking Oracle to put a where clause on DML against an object with a security policy on it.
  • A security policy is defined with DBMS_RLS package.
  • A security policy is normally defined in a CONTEXT (a piece of data that says how the where clause should be built).

(4) Audit
  • Older versions: audit implemented through triggers.
  • Trigger limitations: (a) triggers can easily be disabled or modified; (b) triggers do not fire on SELECT; (c) require extensive coding and testing.
  • Newer versions: Use Fine Grained Auditing (FGA). (DBMS_FGA package)

Oracle Advanced Security

Oracle Advanced Security (OAS)

  • Introduced in Oracle8i.
  • Combines (a) strong authentication with (b) encryption of data in storage and while being transferred to and from the database.
  • Includes Transparent Data Encryption (TDE), Wallet Management, Network Encryption, RADUIS, Kerberos, Secure Socket Layer (SSL) Authentication, etc.
  • Helps customers address regulatory compliance requirements, including
    • - Sarbanes-Oxley (?)
    • - Payment Card Industry Data Security Standard (PCI-DSS),
    • - Health Insurance Portability and Accountability Act (HIPAA), and numerous
    • - Breach notification laws.

OAS provides (transparent) data encryption and strong authentication services
- Protect sensitive data on the network, on storage media and within the database from unauthorized disclosure.
- Also protects against theft, loss, and improper decommissioning of storage media and database backups

OAS Components
Transparent data Encryption (TDE)
  • Encrypts data before it is written to storage and
  • automatically decrypts data when reading it from storage without any changes to existing applications (no need for triggers, views, etc..)
  • Access controls that are enforced by the Oracle database still remain in effect. These include object grants, roles, virtual private database and Oracle Database Vault.
  • Two supported modes: TABLESPACE ENCRYPTION (11g only) and COLUMN ENCRYPTION (Introduced on 10g r2)
    • Tablespace Encryption: good for encrypting entire app tables
    • Column Encryption: good for individual data elements (credit cards, SSNs, etc).
    • Frequently accessed data blocks are cached in memory in the same manner as traditional nonencrypted data blocks

KEY Management
  • Two-tier key management architecture: MASTER encryption key + one or more DATA encryption keys.
  • TDE MASTER Encryption key (MEK): used to encrypt and protect the DATA encryption keys.
  • TDE MEK: can be stored in the Oracle Wallet.

Network encryption
  • Provides standards-based network encryption
  • Connections can be rejected from clients that have encryption turned off
  • No changes to existing applications are required

Strong Authentication
  • Kerberos, PKI or RADIUS
  • SSL-based authentication can make use of Smart Cards.

Encrypted database backups
  • RMAN backups encrypted data.
  • RMAN can call TDE during the backup process to encrypt the entire database (including SYSTEM and SYSAUX).
  • RMAN can COMPRESS and use TDE to ENCRYPT => compact and secure backups.

Identity and Access Control: Authentication and Users

  • Access to a SQL Server database takes place through three levels of security.
  • In each one you need to have access and permissions configured independently.
  • You may need to:
    • (1) Login in to a Windows domain (level 1) (except for non-windows clients)
    • (2) The Windows login is then mapped to SQL Server Login (level 2)
    • (3) The SQL Server Login is mapped to a database user (level 3).

Authentication Mode
  • Controls how application users connect to SQL Server
  • (a) Windows Authentication mode
    • Default and recommended
    • Only authenticated Windows users can access the SQL Server instance
    • Allowed connections:
      • by users who are authenticated through the Windows Active Directory service or
      • by the local user account databse on the SQL Server machine.
    • A Windows Login(within SQL Server) has to be created for each Windows user or group that needs acess to the SQL instance.

  • (b) Mixed Mode Authentication
    • Windows users can be mapped to SQL Server Windows logins or
    • SQL Logins can be created directly in SQL Server.
    • This mode is required when non-Windows clients need to access the server.
    • SQL Logins are not considered as secure as Windows Logins.
    • While Windows uses Kerberos authentication, SQL Logins store username/passwd in the master database.
    • If the use of SQL Logins is a requirement, you should also use some form of lower-layer network encryption (i.e. IPSec or SSL).

  • Principals: Entities (individuals, groups, processes) that can request SQL Server resources.
  • Securables: the server, databases, and objects within databases.
  • Principals can be arranged in a hierarchy
  • Scope of influence of a principal depends on:
    • (a) its definition scope: Windows -> Server -> database
    • (b) whether principal is indivisible (i.e. windows login, SQL Logins) or collection (i.e windows group)
  • Every principal has a security indentifier (SID)

Windows-level principals
  • Windows group
  • Windows domain login
  • Windows local login

SQL Server-level principals
  • SQL Server Login
    • The sa login is created when instance is installed.
    • sa default database: master.
    • Server-level principals for internal use: names enclosed with '##' (certificate-based)
  • Fixed Server Role:
    • sysadmin, serveradmin, securityadmin, processadmin,
    • setupadmin, bulkadmin, diskadmin, dbcreator, public

Database-level principals
  • Database User
    • Every database includes information_schema and sys.
    • These entities appear as users, BUT information_schema and sys ARE NOT principals. Cannot be dropped.
    • The guest user is created by default in every user database.
    • The guest user cannot be dropped, but can be disabled (revoke CONNECT), except in master and tempdb.
  • Fixed | Flexible Database Role
  • (i.e. public. every database user belongs to the public db role.)
  • Application Role

Types of SQL Server Logins:
(neither of which are mapped to an OS user)
  • Windows Logins
  • SQL Server Logins

Creating a SQL Server Login
(a) Create a SQL Server login that uses Windows Authentication
-- domain/user have to be a valid/existing Windows user
Create Login [domain/user] from windows;

-- domain/group has to be a valid Windows group
create login [domain_name/group_name] from windows;

(b) create a SQL Server Login that uses SQL Server authentication


Drop login test_SQLAuth_1;

(a) use sys.server_principals

select name, type_desc, default_database_name
from sys.server_principals;
name type_desc default_database_name
sa SQL_LOGIN master
iranmr-PC\iranmr WINDOWS_LOGIN master
test_SQLAuth_1 SQL_LOGIN Books

(b) use sys.sql_logins
select name, type_desc, default_database_name
from sys.sql_logins;
name type_desc default_database_name
sa SQL_LOGIN master
test_SQLAuth_1 SQL_LOGIN Books

Server-Level Roles (Fixed Server Roles)
  • SQL Server-level Logins can be managed using fixed server roles.
  • There are 9 Server-level roles.
    • sysadmin, serveradmin, securityadmin, processadmin, setupadmin, bulkadmin, diskadmin, dbcreator, public
  • Roles are similar to Windows OS groups
  • Server-level roles: also called fixed server roles (because cannot be created by users)
  • Have server-wide scope.
  • You can assign SQL Server logins, Windows accounts, and Windows groups to server-level roles.
Information about Server-Level Roles:
exec sp_helpsrvrole;
ServerRole Description
sysadmin System Administrators
securityadmin Security Administrators
serveradmin Server Administrators
setupadmin Setup Administrators
processadmin Process Administrators
diskadmin Disk Administrators
dbcreator Database Creators
bulkadmin Bulk Insert Administrators

exec sp_helpsrvrolemember;
ServerRole MemberName MemberSID
sysadmin sa
sysadmin iranmr-PC\iranmr

Adding/Dropping a Server Login to a Server-level Role
exec sp_addsrvrolemember test_SQLAuth_1, sysadmin;

exec sp_helpsrvrolemember;
ServerRole MemberName MemberSID
sysadmin sa
sysadmin iranmr-PC\iranmr
sysadmin        test_SQLAuth_1

exec sp_dropsrvrolemember test_SQLAuth_1, sysadmin;

Database users
Database users are principals at the database level.
A database user needs to be created for each login that needs to access the database.
When a login that does not have a user mapped to it tries to access a database, it is logged as the guest database user.
However, this only happens if the guest user has been granted CONNECT to the database.
(a) Create database used mapped to an existing SQL Server Login
USE [Books]

CREATE USER [test_auth_dbbooks1] FOR LOGIN [test_SQLAuth_1] 

(b) Check the created database user among the database principals
use Books; go select name, type_desc, default_schema_name from sys.database_principals; name type_desc default_schema_name ------- public DATABASE_ROLE NULL dbo WINDOWS_USER dbo guest SQL_USER guest INFORMATION_SCHEMA SQL_USER NULL sys SQL_USER NULL test_auth_dbbooks1 SQL_USER dbo ...

Fixed | Flexible Database Roles
  • Roles are security principals that group other principals.
  • Can be used to group database users with similar access privileges.
  • You can create new (flexible) database roles to define groups based on business requisites.
  • You can nest roles (add role into other role)
  • Create new role: CREATE ROLE role_name
  • Add user to role: exec sp_addrolemember role_name, user_name.
  • Fixed roles exist in all databases:
    • db_onwer, db_securityadmin, db_accessadmin, db_backupoperator, db_ddladmin, db_datawriter, db_datareader, db_denydatawriter, db_denydatareader

Application Role
  • Is a database principal that enables an application to run with its own, user-like permissions.
  • Can be used to enable access to specific data only to users connecting through a specific application.
  • Application roles contain no members.
  • How it works:
    • (1) Client application connects to SQL Server as a database user.
    • (2) Client application executes sp_setapprole.
    • (3) If validated, a context switch occurs and the application assumes the permission of the application role.
    WITH PASSWORD = '987G^bv876sPY)Y5m23', 

Managing Schemas
  • Schemas are collections of database objects that form a single namespace.
  • Starting in SQL Server 2005, schemas and database users are separate entities.
  • User name is no longer part of the object name.
  • Each schema is now a distinct namespace that exists independently of the database user who created it.
  • Each schema is owned either by a user or a role.
  • If you need to drop a user, you need to transfer the schema ownership to someone else first.
  • You can define a default schema for a user.
  • Implications of the separation of ownership:
    • Ownership of schemas is now transferable
    • Objects can be moved between schemas.
    • Multiple database users can share a single default schema.
    • Improved object-permission management.
    • Schema can be owned by any database principal: user, database role, application role.

Checking schemas
use Books;
select * from sys.schemas;

name                schema_id   principal_id
------------------  ----------  ------------
dbo                 1          1
guest 2 2
sys 4 4
db_owner 16384 16384
db_accessadmin 16385 16385
db_securityadmin 16386 16386
db_ddladmin 16387 16387
db_backupoperator 16389 16389
db_datareader 16390 16390
db_datawriter 16391 16391
db_denydatareader 16392 16392
db_denydatawriter 16393 16393

Using SQL Server and Database Principals, Schemas, application roles and context switching
(a) Create a SQL Server login, database user and schema.
(b) Create application role associated with the new schema
(c) Activate the new application role, switching user context
(d) Return to previous context.
-- Using database books
USE books;
(1) Create a SQL Server login user1
CREATE login user1 WITH password = ***;
(2) Create a [books] database user user1. -- user1 is created in the [books] database, -- is associated with user1 SQL Server principal (login) -- has schema1 as default schema.
CREATE USER  user1 FOR login user1 WITH default_schema = schema1;
(3) Query sys.sql_logins to check that the SQL Server principal was created
SELECT name, type_desc, default_database_name
    FROM sys.sql_logins;
name type_desc default_database_name -------------- --------- --------------- sa SQL_LOGIN master ... test_SQLAuth_1 SQL_LOGIN Books user1 SQL_LOGIN master (4) Query books.sys.database_principals to check that databse user user1 was created
SELECT name, principal_id, type_desc, default_schema_name
    FROM sys.database_principals;
name principal_id type_desc default_schema_name ------------------- ------------- ------------- --------------- public 0 DATABASE_ROLE NULL dbo 1 SQL_USER dbo guest 2 SQL_USER guest INFORMATION_SCHEMA 3 SQL_USER NULL sys 4 SQL_USER NULL user1 6 SQL_USER schema1 db_owner 16834 DATABASE_ROLE NULL ... (5) Query books.sys.schemas to check that the schema1 DOES NOT yet exist.
SELECT name, schema_id, principal_id
    FROM sys.schemas;
name schema_id principal_id ----------------- --------- -------- dbo 1 1 guest 2 2 INFORMATION_SCHEMA 3 3 sys 4 4 db_owner 16384 16384 ...

(6) Create schema books.schema1, owned by user1
USE Books;
(7) Query sys.schemas to check schema1 was created.
SELECT name, schema_id, principal_id
    FROM sys.schemas;
name schema_id principal_id ----------------- --------- -------- dbo 1 1 guest 2 2 INFORMATION_SCHEMA 3 3 sys 4 4 schema1 5 6 -- <= owner: user1 db_owner 16384 16384 ... (8) Create table emp_sch1 in schema1, and insert one row.
CREATE TABLE schema1.emp_sch1 (id INT, name VARCHAR(20));
INSERT INTO books.schema1.emp_sch1 
        VALUES (1, 'John');
(9) Query books.schema1.emp_sch1 to check the inserted row.
    FROM books.schema1.emp_sch1;
id name -- ---- 1 John

(10) Create application role approle_sch1, with default schema schema1. Grant select on schema1 to approle_sch1.
USE Books;
CREATE application role approle_sch1
            WITH password = 'p4sswd',
            default_schema = schema1;
SELECT ON schema::schema1 TO approle_sch1;
(11) Query the current user (should return 'dbo')
user_name --------- dbo (12) Switch context to approle_sch1 using sp_setapprole. Use cookies to save orginal context information.
DECLARE @cookie VARBINARY(8000);
EXEC sp_setapprole 'approle_sch1', 'p4sswd',
    @fCreateCookie = true,
    @cookie = @cookie OUTPUT;
    -- should return approle_sch1
    FROM emp_sch1;
    -- should return one row
EXEC sp_unsetapprole @cookie;
user_name ---------- approle_sch1 id name -- ---- 1 John (13) Revert to the original context using sp_unsetapprole.
user_name ---------- dbo

Oracle: Managing User Accounts

Oracle default security features
User Accounts
Authentication Methods
Privileges and Roles
Application Security
Virtual Private Database
Securing Oracle User accounts: basic measures
  • Secure all predefined database accounts
    • During installation a number of predefined (administrative, non-administrative and sample schemas) user accounts (and default paswwords) are created.
  • Predefined administrative user accounts include:
    • Each of these accounts manages an oracle component (such as Ultra Search, Workspace Manager, etc) and they are not present if the component is not installed.
    • Each of these accounts have special privileges necessary to administer areas of the database
  • Predefined non-administrative accounts include:
  • Predefined sample schema user accounts include:
    • BI, HR, OE, PM, IX, SH
    • If the database was upgraded from earlier versions, some of the sample schemas may have default passwords.
-- view existing users 
SQL> select username, user_id, account_status, profile, authentication_type
     from dba_users;
------------------- ------------ -------------------- --------- ------------------- 
SYSTEM              5            OPEN                 DEFAULT   PASSWORD            
SYS                 0            OPEN                 DEFAULT   PASSWORD            
SYSMAN              4            OPEN                 DEFAULT   PASSWORD
FLOWS_FILES         44           LOCKED               DEFAULT   PASSWORD
MDSYS               42           EXPIRED & LOCKED     DEFAULT   PASSWORD
HR                  43           EXPIRED & LOCKED     DEFAULT   PASSWORD

-- view accounts with default password
SQL> select * from dba_users_with_defpwd;

  • Although most predefined accounts are locked, you should secure them by changing their passwords.
  • You can expire passwords of unused accounts.
  • Also make sure that unused accounts are locked.
  • Password aging, expiration rules and history can be managed using profiles. (see below)

Suggested basic security measures
  • Protect prefedined user accounts: change default passwords, lock unused.
  • Establish user naming conventions
  • Define and enforce clear password policies (case sentivity, password complexity, grace_period, life_time, Reuse_time, reuse_max, etc).
  • Use profiles and password_verification_function clause to ensure password complexity.
  • Look for and revoke unnecessary privileges.

Security for users can be configured in a variety of ways:
  1. Limits to the user account can be specified at creation time.
  2. Resouce Limits can also be set on the various system resources available to each user through the use of profiles.
    Profiles: a collection of attributes that apply to a user.
  3. Resource limits can also be imposed on groups of users with the Database Resource Manager.

1. Setting limits at creation time:
  • Define QUOTAs on tablespaces. (catalog view: USER_TS_QUOTAS)
select tablespace_name, username, bytes, max_bytes
from dba_ts_quotas;
TABLESPACE_NAME                USERNAME                       BYTES                  MAX_BYTES   
------------------------------ ------------------------------ ---------------------- ---------------------- 
USERS                          HR                             1638400                -1                     
SYSAUX                         FLOWS_FILES                    458752                 -1   

Note: Oracle x SQL Server
  • Oracle TABLESPACES are logically similar to SQL Server FILEGROUPS.
  • For more on the similarties check here.

  • Grant organization-specific roles
  • Specify a DEFAULT Tablespace (Otherwise SYSTEM will be used => disk contention)
  • Obs: A tablespace designated as the default permanent tablespace cannot be dropped.
  • Specify a PROFILE for the user

2. Setting user resource limits with Profiles
  • Limits can be imposed at the user session level, or for each database call.
  • You can define limits on CPU time, number of logical reads, number of concurrent sessions for each user, session idle time, session elapsed connect time and the amount of private SGA space for a session.
  • Use AUDIT SESSION to gather information about limits CONNECT_TIME, LOGICAL_READS_PER_SESSION.

About Profiles
  • Profile is a named set of resource limits and password parameters.
  • A profile is a collection of attributes that apply to a user, enabling a single point of reference for any of multiple users that share those exact attributes.
  • Can be assigned to each user.
  • A default profile is assigned to each user who has not been explicitly assigned a profile.
  • Profile resource limits are enforced only when you enable resource limitation for the database:
    • (a) Use init parameter RESOUCE_LIMIT = [ true | false ]

Create and Assing a user profile
(a) Creating a profile and checking existing profiles:
SQL> Conn / as sysdba;

SQL> Create PROFILE my_profile LIMIT
  idle_time             20   -- After 20 minutes of continued inactivity database rolls back the current transaction and ends session.
  connect_time          600  -- After 600 minutes database rolls back the current transaction and ends session.
  sessions_per_user     1    -- Max number of concurrent sessions.
  failed_login_attempts 3    -- Account is locked after 3 failed logins.
  password_lock_time    5    -- Number of days account is locked for. UNLIMITED required explicit unlock by DBA.
  password_life_time    30   -- Password expires after 90 days.
  password_grace_time   3    -- Grace period for password expiration.
  password_reuse_time   120  -- Once changed, a password cannot be reused for 120 days. UNLIMITED means never.
  password_reuse_max    10;  -- Number of changes required before a password can be reused. UNLIMITED means never.

SQL> select * from dba_profiles order by profile;
PROFILE                        RESOURCE_NAME                    RESOURCE_TYPE LIMIT                                    
------------------------------ -------------------------------- ------------- ---------------------------------------- 
DEFAULT                        IDLE_TIME                        KERNEL        UNLIMITED                                
DEFAULT                        PASSWORD_VERIFY_FUNCTION         PASSWORD      NULL                                     
DEFAULT                        PASSWORD_LOCK_TIME               PASSWORD      1                                        
DEFAULT                        PASSWORD_LIFE_TIME               PASSWORD      180                                      
DEFAULT                        FAILED_LOGIN_ATTEMPTS            PASSWORD      10                                       
DEFAULT                        PASSWORD_GRACE_TIME              PASSWORD      7                                        
DEFAULT                        CONNECT_TIME                     KERNEL        UNLIMITED                                
MY_PROFILE                     COMPOSITE_LIMIT                  KERNEL        DEFAULT                                  
MY_PROFILE                     CPU_PER_SESSION                  KERNEL        DEFAULT                                  
MY_PROFILE                     CPU_PER_CALL                     KERNEL        DEFAULT                                  
MY_PROFILE                     LOGICAL_READS_PER_SESSION        KERNEL        DEFAULT                                  
MY_PROFILE                     LOGICAL_READS_PER_CALL           KERNEL        DEFAULT                                  
MY_PROFILE                     PRIVATE_SGA                      KERNEL        DEFAULT                                  
MY_PROFILE                     PASSWORD_VERIFY_FUNCTION         PASSWORD      DEFAULT                                  
MY_PROFILE                     PASSWORD_LIFE_TIME               PASSWORD      30                                       
MY_PROFILE                     PASSWORD_REUSE_TIME              PASSWORD      120                                      
MY_PROFILE                     PASSWORD_GRACE_TIME              PASSWORD      3                                        
MY_PROFILE                     PASSWORD_LOCK_TIME               PASSWORD      5                                        
MY_PROFILE                     SESSIONS_PER_USER                KERNEL        1                                        
MY_PROFILE                     PASSWORD_REUSE_MAX               PASSWORD      10                                       
MY_PROFILE                     CONNECT_TIME                     KERNEL        600                                      
MY_PROFILE                     FAILED_LOGIN_ATTEMPTS            PASSWORD      3                                        
MY_PROFILE                     IDLE_TIME                        KERNEL        20                                       

(b) Assign profile to user and check the users' resource constraints:
SQL> alter user scott profile my_profile;

SQL> conn scott/pwd

SQL> select * from user_resource_limits;
RESOURCE_NAME                    LIMIT                                    
-------------------------------- ---------------------------------------- 
COMPOSITE_LIMIT                  UNLIMITED                                
SESSIONS_PER_USER                1                                        
CPU_PER_SESSION                  UNLIMITED                                
CPU_PER_CALL                     UNLIMITED                                
LOGICAL_READS_PER_SESSION        UNLIMITED                                
LOGICAL_READS_PER_CALL           UNLIMITED                                
IDLE_TIME                        20                                       
CONNECT_TIME                     600                                      
PRIVATE_SGA                      UNLIMITED

3. Setting limits with Database Resource Manager
  • You can set limits on the amount of various system resources available to each user.
    Defining resource limits is particularly important in large multiuser systems.
  • The Database Resource Manager enables the management of multiple workloads within a database.

Using the Resource Manager
  • Resource Manager is not enabled by default.
  • To enable Resource Manager:
    • (a) set init parameter RESOURCE_MANAGER_PLAN = plan_name
    • (b) Use dbms_resource_manager.switch_plan procedure
    • (c) Alter system set resource_manager_plan = 'plan_name';
  • Use the DBMS_RESOURCE_MANAGER package.

With the resource manager you can:
  • Guarantee certain sessions a minimum amount of CPU regardless of the load and # of users connected
  • Distribute available CPU (%CPU time across users)
  • Limit parallelism used by users
  • Manage the order of parallel statements in parallel statement queue.
  • Limit # of parallel servers a group of users can use
  • Create an active session pool (max # of active sessions in a group of users)
  • Manage runaway sessions (limit CPU and I/O either by terminating session or swithing consumer group)
  • Define max run-time limit for operations (prevent execution of those that are estimated to violate the limit)
  • Limit amount of idle time.
  • Allow database to use different resource plans (depending on time of day, day of week, etc).

Elements of the Resource manager:
  • (a) Resource consumer groups: group of sessions that share the same set of resource limits.
    • Collections of users grouped together based on their processing needs/limits.
    • Every session is mapped to a consumer group.
    • DBA can manually switch sessions between consumer groups.
  • (b) Resource plans: set of directives that specify resource allocation rules.
    • Only one is active at a time.
    • Includes one or more directives that allocate resources to consumer groups.
  • (c) Resource plan directives: associates resource plans with resource consumer groups.
    • For each resource plan, resource plan directives determine how the various resources are distributed across the various consumer groups.

Configuring resource consumer groups
1. create consumer group
2. Define mapping rules to specify how user sessions are assigned to consumer groups
  • To create a consumer group: dbms_resource_manager.create_consumer_grooup
  • To define a mapping, use code>dbms_resource_manager.set_consumer_group_mapping(attribute, value, consumer_group)
  ( Consumer_group => 'OLTP',
    Comment        => 'OLTP applications');

   (dbms_resource_manager.oracle_user, 'app_fin', 'oltp');  -- maps user 'app_fin' to consumer group 'oltp'

Configuring a resource plan
  • To create a resource plan: dbms_resource_plan.create_plan
   ( plan      => 'Daytime', 
     mgmt_mth  => 'Emphasis',           -- uses percentages to specify how CPU is distributed among consumer groups
     comments  => 'More resources for OLTP apps');

Configuring resource plan directives
  • Create resource plan directive with: dbms_resource_manager.create_plan_directive
  • Each directive belongs to a plan or subplan and allocates resource to either a consumer group or subplan.
  • Resource plan directives specify how resources are allocated to consumer groups or subplans.
  • Resources can be allocated according to the methods (some):
  • CPU, Degree of parallelism, Execution time, and Idle time limit

                        -- Creates a resource plan directive for plan 'daytime'.
    ( plan             => 'daytime',
      group_or_subplan => 'oltp',
      comment          => 'directive for oltp group',
      mgmt_p1          => 75);                 -- Assigns 75% of CPU resources to the OLTP consumer group at level 1.

    ( plan                      => 'daytime',
      group_or_subplan          => 'Reporting',
      comment                   => 'Reporting Group',
      mgmt_p1                   => 15,
      parallel_degree_limit_p1  => 8,         -- sets maximum degree of parallelism 
      active_sess_pool_p1       => 4);        -- sets maximum of 4 concurrently active sessions

    ( plan                      => 'daytime',
      group_or_subplan          => 'other_groups',
      comment                   => 'This is required',
      mgmt_p1                   => 10);


Resource plan "daytime" (example from Oracle documentation)

Creating a resource plan
  1. Create a pending area
  2. Create consumer groups
  3. Create resource plan
  4. Create resource plan directives
  5. Validate pending area
  6. Submit pending area
  dbms_resource_manager.create_pending_area();               -- (1) Create a pending area

  dbms_resource_manager.create_consumer_group                -- (2) Create Consumer groups
    ( Consumer_group => 'OLTP',
      Comment        => 'OLTP applications');
    ( Consumer_group => 'REPORTING',
      Comment        => 'Reporting applications');

  dbms_resource_manager.create_plan                            -- (3) Create Resource Plan
   ( plan      => 'Daytime', 
     mgmt_mth  => 'Emphasis',
     comments  => 'More resources for OLTP apps');

  dbms_resource_manager.create_plan_directive                 -- (4) Create plan directives        
    ( plan             => 'daytime',
      group_or_subplan => 'oltp',
      comment          => 'directive for oltp group',
      mgmt_p1          => 75);         
    ( plan                      => 'daytime',
      group_or_subplan          => 'Reporting',
      comment                   => 'Reporting Group',
      mgmt_p1                   => 15,
      parallel_degree_limit_p1  => 8,   
      active_sess_pool_p1       => 4); 
    ( plan                      => 'daytime',
      group_or_subplan          => 'other_groups',
      comment                   => 'This is required',
      mgmt_p1                   => 10);

  dbms_resource_manager.validate_pending_area();                -- (5) Validate pending area
  dbms_resource_manager.submit_pending_area();                  -- (5) Submit pending area

To view the available resource plans:
SQL> select plan, num_plan_directives, cpu_method, mgmt_method, sub_plan
from dba_rsrc_plans;
PLAN                           NUM_PLAN_DIRECTIVES    CPU_METHOD                     MGMT_METHOD                    SUB_PLAN 
------------------------------ ---------------------- ------------------------------ ------------------------------ -------- 
DSS_PLAN                       8                      EMPHASIS                       EMPHASIS                       NO       
MIXED_WORKLOAD_PLAN            6                      EMPHASIS                       EMPHASIS                       NO       
DEFAULT_MAINTENANCE_PLAN       4                      EMPHASIS                       EMPHASIS                       NO       
DEFAULT_PLAN                   4                      EMPHASIS                       EMPHASIS                       NO       
INTERNAL_PLAN                  1                      EMPHASIS                       EMPHASIS                       NO       
ORA$AUTOTASK_HIGH_SUB_PLAN     4                      EMPHASIS                       EMPHASIS                       YES