Tag: Oracle Osama

The Fastest Way to Create SSH between Servers

Posted on by Osama Mustafa in Operating system
In this short Topic i will provide the 3 steps to create SSH ( User Equivalence ) Without Password you can find lot of way but it’s just simple way and don’t need much steps to create SSH between server.

Introduction for SSH, “Ssh is a secure remote login program that is similar to rlogin and rsh. The major difference between ssh and other remote login programs is that ssh encrypts the password and other information so that it can’t be “sniffed” by others as you type it. Ssh also sets up X11 connections, so the DISPLAY variable does not have to be set on remote machines. Scp is another program used to securely copy files from one host to another.”

Example To Use SSH in Oracle : Real Application Cluster ( RAC ).

The Steps :

#1:

Create New SSH Key

oracle@PrimNode$ ssh-keygen
Generating public/private rsa key pair.
Enter file in which to save the key (/home/oracle/.ssh/id_rsa):[Enter key]
Enter passphrase (empty for no passphrase): [Press enter key]
Enter same passphrase again: [Pess enter key]
Your identification has been saved in /home/oracle/.ssh/id_rsa.
Your public key has been saved in /home/oracle/.ssh/id_rsa.pub.
The key fingerprint is:
33:b3:fe:af:95:95:18:11:31:d5:de:96:2f:f2:35:f9

#2:

This Step will Just copy the password File Generated in Step 1 to Server 2.

oracle@PrimNode$ ssh-copy-id -i ~/.ssh/id_rsa.pub You need to use ip/hostname to another server.
oracle@Server2 password:
Now try logging into the machine, with “ssh ‘remote-host'”, and check in:
.ssh/authorized_keys
to make sure we haven’t added extra keys that you weren’t expecting.

#3:

Just Check

oracle@PrimNode$ ssh Server2
Last login: Thu Mar 28 01:54:21 2013 from primora10g
[Test it no password]
oracle@Server2$

Thank you
Osama Mustafa

Crack Oracle Password using DBMS_METADATA

Posted on by Osama Mustafa in Database
First i would thank my friend Xavier Picamal for remind me in this way, another way to crack oracle password yo could consider it the fastest way to crack oracle password.

SQL> SELECT dbms_metadata.get_ddl(‘USER’,’TEST’) from dual ;

DBMS_METADATA.GET_DDL(‘USER’,’TEST’)
——————————————————————————–

   CREATE USER “TEST” IDENTIFIED BY VALUES ‘1E1A7CDA2ED2730E’
      DEFAULT TA

SQL> conn / as sysdba
Connected.

SQL> alter user Test identified by Test_123 ;
User altered.

SQL> conn test/test_123
Connected.

SQL> alter user Test identified by  VALUES ‘1E1A7CDA2ED2730E’
  2  ;

User altered.

SQL>
SQL>
SQL> conn Test/test;
Connected.
SQL>

 Or you could use Hash Value and use another software to cracks Such as : soonerorlater , Online Crack Website.

Thank you
Osama Mustafa

Crack Oracle Password Example

Posted on by Osama Mustafa in Database
Brute Force One of hacking way used application programs to decode encrypted data such as passwords or Data Encryption Just as a criminal might break into, or “crack” a safe by trying many possible combinations, a brute force cracking application proceeds through all possible combinations of legal characters in sequence. Brute force is considered to be an infallible, although time-consuming, approach.

To help prevent dictionary brute-force attacks many systems will only allow a user to make a mistake in entering their username or password three or four times. If the user exceeds these attempts, the system will either lock them out of the system or prevent any future attempts for a set amount of time.

Today i will provide example how to crack oracle password using Tools called orabf  please remember it’s only example and there’s million tools can do the same i will mention it later in this Topics and work on them in another thread.

Orabf is an extremely fast offline brute force/dictionary attack tool that can be used when the particular username and hash are known for an Oracle account.  Obviously the speed of the brute force attack slows down the longer the amount of characters that it is trying to brute force with but for short username/hash combinations it can be over a million tries per second. 

and you can visit the website here.

Check the below example :

set head off
set feed off
set verify off
Spool Hacker.lis
select password||’:’||username from dba_users
Spool off ;

Output sample for the file look like :

PASSWORD||’:’||USERNAME
————————————————————-
2D594E86F93B17A1:SYSTEM
8A8F025737A9097A:SYS
4F538DF5F344F348:MGMT_VIEW
FFF45BB2C0C327EC:DBSNMP
2CA614501F09FCCC:SYSMAN
BCD8F9C79618E694:MARIA
C43474F068EDB100:MARK
FB129C4425FBCF2E:TEST
224D9FB5D377B8E1:JENNIFER
6399F3B38EDF3288:HR
F894844C34402B67:SCOTT

if you have any tag on the column remove them and save the file again since Orabf  don’t read these tags

Because i am using version 0.7.5 the command should be like :

orabfscript hacker.lis -c default.txt

Remember Default.txt is the file already exist in the software and output append inside this file.

Small trick !!! i will provide another example using another software such as :

  • Repscan 
  • Checkpwd 
  • John The Ripper
  •  bfora

Thank you
Osama Mustafa

Check total Size for Ram/memory For Oracle

Posted on by Osama Mustafa in Database
Check total size of Ram/Memory For Each Database :

select decode( grouping(nm), 1, ‘total’, nm ) nm, round(sum(val/1024/1024)) mb
    from
  (
  select ‘sga’ nm, sum(value) val
     from v$sga
   union all
   select ‘pga’, sum(value)
    from v$sysstat
   where name = ‘session pga memory’
  )
   group by rollup(nm)

The Orginial Post from Tom Kyte.

Thank you
Osama Mustafa

Connect to Oracle without Tnsnames.ora

Posted on by Osama Mustafa in Database
Regarding to oracle documentation :

TNSNAMES.ORA is a SQL*Net configuration file that defines databases addresses for establishing connections to them. This file normally resides in the ORACLE HOME\NETWORK\ADMIN directory.

Example how connection define :

orcl1 =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = my-server )(PORT = 1521))
   )
 (CONNECT_DATA =
   (SERVICE_NAME = orcl1)
 )
)

Sometimes you can Bypass the tnsnames.ora and connect to sqlplus without even Create new connection, all you have to do is  put all of the connectivity information in your connection string this type of connection called “EZCONNECT”.

sqlplus username/password@[//]host[:port][/service_name]

 and to enable EZCONNECT you should add the below line in sqlnet.ora which is located in the same directory $ORACLE_HOME/network/admin
check the below examples :

NAMES.DIRECTORY_PATH=(ezconnect, tnsnames)

The Below using the default listener port 1521 :

1- sqlplus scott/tiger@myservername/orcl1

Different Listener port will be like the below :

2-  sqlplus scott/tiger@myservername:1522/orcl1

3-   sqlplus USER/PASSWORD@//myservername:1521/orcl1

and if you want to disable EZCONNECT For Secuirty Reasons add the below line in :

NAMES.DIRECTORY_PATH=( tnsnames)

Thank you
Osama Mustafa

behind the scenes : Oracle Procedure Security

Posted on by Osama Mustafa in Database
One of the main Reason using Oracle PL/SQL procedures for controlling data access, One of the main reasons is insecure coding practices. One of the widely used attack techniques on applications is SQL injection. I write before about SQL injection but since it’s big topic and need to be covered in more than one parts.

as reminder what is the SQL Injection : One of Hacking way to manipulate the SQL statements using web applications for access/query database.  While run Web Application, the programmer may directly use the user input without hide or even any validation. This opens a new way for the attacker to access and retrieve data . By sending specially crafted user input.

You need to know that Any dynamic SQL query using invalidated user inputs are vulnerable to SQL injection. Some methods that developers use to  prevent SQL injection are parameterized queries or stored procedures

the parameterized queries approach is the most secure way against SQL injection than the traditional approach of joining string to build a dynamic SQL string, in the second type usually leads to data format problems, you have to worry about how to encode the parameter and you need each company have it’s own way to do that :

Query_sql = “SELECT * FROM emp where emp_id = :emp_Id”;

A stored procedure is a database object just like table, Group of SQL statement that form a logical unit and perform a particular task to execute it you need to call it using Procedure name mostly is used as container for the code but the question is if i use them in my Code is this make me secure against SQL Injection ?
Answer also is simple Not always because if code not set properly then SQL Injection could be happened again.

CREATE OR REPLACE PROCEDURE Test (Param_1 IN VARCHAR2) AS
       sql VARCHAR;
       code VARCHAR;
BEGIN
   Sql := ‘SELECT emp_id, Emp_Name, Job, Sal WHERE’ +
          ‘ Emp_Name=”’ || Param_1 || ””;
   EXECUTE IMMEDIATE sql INTO code;
END;

the Value Param_1 will taken from user input  concatenated with the string,The user input is enclosed in the single quotes and concatenated to a string to form SQL query.so the problem is related to this  Instead of the parameter being a search string to the SQL query, the user input has become the part of the query as it is enclosed inside the single quotes. If the user enters the values as 1' or '1'='1'.
Then This Stored Procedure is Not secure as we think.

So How to Write Secure Procedure ?

One Answer : Test Your Code and You should know what you write, If you query working fine that not mean its secure and no hacker will get in.

Validate inputs that comes from users, also like i mention before Use parametrized stored procedure with embedded parameters, don’t forget to Use a low privileged users and give right and correct role/Privileges to application users and finally avoid use of dynamic SQL queriesif you have another way.

so as conclusion is if you are using Stored procedure correctly then you are pretty much safe from SQL Injection and always remember when you attend to do this don’t use :

1 – Dynamic SQL inside the Stored procedure.
2 – try to avoid concatenated string.

Thank you
Osama Mustafa


Switchover_status is Not Allowed

Posted on by Osama Mustafa in Database

Check On the Primary database and the following status will appear to you :

SQL> select name ,open_mode, database_role, switchover_status from v$database;

NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS

MAN MOUNTED PHYSICAL STANDBY NOT ALLOWED

To make sure everything is Ok on both Side Primary and Standby :

SQL> select name ,open_mode, database_role, switchover_status from v$database;

NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS

MAN READ WRITE PRIMARY TO STANDBY

 Another Check :

On Primary Database :

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

56

At Standby :
SQL> select max(sequence#) from v$log_history;

MAX(SEQUENCE#)

56

You Solve this problem By Fire the below command :

SQL > alter database commit to switchover to physical standby with session shutdown
 
The Above Command Should be Run on Primary Database to Generate Special marker on Online Redolog Headers Which Mean transfer the archive to standby , after that the status should be changed.

Please check the below Link from OTN Forum that describe the same problem also and The Solution is Mentioned already there:

1-  Not allowed status in primary db switch_over status  Press here.
2- Not allowed Issue while DR switchover Press here.

Thank you
Osama Mustafa

Database Link Secuirty Issue

Posted on by Osama Mustafa in Database
“Use the CREATE DATABASE LINK statement to create a database link. A database link is a schema object in one database that enables you to access objects on another database. The other database need not be an Oracle Database system. However, to access non-Oracle systems you must use Oracle Heterogeneous Services.”

But did you ask your self before about the impact of using Oracle Database link ? and how to secure my database link ?

One Of Common Issue that you need to be aware of is Privileges , When you create database link most of users use DBA Role which mean user will able to do anything he want in database,which mean
who gains access to a database link can execute queries with the privileges of the DBLINK account  to avoid this try to create user with less Privileges he needs.

Another issue in 10g When you create database link check the below :

CREATE DATABASE LINK “TEST_LINK” CONNECT TO “Test” IDENTIFIED BY Test ;

Database link created. 

After that check the below table :

SQL> select name, userid, passwordx from sys.link$ where name=’TEST_LINK.REGRESS.RDBMS.DEV.US.ORACLE.COM’;

NAME     USERID  PASSWORDX
——————————————————————————–
TEST_LINK.REGRESS.RDBMS.DEV.US.ORACLE.COM OSAMA   05CB53401E442441B428B900A97DE31A10

as you see the password is saved as hash, and can be decrypt .

But what if :

SQL> SELECT DBMS_METADATA.GET_DDL(‘DB_LINK’,’TEST_LINK.REGRESS.RDBMS.DEV.US.ORACLE.COM’) from dual ;

Check the output below :

CREATE DATABASE LINK “TEST_LINK”
CONNECT TO “Osama” IDENTIFIED BY VALUES ‘05CB53401E442441B428B900A97DE31A10‘

another security Issue of using Database link.

Imagine what could be happened next.

Thank you
Osama Mustafa

Check Oracle EBS URL

Posted on by Osama Mustafa in Application
You can the link for Login Page in oracle Apps by fire the below query in sqlplus as apps user :

Query #1 :
SELECT home_url
  FROM icx_parameters;


Query #2:

SELECT profile_option_value  FROM fnd_profile_option_values WHERE profile_option_id= (SELECT profile_option_id  FROM fnd_profile_options WHERE profile_option_name = ‘APPS_FRAMEWORK_AGENT’)  AND level_value = 0;

Thank you
Osama Mustafa

How to Become Oracle DBA

Posted on by Osama Mustafa in Others
How to become Oracle DBA ?

This question asked on lot of blogs for Guru People , Before start thinking about that question you need to ask yourself some question.

Do you love to work with pressure, Do you Love working for long hours ?  Do you use your free time to devour the latest in technology from internet and reports? Do you love to work during weekend ? Last question is Are you able to deal with Sensitive & critical data with no mistake ? If all you answer is Yes,then welcome to Oracle DBA World where the human becoming theory.

Oracle is not easy world and it’s not that hard but you need to know it’s Complex, Just as note Don’t expect to be rich also make your rule is “love what you do and do what you love”.

 Recently i saw Threads On OTN Forum where the poster was asking for information about starting his Career as Oracle DBA, I will advice about that and hope it will be useful, if you have any programming language skills it will make your job easier it’s very useful to understand what you see as DBA you work with Development team and negotiate them about performance and their requirement and understand them is the best, On the other hand if you don’t have any Experience you can’t stop here and saying to yourself i can’t become Oracle DBA Start learning and it’s not bad idea to take courses such as SQL-Fundemtals I & II.

Don’t forget another thing start working on your Oracle training and education Reading is not enough it’s only give you concept and the idea behind that thing, Learning operating system administration skills, and what i mean by Operating system not just windows you need to learn about Linux, Unix …. Etc and how to manage them and using command line, learn batch & Shell Scripting is very useful, Don’t forget to take look at Oracle Documentation everytime you can is the first and the best reference all the time, You need to learn about something read from the source don’t start with others articles, and don’t understand me wrong with that everyone has his point but stat with source and see other point to create yours.

There’s also oracle magazine latest technology and Some tips/trick to do something you can subscribe with it,Maybe you don’t understand something so you need someone to explain it to you Oracle OTN Forum is available to you for free, experts and guru will answer you, Books will increase your knowledge and proof your point.

Create your own Vmware and test don’t make something passed without testing, Practical way will proof and increase your knowledge, then let’s make all the above as point :

  • Attend training courses
  • Read Books
  • Read Magazine.
  • Visit Oracle Documentation site and blogs.
  • Check OTN Forum
  • Talk with DBA

Bookmark this website,
Very Useful & Good Starting Point to become Oracle DBA.

long topic to discuss and need of you lot of working, If you want to be a DBA, find one or two methods of acquiring knowledge that you are comfortable with. Take training courses, read books, whatever. and i will update this topic also to become Comprehensive.

Thank you
Osama Mustafa