Archive

Archive for April, 2012

Database Schema Documentation

April 26, 2012 Leave a comment

Tools that provide options to generate database schema documentation are found all over web. But you may end up with a paid version of the tool in the end.

Oracle SQL Developer itself has an option to generate a HTML document on  database schema.

  • Open Oracle SQL Developer.
  • Create a connection to the desired database.
  • Right click on the connection and find ‘Generate DB Doc’ option.
  • Select the option and specify the target directory.

Other open source tools for database schema documentation:

Advertisements

Using MySQL with .Net

April 25, 2012 Leave a comment

Required Components:

  • MySQL 5.0 database server
  • MySQL Connector/Net 1.0.6 or higher
  • MySQL Administrator 1.1 or higher
  • .NET Framework 1.0 or higher
  • Visual Studio 2002 or higher

Download and install MySQL Community Server
MySQL Community Edition is a freely downloadable version of the world’s most popular open source database that is supported by an active community of open source developers and enthusiasts.

Download Connector/Net
Connector/Net is a fully-managed ADO.NET driver for MySQL.

  • Download link : Connector/Net
  • Unzip the ZIP file to get the .MSI file and install it by double clicking on it.

Connect to MYSQL from .NET

  • Go to the solution explorer in Visual Studio and select Add References option.
  • From Add References dialog box, select “MySQL.Data” option and click on Ok.
  • Refer the following code to connect to MYSQL.
// Connection string for a typical local MySQL installation
string cnnString = 
 "Server=localhost;Port=3306;Database=versedb;Uid=root;Pwd=MySecretPassword";

// Create a connection object and data adapter
MySqlConnection cnx = new MySqlConnection(cnnString);
MySqlDataAdapter adapter = new MySqlDataAdapter();

// Create a SQL command object
string cmdText = "SELECT * FROM verse";
MySqlCommand cmd = new MySqlCommand(cmdText, cnx);

// Create a fill a Dataset
DataSet ds = new DataSet();
adapter.SelectCommand = cmd;
adapter.Fill(ds);

// Bind the DataSet
// ... Place your databinding code here ...

If you’re already accustomed to data binding in .NET, 
you should immediately recognize the similarities.  
The only difference is you use the MySqlXxx classes 
instead of the OleDbXxx or SqlXxx classes.
Source/ Reference Links:
Categories: MySQL Tags: ,

Oracle Database Security Checklist

April 24, 2012 Leave a comment

Checklist to keep secure an Oracle Database:

1. Install only what is required
The Oracle Database CD pack contains a host of options and products in addition to the database server. Install additional products and options only as necessary.

2. Lock and Expire Default User Accounts
If a manual (i.e. without using Database Configuration Assistant) installation of Oracle Database is performed, then no default database users are locked upon successful installation of the database server. Left open in their default states, these user accounts can be exploited to gain unauthorized access to data or disrupt database operations.

a) Enterprise Manager Accounts
The preceding list of accounts depends on whether you choose to install Enterprise Manager. If so, SYSMAN and DBSNMP are open as well, unless you configure Enterprise Manager for Central Administration. In this case, the SYSMAN account (if present) will be locked as well.

3. Change Default User Passwords
The most trivial method by which Oracle Database can be compromised is a default database server user account which still has a default password associated with it even after installation.

a) Change default passwords of administrative users
Oracle Database 10g enables you to use the same or different passwords for the SYS, SYSTEM, SYSMAN and DBSNMP administrative accounts. Use different passwords for each: in any Oracle environment (production or test), assign strong, secure, and distinct passwords to these administrative accounts.

b) Change default passwords of all users
Each of the accounts install with a default password that is exactly the same as that user account (For example, user MDSYS installs with the password MDSYS).

c) Enforce password management
Oracle recommends that basic password management rules (such as password length, history, complexity, and so forth) as provided by the database be applied to all user passwords and that all users be required to change their passwords periodically.

Oracle also recommends, if possible, using Oracle Advanced Security (an option to the Enterprise Edition of Oracle Database) with network authentication services (such as Kerberos), token cards, smart cards or X.509 certificates.

4. Enable Data Dictionary Protection
Oracle recommends that customers implement data dictionary protection to prevent users having the ANY system privileges from using such privileges on the data dictionary.

To enable dictionary protection, set the following configuration parameter to FALSE, in the init<sid>.ora control file: O7_DICTIONARY_ACCESSIBILITY = FALSE

5. Practicing the principle of least privilege

a) Grant necessary privileges only
The principle of least privilege is that users be given only those privileges that are actually required to efficiently perform their jobs.

To implement this principle, restrict the following as much as possible:
1) The number of SYSTEM and OBJECT privileges granted to database users, and
2) The number of people who are allowed to make SYS-privileged connections to the database.

b) Revoke unnecessary privileges from the public user group
Revoke all unnecessary privileges and roles from the database server user group PUBLIC. PUBLIC acts as a default role granted to every user in an Oracle database.

The more powerful packages that may potentially be misused are:
1. UTL_SMTP 2. UTL_TCP 3. UTL_HTTP 4. UTL_FILE

c) Grant a role to users only if they need all privileges of the role
Ensure that the roles you define contain only the privileges that reflect job responsibility. If your application users do not need all the privileges encompassed by an existing role, then apply a different set of roles that supply just the right privileges.
Altenatively, create and assign a more restricted role.

d)Restrict permissions on run-time facilities
Do not assign all permissions to any database server run-time facility such as the Oracle Java Virtual Machine (OJVM). Grant specific permissions to the explicit document root file paths for such facilities that may execute files and packages outside the database server.

6. Enforce access controls cffectively and authenticate clients stringently

a) Authenticate client properly
By default, Oracle allows operating-system-authenticated logins only over secure connections, which precludes using Oracle Net and a shared server configuration. This default restriction prevents a remote user from impersonating another operating system user over a network connection.

Setting the initialization parameter REMOTE_OS_AUTHENT to TRUE forces the RDBMS to accept the client operating system user name received over a nonsecure connection and use it for account access. Since clients, such as PCs, are not trusted to perform operating system authentication properly, it is very poor security practice to turn on this feature.

7. Restrict Operating System Access
Limit the number of operating system users. Limit the privileges of the operating system accounts (administrative, root-privileged or DBA) on the Oracle Database host (physical machine) to the least privileges needed for the user’s tasks.

Oracle also recommends:
•     Restricting the ability to modify the default file and directory permissions for the Oracle Database home (installation) directory or its contents. Even privileged operating system users and the Oracle owner should not modify these permissions, unless instructed otherwise by Oracle.
•     Restricting symbolic links.

8. Restrict Network Access

a) Use a firewall
Keep the database server behind a firewall. Oracle Database network infrastructure, Oracle Net (formerly known as Net8 and SQL*Net), offers support for a variety of firewalls from various vendors.

b) Never poke a hole through a firewall
If Oracle Database is behind a firewall, then do not, under any circumstances, poke a hole through the firewall.
For example, do not leave open port 1521 for Oracle Listener to make a connection to the Internet or vice versa.

c) Protect the Oracle listener
Because the listener acts as the database gateway to the network, it is important to limit the consequences of malicious interference:
Restrict the privileges of the listener, so that it cannot read or write files in the database or the Oracle server address space.

Secure administration of the database by doing the following:

1. Prevent online administration by requiring the administrator to have write privileges on the LISTENER.ORA file and the listener password:

Add or alter this line in the LISTENER.ORA file ADMIN_RESTRICTIONS_LISTENER=ON
Then RELOAD the configuration.

2. Use SSL when administering the listener, by making the TCPS protocol the first entry in the address list as follows:

LISTENER=
 (DESCRIPTION=
 (ADDRESS_LIST=
 (ADDRESS=
 (PROTOCOL=tcps)
 (HOST = ed-pdsun1.us.oracle.com)
 (PORT = 8281)))

3. Always establish a secure, well-formed password for the Oracle listener to prevent remote configuration of the Oracle listener.
4. Remove the external procedure configuration from the listener.ora file if you do not intend to use such procedures.
5. Monitor listener activity.

d) Monitor who accesses your systems

Instead of authenticating client computers over the Internet, make use of user authentication which avoids client system issues that include falsified IP addresses, hacked operating systems or applications, and falsified or stolen client system identities.

The following steps improve client computer security:
a) Configure the connection to use SSL.
Using SSL (Secure Sockets Layer) communication makes eavesdropping unfruitful and enables the use of certificates for user and server authentication.
b) Set up certificate authentication for clients and servers such that:

i. The organization is identified by unit and certificate issuer and the user is identified by distinguished name and certificate issuer.
ii. Applications test for expired certificates.
iii. Certificate revocation lists are audited.

e) Check network IP addresses

Use the Oracle Net valid node checking security feature to allow or deny access to Oracle server processes from network clients with specified IP addresses. To use this feature, set the following protocol.ora (Oracle Net configuration file) parameters:

tcp.validnode_checking = YES
tcp.excluded_nodes = {list of IP addresses}
tcp.invited_nodes = {list of IP addresses}

The first parameter turns on the feature whereas the latter parameters respectively deny and allow specific client IP addresses from making connections to the Oracle listener (This helps in preventing potential Denial of Service attacks).

f) Encrypt network traffic

If possible, use Oracle Advanced Security to encrypt network traffic between clients, databases, and application servers.

g) Harden the operating system

Harden the host operating system by disabling all unnecessary operating system services. Both UNIX and Windows platforms provide a variety of operating system services, most of which are not necessary for most deployments. Such services include FTP, TFTP, TELNET, and so forth. Be sure to close both the UDP and TCP ports for each service that is being disabled. Disabling one type of port and not the other does not make the operating system more secure.

9. Apply all security patches

Always apply all relevant and current security patches for both the operating system on which Oracle Database resides and Oracle Database itself, and for all installed Oracle Database options and components.

10. Contact Oracle Security products if you come across a vulnerability in Oracle Database

Source: Based on Oracle Documentaion

MSVCP71.DLL and MSVCR71.DLL Missing on Windows 7

April 22, 2012 Leave a comment

Both MSVCP71.DLL and MSVCR71.DLL are Microsoft C Runtime Library files. Some programs like SQL Developer require you to have any of this files for it to work successfully.

  • Download the DLL files from here.
  • Copy the files to System32 on 32 bit OS and SysWOW64 on 64 bit OS.

 

RMAN Configure

April 4, 2012 Leave a comment
By default the RMAN configuration is stored in the control file. The default values for the configuration parameters can be viewed by using RMAN to connect to the database and issuing the SHOW ALL command.
RMAN> SHOW ALL;
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 
 'E:\ORACLE\PRODUCT\10.1.0\DB_1\DATABASE\SNCFTEST.ORA'; # default
RMAN>

Changing the configuration in RMAN is easy and simple. The output of ‘SHOW ALL’ command helps to reconfigure, like:

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

To reset the configuration parameter to its default value, ‘CLEAR’ can be used:

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP CLEAR;
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN configuration parameters are successfully reset to default value

List of commands that clear several RMAN settings back to their default values:

RMAN> CONFIGURE RETENTION POLICY CLEAR;
RMAN> CONFIGURE BACKUP OPTIMIZATION CLEAR;
RMAN> CONFIGURE DEFAULT DEVICE TYPE CLEAR;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP CLEAR;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT CLEAR;
RMAN> CONFIGURE DEVICE TYPE DISK CLEAR;
RMAN> CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK CLEAR;
RMAN> CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE SBT CLEAR;
RMAN> CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK CLEAR;
RMAN> CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT CLEAR;
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK CLEAR;
RMAN> CONFIGURE CHANNEL DEVICE TYPE SBT CLEAR;
RMAN> CONFIGURE MAXSETSIZE CLEAR;
RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME CLEAR;
RMAN> # CONFIGURE EXCLUDE FOR TABLESPACE USERS_READ_TBS;

1. Retention Policy

Using RMAN a recovery window (have a rolling window of days) or a set redundancy (rolling number of backups) can be set to define the retention policy.

RMAN> configure retention policy to redundancy 2;

new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
new RMAN configuration parameters are successfully stored

2. Controlfile Autobackup

RMAN can be configured to automatically backup the control file when a data file is added or when a backup is taken with CONTROLFILE AUTOBACKUP. By default CONTROLFILE AUTOBACKUP is set to OFF.

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
RMAN>
3. Controlfile Autobackup Storage Directory
To set the location in which to write the control file auto backup:
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO
  'E:\flash_recovery_area\autobackup\%F';
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO
  'E:\flash_recovery_area\autobackup\%F';
new RMAN configuration parameters are successfully stored

4. Backup Set and Storage on Disk

RMAN can store the backup in one or more binary files called a backup set that contain data from one ore more data files, archive logs, control files or an SPFILE. These files are written in an RMAN specific format and be compressed during the creation process. Each individual file in a back set is called a backup piece.

RMAN can also create exact copies of the data files, archive logs, control files and SPFILE. These image copies are not created in an RMAN specific format.

In RMAN the type of backup is configured via the device type to be used to create the backup. By default the default device type is disk and RMAN is configured to write backup sets to disk.

RMAN> SHOW DEFAULT DEVICE TYPE;
RMAN configuration parameters are:
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
RMAN> SHOW DEVICE TYPE;
RMAN configuration parameters are:
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default

5. Backup Set Piece Size

In order for RMAN to read or write data it must have a channel configured. In configuring the channel we can set the FORMAT, or location on disk, in which to read and write backups. The channel is also were we configure the size of the backup piece in the case of backup sets.

Like the CONTROLFILE AUTOBACKUP FORMAT, the CHANNEL FORMAT has a substitution variable %U. The %U specifies a system generated unique file name.

RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 
 'E:\flash_recovery_area\TEST\BACKUPSET\backup_db_%d_S_%s_P_%p_T_%t' MAXPIECESIZE 1 G;
new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 
 'E:\flash_recovery_area\TEST\BACKUPSET\backup_db_%d_S_%s_P_%p_T_%t' MAXPIECESIZE 1 G;
new RMAN configuration parameters are successfully stored

Source / Reference Links:

ORA-04063

April 3, 2012 Leave a comment

Error: ORA-04063

OEM Error when assigning an email id to SYS user:

Internal error. ORA-04063: package body "SYSMAN.EMD_NOTIFICATION" has errors
ORA-06508: PL/SQL: could not find program unit being called:
 "SYSMAN.EMD_NOTIFICATION"
ORA-06512: at "SYSMAN.MGMT_PREFERENCES", line 1536 ORA-06512: at line 1

Cause:

Oracle policy recommends taking away the execute privilege on UTL_SMTP from PUBLIC. This takes away the privileges from SYSMAN as well. SYSMAN needs execute on UTL_SMTP and UTL_TCP to send notifications.

Workaround:

1. Log into the repository database as sys:

grant execute on sys.utl_smtp to sysman;
grant execute on sys.utl_tcp to sysman;

2. Compile:

alter package sysman.emd_notification compile;

References:

Security Policy Violations in OEM

April 3, 2012 Leave a comment

Found this four policy violations under ‘Security’ category in OEM:

1. EXECUTE UTL_FILE privileges to PUBLIC

Recommendation: Oracle recommends that you revoke EXECUTE privileges on powerful packages from PUBLIC

Violation Count:1 Details: Package ­UTL_FILE

Solution:

REVOKE EXECUTE ON UTL_FILE FROM PUBLIC;

Anyone that happens to get any userid and password on your database will be able to use UTL_FILE.

This may be legitimate, or it may be malicious – and you have absolutely no control over it if the package is granted to public.

For example, person A legitimately dumps confidential data (or has a comma-delimited spreadsheet with payroll info) in the UTL_FILE directory. Person B, a casual employee who is unhappy and about to be fired, happens to get access to the database because the demo user SCOTT is unlocked with password TIGER. Person B can now access the confidential data.

Oracle uses the ‘least privilege’ security principal. That means Oracle recommends several packages, such as UTL_FILE, be granted execute on a need-to-use basis instead of simply granting to public.

2. Excessive PUBLIC EXECUTE privileges

Recommendation: Oracle recommends that you revoke EXECUTE privileges on powerful packages from PUBLIC

Violation Count:4 Details: Package ­UTL_SMTP, ­DBMS_RANDOM, ­UTL_TCP, ­UTL_HTTP

Solution:

REVOKE EXECUTE ON UTL_SMTP FROM PUBLIC;
REVOKE EXECUTE ON DBMS_RANDOM FROM PUBLIC;­
REVOKE EXECUTE ON UTL_TCP­ FROM PUBLIC;
REVOKE EXECUTE ON UTL_HTTP FROM PUBLIC;

3. Well-known accounts Security

Recommendation: Oracle recommends that you to expire and lock well-known accounts

Violation Count:1 Details: Account­ OUTLN(open)

4. Default passwords Security

Recommendation: Oracle recommends that all default passwords be changed

Violation Count:1 Details: Account­OUTLN­

References: