Oracle Database 12c Security Cookbook: Secure your Oracle Database 12c with this valuable Oracle support resource, featuring more than 100 solutions to the challenges of protecting your data

To complete this recipe, you'll need an existing user who has create profile privilege (such as an OS-authenticated user who has database administrators (dba) role, for example, ops$zoran). Also, you'll need an unlocked user account named scott.

Make sure that the resource_limit parameter is set to true.

In step 1, you used OS authentication to connect to the database.

In step 2, you created a password profile with the name userprofile that has the following restrictions:

In step 3, we assigned a newly created password profile to the user scott. If we don't assign a password profile to the user, that user uses the default password profile.

In step 4, we altered the default password profile with the failed_login_attempts restriction.

You can create different password profiles for different users in the database. There are a lot of restrictions that can be applied to a password profile.

In Oracle Database 12c, there are three password verify functions, out of which, two are new and improved:

If password complexity checking is not enabled, and you want to use it, you should run the utlpwdmg.sql script provided by Oracle. It's located in $ORACLE_HOME/rdbms/admin. The ora12c_verify_function function is the default function that the utlpwdmg.sql script uses. If you want, you can customize password verify functions.

If you want to choose which verify function will be used in the default profile, you can achieve that by using the following statement:

alter profile default limit password_verify_function ora12c_strong_verify_function;

In subsequent recipes, it is assumed that default values are set for the default profile and the password verify function is not used.

To complete this recipe, you'll need an existing user who has create user privilege (you may use the OS-authenticated user who has the DBA role).

You'll use Oracle Enterprise Manager Database Express 12c (EM Express). To learn more about it (for example, how to configure an HTTPS port for EM Express and how to start it), see the third chapter of the official Oracle guide -Oracle Database 2 Day DBA, 12c Release 1.

In step 1, you used OS authentication to connect to the database.

In step 2, you created a password-authenticated user jessica with simpler password.

In step 3, you created a password-authenticated user tom with more complex password. In this case (because a password contains special characters), you are using quotation marks (") to enclose the password.

Both of these users are using the default password profile.

In step 4, you created a password-authenticated user with the assigned password profile userprofile.

In step 5, you created user john. This user has to change his password at the first database login.

In step 6, you created the user richard. In the create user statement, quota unlimited on users means that you want to let the user allocate space in the tablespace without bound. The quota clause lets you define the maximum amount of space the user can allocate in the tablespace. You can have multiple quota clauses for multiple tablespaces within one  create user statement. The unlimited tablespace system privilege enables users to have an unlimited quota on all tablespaces in the database.

You can also create users using Oracle Enterprise Manager Cloud Control 12c or Oracle Enterprise Manager Database Express 12c (EM Express). Oracle Enterprise Manager Database Control is no longer available in Oracle Database 12c.

To complete this recipe, you'll need an existing user who has alter user privilege (you may use OS-authenticated user who has the DBA role) and other existing users (for example, jessica and tom).

In step 1, you used OS authentication to connect to the database.

In steps 2 through 4, a privileged user changed jessica's password, where in steps 6 through 9, the user tom changed his own password.

There is another way to change the user's password using the alter user statement as follows:

SQL> alter user jessica identified by oracle_2;

To complete this recipe, you'll need:

In step 1, you used OS authentication to connect to the database.

In step 2, you found a DDL statement that has been used for user creation. This DDL statement may contain default and temporary tablespace assignments (note that even if you haven't explicitly assigned these tablespaces during user creation, the system will assign them implicitly using default values for the database). For instance, output in step 2 may look like this:

SQL> select dbms_metadata.get_ddl('USER', 'JESSICA') from dual;
    
DBMS_METADATA.GET_DDL('USER','JESSICA')
------------------------------------------------------------------
CREATE USER "JESSICA" IDENTIFIED BY VALUES 'S:D82E6EF961F2EA7A878BCDDBC7E5C542BC148C4759D19A720A96BBF65658;H:F297A50FD538EF4AB1 19EB0278C9E72D;C50B1E9C9AA52EC2'
DEFAULT TABLESPACE "USERS"            TEMPORARY TABLESPACE "TEMP"

However, we used only the first part of this DDL in step 4 to create a user on the second database (and let the database decide about default tablespaces).

There is another way to accomplish the task.

This way requires select on the sys.user$ table:

To complete this recipe, you'll need an existing (for example, OS-authenticated) user who has alter user privilege (you may use user who has a DBA role) and another existing user (for example, mike).

In step 1, you used OS authentication to connect to the database.

In step 2, you locked the account of user mike. This means that user mike cannot connect to the database:

    SQL> alter user mike account lock;

    User altered

    SQL> connect mike/welcome1

    ERROR: ORA-28000: the account is locked

However, objects in mike's schema are available, so users can access them (considering that they have necessary privileges):

    SQL> select a, b from mike.table1;
      A         B 
     ---------- --------- 
             1         3 
             2         4 
             4         9

In step 3, you unlocked the account of user mike. Now user mike can successfully connect to the database:

    SQL> alter user mike account unlock;

    User altered.

    SQL> conn mike/welcome1

    Connected.

To complete this recipe, you'll need an existing (for example, OS-authenticated) user who has the alter user privilege (you may use user who has a DBA role) and another existing user (for example, mike).

In step 1, you used OS authentication to connect to the database.

In step 2, you expired password for the user mike. This means that the password is no longer valid and user mike must change his password after the next login:

    SQL> alter user mike password expire;

    User altered.

    SQL> conn mike/welcome1
    ERROR: ORA-28001: the password has expired
    Changing password for mike
    New password:
    Retype new password:
    Password changed
    Connected.

To complete this recipe, you'll need an existing user who has a dba role, for example, johndba. It is assumed that you are working on Linux.

In OS authentication, database delegates user authentication to the operating system. This means that in order for OS authentication to work, user must exist as the user of the operating system. In database, these users are created with a prefix that is defined in the os_authent_prefix parameter (default is ops$). If an OS-authenticated user has the create session privilege, he or she can connect to the database using the following syntax:

    SQL> connect /
    Connected.
    
    SQL> show user
    USER is "OPS$ZORAN"

 SQL> grant sysdba to ops$zoran;
    grant sysdba to ops$zoran

ERROR at line 1: ORA-01997: GRANT failed: user
      'OPS$ZORAN' identified externally

If you want to connect as sysdba using OS authentication, you have to add OS user zoran to OS group DBA:

[root@db121 ~]# usermod -a -G dba zoran
[root@db121 ~]# su - zoran
[zoran@db121 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Fri Sep 03 20:14:03 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64 bit
Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

You can change the os_authent_prefix parameter with custom value (or you can leave it blank if you want OS-authenticated database users to have the same name as OS users).

To complete this recipe, you'll need an existing (for example, OS-authenticated) user who has a DBA role and another existing user (for example, mike).

Proxy authentication is best-suited type of authentication for three-tiered environments. The middle tier is represented as a proxy user in the database and this user can authenticate end-users in such a way that these end users can be audited by the database. In the second step, you created a user appserver (to be the proxy user). In the third step, you granted this user only the create session privilege.

In step 4, you authorized user mike to connect through proxy user appserver. This means that the user appserver can connect to the database on behalf of user mike:

SQL> connect appserver[mike]

Enter password:
Connected.

SQL> show user
USER is "MIKE"

SQL> select sys_context('USERENV','PROXY_USER') from dual;
SYS_CONTEXT('USERENV','PROXY_USER')
-----------------------------------
APPSERVER

To see proxy users, you can query the proxy_users view:

SQL> select * from proxy_users;

PROXY      CLIENT  AUT   FLAGS---------- ------- ---- ------------------------------------
APPSERVER   MIKE    NO   PROXY MAY ACTIVATE ALL CLIENT ROLES

In the last step, you revoked authorization from user mike to connect through proxy user appserver. This means that the user appserver can no longer connect to the database on behalf of user mike.

You can control which roles the proxy user can activate for user. By default, all user roles are activated. If you want the proxy user to activate only particular roles (or no roles) for a user, you can do that by adding the WITH ROLES <role1, role2, .., roleN> (or WITH NO ROLES) clause at the end of the alter user statement. For instance, if the user mike has many roles (including usr_role), and you want him to have only usr_role when he is connected through proxy user appserver, statement will look like this:

SQL> alter user mike grant connect through appserver with roles usr_role;


User altered.


SQL> connect appserver[mike]


Enter password:
Connected.


SQL> select * from session_roles;


ROLE
------------
USR_ROLE


SQL> connect mike


Enter password:
Connected.


SQL> select count(*) from session_roles;


COUNT(*)
--------
25

You can request reauthentication of a user to the database. This means that during proxy authentication, a user's password must be provided. This is done by using the authentication required clause at the end of alter user statement:

SQL> alter user mike grant connect through appserver authentication required;
User altered.

For this recipe, you will need an existing (for example, OS-authenticated) user that has a dba role and another three existing users (for example, mike, tom, and jessica). It is assumed that sample schemas are installed.

In the first step, you used OS authentication to connect to the database. In steps 2 and 3, you granted system privileges and object privileges, respectively, to the role usr_role. In the next steps, you practiced using database roles; you granted the following:

You revoke privileges and roles by using a revoke statement. For example:

SQL> revoke usr_role from mike;

SQL> grant role1 to role2;
Grant succeeded.

SQL> grant role2 to role1;
grant role2 to role1
*
ERROR at line 1: ORA-01934: circular role grant detected

Suppose that user mike grants object privilege to user jessica with a grant option and user jessica grants that privilege to user tom. If user mike revokes that privilege from jessica, it will be automatically revoked from tom.

SQL> grant select on hr.employees to jessica with grant option;
Grant succeeded.

SQL> connect jessica
Enter password:
Connected.

SQL> grant select on hr.employees to tom;
Grant succeeded.

SQL> connect tom/oracle_123
Connected.

SQL> select count(*) from hr.employees;
COUNT(*)
----------
 107

SQL> connect mike/welcome1
Connected.

SQL> revoke select on hr.employees from jessica;
Revoke succeeded.

SQL> connect tom/oracle_123
Connected.

SQL> select count(*) from hr.employees;
select count(*) from hr.employees
*
ERROR at line 1:
ORA-00942: table or view does not exist

For this recipe, you'll need:

Instructions are given in the Database authentication and OS authentication sections.

You can use either Oracle Recovery Manager (RMAN) or SQL*Plus to perform the operations. When you connect to the database as sysbackup, you are connected as a predefined user sysbackup. If you want to check this, run the following statement:

SQL> select user from dual;

Otherwise, the following statement:

SQL> show user

Using the sysbackup privilege, you can connect to the database even when it is not open. This privilege enables better separation of duties and the implementation of the least privilege principle.

To view the list of privileges a user can exercise when connected to the database using sysbackup privilege, you can create a user (for example, tom) and grant the user only sysbackup privileges. The next step is to connect to the database as user tom, using the sysbackup privilege and the execute statement:

select * from session_privs;

These privileges are shown in the following table:

This is how you can check enabled roles:

SQL> select * from session_roles;

ROLE
-------------------
 SELECT_CATALOG_ROLE
 HS_ADMIN_SELECT_ROLE

If you want to view the roles and privileges granted to sysbackup, you can query DBA_ROLE_PRIVS and DBA_SYS_PRIVS:

SQL> select * from dba_role_privs where grantee='SYSBACKUP';
SQL> select * from dba_sys_privs where grantee='SYSBACKUP';

Also, this new administrative privilege enables you to select, insert, delete, execute, and perform operations:

You can't drop user sysbackup. In a multitenant environment, you can restrict a user to be able to perform backups only for the PDB it can connect to. You can accomplish that by creating a local user in the PDB and granting the sysbackup privilege to the user. When you are connected to the database as the sysbackup, you are connected as sysbackup user to SYS schema:

SQL> connect / as sysbackup
Connected.

SQL> show user
USER is "SYSBACKUP"

SQL> select sys_context( 'userenv', 'current_schema' ) from dual;
SYS_CONTEXT('USERENV','CURRENT_SCHEMA')---------------------------------------SYS

For this recipe, you'll need:

Instructions are split into sections for database authentication and OS authentication.

When you connect to the database as syskm, you are connected as a predefined user, syskm. Using the syskm privilege, you can connect to the database even when it is not open.

In most circumstances when using TDE, you don't have to have syskm administrative privilege. For a more detailed discussion about TDE operations and which privileges users need, see recipes in Chapter 8, Transparent Data Encryption.

In the Database authentication section after completing step 3, you can perform operations related to managing the TDE keystore. Step 4 is not necessary and its sole purpose is to show you which privileges you can use when connected as syskm. These privileges are:

You can't drop user syskm. When you are connected to the database as syskm, you are connected as the syskm user to SYS schema:

SQL> connect / as syskm
Connected.

SQL> show user
USER is "SYSKM"

SQL> select sys_context( 'userenv', 'current_schema' ) from dual;
SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
---------------------------------------
SYS

For this recipe, you'll need:

Instructions are split into sections for database authentication and OS authentication.

When you connect to the database as sysdg, you are connected as a predefined user, sysdg. Using the sysdg privilege, you can connect to the database even when it is not open.

After completing step 2 successfully in the Database authentication section, user mike, as expected, can grant/revoke sysdg privilege to/from another existing user. If you want to try it out, type the statements given here.

After you connect to the database using the sysdg administrative privilege, you can perform the following operations:

You can't drop user sysdg. When you are connected to the database as sysdg, you are connected as sysdg user to the SYS schema:

SQL> connect / as sysdg
Connected.

SQL> show user
USER is "SYSDG"

SQL> select sys_context( 'userenv', 'current_schema' ) from dual;
SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
------------------------------------------------------------------
SYS