Security settings in Oracle

You should grant a privilege only to a user who absolutely requires the privilege to accomplish necessary work.

Excessive granting of unnecessary privileges can compromise security.

Security settings in Oracle: Roles

Because roles allow for easier and better management of privileges, you should normally grant privileges to roles and not to specific users. For security settings in Oracle there are two distinct categories of privileges:

• System privileges
• Schema object privileges

A schema object privilege is a privilege or right to perform a particular action on a specific table, view, sequence, procedure, function, or package.

Different object privileges are available for different types of schema objects.

For example, the privilege to delete rows from the table DEPT is an object privilege. Some schema objects such as clusters, indexes, triggers, and database links do not have associated object privileges; their use is controlled with system privileges.

A schema object and its synonym are equivalent with respect to privileges; that is, the object privileges granted for a table, view, sequence, procedure, function, or package apply whether referencing the base object by name or using a synonym.

Look at these examples:

• CREATE USER scott IDENTIFIED BY tiger -- Assign name and password
• DEFAULT TABLESPACE tools -- Assign space for table and index segments
• TEMPORARY TABLESPACE temp; -- Assign sort space
• DROP USER scott CASCADE;-- Remove user

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

Oracle user accounts can be locked, unlocked, forced to choose new passwords, etc. for security settings in Oracle.

For example, all accounts except SYS and SYSTEM will be locked after creating an OracleXiDB database using the DB Configuration Assistant (DBCA). DBA's must unlock these accounts to make them available to users.

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

Also, passwords don't ever expire. This means that one can hack an account for years without ever locking the user.

From OracleX 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;

Oracle DBAs and operators typically use administrative accounts to manage the database and database instance. An administrative account is a user that is granted SYSOPER or SYSDBA privileges. 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. This password file is created with the orapwd utility.

If an administrative user belongs to the DBA group on Unix, or the "ORA_DBA" (ORA_sid_DBA) group on NT, 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 NetX are classified as non-secure. Look at this example:

connect sys/password as sysdba

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 as part of your security settings in Oracle:

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

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 of many systems.

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;

This article on security settings in Oracle is meant to be a primer. Read full Oracle documentation for further details.

Return to installing the Oracle database