11g - Security New Features

11g - Security New Features :

1) A new view to know the users with default passwords (DBA_USERS_WITH_DEFPWD)
2) Now the passwords are case sensitive
3) Making SYSDBA password case sensitive
orapwd file=orapwPRODB3 password=abc123 entries=10 ignorecase=n
4) Profiles and Password Verify Function
5) Improved Out-of-Box Auditing
6) Transparent Tablespace Encryption
7) Encryption of Data Pump Dumpfiles
8 ) Access Control Lists for UTL_TCP/HTTP/SMTP
9) Data Masking

================================================================================

Run the following script to add the 11g security feature "profiles and password verification"
$ORACLE_HOME/rdbms/admin/verify_fnction_11g

The script attaches the function to the profile DEFAULT, which is the default profile for all users.

Profiles and function details :
ALTER PROFILE DEFAULT LIMIT
PASSWORD_LIFE_TIME 180
PASSWORD_GRACE_TIME 7
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LOCK_TIME 1
PASSWORD_VERIFY_FUNCTION verify_function_11G;

==========================================================================

Transparent Tablespace Encryption :
In 10g -> Transparent Data Encryption -> DBMS_CRYPTO and DBMS_OBFUSCATION_TOOLKIT ->
In 11g -> Transparent Tablespace Encryption
DBA_TABLESPACES -> column ENCRYPTED

V$ENCRYPTED_TABLESPACES -> what type of encryption is enabled for the tablespace.

Procedure:
===========
1) Create a wallet directory $ORACLE_BASE/admin/wallet.
2) Create the encryption key using
SQL> alter system set encryption key identified by "abcd1234!";
3) Issue the following command to open the wallet for encryption
SQL> alter system set wallet open identified by "abcd1234!"
4) Create a tablespace with encryption option and also create some objects in it

Encryption of Data Pump Dumpfiles:
A new parameter called ENCRYPTION included.

Procedure
=========
1) Create a wallet directory $ORACLE_BASE/admin/wallet.
2) Create the encryption key using
SQL> alter system set encryption key identified by "abcd1234!";
3) Issue the following command to open the wallet for encryption
SQL> alter system set wallet open identified by "abcd1234!"
4) Export the data using the following syntax
$expdp system/xxxx tables=sh.sales dumpfile=sales_bkp.dmp directory=EXP_DIR encryption=data_only encryption_algorithm=aes128
5) Verification by
$ cat /u01/app/oracle/exports/sales_bkp.dmp | grep "region"

===================================================================

Data Masking :
Datapump new parameter remap_data

Data masking to mask the sensitive data when we are creating a test instance from production.

Procedure
=========1) Create a function or procedure which generates a randon key and returns it.
2) Export the data using the above function to mask the sensitive data ..
$expdp system/xxxxx tables=sh.sales dumpfile=sales_bkp.dmp directory=EXP_DIR remap_data=sh.sales:pkg_to_msk.fun_mask
[ remap_data = [<SchemaName>.]<TableName>.<ColumnName>:[<SchemaName>.]<PackageName>.<FunctionName> ]
3) If the data to the target system using
$impdp system/xxxxx tables=sh.sales dumpfile=sales_bkp.dmp directory=EXP_DIR remap_data=sh.sales:pkg_to_msk.fun_mask

========================================================

Users with Default Passwords and Password Case Sensitive :

SQL> conn sh/sh
Connected.
SQL> conn sh/Sh
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.
SQL> conn /as sysdba
Connected.
SQL> alter system set sec_case_sensitive_logon = false;

System altered.

SQL> conn sh/sh
Connected.
SQL> conn sh/SH
Connected.
SQL> alter system set sec_case_sensitive_logon =true;
alter system set sec_case_sensitive_logon =true
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> conn /as sysdba
Connected.
SQL> alter system set sec_case_sensitive_logon =true;
System altered.

SQL> desc DBA_USERS_WITH_DEFPWD
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
USERNAME                                  NOT NULL VARCHAR2(30)

SQL> select count(1) from DBA_USERS_WITH_DEFPWD;

COUNT(1)
----------
24

SQL> select * from DBA_USERS_WITH_DEFPWD where username='SCOTT';

USERNAME
------------------------------
SCOTT

SQL> alter user scott identified by Tiger1;

User altered.

SQL> select * from DBA_USERS_WITH_DEFPWD where username='SCOTT';

no rows selected

SQL> alter user scott identified by tiger;

User altered.

SQL> select * from DBA_USERS_WITH_DEFPWD where username='SCOTT';

USERNAME
------------------------------
SCOTT

SQL> desc dba_users
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
USERNAME                                  NOT NULL VARCHAR2(30)
USER_ID                                   NOT NULL NUMBER
PASSWORD                                           VARCHAR2(30)
ACCOUNT_STATUS                            NOT NULL VARCHAR2(32)
LOCK_DATE                                          DATE
EXPIRY_DATE                                        DATE
DEFAULT_TABLESPACE                        NOT NULL VARCHAR2(30)
TEMPORARY_TABLESPACE                      NOT NULL VARCHAR2(30)
CREATED                                   NOT NULL DATE
PROFILE                                   NOT NULL VARCHAR2(30)
INITIAL_RSRC_CONSUMER_GROUP                        VARCHAR2(30)
EXTERNAL_NAME                                      VARCHAR2(4000)
PASSWORD_VERSIONS                                  VARCHAR2(8)
EDITIONS_ENABLED                                   VARCHAR2(1)
AUTHENTICATION_TYPE                                VARCHAR2(8)

SQL> select username,user_id,password, password_versions from dba_users where username in ('SH','SCOTT');

USERNAME        USER_ID PASSWORD   PASSWORD
--------------- ------- ---------- --------
SH                   88            10G 11G
SCOTT                84            10G 11G

Note the column PASSWORD_VERSIONS, which is new in Oracle Database 11g. This column signifies the case
sensitivity of the password. The value "10G 11G" signifies that the user was either created in 10g and migrated to 11g or
created in 11g directly.

SQL> desc user$
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
USER#                                     NOT NULL NUMBER
NAME                                      NOT NULL VARCHAR2(30)
TYPE#                                     NOT NULL NUMBER
PASSWORD                                           VARCHAR2(30)
DATATS#                                   NOT NULL NUMBER
TEMPTS#                                   NOT NULL NUMBER
CTIME                                     NOT NULL DATE
PTIME                                              DATE
EXPTIME                                            DATE
LTIME                                              DATE
RESOURCE$                                 NOT NULL NUMBER
AUDIT$                                             VARCHAR2(38)
DEFROLE                                   NOT NULL NUMBER
DEFGRP#                                            NUMBER
DEFGRP_SEQ#                                        NUMBER
ASTATUS                                   NOT NULL NUMBER
LCOUNT                                    NOT NULL NUMBER
DEFSCHCLASS                                        VARCHAR2(30)
EXT_USERNAME                                       VARCHAR2(4000)
SPARE1                                             NUMBER
SPARE2                                             NUMBER
SPARE3                                             NUMBER
SPARE4                                             VARCHAR2(1000)
SPARE5                                             VARCHAR2(1000)
SPARE6                                             DATE

SQL> select user#,password from user$ where user# in (88,84);

USER# PASSWORD
---------- -------------------------
84 F894844C34402B67
88 54B253CBBAAA8C48

askMLabs