A Appendix: Database Security-Related Practices

The following practices should be followed for ensuring database security.

Application Schema Owners

The following recommendations should be considered for the schema owners:

Database Administrators should create an individual schema owner for each application, unless the applications share the same data.

For example, the Oracle Retail Point-of-Service and Back Office applications share the same database.
The schema owners should only have enough rights to install the applications.

Set the following rights when using an Oracle database:
- CREATE TABLE
- CREATE VIEW
- CREATE INDEX
- CREATE SEQUENCE
- CREATE PROCEDURE
- ALTER SESSION
- CONNECT
- CREATE PROCEDURE
The user ID and password for schema owners should comply with PA-DSS user and password policies:
- Do not use group, shared, or generic accounts and passwords.
- Change user passwords at least every 90 days.
- Require a minimum password length of at least seven characters.
- Use passwords containing both numeric and alphabetic characters.
- Do not allow an individual to submit a new password that is the same as any of the last four passwords used.
- Limit repeated access attempts by locking out the user ID after not more than six attempts.
- Set the lockout duration to 30 minutes or until an administrator enables the user ID.
- If a session has been idle for more than 15 minutes, require the user to re-enter the password in order to re-activate the terminal.
Maintenance scripts should have their own user schema. Oracle Retail recommends a limited user be created at the database to handle schema maintenance (deleting data from the database). This can be accomplished by granting execute rights only on delete stored procedures.

Set the following rights when using an Oracle database:
- ALTER SESSION
- CONNECT
- GRANT EXECUTE on PROCEDURE
Maintenance scripts should only be run using a non-operating system administrator user ID.
Maintenance scripts should run as part of a secure schedule job:
- An operating system user should not have the ability to log in remotely to the database server.
- An operating system user should only use the scripts for maintenance purposes.
- An operating system user should not have administrator rights.
- An operating system user should not have execute, read, or write permission of any file beyond the file to execute the data maintenance script.

Database Security Considerations

The following recommendations should be considered for the database:

The database should be on its own dedicated server.
The database server should be in a private network.
The database server should be in a locked secure facility and inaccessible to non-administrator personnel.
The database should only be accessed using trusted network hosts.
The database server should have minimal use of ports and any communications should be under secure protocols.
The database server should be behind a firewall.
Any database user beyond the schema application owner should be audited.
Only minimal rights should be granted to the owner of database processes and files such that only that owner has the right to read and write from the database related files, and no one else has the capability to read and write from such files.

The purge script is usually put into an automation script, which runs once a day. As previously described, this script is usually run by a user with limited access (only execute procedure and connect access).

Restricted Access to Purge Scripts

The Release 14.1 Oracle Retail Stores applications come with stored procedures and scripts that permit a DBA to purge the databases of data that the retailer determines are no longer necessary to store. Access to these scripts should be restricted. Oracle Retail suggests the following guidelines to protect access to the database purge scripts:

The purge stored procedures should not be created unless the retailer is going to purge data.
Create only those stored procedures that will be used.
Create a separate database login that will execute the purge routine.
Assign minimal database access privileges to this user.
Create a public synonym to provide another layer of transparency.
Create a role that contains the privileges to execute the stored procedure.
Assign this role to the purge user.

Note:

The role with the execute procedure privilege does not need specific access to the underlying tables.

When a user executes another user's procedure, the procedure is executed using the privileges of the procedure owner, not the invoker. Thus the invoker does not have direct delete privileges on the tables contained within the stored procedure.

A public synonym provides a name for an object. This name can be used instead of the physical name of the object and its owner, thus providing another layer of transparency and security.

For example, the physical name for the purge EJournal stored procedure is
purge_ejrl. The synonym purge_ejournal can be created to rename the object logically. Then, the procedure can be referenced either by purge_ejrl or
purge_ejournal.

The following steps show how to create the synonym. Examples of the SQL statements used to create the synonym for an Oracle database are included.

Create the procedure.
```
        SQL> start purge_ejrl.sql
```

Create a public synonym for the procedure.

        SQL> CREATE PUBLIC SYNONYM purge_ejournal FOR pos.purge_ejrl;

Create a role.

        SQL> CREATE ROLE do_ejrl_purge;

Grant execute on the procedure to the role.

        SQL> GRANT EXECUTE ON purge_ ejrl TO do_ejrl_purge;

Create the user that will be calling the procedure.

        SQL> CREATE USER dopurge IDENTIFIED BY dopurge;

Grant minimal access to the user.
```
        SQL> GRANT SESSION TO dopurge;
```

Grant the role to the user.

        SQL> GRANT do_ejrl_purge TO dopurge;

Creating a Database Schema Owner and Data Source Users for Oracle Database

To create the database schema owner and data source users:

Log in using the database administrator user ID.
Create a role in the database to be used for the schema owner.
```
CREATE ROLE <schema_owner_role>;
```

Grant the privileges, shown in the following example, to the role.

GRANT CREATE TABLE, CREATE VIEW, CREATE SEQUENCE, CREATE PROCEDURE, ALTER SESSION, CONNECT TO <schema_owner_role>;

Create a role in the database to be used for the data source user.
```
CREATE ROLE <data_source_role>;
```

Grant the privileges, shown in the following example, to the role.

GRANT CONNECT, CREATE SYNONYM TO <data_source_role>;

Note:

After the product is installed successfully, the CREATE SYNONYM privilege must be revoked from the data source role. Before the application installer program exits, it prompts for a database administrator to revoke the privilege. For more information, see the product Installation Guide.

Create the schema owner user in the database.

CREATE USER <schema_username>
IDENTIFIED BY <schema_password>
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON users;

Grant the schema owner role to the user.

GRANT <schema_owner_role> TO <schema_username>;

Create the data source user.

CREATE USER <data_source_username>
IDENTIFIED BY <data_source_password>
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON users;

Grant the data source role to the user.

GRANT <data_source_role> TO <data_source_username>;

Special Security Options for Oracle Databases

Password policies can be enforced using database profiles. The options in the following table are based on version 11.2.0.4 of Oracle Database. The options can be changed using a SQL statement, for example:

alter profile appsample limit

Option	Setting	Description
PASSWORD_LOCK_TIME	30	Time account will be locked in minutes
PASSWORD_LIFE_TIME	90	Duration of current password in days
FAILED_LOGIN_ATTEMPTS	4	Maximum number of login attempts before the account is locked
PASSWORD_GRACE_TIME	3	Number of days a user has to change an expired password before the account is locked
PASSWORD_REUSE_MAX	10	Number of unique passwords the user must supply before the first password can be reused
PASSWORD_VERIFY_FUNCTION	<routine_name>	Name of the procedure that can be created to ensure the password is acceptable

Password policies can be enforced using a password complexity verification script, for example:

UTLPWDMG.SQL

The password complexity verification routine can ensure that the password meets the following requirements:

Is at least four characters long
Differs from the user name
Has at least one alpha, one numeric, and one punctuation mark character
Is not simple or obvious, for example, welcome, account, database, or user
Differs from the previous password by at least three characters

For example, to set the password to expire as soon as the user logs in for the first time:

CREATE USER jbrownIDENTIFIED BY zX83yT...PASSWORD EXPIRE;

Default Application Administrative Users

Oracle Retail Central Office, Back Office, and Point-of-Service have no pre-installed users defined by default. During installation, you are prompted for three separate user ID and password combinations:

Schema owner—This user is used to create the database. The schema owner is usually determined by the DBA.
Data source user—This user is used by the application to access the database. The data source user is usually determined by the DBA.
Application administrator user—This user is used to log in to the application. The password must be compliant with PCI-DSS section 8.2.