Oracle Security

Oracle Security

1-4 Ways to Audit in Oracle
2-Connecting to a schema without knowing the password
3-view log file by logMiner 
4-view history of DML statement
5-Unlock and change user name password
6-Grant SYSDBA privilege to other user
7-Last SQL Fired by The User on Database
8-oracle authentication mode 
9-create role with password 
10-bypassed oracle role password 
11-oralce Privileges 
12- access role in a specific session 
13-Complexity function=verify_function_11g
14-Resource_limit 
15-Configure shared / Dedicated server process in oracle 
16-Remove S from HTTPS in oracle OEM
17-Encrypt and Decryption on Oracle Backup

---

1-4 Ways to Audit in Oracle
There are several ways to audit in oracle

1. SQL Audit
2. Trigger Audit
3. Audit With Log miner
4. Fine Grained Audit

2-Connecting to a schema without knowing the password

your can connect by sqlplus or other third party program (pl/sql developer)

Proxy Authentication:
Proxy Authentication allow you to login with your username and password and then switching to another account

A-Connect as SYS
sqlplus / as sysdba
sqlplus> alter user X2 grant connect through X1;

B-sqlplus X1[X2]
Enter Password
sql>show user
User is "X2"

C-after finish your work remove the proxy right
sql> conn / as sysdba
sql>alter user X2 revoke connect through X1;

D-conn X1[X2]
proxy not authorized to connect  as client


3-view log file by logMiner

select * from v$logfile

begin
dbms_logmnr.add_logfile('/u03/xx/redo06.log');
dbms_logmnr.add_logfile('/u03/xxredo05.log');
dbms_logmnr.start_logmnr();
end;

select * from  v$logmnr_contents
where timestamp like '08/28/2003%' 

4-view history of DML statement
by:
A-v$sql
B-v$sqlArea
C -v$sqlText
D-Log miner
E-flashback



5-Unlock and change user name password
Alter user hr identified by p123456 account unlock

 6-Grant SYSDBA privilege to other user
SQL> grant sysdba to hr
Grant sysdba to hr, so hr can connect in two way

hr/hr (no sysdba Previledge)
hr/hr as sysdba (has sysdba previledge)

7-Last SQL Fired by The User on Database

SELECT S.USERNAME || '(' || s.sid || ')-' || s.osuser UNAME,

         s.program || '-' || s.terminal || '(' || s.machine || ')' PROG,

         s.sid || '/' || s.serial# sid,

         s.status "Status",

         p.spid,

         sql_text sqltext

    FROM v$sqltext_with_newlines t, V$SESSION s, v$process p

   WHERE     t.address = s.sql_address

         AND p.addr = s.paddr(+)

         AND t.hash_value = s.sql_hash_value

ORDER BY s.sid, t.piece;

8-oracle authentication mode

Authentication mode in oracle
I-password
II-External
III-


external authentication in oracle :
mean you can connect to oracle without using password,
you use user account in os-
1-show parameter os_
if os_authent_prefix is not set to OPS$ -->change it to OPS$
2-check sqlnet.ora file
c:\oracle\product\10.2.0\network\Admin\sqlnet.ora
sqlnet.authentication_services=(NTS) if not change it
3-show current user name and domain name in window
>Echo %username%
>Echo %userdomain%
4-create user in oracle
create user "OPS$domainName\userAccount" identified externally;
5-connect to oracle
>sqlplus / 

9-create role with password 
1-create user role_test
2-grant create session to role_test
3-create role MyRole identified by 123
4-grant select on DBA_users to MyRole
5-grant MyRole to role_test
6-alter user role_test default role none;
7-select * from sys.dba_users; --> error
8-set role MyRole identified by 123;
select * from sys.dba_users;--> show data

10-bypassed oracle role password  
1-create role Non_pwd
2-grant pwd_role to Non_pwd
3-grant Non_pwd to role_user
4-alter user role_test default role none;
5-select * from sys.dba_users;-->error
6-set role non_pwd
select * from sys.dba_users;--> show data
 
11-oralce Privileges
Privileges--> system and object privilege
System Privileges
RESTRICTED SESSION: This privilege allows you to log in even if the

database has been opened in restricted mode
SYSOPER allows a user to perform basic operational tasks, but without the

ability to look at user data
SYSASM: This privilege allows you to start up, shut down and administer

an ASM instance

System Privileges-->with admin option
A-->B-->C
Revoke B, C still have priviledge

object privileges-->with grant option
A-->B-->C
Revoke B, C auto remove priviledge 


12- access role in a specific session
set role vacationdba

13-Complexity function=verify_function_11g
1-at least 8 char
2-dif from username, username+number,username reversed
3-dif from database name, database name + number
4-A string at least 1 char + 1 number
5-dif from previous pass by at least 3 letters.

 14-Resource_limit =True before profile can impose



15-Configure shared / Dedicated server process in oracle

### SHARED CONNECTION TO TEST DATABASE TEST_SHARED = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = alpha2)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = SHARED) (SERVICE_NAME = test) ) ) ### DEDICATED CONNECTION TO TEST DATABASE TEST_DEDICATED = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = alpha2)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = test) ) ) Now, to connect via a dispatcher, try this: SQLPLUS scott/tiger@test_shared to connect via a dedicated server process, try this: SQLPLUS scott/tiger@test_dedicated

 16-Remove S from HTTPS in oracle OEM

emctl unsecure dbconsole

ENCRYPT and Decryption Backup

17-Encrypt and Decryption on Oracle Backup

export oracle_sid=mydb

rman target sys/oracle

rman>set encryption on identified by azar only;

rman>configure encryption for tablespace users on;

rman>backup tablespace users;

rman>sql 'alter tablespace users offline';

rman>restore tablespace users; -->error

rman>set decryption identified by azar;

rman>restore tablespace users;

rman>recover tablespace users;

rman>sql 'alter tablespace users online';