Oracle Interview Preparation Notes Part 4

Managing Passwords:

Profiles:
· Contain password aging and expiration, history, verification and account lock info.
· Contain resource limits (CPU, IO, idle time, connect time, sessions) Per session or per call.

SET RESOURCE_LIMIT = TRUE in init.ora or ALTER SYSTEM SET RESOURCE_LIMIT=TRUE
UTLPWDMG.SQL – Creates the verify function for passwords and changes the default profile.

ALTER PROFILE DEFAULT LIMIT
PASSWORD_LIFE_TIME=60 PASSWORD_ GRACE_TIME 10 PASSWORD_REUSE_TIME 1800
PASSWORD_REUSE_MAX Unlimited FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME 1/1440 PASSWORD_VERIFY_FUNCTION verify_function

Password files:

Used when SYSTEM authentication is not used to verify groups of DBAs (SYSOPER and SYSDBA) – need password to connect internal.
· Set parameter REMOTE_LOGIN_PASSWORD_FILE=EXCLUSIVE (SHARED=only SYS and INTERNAL, NONE=Use OS authentication.
· Run the ORAPWD utility
Granting SYSOPER or SYSDBA to a user adds an entry to the password file.

V$PWFILE_USERS

Managing Users:

CREATE USER u1 IDENTIFIED [BY pwd | EXTERNALLY]
PASSWORD EXPIRE (force user to change) ACCOUNT [LOCK|UNLOCK] PROFILE DEFAULT
Users can be authenticated by OS or by Database using OS_AUTHENT_PREFIX of OPS$
CREATE USER OPS$user IDENTIFIED BY pswd
Set REMOTE_OS_AUTHENT=TRUE to allow other OS to authenticate (not recommended).

CREATE USER | ROLE … IDENTIFIED GLOBALLY
Allows single sign on in a distributed security domain server (SNS - Secure Network Services)

Managing Privileges:

GRANT system priv TO [user|role|PUBLIC] WITH ADMIN OPTION
· With admin – user can grant or revoke this privilege from others.
· Revoking sys privs from user with admin does NOT cascade (but can cause invalid objects)
User A is granted Create any table with Admin, A grants it to B, A create table, B creates table
DBA revokes priv from A è No tables are dropped, B can still create tables, A cannot.
Execute any procedure and select any table privs do NOT give access to data dictionary (SYS) object unless the parameter 07_DICTIONARY_ACCESSIBILITY=TRUE

GRANT object priv (column list) ON objectname TO [user|role|PUBLIC] WITH GRANT OPTION
· Grant option cannot be given to roles, only to users
· Revoking object privs with grant option DO cascade
User A grants select on tab1 to B, B grants select to C,
A revokes from B è User C can no longer select.

DBA_SYS_PRIVS, DBA_TAB_PRIVS, DBA_COL_PRIVS, SESSION_PRIVS

Auditing: Is done at execution only, not on parse
SYS.AUD$ - If full, all audited actions will fail. Since highly volatile, move out of system tablespace:
ALTER TABLE SYS.AUD$ MOVE TABLESPACE auditts
AUDIT_TRAIL=DB - Enables database auditing (sys.aud$), =OS - audit to the OS audit trail

Privilege Auditing:

AUDIT [statemnt | sys priv] [BY USER] [BY SESSION | ACCESS] WHENEVER [NOT] SUCCESSFUL
Object Auditing:
AUDIT [statement, ,] ON schema.object [BY SESSION | ACCESS] WHENEVER [NOT] SUCCESSFUL

ALL_DEF_AUDIT_OPTS, DBA_STMT_AUDIT_OPTS, DBA_PRIV_AUDIT_OPTS, DBA_OBJ_AUDIT_OPTS.

Managing Roles:

CREATE ROLE rolename [IDENTIFIED {BY passwd | EXTERNALLY}]
ALTER USER scott DEFAULT ROLE [ role,role | ALL {EXCEPT role,role} | NONE]
SET ROLE role [IDENTIFIED BY passwd] - cannot be executed in PL/SQL.
Embed passwd in application to prevent role access from outside application.

Fine Grained Access Control = Associate security policies within tables or views.
Appends a where clause to any SQL that accesses the table or view.

DBA_ROLES, DBA_ROLE_PRIVS, DBA_SYS_PRIVS, ROLE_ROLE_PRIVS, ROLE_SYS_PRIVS, ROLE_TAB_PRIVS, SESSION_ROLES.

USING NLS:

3 ways to set:
1) Set parameters in init.ora (server side defaults only).
2) As environment variables for the client (overrides server default)
3) As ALTER SESSION to override default for the current session only.

NLS_DATABASE_PARAMETERS (DB charset and National Char set only); NLS_INSTANCE_PARAMETERS, NLS_SESSION_PARAMETERS.

ALTER DATABASE

Use for physical changes to db structure
· Enable archiving (Alter database archivelog)
· Redo file management (add logfile group or member)
· File management (rename, resize)
· Mount, Open or Recover
· Rename or Resize Datafiles
· Media recovery
· Backup controlfile

ALTER SYSTEM

Use for instance changes
· Start archiving to disk (alter system archive log START) and switching
· Switch logfiles
· Force Checkpoints
· Enabling Restricted Session
· Set some instance parameters
· Kill sessions