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      

SQL Server Architecture: Pages and Extents

Logical Architecture: Pages and Extents

  • Page is the fundamental unit of data storage in SQL Server (datablocks in Oracle).
  • Eight(8) physically contiguous pages => One (1) EXTENT.
  • Disk space allocated to a data file (.mdf or .ndf) in a database is logically divided into pages numbered from 0 to n.
  • Disk I/O operations are performed at the page level. (SQL Server reads/writes WHOLE pages).
  • Page size: 8kb. ==> 128 pages = 1Mb.

On SQL Server Pages

  • Header: 96-bytes. (page#, type, free space, allocation unit ID of the object that owns the page)
  • data rows: inserted serially in the page.

Types of Pages:
  • Data
  • Index
  • Text/Image
  • Global allocation Map: extent allocation info
  • Shared allocation Map: extent allocation info
  • Page Free Space: free space info
  • Index allocation map: extents used by table or index per allocation unit.
  • Bulk changed map: extents modified by BULK ops since last BACKUP LOG
  • Differential Changed Map: extents modified since last BACKTUP DATABASE

  • The basic unit of space management.
  • One EXTENT = EIGHT (8) contiguous pages (64Kb). ==> 16 Extents = 1Mb
Types of extents: Mixed and Uniform

(a) Uniform: owned by a single object

(b) Mixed: shared by up to eight objects.

Install SQL Developer 3.0 on Ubuntu 10.04

Ok, so I needed to perform some tests of Oracle's SQL Developer 3.0 runing on Ubuntu, so I decided to install it on a machine running Lucid Lynx 10.4. These are the steps I followed (following the guidelines that mick has posted.)

Requirement: Ensure you have a JDK installed
SQL Developer requires that the Sun Java J2SE JDK 1.6.11 or later be installed on the system. To install JDK, use the command:
$ sudo apt-get install sun-java6-jdk

Here you may receive the error message below:
Package sun-java6-jdk is not available, but is referred to by another package.
This may mean that the package is missing, has been obsoleted, or
is only available from another source
E: Package sun-java6-jdk has no installation candidate

To solve this problem, add a new source to the aptitude repository, then try installing again:
$ sudo add-apt-repository "deb lucid partner"
$ sudo apt-get update
$ sudo apt-get install sun-java6-jdk

Download the files below
(a) Download the software
You will need to log in the OTN site and go to the address below and download the file titled “Oracle SQL Developer for other platforms.”

(b) Install the make-sqldeveloper-package package
$ sudo apt-get install sqldeveloper-package
builds a .deb package
// builds a Debian package of Oracle SQL Developer from the architecture independent archive from , identified as "Oracle SQL Developer for other platforms."

// you can check the contents of a package with

$ dpkg -L

To download the sqldeveloper-package you will need Ubuntu’s multiverse software repository.

(c) Install the tofrodos package
$ sudo apt-get install tofrodos

// Create symbolic links to the tofrodos commands to 
// allow the sqldeveloper-package to work:
$ sudo ln -s /usr/bin/fromdos /usr/bin/dos2unix
$ sudo ln -s /usr/bin/todos /usr/bin/unix2dos
tofrodos package
Converts DOS <-> Unix text files
// DOS text files have CR/LF pairs as their new line delimiters while Unix text files have LFs (line feeds) only
// Tofrodos comprises one program, "fromdos" alias "todos", which converts text files to and from these formats. Use "fromdos" to convert DOS text files to the Unix format, and "todos" to convert Unix text files to the DOS format.

Convert the ZIP file to .DEB package
To convert the .zip file to a .deb package, use the command below::

The make-sqldeveloper-package command will
(1) create a temporary directory at the location indicated by (i.e. /tmp/build)
(2) use the temporary directory to build the .deb package
(3) write the new package in the directory
(4) remove the temporary directory

So, if you saved the .zip file in the current directory, you could issue the command:

Install SQL Developer
To install, run:

$ sudo dpkg -i sqldeveloper_3.0.04.34+0.2.3-1_all.deb

This should install Oracle SQL Developer and you'll end up with a new entry in Gnome's menu:
Applications->Programming->SQL Developer

Inform the location of JDK
When you run SQL Developer for the first time, you may receive the message below on a terminal window, if it cannot find the location of JDK (SQL Developer expects to find the path information in the file ~/.sqldeveloper/jdk):
Oracle SQL Developer
Copyright (c) 2008, Oracle. All rights reserved.

Type the full pathname of a J2SE installation (or Ctrl-C to quit), the path will be stored in ~/.sqldeveloper/jdk

To fix this, you need to (a) create the directory .sqldeveloper; (b) create the jdk file and type in the path to the JDK:
$ mkdir -p ~/.sqldeveloper
$ vi ~/.sqldeveloper/jdk
# enter the path to JDK: /usr/lib/jvm/java-6-sun

Now you should be able to use SQL Developer 3.0