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…

Oracle SYSDBA and SYSOPER Difference

jpramaniktech

Broadly following are the essential difference between SYSDBA and SYSOPER Privs

SYSDBA is like the GOD of the Oracle Database with all and every priviledges.

SYSDBA SYSOPER
START & STOP(SHUTDOWN) Database START & STOP(SHUTDOWN) Database
BACKUP & RECOVER Database  BACKUP & RECOVER Database
ARCHIVE Database  ARCHIVE Database
CREATE & DROP Database
VIEW USER Data

View original post

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

Access Control Using Procedure on Oracle Database

  • User được tương tác với dữ liệu thông qua thực thi Procedure.
  • Có 2 dạng Procedure cần quan tâm:
    • Definer-rights Procedure (thực thi dưới quyền của User làm chủ Procedure): user chỉ cần được cấp quyền EXECUTE là có thể thực hiện được procedure mà không cần phải có quyền trên các đối tượng mà procedure tham chiếu tới.
    • Invoker-rights Procedure (thực hiện dưới quyền của user gọi thực thi nó): user thực thi cần phải được cấp quyền trên các đối tượng mà Procedure tham chiếu tới.

– Ví dụ:

Có 2 bảng SYS.USERS và DUC.USERS có các record như sau:

SYS.USERS

1

DUC.USERS

2

Trong đó user DUC không có quyền select trên table SYS.USERS. Bây giờ ta tạo 2 procedure bằng quyền SYS để xem sự khác biệt.

Definer-rights Procedure

3

Invoker-rights Procedure

4

Sau khi cấp quyền thực thi 2 procedure cho user DUC, ta kết nối database bằng user DUC và gọi thực thi 2 procedure này, chú ý xem kết quả trả về:

5

Oracle Audit Vault Installation – Step by Step

Phạm Bảo Nguyên's Blog

Oracle Audit Vault Installatio n

(Step by Step)

1. Audit Vault Server install:

$cd /source/avserver

$./runInstaller

2. Tạo user cho Audit vault server

$ sqlplus avmanager/manager-123

SQL> create user agentav identified by xxxx;

3. Add agent name vào Audit vault server

$ avca add_agent -agentname  agent_fcrac1 -agenthost  fcrac1 -agentusr agentav

AVCA started

Adding agent…

Agent added successfully.

4. Cài đặt Audit vault agent:

$cd  /source/avagent/hpux_ia64

$./runInstaller

5. Cấu hình Audit vault agent:

On Source Database (On Client):

$sqlplus / as sysdba

SQL> CREATE USER audituser IDENTIFIED BY xxxxx;

SQL> exit

$cd $AVAGENT_HOME/av/scripts/streams/source

$sqlplus / as sysdba

SQL>@zarsspriv.sql audituser SETUP

Granting privileges to AUDITUSER … Done.

SQL>@zarsspriv.sql audituser REDO_COLL

Granting privileges to AUDITUSER … Done.

SQL>exit

ON Audit Vault Server:

$ avorcldb add_source -src fcrac1-vip:1521:naprod -srcname fcrac1  -agentname  agent_fcrac1

$avorcldb add_collector -srcname fcrac1  -agentname agent_fcrac1 -colltype OSAUD -orclhome /u01/app/db

$avorcldb add_collector -srcname fcrac1  -agentname agent_fcrac1 -colltype DBAUD

On Source Database (On…

View original post 197 more words