Lecture 6 - DBryant

Since I submitted this through Angel, I cannot go back and retrieve what I typed.  Here is the assignment I copied from your post:

1.DBA creates users and administers User Security.

Example:

CREATE USER james
  IDENTIFIED BY mia0101
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp
  QUOTA UNLIMITED ON users
  PROFILE default
  PASSWORD EXPIRE
  ACCOUNT UNLOCK;

2.DBA grants and revoke privileges.

Example:
GRANT SELECT,INSERT,UPDATE,DELETE ON customers TO sales_manager;
GRANT SELECT ON customers TO public;
GRANT SELECT ON sales.customers TO sales_admin WITH GRANT OPTION;


3.DBA creates and manages roles.

Example:
CREATE ROLE appl_dba;
SET ROLE appl_secure IDENTIFIED BY seekwrit;

4.DBA assigns resource limits to a profile, use the CREATE PROFILE or ALTER PROFILE statement.

Example:
CREATE PROFILE "TEST1" LIMIT
CPU_PER_SESSION DEFAULT
CPU_PER_CALL DEFAULT
CONNECT_TIME DEFAULT
IDLE_TIME 10
SESSIONS_PER_USER DEFAULT
LOGICAL_READS_PER_SESSION DEFAULT
LOGICAL_READS_PER_CALL 250000
PRIVATE_SGA 25000
COMPOSITE_LIMIT DEFAULT;

5.DBA is responsible for implementing Password Security

For example:
CREATE PROFILE "TEST2" LIMIT
PASSWORD_LIFE_TIME 60  PASSWORD_GRACE_TIME 7
PASSWORD_REUSE_MAX 2   PASSWORD_REUSE_TIME 4
PASSWORD_LOCK_TIME DEFAULT FAILED_LOGIN_ATTEMPTS 5
        PASSWORD_VERIFY_FUNCTION DEFAULT;


6.DBA enables auditing of specific SQL statements with an AUDIT statement.

Example:
AUDIT table;
AUDIT table BY juanita;
AUDIT table BY juanita WHENEVER NOT SUCCESSFUL;


7.DBA disable auditing of a specific SQL statement, use a NOAUDIT statement.

Example:
NOAUDIT session;
NOAUDIT not exists;
NOAUDIT table BY juanita;
AUDIT INSERT TABLE BY juanita BY ACCESS;

8.DBA purges audit records older than 90 days, execute the following as user SYS:

Example:
DELETE FROM sys.aud$ WHERE timestamp# < SYSDATE -90;

9.DBA creates a new Fine-grained auditing (FGA) policy, use the packaged procedure DBMS_FGA.ADD_POLICY.

Example:
DBMS_FGA.ADD_POLICY(object_schema=>’HR’
   ,object_name=>’EMPLOYEES’
   ,policy_name=>’COMPENSATION_AUD’
   ,audit_column=>’SALARY, COMMISSION_PCT’
   ,enable=>FALSE
   ,statement_types=>’SELECT’);

10.DBA enables Unified Auditing, shut down the database and listener, and then relink the oracle executable using the following options:

Example:
cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk uniaud_on ioracle ORACLE_HOME=$ORACLE_HOME