Oracle database Security FAQ

Oracle database Security FAQ:

Why should databases be secured/ hardened?

Data is any company's greatest asset and the only "safe" database is one that has nothing in it. A lot of people think that hackers are all outside the firewall which is false. The greatest threat to your database is the person in the cube next to you who has access to it. For this reason databases should be secured to ensure its data is properly protected.

How does one change an Oracle user's password?

Issue the following SQL command to change a user's password:

ALTER USER <username> IDENTIFIED BY <new_password>;

Starting from Oracle 8 you can just type password from SQL*Plus, or if you need to change another user's password, type password user_name. Look at these examples:

SQL> password
Changing password for SCOTT
Old password:
New password:
Retype new password:

SQL> passw scott
Changing password for scott
New password:
Retype new password:
Password changed

Note: Oracle usernames and passwords are not case sensitive in database versions below Oracle 11g.

How does one create, manage and drop database users?

One can add, drop and manage database users from the Enterprise Manager GUI. The following examples will show how the same can be achieved from the SQL*Plus command prompt:

CREATE USER scott 
       IDENTIFIED BY tiger		-- Assign password
        DEFAULT	TABLESPACE  tools	-- Assign space for table and index segments
        TEMPORARY TABLESPACE temp;	-- Assign sort space

DROP USER scott CASCADE;		-- Remove user

After creating a new user, assign the required privileges:

GRANT CONNECT, RESOURCE TO scott;
GRANT DBA TO scott;	-- Make user a DB Administrator

Remember to give the user some space quota on its tablespaces:

ALTER USER scott QUOTA UNLIMITED ON tools;

Oracle user accounts can be locked, unlocked, forced to choose new passwords, etc. For example, all accounts except SYS and SYSTEM will be locked after creating an Oracle9iDB database using the DB Configuration Assistant (dbca). DBA's must unlock these accounts to make them available to users.

Look at these examples:

ALTER USER scott ACCOUNT LOCK    -- lock a user account
ALTER USER scott ACCOUNT UNLOCK; -- unlocks a locked users account

ALTER USER scott PASSWORD EXPIRE;  -- Force user to choose a new password

How does one enforce strict password controls?

By default Oracle's security is not extremely good. For example, Oracle will allow users to choose single character passwords and passwords that match their names and userids. Also, passwords don't ever expire. This means that one can hack an account for years without ever locking the user.

From Oracle 8 one can manage passwords through profiles. Some of the things that one can restrict:

• FAILED_LOGIN_ATTEMPTS - failed login attempts before the account is locked
• PASSWORD_LIFE_TIME - limits the number of days the same password can be used for authentication
• PASSWORD_REUSE_TIME - number of days before a password can be reused
• PASSWORD_REUSE_MAX - number of password changes required before the current password can be reused
• PASSWORD_LOCK_TIME - number of days an account will be locked after maximum failed login attempts
• PASSWORD_GRACE_TIME - number of days after the grace period begins during which a warning is issued and login is allowed
• PASSWORD_VERIFY_FUNCTION - password complexity verification script

Look at this simple example:

CREATE PROFILE my_profile LIMIT
       PASSWORD_LIFE_TIME 30;
ALTER USER scott PROFILE my_profile;

Can one switch to another database user without a password?

Users normally use the "CONNECT" statement to connect from one database user to another. However, DBAs can switch from one user to another without a password. Of course it is not advisable to bridge Oracle's security, but look at this example:

SQL> CONNECT / as sysdba
Connected.

SQL> SELECT password FROM dba_users WHERE  username='SCOTT';
PASSWORD
--------------- ---------------
F894844C34402B67

SQL> ALTER USER scott IDENTIFIED BY anything;
User altered.

SQL> CONNECT scott/anything
Connected.

OK, we're in. Let's quickly change the password back before anybody notices.

SQL> ALTER USER scott IDENTIFIED BY VALUES 'F894844C34402B67';
User altered.

Note: Also see the su.sql script in the Scripts and Sample Programs section of this site.

Under ORACLE 11 things are slightly different: Connect without knowing the password can be done. See example.

(connect as SYSTEM)

SQL> alter user APEX_030200 grant connect through system;
User altered.

SQL> alter user apex_030200 account unlock;
User altered.

SQL> connect system[apex_030200]/systempassword
Connected.

SQL> select user from dual;
APEX_030200

Why are OPS$ accounts a security risk in a client/server environment?

If you allow people to log in with OPS$ accounts from Windows Workstations, you cannot be sure who they really are. With terminals, you can rely on operating system passwords, with Windows, you cannot.

If you set REMOTE_OS_AUTHENT=TRUE in your init.ora file, Oracle assumes that the remote OS has authenticated the user.

If REMOTE_OS_AUTHENT is set to FALSE (recommended), remote users will be unable to connect without a password. IDENTIFIED EXTERNALLY will only be in effect from the local host. Also, if you are using "OPS$" as your prefix, you will be able to log on locally with or without a password, regardless of whether you have identified your ID with a password or defined it to be IDENTIFIED EXTERNALLY.

Managing administrative (privileged) users and password files

An administrative account is a user that is granted SYSOPER or SYSDBA privileges. Oracle DBAs and operators typically use administrative accounts to manage the database and database instance.

SYSDBA and SYSOPER allow access to a database instance even if it is not running. Control of these privileges is managed outside of the database via password files and special operating system groups (dba on Unix/Linux and ORA_DBA on Windows). External password files are created with the orapwd utility.

Connecting as an administrative user:

If an administrative users belongs to the "dba" group on Unix, or the "ORA_DBA" (ORA_sid_DBA) group on Windows, he/she can connect like this:

connect / as sysdba

No password is required. This is equivalent to the desupported "connect internal" method.

A password is required for "non-secure" administrative access. These passwords are stored in password files. Remote connections via Net8 are classified as non-secure. Look at this example:

connect sys/password as sysdba

Password files:

The Oracle Password File ($ORACLE_HOME/dbs/orapw or orapwSID) stores passwords for users with administrative privileges. One needs to create a password files before remote administrators (like OEM) will be allowed to connect.

Follow this procedure to create a new password file:

• Log in as the Oracle software owner
• Run command: orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=mypasswd
• Shutdown the database (SQLPLUS> SHUTDOWN IMMEDIATE)
• Edit the INIT.ORA file and ensure REMOTE_LOGIN_PASSWORDFILE=exclusive is set.
• Startup the database (SQLPLUS> STARTUP)[/list]

NOTE: The orapwd utility presents a security risk in that it receives a password from the command line. This password is visible in the process table (at least as long as orapwd is running) and in the shell's history file of many systems. Administrators needs to be aware of this!

Adding users to Password File:

One can select from the SYS.V_$PWFILE_USERS view to see which users are listed in the password file. New users can be added to the password file by granting them SYSDBA or SYSOPER privileges, or by using the orapwd utility.

GRANT SYSDBA TO scott;

What is a Virtual Private Database?

Oracle 8i introduced the notion of a Virtual Private Database (VPD). A VPD offers Fine-Grained Access Control (FGAC) for secure separation of data. This ensures that users only have access to data that pertains to them. Using this option, one could even store multiple companies' data within the same schema, without them knowing about it.

VPD configuration is done via the DBMS_RLS (Row Level Security) package. Select from SYS.V$VPD_POLICY to see existing VPD configuration.

What is Fine Grained Auditing?

Fine Grained Auditing (DBMS_FGA) allows auditing records to be generated when certain rows are selected from a table. A list of defined policies can be obtained from DBA_AUDIT_POLICIES. Audit records are stored in DBA_FGA_AUDIT_TRAIL. Look at this example:

-- Add policy on table with auditing condition...
execute dbms_fga.add_policy('HR', 'EMP', 'policy1', 'deptno > 10');
-- Must ANALYZE, this feature works with CBO (Cost Based Optimizer)
analyze table EMP compute statistics;

select * from EMP where c1 = 11;  -- Will trigger auditing
select * from EMP where c1 = 09;  -- No auditing

-- Now we can see the statements that triggered the auditing condition...
select sqltext from sys.fga_log$;
delete from sys.fga_log$;

What is Oracle Label Security?

Oracle Label Security (formerly called Trusted Oracle MLS RDBMS) uses the VPD (Virtual Private Database) feature of Oracle 8i to implement row level security. Access to rows are restricted according to a user's security sensitivity tag or label. Oracle Label Security is configured, controlled and managed from the Policy Manager, an Enterprise Manager-based GUI utility.