Controlling Data Access and Restricting Privileged Data in Oracle Database
By Troy Kitch-Oracle on Feb 19, 2014
In a series of blog posts I will be pulling excerpts directly from the ebook Securing Oracle Database 12c: A Technical Primer by Michelle Malcher, Paul Needham, and Scott Rotondo. Previously, I posted the introduction of the book and now I will continue with the first chapter: Controlling Data Access and Restricting Privileged Users. If you don't want to wait for each post, I encourage you to download your own free copy of the book.
Controlling Data Access and Restricting Privileged Users
The most fundamental step in securing a database system is determining who should be able to access which data. This chapter describes the management of user accounts and the mechanisms for determining the access that each user has. It continues with a discussion of the types of privileged access that a user may have and available tools for removing any additional access they do not need.
All access to the database is through users, whether these are administrative users, application accounts, or regular users. As the users have direct connection to the database, it is important that they are properly authenticated and have appropriate roles, and that their accounts cannot easily be compromised. It is also important to ensure that there are proper resource constraints on their usage, or else the rest of the database may be indirectly affected.
The CREATE USER statement is used to create a database user and its associated schema. In the following example, the user is identified by a password, and the account follows the policy specified by org_profile.
CREATE USER jsmith IDENTIFIED BY NoOne!Knows PROFILE org_profile DEFAULT TABLESPACE data_ts TEMPORARY TABLESPACE temp_ts;
A profile specifies a named set of resource limits and password parameters that restricts excessive consumption of system resources and enforces constraints on the passwords. The password-specific parameters provide password management including account locking, password aging, password history, and password complexity verification. The password verification function is perhaps the most important control to ensure that users pick complex passwords, making it difficult for intruders to guess them. The FAILED_LOGIN_ATTEMPTS parameter limits brute-force password-guessing attacks by locking the account after a specified number of incorrect logins.
CREATE PROFILE org_profile LIMIT
FAILED_LOGIN_ATTEMPTS 6 -- attempts allowed before locking
PASSWORD_LIFE_TIME 180 -- max life-time for the password
PASSWORD_VERIFY_FUNCTION ora12c_verify_function; -- Password complexity check
The dictionary views DBA_USERS and DBA_PROFILES describe the users and profiles, respectively. The privilege to create users must be limited to the DBA or the security administrator. Each user should have an assigned tablespace; otherwise, any objects they create would go into the SYSTEM tablespace, thus creating contention between the data dictionary objects and the user objects.
Oracle Multitenant Database Users
Oracle Multitenant, an Oracle Database 12c option, includes both common and local users. A common user is created in the container database and has the same user name and password in all of the pluggable databases that are part of the container database. The common user can have privileges that are granted at the container level, and other privileges that are granted in each pluggable database. The privileges can be different in each of the pluggable databases, but the user doesn’t need to be created in each pluggable database.
To create a common user for the container database and all of the pluggable databases, log in to the container database as SYSTEM and create a user with CONTAINER=ALL. Note that all common user names begin with the prefix C##.
SQLPLUS> CONNECT SYSTEM@root
Enter password: **********
SQLPLUS> CREATE USER C##DB_ADMIN
IDENTIFIED BY IronMan4
CONTAINER = ALL;
A local user, on the other hand, is created in the pluggable database, and does not have access to the container. This is good for the administrator who manages a pluggable database but does not manage the overall system. To create a local user, connect to the pluggable database as SYSTEM, create the user, and grant the needed roles and privileges as before, but specify CONTAINER=CURRENT instead of CONTAINER=ALL.
SQLPLUS> CONNECT SYSTEM@pdb1
Enter password: *********
SQLPLUS> CREATE USER pdb1_admin
IDENTIFIED BY SpiderMan3
CONTAINER = CURRENT;
Stay tuned for more...