SG I: Chapter 6 | User Administration and Security

Three authentication methods for your user accounts

Password Authenticated Users

Password authenticated user accounts are the most common and are sometimes referred to as database authenticated accounts.

CREATE USER rajesh IDENTIFIED BY welcome;

Externally Authenticated Users

to create an externally authenticated user named oracle, using the default OS_AUTHENT_PREFIX, you execute the following:

CREATE USER ops$oracle IDENTIFIED EXTERNALLY;

The keywords IDENTIFIED EXTERNALLY tell the database that this user account is an externally authenticated account.

Globally Authenticated Users

The syntax for creating a globally authenticated account depends on the service called, but all use the keywords IDENTIFIED GLOBALLY, which tell the database to engage the advanced security option for authentication. Here is an example:

CREATE USER spy_master IDENTIFIED GLOBALLY AS 'CN=spy_master, OU=tier2, O=security, C=US';

Assigning a Default Tablespace

ALTER USER rajesh DEFAULT TABLESPACE users;

ALTER DATABASE DEFAULT TABLESPACE users;

Assigning a Temporary Tablespace

Every user is assigned a temporary tablespace in which the database stores temporary segments. Temporary segments are created during large sorting operations, such as ORDER BY, GROUP BY, SELECT DISTINCT, MERGE JOIN, or CREATE INDEX

ALTER USER rajesh TEMPORARY TABLESPACE temp; -- temporary tablespace

Assigning a Profile to a User

A profile serves two purposes: first, it can limit the resource usage of some resources, and second, it can enforce password-management rules.

CREATE USER jiang IDENTIFIED BY "kneehow.ma"

DEFAULT TABLESPACE users

TEMPORARY TABLESPACE temp

PROFILE resource_profile;

Removing a User from the Database

To drop both user rajesh and all objects he owns, execute the following:

DROP USER rajesh CASCADE;

Granting and Revoking Privileges

Object privileges Permissions on schema objects such as tables, views, sequences, procedures, and packages.

System privileges Permissions on database-level operations, such as connecting to the database, creating users, altering the database, or consuming unlimited amounts of tablespace.

Role privileges Object and system privileges that a user has by way of a role. Roles are tools for administering groups of privileges.

Granting Object Privileges

Object privileges bestow upon the grantee the permission to use a schema object owned by another user in a particular way.

GRANT SELECT ON sales.customers TO sales_admin WITH GRANT OPTION;

Revoke select on customers from user –revoke will be cascade

Granting System Privileges

In general, system privileges permit the grantee to execute Data Definition Language (DDL) statements—such as CREATE, ALTER, and DROP—or Data Manipulation Language (DML) statements system wide.

GRANT index any table TO appl_dba WITH ADMIN OPTION;

--the revoke will not be cascade. Because only the grantee is recorded.

Role Privileges

Role privileges confer on the grantee a group of system, object, and other role privileges.

Creating and Managing Roles

A role is a tool for administering privileges.

To create the role APPL_DBA, execute the following:

CREATE ROLE appl_dba;

To enable a role, execute a SET ROLE statement, like this:

SET ROLE appl_dba IDENTIFIED BY seekwrit;

Granting Role Privileges

Granting privileges to PUBLIC allows anyone with a database account to exercise this privilege.

GRANT index any table TO appl_dba WITH ADMIN OPTION;

When it comes to granting a role WITH ADMIN OPTION, roles behave like system privileges, and subsequent revocations do not cascade.

Enabling Roles

Roles can be enabled—or disabled for that matter—selectively in each database session.

For example, to enable the password-protected role HR_ADMIN, together with the unprotected role EMPLOYEE, execute the following:

SET ROLE hr_admin IDENTIFIED BY “my!seekrit”, employee;

Identifying Enabled Roles

The roles that are enabled in your session are listed in the data dictionary view SESSION_ROLES.

To identify the roles granted to either user or the special user PUBLIC, run the following:

SELECT granted_role FROM user_role_privs

WHERE username IN (USER, 'PUBLIC');

To identify the roles that are both enabled in your session and granted directly to you or PUBLIC but not those roles that you inherited, run this:

SELECT role FROM session_roles

INTERSECT

SELECT granted_role FROM user_role_privs

WHERE username IN (USER, 'PUBLIC');

Disabling Roles
Roles can be disabled in a database session either en masse or by exception.
There is no way to selectively disable a single role. There is no way to selectively disable a single role. Also, you cannot disable roles that you inherit by way of another role without disabling the parent role.

Setting Default Roles
Roles that are enabled by default when you log on are called default roles. You do not need to specify a password for default roles and do not have to execute a SET ROLE statement to enable a default role.
Controlling Resource Usage by Users
Assigning Tablespace Quotas
CREATE USER chip IDENTIFIED BY "Seek!r3t"
QUOTA 100M ON USERS;

Assigning Resource Limits with a Profile
Profiles let you set limits for several resources, including CPU time, memory, and the number of logical reads performed during a user session or database call.
To enable resource limit restrictions with profiles, first enable them in the database by setting the initialization parameter resource_limit to true, like this:
ALTER SYSTEM SET resource_limit = TRUE SCOPE = BOTH;
Applying the Principle of Least Privilege
The principle of least privilege states that each user should only be given the minimal privileges needed to perform their job.
To implement the principle of least privilege on your production or development systems, you should take several actions, or best practices, while setting up or locking down the database.

Protect the data dictionary.
Revoke unnecessary privileges from PUBLIC.
Limit the users who have administrative privileges.
Do not enable REMOTE_OS_AUTHENT.

Managing Default User Accounts
When created via the DBCA, these special accounts are locked and expired, leaving only SYS, SYSTEM, SYSMAN, and DBSNMP open. The SYS and SYSTEM accounts are the data dictionary owner and an administrative account, respectively. SYSMAN and DBSNMP are used by Enterprise Manager.
ALTER USER mdsys PASSWORD EXPIRE ACCOUNT LOCK;

Implementing Standard Password Security Features

After creating this function as user SYS, assign it to a profile, like this:
ALTER PROFILE student LIMIT password_verify_function my_password_verify;

CREATE OR REPLACE FUNCTION my_password_verify
(username VARCHAR2
,password VARCHAR2
,old_password VARCHAR2
) RETURN BOOLEAN
IS
BEGIN
-- Check for the minimum length of the password
IF LENGTH(password) < 6 THEN
raise_application_error(-20001,'Password must be at least 6 characters long');
END IF;
-- Check that the password does not contain any
-- upper/lowercase version of either the user name
-- or the keyword PASSWORD
IF ( regexp_like(password,username,'i')
OR regexp_like(password,'password','i')) THEN
raise_application_error(-20002,'Password cannot contain username or PASSWORD');
END IF;
-- Check that the password contains at least one letter,
-- one digit and one punctuation character
IF NOT( regexp_like(password,'[[:digit:]]')
AND regexp_like(password,'[[:alpha:]]')
AND regexp_like(password,'[[:punct:]]')
) THEN
raise_application_error(-20003,'Password must contain at least one digit '||
'and one letter and one punctuation character');
END IF;
-- password is okey dokey
RETURN(TRUE);
END;

Auditing Database Activity
Auditing involves monitoring and recording specific database activity.
The default is NONE.
AUDIT_TRAIL=DB tells the database to record audit records in the database.
AUDIT_TRAIL=OS tells the database to record audit records in operating system files.
The four levels of auditing—statement, privilege, object, and fine-grained access—are described in detail in the following sections.

Managing Statement Auditing
Enabling Statement Auditing:
You enable auditing of specific SQL statements with an AUDIT statement.
AUDIT table;
AUDIT table BY juanita;
AUDIT table BY juanita WHENEVER NOT SUCCESSFUL;
AUDIT INSERT TABLE BY juanita BY ACCESS;

Identifying Enabled Statement Auditing Options:
FROM dba_stmt_audit_opts

Disabling Statement Auditing
To disable auditing of a specific SQL statement, use a NOAUDIT statement, which allows the same BY and WHENEVER options as the AUDIT statement.

Examining the Audit Trail
Statement, privilege, and object audit records are written to the SYS.AUD$ table and made available
via the data dictionary views DBA_AUDIT_TRAIL and USER_AUDIT_TRAIL.

Managing Privilege Auditing

Enabling Privilege Auditing
AUDIT create any table;
AUDIT create any table BY juanita;
AUDIT DELETE ANY TABLE BY juanita BY ACCESS;

Identifying Enabled Privilege Auditing Options
DBA_PRIV_AUDIT_OPTS

Disabling Privilege Auditing
NOAUDIT alter profile;
NOAUDIT delete any table BY juanita;
NOAUDIT alter user BY juanita;

Managing Object Auditing
Object auditing involves monitoring and recording the execution of SQL statements that require a specific object privilege, such as SELECT, INSERT, UPDATE, DELETE, or EXECUTE.
Unlike either statement or system privilege auditing, schema object auditing cannot be restricted
to specific users—it is enabled for all users or no users.

Enabling Object Auditing
AUDIT select ON hr.employee_salary;
-- one audit entry for each trigging statement
AUDIT select ON hr.employee_salary
BY ACCESS WHENEVER SUCCESSFUL;
-- one audit entry for the session experiencing one or more triggering statements
AUDIT select ON hr.employee_salary
BY SESSION WHENEVER NOT SUCCESSFUL;

Identifying Enabled Object Auditing Options
DBA_OBJ_AUDIT_OPTS
an A to indicate BY ACCESS, or an S to indicate BY SESSION.

Disabling Object Auditing
NOAUDIT select ON hr.employee_salary WHENEVER NOT SUCCESSFUL;
Purging the Audit Trail
Database audit records for statement, privilege, and object auditing are stored in the table SYS.AUD$.
DELETE FROM sys.aud$ WHERE timestamp# < SYSDATE -90;
To keep your SYSTEM tablespace from getting too large, you should regularly delete old entries from the sys.aud$ table.
Managing Fine-Grained Auditing
Fine-grained auditing (FGA) lets you monitor and record data access based on the content of the
data.With FGA, you define an audit policy on a table and optionally a column.

Creating an FGA Policy
To create a new FGA policy, use the packaged procedure DBMS_FGA.ADD_POLICY. This procedure has the following parameters:
Many parameter:
To create a new disabled audit policy named COMPENSATION_AUD that looks for SELECT statements that access the HR.EMPLOYEES table and references either SALARY or COMMISSION_PCT, execute the following:
DBMS_FGA.ADD_POLICY(object_schema=>’HR’
,object_name=>’EMPLOYEES’
,policy_name=>’COMPENSATION_AUD’
,audit_column=>’SALARY, COMMISSION_PCT’
,enable=>FALSE
,statement_types=>’SELECT’);

Enabling an FGA Policy
Use the procedure DBMS_FGA.ENABLE_POLICY to enable an FGA policy.
DBMS_FGA.ENABLE_POLICY(object_schema=>'HR'
,object_name=>'EMPLOYEES'
,policy_name=>'COMPENSATION_AUD');

Disabling an FGA Policy
To turn off a fine-grained access policy, use the DBMS_FGA.DISABLE_POLICY procedure.

Dropping an FGA Policy
To remove an FGA audit policy, use the DBMS_FGA.DROP_POLICY procedure.

Identifying FGA Policies in the Database
Query the DBA_AUDIT_POLICIES data dictionary view to report on the FGA policies defined in your database.Audit records from this policy, when enabled, capture the standard auditing information as well as the text of the SQL statement that triggered the auditing (DB_EXTENDED).

Reporting on the FGA Audit Trail Entries
The DBA_FGA_AUDIT_TRAIL data dictionary view is used in reporting on the FGA audit entries that have been recorded in the database.