Security & User Administration

SECURITY ADMINISTRATION

How does one change an oracle’s user password?

ALTER USER <username> IDENTIFIED BY <new_password>;

If you need to change another Users Password?

SQL> password SCOTT

Changing password for scott

New password:

Retype new password:

Password changed

How does one create, manager and drop Users?

CREATE USER scott 

       IDENTIFIED BY tiger                -- Assign password

        DEFAULT    TABLESPACE tools            -- Assign space for table and index segments

        TEMPORARY TABLESPACE temp;        -- Assign sort space

DROP USER scott CASCADE;                         -- Remove user

GRANT CONNECT, RESOURCE TO scott;

GRANT DBA TO scott;      -- Make user a DB Administrator

ALTER USER scott QUOTA UNLIMITED ON Users; --Give Some Quota on Tablespaces for users

ALTER USER scott ACCOUNT LOCK    -- lock a user account

ALTER USER scott ACCOUNT UNLOCK; -- unlocks a locked users account

ALTER USER scott PASSWORD EXPIRE; -- Force user to choose a new password

How does one enforce strict Password Policies?

CREATE PROFILE my_profile LIMIT

       PASSWORD_LIFE_TIME 30;

ALTER USER scott PROFILE my_profile;

• 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_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_LOCK_TIME - number of days an account will be locked after maximum failed login attempts
• PASSWORD_GRACE_TIME - number of days after the grace period begins during which a warning is issued and login is allowed
• PASSWORD_VERIFY_FUNCTION - password complexity verification script

Can one switch to another database user without a password?

Note:- DBAs can switch from one user to another without a password

SQL> ALTER USER scott IDENTIFIED BY anything;

User altered.

SQL> CONNECT scott/anything

Connected

OK, we're in. Let's quickly change the password back before anybody notices.

SQL> ALTER USER scott IDENTIFIED BY VALUES 'F894844C34402B67';

User altered.

How to create a Password file for an Oracle Database?

orapwd file=$ORACLE_HOME/dbs/orapw_orcl password=mypasswd;

What is a Virtual Private Database?

A VPD offers Fine-Grained Access Control (FGAC) for secure separation of data. This ensures that users only have access to data that pertains to them. Using this option, one could even store multiple companies' data within the same schema, without them knowing about it.

SQL> EXEC dbms_rls.add_policy

('SCOTT', 'MYTAB', 'LOCKIT', 'SCOTT', 'LOCKFUNC','INSERT,UPDATE', TRUE);

PL/SQL procedure successfully completed.

SQL> SELECT policy_name, sel, ins, upd, del, enable

  2    FROM user_policies

  3  /

POLICY_NAME                    SEL INS UPD DEL ENA

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

LOCKIT                         NO  YES YES NO  YES

SQL> INSERT INTO locktab SELECT 'two', 'twot' from dual;

INSERT INTO mytab SELECT 'two', 'twot' from dual

            *

ERROR at line 1:

ORA-28115: policy with check option violation

What is Fine Grained Auditing?

Fine Grained Auditing (DBMS_FGA) allows auditing records to be generated when certain rows are selected from a table. A list of defined policies can be obtained from DBA_AUDIT_POLICIES. Audit records are stored in DBA_FGA_AUDIT_TRAIL

-- Add policy on table with auditing condition...

execute dbms_fga.add_policy('HR', 'EMP', 'policy1', 'deptno > 10');

-- Must ANALYZE, this feature works with CBO (Cost Based Optimizer)

analyze table EMP compute statistics;

select * from EMP where c1 = 11;  -- Will trigger auditing

select * from EMP where c1 = 09;  -- No auditing

-- Now we can see the statements that triggered the auditing condition...

select sqltext from sys.fga_log$;

delete from sys.fga_log$;