Oracle: Managing User Accounts





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:
  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 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 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 ]
    • (b) Use ALTER SYSTEM SET RESOURCELIMIT = true;

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.
  • Requires ADMINISTER_RESOURCE_MANAGER privilege

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;
/


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
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      


No comments:

Post a Comment