Audit by Table Level

                Table Level Auditing

SQL> show user

USER is "SYS"

SQL> show parameter audit_trail;

 NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

audit_trail                          string      NONE

SQL> show parameter audit_file_dest

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

audit_file_dest                      string      /u03/oravis/VIS/12.1.0/rdbms/audit

SQL> alter system set AUDIT_TRAIL=db, extended scope=spfile;

System altered.

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area 2147483648 bytes

Fixed Size                  3712904 bytes

Variable Size             905971832 bytes

Database Buffers         1224736768 bytes

Redo Buffers               13062144 bytes

Database mounted.

Database opened.

SQL> show parameter audit_trail;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

audit_trail                          string      DB, EXTENDED

SQL> audit table;

 Audit succeeded.

SQL> audit select,update,delete on scott.emp by access;

 Audit succeeded.

SQL> desc dba_audit_trail

SQL> conn shyam/shyam

Connected.

SQL> select *from scott.emp;

    EMPNO ENAME      JOB              MGR HIREDATE                  SAL

---------- ---------- --------- ---------- ------------------ ----------

      COMM     DEPTNO

      7839 KING       PRESIDENT            17-NOV-81                5000

                   10

      7698 BLAKE      MANAGER         7839 01-MAY-81                2850

                   30

      7782 CLARK      MANAGER         7839 09-JUN-81                2450

                   10

  

     EMPNO ENAME      JOB              MGR HIREDATE                  SAL

---------- ---------- --------- ---------- ------------------ ----------

      COMM     DEPTNO

---------- ----------

      7566 JONES      MANAGER         7839 02-APR-81                2975

                   20

      7788 SCOTT      ANALYST         7566 19-APR-87                3000

                   20

      7902 FORD       ANALYST         7566 03-DEC-81                3000

                   20

     EMPNO ENAME      JOB              MGR HIREDATE                  SAL

---------- ---------- --------- ---------- ------------------ ----------

      COMM     DEPTNO

---------- ----------

      7369 SMITH      CLERK           7902 17-DEC-80                 800

                   20

      7499 ALLEN      SALESMAN        7698 20-FEB-81                1600

       300         30

      7521 WARD       SALESMAN        7698 22-FEB-81                1250

       500         30

     EMPNO ENAME      JOB              MGR HIREDATE                  SAL

---------- ---------- --------- ---------- ------------------ ----------

      COMM     DEPTNO

---------- ----------

      7654 MARTIN     SALESMAN        7698 28-SEP-81                1250

      1400         30

      7844 TURNER     SALESMAN        7698 08-SEP-81                1500

         0         30

      7876 ADAMS      CLERK           7788 23-MAY-87                1100

                   20

     EMPNO ENAME      JOB              MGR HIREDATE                  SAL

---------- ---------- --------- ---------- ------------------ ----------

      COMM     DEPTNO

---------- ----------

      7900 JAMES      CLERK           7698 03-DEC-81                 950

                   30

      7934 MILLER     CLERK           7782 23-JAN-82                1300

                   10

14 rows selected.

sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Aug 9 16:31:05 2018

Copyright (c) 1982, 2014, Oracle.  All rights reserved. 

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select os_username,username,owner,timestamp,action_name,sql_text from dba_audit_trail;

OS_USERNAME

--------------------------------------------------------------------------------

USERNAME

--------------------------------------------------------------------------------

OWNER

--------------------------------------------------------------------------------

TIMESTAMP          ACTION_NAME

------------------ ----------------------------

SQL_TEXT

--------------------------------------------------------------------------------

oravis

SHYAM

  

OS_USERNAME

--------------------------------------------------------------------------------

USERNAME

--------------------------------------------------------------------------------

OWNER

--------------------------------------------------------------------------------

TIMESTAMP          ACTION_NAME

------------------ ----------------------------

SQL_TEXT

--------------------------------------------------------------------------------

09-AUG-18          LOGON

OS_USERNAME

--------------------------------------------------------------------------------

USERNAME

--------------------------------------------------------------------------------

OWNER

--------------------------------------------------------------------------------

TIMESTAMP          ACTION_NAME

------------------ ----------------------------

SQL_TEXT

--------------------------------------------------------------------------------

oravis

SHYAM

SCOTT

OS_USERNAME

--------------------------------------------------------------------------------

USERNAME

--------------------------------------------------------------------------------

OWNER

--------------------------------------------------------------------------------

TIMESTAMP          ACTION_NAME

------------------ ----------------------------

SQL_TEXT

--------------------------------------------------------------------------------

09-AUG-18          SELECT

select *from scott.emp

SQL> audit table by shyam whenever not successful;

 Audit succeeded.

RMAN Configurations

[oravis@test ~]$ echo $RECOVERY_FILE_DEST

/u03/oravis/VIS/recovery_area

[oravis@test ~]$ cd /u03/oravis/VIS/recovery_area

[oravis@test recovery_area]$ ls

vis  VIS

[oravis@test recovery_area]$ cd vis

 [oravis@test vis]$ cd ../VIS/

[oravis@test VIS]$ ls

1_813_931529841.dbf  1_818_931529841.dbf  1_823_931529841.dbf

1_814_931529841.dbf  1_819_931529841.dbf  1_824_931529841.dbf

1_815_931529841.dbf  1_820_931529841.dbf  1_825_931529841.dbf

1_816_931529841.dbf  1_821_931529841.dbf

1_817_931529841.dbf  1_822_931529841.dbf

[oravis@test VIS]$ cd ..

[oravis@test recovery_area]$ ls

vis  VIS

[oravis@test recovery_area]$ cd vis/

[oravis@test vis]$ ls

controlfile.bkp  createcontrol.sql

[oravis@test vis]$ cd ..

[oravis@test recovery_area]$ ls

vis  VIS

[oravis@test recovery_area]$ !sq

sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Aug 3 12:49:19 2018

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area 2147483648 bytes

Fixed Size                  3712904 bytes

Variable Size             905971832 bytes

Database Buffers         1224736768 bytes

Redo Buffers               13062144 bytes

Database mounted.

SQL> show parameter db_recovery

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest                string      /u03/oravis/VIS/recovery_area/

                                                 VIS

db_recovery_file_dest_size           big integer 5G

SQL> alter system set db_recovery_file_dest_size=200G;

System altered.

SQL> SHOW PARAMETER RECOVERY

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest                string      /u03/oravis/VIS/recovery_area/

                                                 VIS

db_recovery_file_dest_size           big integer 200G

recovery_parallelism                 integer     0

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

[oravis@test recovery_area]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Fri Aug 3 12:52:16 2018

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: VIS (DBID=485335574, not open)

RMAN> show all;

using target database control file instead of recovery catalog

RMAN configuration parameters for database with db_unique_name VIS are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

CONFIGURE BACKUP OPTIMIZATION OFF; # default

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

CONFIGURE DEVICE TYPE DISK PARALLELISM 5 BACKUP TYPE TO BACKUPSET;

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE MAXSETSIZE TO 30000 M;

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default

CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u03/oravis/VIS/12.1.0/dbs/snapcf_VIS.f'; # default

RMAN> configure retention policy to redundancy 2;

new RMAN configuration parameters:

CONFIGURE RETENTION POLICY TO REDUNDANCY 2;

new RMAN configuration parameters are successfully stored

RMAN> configure controlfile autobackup on;

new RMAN configuration parameters:

CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters are successfully stored

RMAN> show all;

RMAN configuration parameters for database with db_unique_name VIS are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 2;

CONFIGURE BACKUP OPTIMIZATION OFF; # default

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

CONFIGURE DEVICE TYPE DISK PARALLELISM 5 BACKUP TYPE TO BACKUPSET;

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE MAXSETSIZE TO 30000 M;

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default

CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u03/oravis/VIS/12.1.0/dbs/snapcf_VIS.f'; # default

RMAN> list backup summary;

specification does not match any backup in the repository

RMAN> configure controlfile autobackup format for device type disk to '/u03/orav                       is/VIS/recovery_area/VIS%F';

new RMAN configuration parameters:

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u03/oravis/VIS                       /recovery_area/VIS%F';

new RMAN configuration parameters are successfully stored

RMAN> show all;

RMAN configuration parameters for database with db_unique_name VIS are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 2;

CONFIGURE BACKUP OPTIMIZATION ON;

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u03/oravis/VIS                       /recovery_area/VIS%F';

CONFIGURE DEVICE TYPE DISK PARALLELISM 5 BACKUP TYPE TO BACKUPSET;

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE MAXSETSIZE TO 30000 M;

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ;   # default

CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u03/oravis/VIS/12.1.0/dbs/snapcf_VIS.f' ; # default

RMAN> configure channel device type disk format '/u03/oravis/VIS/recovery_area/VIS/%U';

new RMAN configuration parameters:

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/u03/oravis/VIS/recovery_area/VIS/%U';

new RMAN configuration parameters are successfully stored

RMAN> show all;

RMAN configuration parameters for database with db_unique_name VIS are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 2;

CONFIGURE BACKUP OPTIMIZATION ON;

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u03/oravis/VIS/recovery_area/VIS%F';

CONFIGURE DEVICE TYPE DISK PARALLELISM 5 BACKUP TYPE TO BACKUPSET;

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/u03/oravis/VIS/recovery_area/VIS/%U';

CONFIGURE MAXSETSIZE TO 30000 M;

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default

CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u03/oravis/VIS/12.1.0/dbs/snapcf_VIS.f'; # default

Privileges

Privilege is a right to execute a particular type of SQL statement or to access another user's object. Example: Connect to the DB(create session), create table.

DBA can grant privileges to user. So that users can accomplish that task required for their jobs. DBA should grant a privilege only to a user who requires that privilege to accomplish the necessary work. Excessive granting of unnecessary privileges can compromise
There are five major categories of  Privileges. They are

• System Privileges 
• Object Privileges
• View Privileges
• Procedure Privileges
• Type Privileges

System Privileges :

A system privilege is the right to perform a particular action, or to perform an action on any schema objects of a particular type. DBA can grant / revoke system previleges to users and roles.

Who can Grant or Revoke system privilege:

• Users who have been granted a specific system privilege with the ADMIN OPTION
• Users with the system privilege GRANT ANY PRIVILEGE

SQL> desc system_privilege_map

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 PRIVILEGE                                 NOT NULL NUMBER

 NAME                                      NOT NULL VARCHAR2(40)

 PROPERTY                                  NOT NULL NUMBER

Approximately 250 privileges are there under System Privileges

SQL> desc dba_sys_privs        (Shows which user has system privileges)

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 GRANTEE                                            VARCHAR2(128)

 PRIVILEGE                                          VARCHAR2(40)

 ADMIN_OPTION                                       VARCHAR2(3)

 COMMON                                             VARCHAR2(3)

SQL> select privilege from dba_sys_privs where grantee='SHYAM';

PRIVILEGE

----------------------------------------

CREATE JOB

CREATE SESSION

SELECT ANY TABLE

SQL> revoke select any table from shyam;

Revoke succeeded.

SQL> select privilege from dba_sys_privs where grantee='SHYAM';

PRIVILEGE

----------------------------------------

CREATE JOB

CREATE SESSION

Schema Object Privileges :

A Schema Object privilege is the permission to perform a particular action on specific schema object.

 

Who can Grant or Revoke system privilege:

• A user automatically has all object privileges for schema objects contained in his or her schema. A user can grant any object privilege on any schema object he or she owns to any other user or role. A user with the GRANT ANY OBJECT PRIVILEGE can grant or revoke any specified object privilege to another user with or without the GRANT OPTION of the GRANT statement. Otherwise, the grantee can use the privilege, but cannot grant it to other users.

SQL> desc table_privilege_map

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 PRIVILEGE                                 NOT NULL NUMBER

 NAME                                      NOT NULL VARCHAR2(40)

SQL> grant alter on scott.emp to shyam;

Grant succeeded.

SQL> desc dba_tab_privs;    (Shows which user has table/object privileges)

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 GRANTEE                                            VARCHAR2(128)

 OWNER                                              VARCHAR2(128)

 TABLE_NAME                                         VARCHAR2(128)

 GRANTOR                                            VARCHAR2(128)

 PRIVILEGE                                          VARCHAR2(40)

 GRANTABLE                                          VARCHAR2(3)

 HIERARCHY                                          VARCHAR2(3)

 COMMON                                             VARCHAR2(3)

 TYPE                                           VARCHAR2(24)   

SQL> select privilege from dba_tab_privs where grantee='SHYAM';

PRIVILEGE

----------------------------------------

ALTER

Administrative Privileges from System_privilege_map are given below:

SQL> select name,privilege,property from system_privilege_map where name='SYSDBA';

NAME                                      PRIVILEGE   PROPERTY

---------------------------------------- ---------- ----------

SYSDBA                                          -83          0

SQL> select name,privilege from system_privilege_map where name='SYSOPER';

NAME                                      PRIVILEGE

---------------------------------------- ----------

SYSOPER                                         -84

SQL> select name,privilege from system_privilege_map where name='SYSDG';

NAME                                      PRIVILEGE

---------------------------------------- ----------

SYSDG                                          -341

SQL> select name,privilege from system_privilege_map where name='SYSBACKUP';

NAME                                      PRIVILEGE

---------------------------------------- ----------

SYSBACKUP                                      -340

SQL> select name,privilege from system_privilege_map where name='SYSKM';

NAME                                      PRIVILEGE

---------------------------------------- ----------

SYSKM                                          -342

SQL> desc system_privilege_map

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 PRIVILEGE                                 NOT NULL NUMBER

 NAME                                      NOT NULL VARCHAR2(40)

 PROPERTY                                  NOT NULL NUMBER

SQL> desc dba_sys_privs

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 GRANTEE                                            VARCHAR2(128)

 PRIVILEGE                                          VARCHAR2(40)

 ADMIN_OPTION                                       VARCHAR2(3)

 COMMON                                             VARCHAR2(3)

SQL> select privilege,admin_option from dba_sys_privs where grantee='SHIRISHA';

PRIVILEGE                                ADM

---------------------------------------- ---

CREATE SESSION                           NO

SQL> select privilege,admin_option from dba_sys_privs where grantee='SHYAM';

PRIVILEGE                                ADM

---------------------------------------- ---

CREATE JOB                               NO

CREATE SESSION                           NO

SQL> desc dba_tab_privs

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 GRANTEE                                            VARCHAR2(128)

 OWNER                                              VARCHAR2(128)

 TABLE_NAME                                         VARCHAR2(128)

 GRANTOR                                            VARCHAR2(128)

 PRIVILEGE                                          VARCHAR2(40)

 GRANTABLE                                          VARCHAR2(3)

 HIERARCHY                                          VARCHAR2(3)

 COMMON                                             VARCHAR2(3)

 TYPE                                               VARCHAR2(24)

SQL> column privilege format a10;

SQL> column grantor format a20;

SQL> select privilege,grantable,grantor from dba_tab_privs where grantee='SHYAM';

PRIVILEGE  GRA GRANTOR

---------- --- --------------------

ALTER      NO  SCOTT

SQL> select privilege,grantable,grantor from dba_tab_privs where grantee='SHIRISHA';

no rows selected

Granting System Privilege to a user:

SQL> select name from system_privilege_map where name='CREATE USER';

NAME

------------------------------

CREATE USER

User SQL> SHOW USER 

 USER is "SHIRISHA"

SQL> create user srikanth identified by srikanth123;

create user srikanth identified by srikanth123

            *ERROR at line 1:

ORA-01031: insufficient privileges

SQL> grant create user to shirisha;

Grant succeeded.

sys SQL> select privilege from dba_sys_privs where grantee='SHIRISHA';

PRIVILEGE

----------

CREATE SESSION

CREATE USER

User  SQL> create user srikanth identified by srikanth123;

User created.

 

 

Profile

           Profiles are created by DBA and assigned to the users by DBA only. Only one profile can be assigned to one user account. Profile is nothing but security properties. If didn't assign / implement any profile default profile will be there. Various companies has its own profile for different teams as their policy.

SQL> select distinct profile from dba_users;

PROFILE

--------------------------------------------------------------------------------

EBS_APPS

TRAINEE

AD_PATCH_MONITOR_PROFILE

EM_OAM_MONITOR_PROFILE

DEFAULT

SQL> desc dba_profiles;

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 PROFILE                                   NOT NULL VARCHAR2(128)

 RESOURCE_NAME                             NOT NULL VARCHAR2(32)

 RESOURCE_TYPE                                      VARCHAR2(8)

 LIMIT                                              VARCHAR2(128)

 COMMON                                             VARCHAR2(3)

SQL> column limit format a10;

SQL> column resorce_name format a30;

SQL> select resource_name,limit from dba_profiles where profile='TRAINEE';

RESOURCE_NAME                                LIMIT

------------------------------                   ----------

COMPOSITE_LIMIT                                DEFAULT

SESSIONS_PER_USER                            2

CPU_PER_SESSION                                DEFAULT

CPU_PER_CALL                                      DEFAULT

LOGICAL_READS_PER_SESSION             DEFAULT

LOGICAL_READS_PER_CALL                   DEFAULT

IDLE_TIME                                            5

CONNECT_TIME                                    DEFAULT

PRIVATE_SGA                                       DEFAULT

FAILED_LOGIN_ATTEMPTS                     3

PASSWORD_LIFE_TIME                          30

RESOURCE_NAME                                 LIMIT

------------------------------                    ----------

PASSWORD_REUSE_TIME                       2

PASSWORD_REUSE_MAX                        3

PASSWORD_VERIFY_FUNCTION             DEFAULT

PASSWORD_LOCK_TIME                        DEFAULT

PASSWORD_GRACE_TIME                      5

16 rows selected.

Changing Limit for a Resource:

SQL> select resource_name,limit from dba_profiles where profile='TRAINEE';

RESOURCE_NAME                                 LIMIT

------------------------------                  ----------

COMPOSITE_LIMIT                            DEFAULT

SESSIONS_PER_USER                        2

CPU_PER_SESSION                           DEFAULT

CPU_PER_CALL                                 DEFAULT

LOGICAL_READS_PER_SESSION        DEFAULT

LOGICAL_READS_PER_CALL              DEFAULT

IDLE_TIME                                       5

CONNECT_TIME                                DEFAULT

PRIVATE_SGA                                   DEFAULT

FAILED_LOGIN_ATTEMPTS                 3

PASSWORD_LIFE_TIME                     30

RESOURCE_NAME                               LIMIT

------------------------------                ----------

PASSWORD_REUSE_TIME                     2

PASSWORD_REUSE_MAX                      3

PASSWORD_VERIFY_FUNCTION           DEFAULT

PASSWORD_LOCK_TIME                     DEFAULT

PASSWORD_GRACE_TIME                     5

16 rows selected.

SQL> alter profile trainee limit password_life_time 40;

Profile altered.

SQL> select resource_name,limit from dba_profiles where profile='TRAINEE';

RESOURCE_NAME                                     LIMIT

------------------------------                      ----------

COMPOSITE_LIMIT                                 DEFAULT

SESSIONS_PER_USER                             2

CPU_PER_SESSION                                 DEFAULT

CPU_PER_CALL                                       DEFAULT

LOGICAL_READS_PER_SESSION              DEFAULT

LOGICAL_READS_PER_CALL                    DEFAULT

IDLE_TIME                                               5

CONNECT_TIME                                     DEFAULT

PRIVATE_SGA                                        DEFAULT

FAILED_LOGIN_ATTEMPTS                      3

PASSWORD_LIFE_TIME                          40

RESOURCE_NAME                                 LIMIT

------------------------------                 ----------

PASSWORD_REUSE_TIME                      2

PASSWORD_REUSE_MAX                       3

PASSWORD_VERIFY_FUNCTION           DEFAULT

PASSWORD_LOCK_TIME                     DEFAULT

PASSWORD_GRACE_TIME                      5

16 rows selected.

Assigning Profile to User:

SQL> select profile from dba_users where username='SHIRISHA';

PROFILE

--------------------------------------------------------------------------------

DEFAULT

SQL> alter user shirisha profile trainee;

User altered.

SQL> select profile from dba_users where username='SHIRISHA';

PROFILE

--------------------------------------------------------------------------------

TRAINEE

Profiles and Password Verify Function: 

Remember the password verification function in Oracle Database? Many of you may not be even aware of its existence, let alone use it. The function is a quick and easy way to enforce quality of database passwords—for example, they should contain a certain number of characters, should not be identical to the username, and so on. Perhaps its best feature is that it is built-in; all you have to do is turn it on. More likely than not, you didn't.

In Oracle Database 11g/12c the password management function has new and improved verification logic. If you examine the password verification file utlpwdmg.sql in $ORACLE_HOME/rdbms/admin, you will notice that the script creates a new password function called verify_fnction_11g / ora12c_verify_function. At the end, the script has the following lines:

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;

The script attaches the function to the profile DEFAULT, which is the default profile for all users, unless something else is explicitly assigned. This makes the authentication compliant with many regulations. All you have to do is run this script to create the 11g/12c version of the password checking function, and the script will enable the password verification feature by attaching itself to the default profile.

[oravis@test ~]$ cd $ORACLE_HOME/rdbms/admin

[oravis@test admin]$ pwd

/u03/oravis/VIS/12.1.0/rdbms/admin

[oravis@test admin]$ ls utlpwd*

utlpwdmg.sql                                         (Password management Utility)

SQL> select resource_name,limit from dba_profiles where profile='DEFAULT';

RESOURCE_NAME                    LIMIT

--------------------------------   ----------

COMPOSITE_LIMIT                   UNLIMITED

SESSIONS_PER_USER              UNLIMITED

CPU_PER_SESSION                  UNLIMITED

CPU_PER_CALL                        UNLIMITED

LOGICAL_READS_PER_SESSION    UNLIMITED

LOGICAL_READS_PER_CALL           UNLIMITED

IDLE_TIME                                    UNLIMITED

CONNECT_TIME                            UNLIMITED

PRIVATE_SGA                               UNLIMITED

FAILED_LOGIN_ATTEMPTS            10

PASSWORD_LIFE_TIME               180

RESOURCE_NAME                    LIMIT

-------------------------------- ----------

PASSWORD_REUSE_TIME              UNLIMITED

PASSWORD_REUSE_MAX               UNLIMITED

PASSWORD_VERIFY_FUNCTION         NULL

PASSWORD_LOCK_TIME                  1

PASSWORD_GRACE_TIME                7

16 rows selected.

[oravis@test admin]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Aug 1 12:42:33 2018

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> @?/rdbms/admin/utlpwdmg.sql    (To enable pw_verify_function)

Function created.        (Here ?=$ORACLE_HOME)

Grant succeeded.

Function created.

Grant succeeded.

Function created.

Grant succeeded.

Profile altered.

SQL> select resource_name,limit from dba_profiles where profile='DEFAULT';

RESOURCE_NAME                           LIMIT

------------------------------             ----------

COMPOSITE_LIMIT                       UNLIMITED

SESSIONS_PER_USER                  UNLIMITED

CPU_PER_SESSION                      UNLIMITED

CPU_PER_CALL                            UNLIMITED

LOGICAL_READS_PER_SESSION    UNLIMITED

LOGICAL_READS_PER_CALL          UNLIMITED

IDLE_TIME                                   UNLIMITED

CONNECT_TIME                            UNLIMITED

PRIVATE_SGA                               UNLIMITED

FAILED_LOGIN_ATTEMPTS             10

PASSWORD_LIFE_TIME                  180

RESOURCE_NAME                              LIMIT

------------------------------               ----------

PASSWORD_REUSE_TIME              UNLIMITED

PASSWORD_REUSE_MAX               UNLIMITED

PASSWORD_VERIFY_FUNCTION      ORA12C_VERIFY_FUNCTION

                               

PASSWORD_LOCK_TIME                 1

PASSWORD_GRACE_TIME               7

16 rows selected.

Example for Password Verify Function:

sys SQL> alter user shirisha password expire;

User altered.

user 

[oravis@test ~]$ sqlplus shirisha/shirisha123;

SQL*Plus: Release 12.1.0.2.0 Production on Wed Aug 1 13:22:35 2018

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

ERROR:

ORA-28001: the password has expired

Changing password for shirisha

New password:

Retype new password:

ERROR:

ORA-28003: password verification for the specified password failed

ORA-20001: Password length less than 8

Password unchanged

Enter user-name: shirisha/shirisha123

ERROR:

ORA-28001: the password has expired

Changing password for shirisha

New password:

Retype new password:

ERROR:

ORA-28007: the password cannot be reused

Password unchanged

Errors:

ORA-20025: Password must contain at least 1 digit(s)

ORA-20022: Password must contain at least 1 letter(s)

ORA-20002: Password contains the username

To Disable Password_verify_function:

SQL> alter profile default limit password_verify_function null;

Profile altered.

SQL> select resource_name,limit from dba_profiles where profile='DEFAULT';

RESOURCE_NAME                                  LIMIT

------------------------------                           ----------

COMPOSITE_LIMIT                             UNLIMITED

SESSIONS_PER_USER                        UNLIMITED

CPU_PER_SESSION                            UNLIMITED

CPU_PER_CALL                                  UNLIMITED

LOGICAL_READS_PER_SESSION         UNLIMITED

LOGICAL_READS_PER_CALL               UNLIMITED

IDLE_TIME                                        UNLIMITED

CONNECT_TIME                                 UNLIMITED

PRIVATE_SGA                                    UNLIMITED

FAILED_LOGIN_ATTEMPTS                  10

PASSWORD_LIFE_TIME                      180

RESOURCE_NAME                                 LIMIT

------------------------------                  ----------

PASSWORD_REUSE_TIME                    UNLIMITED

PASSWORD_REUSE_MAX                    UNLIMITED

PASSWORD_VERIFY_FUNCTION          NULL

PASSWORD_LOCK_TIME                     1

PASSWORD_GRACE_TIME                  7

16 rows selected.

Now we can check 

[oravis@test ~]$ sqlplus shirisha/shirisha123

SQL*Plus: Release 12.1.0.2.0 Production on Wed Aug 1 13:31:27 2018

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

ERROR:

ORA-28001: the password has expired

Changing password for shirisha

New password:

Retype new password:

Password changed

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show user

USER is "SHIRISHA"

SQL> password

Changing password for SHIRISHA

Old password:

New password:

Retype new password:

Password changed

Click Here Click Here for Apps DBA Blog