Critical data are stored in database and data leaks can jeopardize the organization. So database security is very important.
Database Security has always been a concern for a Database Administrator. However, Oracle Database is very much secure, even then there are gaps which has to be filled by DBA to make Oracle Database Secure.
Oracle Database Security involves many aspects like security at OS Level, Network Level, S/W level etc. Here, are Top 10 Oracle Database Best Practices
Change Default Installation Passwords
Many times After an installation, the application database instance contains default, open schema with default passwords. These accounts and corresponding passwords are well-known to every one and must be changed in production env. If, DBA will not change any one can login into database using these user name and can harm the database.
With 11g, if the database is created using DBCA,Post database install, the Oracle database configuration assistant (DBCA) automatically locks and expires the majority of the default database user accounts. Additionally DBCA changes the SYSTEM account to the value specified during the installation routine.
Here is the Command to change user password
alter user {user_name} identified by {New_password};
Starting with Oracle version 11g, DBA’s can easily locate any accounts with default passwords (same as username) by using the database view DBA_USERS_WITH_DEF_PWD
Keep Oracle Software Up to Date
- First and foremost important aspect of Database Security is to keep your OS and Database Software up to date.
- Oracle releases Critical Patch Updates every quarterly on the Tuesday closest to 17th day of the month. Oracle has a special bulletin page that describes all of the most Oracle Critical Patch Updates and Advisories. Fortunately, CPU’s are cumulative in nature. You just need to install the latest Oracle CPU to gain all of the security patches from the product’s initial release.
- Same in case of O/S software, Check for Database Server vendor site and apply security patches recommended by vendor.
- It is always good to have latest patch set installed to remove any security vulnerabilities from the system
Implement Password Manage Policy
- It is imperative to have strong password management policy in the organization. For example policy should be like password should be change every three months, Password should have number,literals and special character,Account should be locked after some failed attempt.
- Oracle provides fairly robust password management for Oracle logins.
- In Oracle, logins are assigned an account policy through an Oracle profile. Every login can only be applied one Oracle profile. If no Oracle profile is specified when the login is created, it is assigned the default Oracle profile.
- We can create an new profile with these policies in place and assign that to every user.Some of the parameter are
FAILED_LOGIN_ATTEMPTS 5
PASSWORD_LOCK_TIME 30
PASSWORD_LIFE_TIME 90
PASSWORD_REUSE_TIME 1
PASSWORD_REUSE_MAX 10
Oracle 11g introduced very good password management policy. Oracle 11g provide a inbuilt password verification function which helps to provide strong database user password. Below are steps to enable password verification function.
SQL> SELECT * FROM DBA_PROFILES WHERE RESOURCE_NAME LIKE 'PASSWORD%' AND PROFILE LIKE 'DEFAULT'; PROFILE RESOURCE_NAME RESOURCE LIMIT -------------------- ------------------------- -------- --------------- DEFAULT PASSWORD_LIFE_TIME PASSWORD 180 DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL DEFAULT PASSWORD_LOCK_TIME PASSWORD 1 DEFAULT PASSWORD_GRACE_TIME PASSWORD 7 6 rows selected. $ sqlplus sys as sysdba SQL> @ $ORACLE_HOME/rdbms/admin/utlpwdmg.sql Function created. Profile altered. Function created. SQL> SELECT * FROM DBA_PROFILES WHERE RESOURCE_NAME LIKE 'PASSWORD_VERIFY_FUNCTION' AND PROFILE LIKE 'DEFAULT'; PROFILE RESOURCE_NAME RESOURCE LIMIT ---------- ------------------------- -------- -------------------- DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD VERIFY_FUNCTION_11G
Now, Every new password provided using default profile will follow “VERIFY_FUNCTION_11G” rules.
Remove all unnecessary privileges from the PUBLIC role
We should follow the principle of least privileges granted to user always for good database security.Every user is granted the Public role.At time of Database creation by DBA, few grants are given by default to public. DBA has to review and revoke these grants accordingly
REVOKE EXECUTE ON utl_tcp FROM public; REVOKE EXECUTE ON utl_smtp FROM public; REVOKE EXECUTE ON utl_http FROM public; REVOKE EXECUTE ON utl_mail FROM public; REVOKE EXECUTE ON utl_inaddr FROM public; REVOKE EXECUTE ON utl_file FROM public; REVOKE EXECUTE ON dbms_java FROM public;
Enable Database Auditing
We should put database auditing in place to verify and check if the database is secured
We can audit two operations
Auditing SYS Operations
By default Oracle databases do not audit SQL commands executed by the privileged SYS, and users connecting with SYSDBA or SYSOPER privileges. If your database is hacked, these privileges are going to the be the hackers first target. Fortunately auditing SQL commands of these privileged users is very simple:
sqlplus> alter system set audit_sys_operations=true scope=spfile;
Enable Database Auditing
Again, by default Oracle auditing of SQL commands is not enabled by default. Auditing should be turned on for all SQL commands. Database auditing is turned on with the audit_trail parameter:
alter system set audit_trail=DB,EXTENDED scope=spfile;
Note: The command above would enable auditing from the database, but not the database vault information, into the table SYS.AUD$.
alter system set audit_trail=OS scope=spfile;
Note :Database auditing directs all audit records to the operating system’s audit trail. The audit information will be populated in the OS file.
Set parameter AUDIT_FILE_DEST to the directory where the audit records should be stored.When not set,AUDIT_FILE_DEST defaults to $ORACLE_HOME/rdbms/audit.
alter system set audit_trail=xml,EXTENDED scope=spfile;
Note:Enables database auditing and prints all columns of the audit trail, including SqlText and SqlBind values and writes all audit records to XML format OS files.
we need to restart the database to put these setting into effect.We should also regularly check the audit logs for hack.
ADD IP Restriction and CONNECT_TIMEOUT
A DBA can restrict access to Database server from specific IP address only and if this happens, Database will be accessed from given IP addresses only. To achieve this DBA needs to do some entries in $TNS_ADMIN/sqlnet.ora file. One prerequisite for this is your IP’s should be static. DHCP will not help, since it used to change IP address.
Step to add IP Restriction in Database.
tcp.validnode_checking = YES
tcp.excluded_nodes = {list of IP addresses}
tcp.invited_nodes = {list of IP addresses}
CONNECT_TIMEOUT is another help for DBA to make sure Database connection from client to server completes in give seconds. DBA has to add CONNECT_TIMEOUT parameter in to $TNS_ADMIN/listener.ora file to specifies the time, in seconds, for a client to establish an Oracle Net connection to the database instance.
Restrict Network Access with Firewalls:
Always keep your database servers behind firewalls. With the help of firewall, System admin can make sure access to Database Server is given to known network routes only. Ask you system admin to open only required ports on DB servers like 1521 etc.
Disable XDB Protocol
To support XDB, the TNS Listener process listens on two additional TCP ports: 2100 for ftp access and 8080 for http access. Normally, DBA need not this XDB database.
To disable XDB, remove or comment out the line in init.ora/spfile.ora that reads
*.dispatchers='(PROTOCOL=TCP) (SERVICE=sidXDB)’
Data security
- Today database contains sensitive data and you should consider using encryption to make sure it stays secure. Being able to identify the data that needs this extra level of protection is the first step. Financial information, personnel data and classified information are all good candidates for encryption.
- Oracle offers many different ways to encrypt data, both inside and outside the database using the separately licensed Advanced Security Option.
- (TDE) can be used to encrypt data in the database and will automatically decrypt it when queried by anyone with appropriate privileges, making it transparent to the application. This will protect the data as it sits in the datafiles but won’t protect it from users. In other words, if someone steals your actual datafiles, they wouldn’t be able to read the data because it is encrypted
- You can also encrypt SQLNet traffic to and from your database with the Advanced Security Option. This can be setup via the NetManager GUI or by setting various
- SQLNET.ENCRYPTION* and SQLNET.CRYPTO* parameters in the sqlnet.ora on both client and server
Monitor the Database security on Regular basis
Things change quite frequently in today’s world. It is important to Monitor the Database security on Regular basis
Hope you find this Oracle Database Security Best Practices articles interesting and Useful. Please do provide the feedback
Related Articles
oracle dba interview questions and answers : check out awesome oracle dba interview questions and answers to succeed in any oracle database interviews . This will test your knowledge on various fronts
Oracle RAC interview Questions : Check out top 40 Useful Oracle RAC interview questions. I have covered questions in both Oracle clusterware and Oracle Database RAC
alter user identified by values : Learn about Oracle password ,stored in database, algorithm , how to login as user without changing the oracle password using alter user identified by values
ORA-28002 : Check out this post on how to resolve ORA-28002 the password will expire. What can be done to altogether avoid it by creating new profile