Oracle default security features
| User Accounts |
| Authentication Methods |
| Privileges and Roles |
| Application Security |
| Virtual Private Database |
| Encryption |
| Audit |
 | 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:
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- 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:
APEX_PUBLIC_USER, DIP, FLOWS_30000, FLOWS_FILES, MDDATA, ORACLE_OCM, SPATIAL_CSQ_ADMIN_USR, SPATIAL_WFS_ADMIN_USR, XS$NULL- 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;
USERNAME USER_ID ACCOUNT_STATUS PROFILE AUTHENTICATION_TYPE
------------------- ------------ -------------------- --------- -------------------
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;
USERNAME
----------
HR
- 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)
ALTER USER HR ACCOUNT LOCK;
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: |
- Limits to the user account can be specified at creation time.
- 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. - 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 |
|---|
|
- GRANT CREATE SESSION instead of CONNECT role
- 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 SESSIONto gather information about limitsCONNECT_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 ] - (b) Use
ALTER SYSTEM SET RESOURCELIMIT = true;
 | Create and Assing a user profile |
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_planprocedure - (c)
Alter system set resource_manager_plan = 'plan_name'; - Use the
DBMS_RESOURCE_MANAGERpackage. - Requires
ADMINISTER_RESOURCE_MANAGERprivilege
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)
- Attribute can be:
ORACLE_USER, SERVICE_NAME, CLIENT_OS_USER, CLIENT_PROGRAM, CLIENT_MACHINE, MODULE_NAME, SERVICE_MODULE, ORACLE_FUNCTION
Begin
dbms_resource_manager.create_consumer_group
( Consumer_group => 'OLTP',
Comment => 'OLTP applications');
End;
/
Begin
dbms_resource_manager.set_consumer_group_mapping
(dbms_resource_manager.oracle_user, 'app_fin', 'oltp'); -- maps user 'app_fin' to consumer group 'oltp'
End;
/
 | Configuring a resource plan |
- To create a resource plan:
dbms_resource_plan.create_plan
Begin
dbms_resource_manager.create_plan
( plan => 'Daytime',
mgmt_mth => 'Emphasis', -- uses percentages to specify how CPU is distributed among consumer groups
comments => 'More resources for OLTP apps');
End;
/
 | 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
Begin
-- Creates a resource plan directive for plan 'daytime'.
dbms_resource_manager.create_plan_directive
( 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.
dbms_resource_manager.create_plan_directive
( 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
dbms_resource_manager.create_plan_directive
( plan => 'daytime',
group_or_subplan => 'other_groups',
comment => 'This is required',
mgmt_p1 => 10);
End;
/
| Creating a resource plan
|
Begin
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');
dbms_resource_manager.create_consumer_group
( 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);
dbms_resource_manager.create_plan_directive
( plan => 'daytime',
group_or_subplan => 'Reporting',
comment => 'Reporting Group',
mgmt_p1 => 15,
parallel_degree_limit_p1 => 8,
active_sess_pool_p1 => 4);
dbms_resource_manager.create_plan_directive
( 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
End;
/
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
