Hướng dẫn cài đặt CSDL Oracle trên CentOS [4]

Create a Database.
[1] Login with the “oracle” user and input a command “dbca” like follows.

[oracle@db01 ~]$ dbca

[2] Click “Next” to proceed.

[3] Select “Create Database” and go next.

[4] This example selects “General Purpose ***” and go next.

[5] Set Grobal Database name and SID like follows. Input any one you like.

[6] This example goes next with keeping default.

[7] Set passwords. Please set a password for a user for security.

[8] This example goes next with keeping default “File System”.

[9] Configure recovery settings. If you’d like to change it, Set it.

[10] Configure sample schema and scripts. If you’d like to add them, Set them.

[11] Configure memory settings. After setting, go to next tab.

[12] Specify max processes.

[13] Set Character setting.

[14] Select a connection mode. If your server does not have many clients, Select Dedicated server mode. If your server has many clients, Select Shared server mode.

[15] Confirm parameters for Storage settings. If you’d like to change, set them.

[16] Configuration completed. Click “Finish” button to finish.

[17] Confirm settings and Click “OK” if all are OK.

[18] Database creation starts.

[19] After completing to create a database, Click “Exit” to finish.

[20] Access to the URL that is shown on finished screen above with web browser, then follwing screen is shown. Input a user name and password that you configured on the section [6].

[21] Just logined. It’s possible to manage the database on here.

Advertisements

Hướng dẫn cài đặt CSDL Oracle trên CentOS [2]

Install Oracle Database 11g R2.
[1] Login and work with “oracle” user which you create in previous section.

[2] Download Oracle Databse 11g R2 for Linux and upload on your server.
http://www.oracle.com/technology/software/products/database/index.html
[3] After uploading Oracle files, move to a tmp directory and run an Installer like follows.

[oracle@db01 ~]$ cd tmp
[oracle@db01 tmp]$ unzip linux.x64_11gR2_database_1of2.zip
[oracle@db01 tmp]$ unzip linux.x64_11gR2_database_2of2.zip
[oracle@db01 tmp]$ ./database/runInstaller

[4] Oracle Installer starts like follows. First, Set your email address and password for receiving some infomation from Oracle like security issues and so on.

[5] On this example, Select “Install database software only”.

[6] On this example, Select “Single Instance Database ***”.

[7] Select your language.

[8] Select which edition you install.

[9] Specify the base directory and files for Oracle. On this example, keep default and proceed to next.

[10] Specify the installed directory. On this example, keep default and proceed to next.

[11] Specify the priviledged group. On this example, keep default and proceed to next.

[12] Checking settings runs automatically for requirements of installing Oracle. Generally, some packages requirements failed like follows, but it’s noplobmen because most of them are higher version packages than requirements. Ignore them if the result is the same to the follows. (For only “pdksh”, it’s not a big ploblem if it is not installed. )

[13] The summary is shown for configuration. Click “Finish” if it’s OK all.

[14] Installation starts.

[15] Following screen is shown, then open a terminal and execute follwong commands with the root user.

[root@db01 ~]# /usr/oracle/oraInventory/orainstRoot.sh 
Changing permissions of /usr/oracle/oraInventory.
Adding read,write permissions for group.
Removing read,write,execute permissions for world.

Changing groupname of /usr/oracle/oraInventory to oinstall.
The execution of the script is complete.
[root@db01 ~]#
[root@db01 ~]# /usr/oracle/app/product/11.2.0/dbhome_1/root.sh 
Running Oracle 11g root.sh script...
The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME= /usr/oracle/app/product/11.2.0/dbhome_1
Enter the full pathname of the local bin directory: [/usr/local/bin]:    # Enter
    Copying dbhome to /usr/local/bin ...
    Copying oraenv to /usr/local/bin ...
    Copying coraenv to /usr/local/bin ...
Creating /etc/oratab file...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
Finished product-specific root actions.

[16] Installation completed. Click “Close” button.

[17] Configure some settings for Oracle user.

[oracle@db01 ~]$ vi ~/.bash_profile
# add follows to the end
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export PATH=$PATH:$ORACLE_HOME/bin
[oracle@db01 ~]$ source ~/.bash_profile 
[oracle@db01 ~]$ rm -rf tmp

Hướng dẫn cài đặt CSDL Oracle trên CentOS [1]

Bài này tôi sẽ hướng dẫn các bạn cài đặt CSDL Oracle phiên bản 11g R2 trên CentOS 6.4. Yêu cầu phải có giao diện đồ họa vì cần tương  tác trên GUI.

Đầu tiên là cài đặt các package cần thiết:

[root@db01 ~]# yum -y install binutils compat-libstdc++-33
 elfutils-libelf elfutils-libelf-devel glibc glibc-common
 glibc-devel gcc gcc-c++ libaio libaio-devel libgcc libstdc++
 libstdc++-devel make sysstat unixODBC unixODBC-devel

Sau đó tùy chỉnh thông số trong kernel:

[root@db01 ~]# vi /etc/sysctl.conf
# make it comment
#net.bridge.bridge-nf-call-ip6tables = 0
#net.bridge.bridge-nf-call-iptables = 0
#net.bridge.bridge-nf-call-arptables = 0
# add at the last line
net.ipv4.ip_local_port_range = 9000 65500
fs.file-max = 6815744
kernel.shmall = 10523004
kernel.shmmax = 6465333657
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.core.rmem_default=262144
net.core.wmem_default=262144
net.core.rmem_max=4194304
net.core.wmem_max=1048576
fs.aio-max-nr = 1048576
[root@db01 ~]# sysctl -p
net.ipv4.ip_forward = 0
net.ipv4.conf.default.rp_filter = 1
net.ipv4.conf.default.accept_source_route = 0
kernel.sysrq = 0
kernel.core_uses_pid = 1
net.ipv4.tcp_syncookies = 1
net.ipv4.ip_local_port_range = 9000 65500
fs.file-max = 65536
kernel.shmall = 10523004
kernel.shmmax = 6465333657
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.core.rmem_default = 262144
net.core.wmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_max = 1048576
fs.aio-max-nr = 1048576

Ta cần tạo một user riêng để chạy chương trình Oracle database:

[root@db01 ~]# groupadd -g 200 oinstall
[root@db01 ~]# groupadd -g 201 dba
[root@db01 ~]# useradd -u 440 -g oinstall -G dba -d /usr/oracle oracle
[root@db01 ~]# vi /etc/pam.d/login
# near line 14: add
session required pam_selinux.so open
session required pam_namespace.so
session required pam_limits.so
session optional pam_keyinit.so force revoke
session include system-auth
-session optional pam_ck_connector.so

[root@db01 ~]# vi /etc/security/limits.conf
# add at the last line
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
[root@db01 ~]# vi /etc/profile
# add at the last line
if [ $USER = "oracle" ]; then
      if [ $SHELL = "/bin/ksh" ]; then
           ulimit -p 16384
           ulimit -n 65536
      else
           ulimit -u 16384 -n 65536
      fi
fi

Chuyển sang user “oracle” và cấu hình một số thứ:

[oracle@db01 ~]$ chmod 755 /usr/oracle
[oracle@db01 ~]$ mkdir /usr/oracle/app
[oracle@db01 ~]$ chmod 775 /usr/oracle/app
[oracle@db01 ~]$ mkdir /usr/oracle/oradata
[oracle@db01 ~]$ chmod 775 /usr/oracle/oradata
[oracle@db01 ~]$ vi ~/.bash_profile
# add at the last line
umask 022
export ORACLE_BASE=/usr/oracle/app
[oracle@db01 ~]$ mkdir tmp # create a temporary dir for installation

Xem tiếp phần 2…

How To Fix SQL Injection In Oracle

How to Fix SQL Injection using Oracle Database Code

A stored procedure is a logical set of SQL statements, performing a specific task; it is compiled once and stored on a database server for all clients to execute; they are used very commonly for the many benefits that they provide. Often times, stored procedures are blindly considered secure; however, it is not so always. SQL Injection is a concern when dynamic SQL is handled incorrectly in a stored procedure.

In Oracle, dynamic SQL can be used in 1. EXECUTE IMMEDIATE statements, 2. DBMS_SQL package and 3. Cursors. This article illustrates how dynamic SQL can be built securely to defend against SQL injection attacks.

Execute Immediate Statement

Secure Usage
--Execute Immediate - named parameter
sqlStmt := 'SELECT emp_id FROM employees WHERE emp_email = :email';
EXECUTE IMMEDIATE sqlStmt USING email;

and

--Execute Immediate - positional parameter
sqlStmt := 'SELECT emp_id FROM employees WHERE emp_email = :1 and emp_name = :2';
EXECUTE IMMEDIATE sqlStmt USING email, name;
Vulnerable Usage
sqlStmt:= 'SELECT emp_id FROM employees WHERE emp_email = ''' || email || '''';
EXECUTE IMMEDIATE sqlStmt INTO empId;

Here, the input variable “email” is used directly in the query using concatenation; opening up the possibility to manipulate the “where” clause.

DBMS_SQL Package

Secure Usage
sqlStmt := 'SELECT emp_id FROM employees WHERE emp_email = :email';
empcur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(empcur, sqlStmt, DBMS_SQL.NATIVE);
DBMS_SQL.BIND_VARIABLE(empcur, ':email', email);
DBMS_SQL.EXECUTE(empcur);

Here, bind variable is used to set data to query, hence sql injection proof.

Vulnerable Usage
sqlStmt := 'SELECT emp_id FROM employees WHERE emp_email = ''' || email || '''';
empcur:= DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(empcur, sqlStmt, DBMS_SQL.NATIVE);
DBMS_SQL.EXECUTE(empcur);

Here, the input variable “email” is used directly in the query using concatenation; opening up the possibility to manipulate the “where” clause.

Cursor with dynamic query

Secure Usage
sqlStmt := 'SELECT emp_id FROM employees WHERE emp_email = :email';
OPEN empcur FOR sqlStmt USING email;

Here, bind variable is used to set data to the query, hence sql injection proof.

Vulnerable Usage
sqlStmt := 'SELECT emp_id FROM employees WHERE emp_email = ''' || email || '''';
OPEN empcur FOR sqlStmt;

Here, the input variable “email” is used directly in the query using concatenation; opening up the possibility to manipulate the “where” clause.

——

Thanks Priya Gnanasundar!

Link: http://software-security.sans.org/developer-how-to/fix-sql-injection-in-oracle-database-code

Password file (orapwd utility) in Oracle

Oracle password file stores passwords for users with administrative privileges.

If the DBA wants to start up an Oracle instance there must be a way for Oracle to authenticate the DBA. Obviously, DBA password cannot be stored in the database, because Oracle cannot access the database before the instance is started up. Therefore, the authentication of the DBA must happen outside of the database. There are two distinct mechanisms to authenticate the DBA:

  1. Using the password file or
  2. Through the operating system (groups). Any OS user under dba group, can login as SYSDBA.

The default location for the password file is:
$ORACLE_HOME/dbs/orapw$ORACLE_SID on Unix, %ORACLE_HOME%\database\PWD%ORACLE_SID%.ora on Windows.

1. REMOTE_LOGIN_PASSWORDFILE

The init parameter REMOTE_LOGIN_PASSWORDFILE specifies if a password file is used to authenticate the Oracle DBA or not. If it set either to SHARED or EXCLUSIVE, password file will be used.

REMOTE_LOGIN_PASSWORDFILE is a static initialization parameter and therefore cannot be changed without bouncing the database.

Following are the valid values for REMOTE_LOGIN_PASSWORDFILE:

NONE – Oracle ignores the password file if it exists i.e. no privileged connections are allowed over non secure connections. If REMOTE_LOGIN_PASSWORDFILE is set to EXCLUSIVE or SHARED and the password file is missing, this is equivalent to setting REMOTE_LOGIN_PASSWORDFILE to NONE.

EXCLUSIVE (default) – Password file is exclusively used by only one (instance of the) database. Any user can be added to the password file. Only an EXCLUSIVE file can be modified. EXCLUSIVE password file enables you to add, modify, and delete users. It also enables you to change the SYS password with the ALTER USER command.

SHARED – The password file is shared among databases. A SHARED password file can be used by multiple databases running on the same server, or multiple instances of an Oracle Real Application Clusters (RAC) database. However, the only user that can be added/authenticated is SYS.

A SHARED password file cannot be modified i.e. you cannot add users to a SHARED password file. Any attempt to do so or to change the password of SYS or other users with the SYSDBA or SYSOPER or SYSASM (this is from Oracle 11g) privileges generates an error. All users needing SYSDBA or SYSOPER or SYSASM system privileges must be added to the password file when REMOTE_LOGIN_PASSWORDFILE is set to EXCLUSIVE. After all users are added, you can change REMOTE_LOGIN_PASSWORDFILE to SHARED.

This option is useful if you are administering multiple databases or a RAC database.

If a password file is SHARED or EXCLUSIVE is also stored in the password file. After its creation, the state is SHARED. The state can be changed by setting REMOTE_LOGIN_PASSWORDFILE and starting the database i.e. the database overwrites the state in the password file when it is started up.

 

2. ORAPWD
You can create a password file using orapwd utility. For some Operating systems, you can create this file as part of standard installation.

Users are added to the password file when they are granted the SYSDBA or SYSOPER or SYSASM privilege.

The Oracle orapwd utility assists the DBA while granting SYSDBA, SYSOPER and SYSASM privileges to other users. By default, SYS is the only user that has SYSDBA and SYSOPER privileges. Creating a password file, via orapwd, enables remote users to connect with administrative privileges.

$ orapwd file=password_file_name [password=the_password] [entries=n] [force=Y|N] [ignorecase=Y|N] [nosysdba=Y|N]

Examples:
$ orapwd file=orapwSID password=sys_password force=y nosysdba=y
$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=secret
$ orapwd file=orapwprod entries=30 force=y
C:\orapwd file=%ORACLE_HOME%\database\PWD%ORACLE_SID%.ora password=2012 entries=20
C:\orapwd file=D:\oracle11g\product\11.1.0\db_1\database\pwdsfs.ora password=id entries=6 force=y
$ orapwd file=orapwPRODB3 password=abc123 entries=10 ignorecase=n
$ orapwd file=orapwprodb password=oracle1 ignorecase=y

There are no spaces permitted around the equal-to (=).

The following describe the orapwd command line arguments.

FILE
Name to assign to the password file, which will hold the password information. You must supply complete path. If you supply only filename, the file is written to the current directory. The contents are encrypted and are unreadable. This argument is mandatory.

The filenames allowed for the password file are OS specific. Some operating systems require the password file to adhere to a specific format and be located in a specific directory. Other operating systems allow the use of environment variables to specify the name and location of the password file.

If you are running multiple instances of Oracle Database using Oracle Real Application Clusters (RAC), the environment variable for each instance should point to the same password file.

It is critically important to secure password file.

PASSWORD
This is the password the privileged users should enter while connecting as SYSDBA or SYSOPER or SYSASM.

ENTRIES
Entries specify the maximum number of distinct SYSDBA, SYSOPER and SYSASM users that can be stored in the password file.

This argument specifies the number of entries that you require the password file to accept. The actual number of allowable entries can be higher than the number of users, because the orapwd utility continues to assign password entries until an OS block is filled. For example, if your OS block size is 512 bytes, it holds four password entries. The number of password entries allocated is always a multiple of four.

Entries can be reused as users are added to and removed from the password file. When you exceed the allocated number of password entries, you must create a new password file. To avoid this necessity, allocate a number of entries that is larger than you think you will ever need.

FORCE
(Optional) If Y, permits overwriting an existing password file. An error will be returned if password file of the same name already exists and this argument is omitted or set to N.

IGNORECASE
(Optional) If Y, passwords are treated as case-insensitive i.e. case is ignored when comparing the password that the user supplies during login with the password in the password file.

NOSYSDBA
(Optional) For Oracle Data Vault installations.

 

3. Granting SYSDBA or SYSOPER or SYSASM privileges
Use the V$PWFILE_USERS view to see the users who have been granted SYSDBA or SYSOPER or SYSASM system privileges for a database.

SQL> select * from v$pwfile_users;
USERNAME SYSDBA SYSOPER SYSASM
-------- ------ ------- ------
SYS      TRUE   TRUE    FALSE

The columns displayed by the view V$PWFILE_USERS are:

Column Description
USERNAME This column contains the name of the user that is recognized by the password file.
SYSDBA If the value of this column is TRUE, then the user can log on with SYSDBA system privilege.
SYSOPER If the value of this column is TRUE, then the user can log on with SYSOPER system privilege.
SYSASM If the value of this column is TRUE, then the user can log on with SYSASM system privilege.

 

If orapwd has not yet been executed or password file is not available, attempting to grant SYSDBA or SYSOPER or SYSASM privileges will result in the following error:
SQL> grant sysdba to satya;
ORA-01994: GRANT failed: cannot add users to public password file

If your server is using an EXCLUSIVE password file, use the GRANT statement to grant the SYSDBA or SYSOPER or SYSASM system privilege to a user, as shown in the following example:

SQL> grant sysdba to satya;
SQL> select * from v$pwfile_users;
USERNAME SYSDBA SYSOPER SYSASM
-------- ------ ------- ------
SYS      TRUE   TRUE    FALSE
SATYA    TRUE   FALSE   FALSE

SQL> grant sysoper to satya;
SQL> select * from v$pwfile_users;
USERNAME SYSDBA SYSOPER SYSASM
-------- ------ ------- ------
SYS      TRUE   TRUE    FALSE
SATYA    TRUE   TRUE    FALSE

SQL> grant sysasm to satya;
SQL> select * from v$pwfile_users;
USERNAME SYSDBA SYSOPER SYSASM
-------- ------ ------- ------
SYS      TRUE   TRUE    FALSE
SATYA    TRUE   TRUE    TRUE

When you grant SYSDBA or SYSOPER or SYSASM privileges to a user, that user’s name and privilege information are added to the password file. If the server does not have an EXCLUSIVE password file (i.e. if the initialization parameter REMOTE_LOGIN_PASSWORDFILE is NONE or SHARED, or the password file is missing), Oracle issues an error if you attempt to grant these privileges.

Use the REVOKE statement to revoke the SYSDBA or SYSOPER or SYSASM system privilege from a user, as shown in the following example:

SQL> revoke sysoper from satya;
SQL> select * from v$pwfile_users;
USERNAME SYSDBA SYSOPER SYSASM
-------- ------ ------- ------
SYS      TRUE   TRUE    FALSE
SATYA    TRUE   FALSE   TRUE

A user’s name remains in the password file only as long as that user has at least one of these three privileges. If you revoke all 3 privileges, Oracle removes the user from the password file.

Because SYSDBA, SYSOPER and SYSASM are the most powerful database privileges, the WITH ADMIN OPTION is not used in the GRANT statement. That is, the grantee cannot in turn grant the SYSDBA or SYSOPER or SYSASM privilege to another user. Only a user currently connected as SYSDBA can grant or revoke another user’s SYSDBA or SYSOPER or SYSASM system privileges. These privileges cannot be granted to roles, because roles are available only after database startup.

If you receive the file full error (ORA-01996) when you try to grant SYSDBA or SYSOPER or SYSASM system privileges to a user, you must create a larger password file and regrant the privileges to the users.

 

4. Removing Password File
If you determine that you no longer require a password file to authenticate users, you can delete the password file and then optionally reset the REMOTE_LOGIN_PASSWORDFILE initialization parameter to NONE. After you remove this file, only those users who can be authenticated by the OS can perform SYSDBA or SYSOPER or SYSASM database administration operations.

Source: http://satya-dba.blogspot.com/2009/11/password-file-in-oracle.html