Part 2: Controlling Data Access and Restricting Privileged Data in Oracle Database
By Troy Kitch on Feb 21, 2014
This is the second post on controlling data access and restricting privileged data in Oracle Database, pulled from the ebook, Securing Oracle Database 12c: A Technical Primer. The first post can be found here. The book highlights new features found in Oracle Database 12c; however, the majority of the solutions are applicable to earlier Oracle Database releases as well.
Users are expected to provide the password when they connect to the database, but applications, middle-tier systems, and batch jobs cannot depend on a human to type the password. Earlier, a common way to provide passwords was to embed user names and passwords in the code or in scripts. This increased the attack surface and people had to make sure that their scripts were not exposed to anyone else. Also, if passwords were ever changed, changes to the scripts were required. Now you can store password credentials by using a client-side Oracle wallet. This reduces risks because the passwords are no longer exposed on command-line history, and password management policies are more easily enforced without changing application code whenever user names or passwords change.
To configure password storage using an Oracle wallet, set the WALLET_LOCATION parameter in the sqlnet.ora file. The applications can then connect to the database without providing login credentials, as follows:
Users need to be authenticated before being allowed to connect to the database. Oracle supports different means of authentication including passwords stored locally within the database or in directories. Users can also be authenticated by the operating system, using the IDENTIFIED EXTERNALLY clause when creating the user, or by various third-party authentication services, including Kerberos, SSL/TLS, and RADIUS. Passwords are only used for one-way authentication of the user to the database, while Kerberos and PKI support mutual authentication, ensuring that the user is indeed connecting to the proper database.
Oracle clients and servers communicating over SSL/TLS must have a wallet containing an X.509 certificate, a private key, and a list of trusted certificates. An administrator sets up this configuration using Oracle Wallet Manager to create the wallet to store the PKI credentials and Oracle Net Manager to configure sqlnet.ora and listener.ora for SSL authentication. The following example shows how to create a user with the PKI certificate:
SQL> CREATE USER jsmith IDENTIFIED EXTERNALLY AS 'cn=jsmith,OU=HR,O=oracle,c=US';
Users can authenticate to the database using Kerberos in environments that support that service. This capability is configured by setting the required parameters in the Oracle Database server and client sqlnet.ora files using Oracle Net Manager. The following example shows how to create an externally authenticated user that corresponds to the Kerberos user:
SQL> CREATE USER jsmith IDENTIFIED EXTERNALLY AS 'email@example.com';
You can now connect to an Oracle Database server without using a user name or password as follows:
$ sqlplus /@hr_db.example.com;
Centralized User Management
In an enterprise with a number of users accessing a number of databases, it is difficult to manage unique accounts for each user in every database. Oracle Enterprise User Security (EUS) enables centralized management of users and roles across multiple databases in Oracle Internet Directory, which integrates with other directories such as Microsoft Active Directory. Such users are called enterprise users, and they can be assigned enterprise roles that determine access privileges across multiple databases. An enterprise role consists of one or more global roles that grant database privileges to specific databases.
EUS allows users and administrators to be authenticated by Oracle Internet Directory using a password, Kerberos, or SSL. Upon connecting, the database refers to the directory for user authentication, authorization (roles) information, and schema mapping. Enterprise users can have their own schema, or they can share a global schema in the databases they access. Here is an example of an enterprise user with an exclusive schema, jsmith.
CREATE USER jsmith IDENTIFIED GLOBALLY AS 'CN=jsmith,OU=HR,O=oracle,C=US';
Stay tuned for Users with Administrative Privileges, Proxy Authentication and Authorization, Basic Access Control, and more. Or, you can read ahead by downloading the complimentary ebook here.