In Oracle : Managing Data Security in Oracle

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...

Oracle DBA

Real Application Cluster (RAC) Maintenance tasks Backup and Recovery Database Architecture Replication Oracle Performance Tuning Oracle DBA - Other articles Oracle scripts/ Selects for DBA Oracle Errors (ORA-nnnnn)

The last articles in the site (HOT)

The most visited articles in the site

In-Oracle.com  -> Oracle DBA -> Oracle Maintenance Tasks

-> Managing Data Security in Oracle

 

 Managing Data Security in Oracle

 

 

Here are the main topics for this article:

How to change the password for an user

 

ALTER USER scott identified by scott;

 

 

How to enforce strict password control

 

By default, Oracle will allow users to choose single character passwords and passwords that match their names and userids. Also, by default the password will not expire. However, Oracle manage passwords through profiles. Some of the things that we can restrict:

 

             FAILED_LOGIN_ATTEMPTS - failed login attempts before the account is locked

             PASSWORD_LIFE_TIME - limits the number of days the same password can be used for authentication

             PASSWORD_GRACE_TIME - number of days after the grace period begins during which a warning is issued and login is allowed

             PASSWORD_LOCK_TIME - number of days an account will be locked after maximum failed login attempts

             PASSWORD_REUSE_TIME - number of days before a password can be reused

             PASSWORD_REUSE_MAX - number of password changes required before the current password can be reused

             PASSWORD_VERIFY_FUNCTION - password complexity verification script

 

             Example:

              1) Create the profile:                                  CREATE PROFILE profile_A LIMIT FAILED_LOGIN_ATTEMPTS  7; 

  2) Associate the profile with an user:          ALTER USER scott PROFILE profile_A

 

 

How to connect as sys without knowing his password

 

If an administrative OS users belongs to the "dba" group on Unix or to the "ORA_DBA" (ORA_sid_DBA) group on NT, we can connect to oracle like this:

 

connect / as sysdba

 

We can use show user  command to verify that we are connected as SYS.

 

 

How to connect as a regular user without knowing his password

 

   1) Select the encrypted password value

        SQL> SELECT password FROM dba_users WHERE username='SCOTT';

           

          PASSWORD

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

          D794344J35502S67

 

    2) Change Scott's password (temporarily)

    

           SQL> ALTER USER scott identified by new_pass;

    

    3) Connect using this new password

    

         SQL> connect scott/lion

   

    4) Connect as SYS (or SYSTEM)

  

           SQL> connect / as sysdba

    

    5) re-enable the first Scott's password

           SQL> ALTER USER scott identified by values 'D794344J35502S67';

 

Allowing/ Removing SELECT, DELETE, UPDATE, INSERT privileges on tables

 

GRANT select, update, delete, insert on SCOTT.EMP to PAUL;

REVOKE select, update, delete, insert on SCOTT.EMP FROM PAUL;

 

"PAUL" could be a user (schema) or a role.

 

 

Allowing/ Removing EXECUTE privileges on procedures, functions, packages

 

grant EXECUTE on SCOTT.F1 to PAUL;               (Paul will be able to run (execute) scott.F1 function )

revoke EXECUTE on SCOTT.F1 from PAUL;

 

 

Using Roles

 

Sometimes, the same object, system privileges must be granted to many users. For this purpose we can create a ROLE, grant all the privileges we want to this role and after that we can grant the role to a user or many users. 

 

1) Create the role (named "ROLE1" in this example)

    CREATE ROLE role1;

 

2) Grant privileges to this role

     GRANT select, update, delete, insert on SCOTT.EMP to ROLE1;

     grant EXECUTE on SCOTT.F1 to ROLE1;  

 

 3) Grant the Role to an user

      GRANT role1 TO paul;

 

 

REMOTE_LOGIN_PASSWORDFILE parameter 

 

This parameter is used for managing the SYS authentication. 

 

If REMOTE_LOGIN_PASSWORDFILE='NONE', Oracle will not check for a password file (we can input any user name, any password, we will be connected as SYS).

If REMOTE_LOGIN_PASSWORDFILE='SHARED' (or 'EXCLUSIVE' in 9i), Oracle will check for a password file and we can connect as SYS only if the password is the good one. (RECOMMENDED)

 

 

REMOTE_OS_AUTHENT parameter

 

The REMOTE_OS_AUTHENT parameter lets the Oracle database decide if the authentication can be performed by the remote operating system or if it must be performed by the database. 

 

REMOTE_OS_AUTHENT = TRUE  => Authentication just on the remote OS and no authentication on the database side.

REMOTE_OS_AUTHEN = FALSE =>  Only database authentication is available from these remote systems. (RECOMMENDED)

 

 

In-Oracle.com  -> Oracle DBA -> Oracle Maintenance Tasks

-> Managing Data Security in Oracle

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...

 

     Copyright (c) 2011-2015  www.in-oracle.com  |  Disclaimer: The views expressed on this web site are my own and do not reflect the views of Oracle Corporation. You may use the information from this site only at your risk.