The Database Design Resource Center



Security settings in Oracle

This article on security settings in Oracle explains how you can control users' ability to execute system operations and to access schema objects by using privileges, roles, and security policies.

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
After creating a new user, assign the required privileges:
  • 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
By default security settings in Oracle are 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 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


Exclusive interviews with:
Steven Feuerstein, PLSQL expert
Donald Burleson, Top IT consultant


Free eBook

Subscribe to my newsletter and get my ebook on Entity Relationship Modeling Principles as a free gift:


What visitors say...

"I just stumbled accross your site looking for some normalization theory and I have to say it is fantastic.

I have been in the database field for 10+ years and I have never before come across such a useful site. Thank you for taking the time to put this site together."

Mike, USA

Read more Testimonials



Database Normalization eBook:


Database Normalization eBook




Copyright © www.databasedesign-resource.com /
All rights reserved.
All information contained on this website is for informational purposes only.
Disclaimer: www.databasedesign-resource.com does not warrant any company, product, service or any content contained herein.

Return to top

Copyright acknowledgement note:

The name Oracle is a trademark of Oracle Corporation.
The names MS Access/MS SQL Server are trademarks of Microsoft Corporation.
Any other names used on this website may be trademarks of their respective owners, which I fully respect.