Managing Database Users

3 Managing Database Users

Oracle recommends using named accounts instead of the generic SYS and SYSTEM accounts. This section explains how to create database users and separate duties through roles, realms, command rules, and authorizations.

Once Oracle Database Vault is enabled, separation of duties is enforced through roles, realms, command rules, and authorizations. For example:

To create a user, you must have the DV_ACCTMGR role which is granted the CREATE USER system privilege.
To protect a schema from database users granted SELECT ANY TABLE system privilege, create a realm to limit access to only authorized users.
To stop a user with the DROP TABLE privilege from performing this action, create a command rule on DROP TABLE.
To perform an Oracle Data Pump import of a table that is protected by a Database Vault realm or command rule, you must have the ability to import with Oracle Data Pump and be granted the Database Vault authorization.

For more information on these controls, see the Oracle Database Vault Administrator’s Guide.

After you have enabled Oracle Database Vault, you will see that SYS is no longer able to perform certain actions. This is intentional because SYS should not be an account used except for patching, upgrading, and special circumstances. The SYSTEM account is also an account that should not be used unless necessary. SYSTEM is a highly privileged account that is difficult to assign to a single user. Oracle recommends using named accounts (for example, jsmith, cmack, gkramer, and so on) instead of shared, or generic, accounts.

For example, named accounts can be set up like those in the following table to ensure separation of duties. You will learn how to set up these accounts in the following topic.

Table 3-1 Example Named Accounts

Username	Location	Responsibilities
`C##DVOWNER`	CDB & PDBs	Database Vault owner
`C##DVOWNER_BACKUP`	CDB & PDBs	Database Vault owner backup account
`C##DVACCTMGR`	CDB & PDBs	Database Vault account management
`C##DVACCTMGR_BACKUP`	CDB & PDBs	Database Vault account management backup account
`C##JSMITH`	CDB & PDBs	DBA, Database Vault owner and account manager
`C##CMACK`	CDB & PDBs	Audit administration
`GKRAMER`	PDB	DBA
`HR`	PDB	Application owner

Oracle Database Vault attempts to protect database user accounts from being misused or abused by privileged users. Once you have configured and enabled Oracle Database Vault, you must have the Oracle Database Vault role DV_ACCTMGR to create a user. This applies to the ALTER USER and the DROP USER system privileges as well as PROFILE management system privileges.

3.1 Creating Named Database Accounts

Learn how to create named database accounts to replace the generic, SYS and SYSTEM, accounts.

Prerequisites

Have an account that has been granted the DV_ACCTMGR role, such as the C##DVACCTMGR user that you created during Configuring Database Vault on the Container Database. This user should have the privileges to create accounts and grant the DV_ACCTMGR role to other users.

Connect as a user that has been granted the DV_ACCTMGR role:
```
connect c##dvacctmgr
```

Create a named user account, C##JSMITH and grant them the DV_ACCTMGR role:

CREATE USER C##JSMITH IDENTIFIED BY <password> CONTAINER=ALL;
GRANT DV_ACCTMGR TO C##JSMITH WITH ADMIN OPTION CONTAINER=ALL;

Connect as a user that has been granted the DV_OWNER role:
```
connect c##dvowner
```
Grant JSMITH the role of DV_ADMIN with the ability to pass the role on to other users:
```
GRANT DV_ADMIN TO C##JSMITH WITH ADMIN OPTION CONTAINER=ALL;
```
Granting DV_ADMIN WITH ADMIN OPTION will allow JSMITH to create, manage and delete policies, realms, command rules, rules and rule sets but not disable Oracle Database Vault. These privileges are a subset of the privileges granted to DV_OWNER.

Granting this role to a named account should allow you to securely store the shared accounts (C##DVOWNER, C##DVOWNER_BACKUP, C##DVACCTMGR, C##DVACCTMGR_BACKUP) and only use them for emergencies. Day-to-day operations should be completed by database users who are using their own named credentials.
Connect as SYSDBA user:
```
connect / as sysdba
```
Grant the DBA role to C##JSMITH and include WITH ADMIN OPTION so the user can forward-grant privileges to other database users:
```
GRANT DBA TO C##JSMITH WITH ADMIN OPTION CONTAINER=ALL;
GRANT RESOURCE TO C##JSMITH WITH ADMIN OPTION CONTAINER=ALL;
GRANT AUDIT_ADMIN TO C##JSMITH WITH ADMIN OPTION CONTAINER=ALL;
```
Note:
Oracle recommends creating a subset of system and object privileges in a custom role, rather than using the DBA role.