Security settings in OracleThis 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:
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:
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:
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:
Look at this simple example:
CREATE PROFILE my_profile LIMIT
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:
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.
Free eBookSubscribe 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.Read more Testimonials
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."
Theory & Practice
SQL Server DBA
Install SQL Server
Database Normalization eBook:
Copyright © www.databasedesign-resource.com / Alf A. Pedersen
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
The name Oracle is a trademark of Oracle Corporation.