This chapter provides information about the security best practices that can be implemented for database management using Enterprise Manager 13. It contains the following sections:
Enterprise Manager 13.1 introduces flexible database access control for Enterprise Manager Database Plug-in. The new out of box roles align with database personas and provide tighter access control on managed target databases. Before the introduction of this feature an Enterprise Manager user granted access on the database had access to all of the database management features, such as performance management, high availability management, storage management, security management and so forth. Enterprises have different classes of users such as DBA, Application Developer, Application DBA, and Infrastructure DBA that need to access database management functions. There is a need for a flexible privilege model to accommodate these roles. For example, enterprises may want their application developers to access only performance management functions in a View Only mode.
Providing enterprise users access to unnecessary features and pages opens up the database to security vulnerabilities. Oracle recommends that you grant Enterprise Manager users the minimum number of privileges required to perform their job. Introducing these out of box database management roles grants users access to only the Enterprise Manager pages required to perform their job.
Fine grained privilege control for Enterprise Manager Database plug-in provides a privilege control model for database pages. This enables Enterprise Manager super administrators to grant the minimum access to Enterprise Manager administrators and users required to complete their more specific responsibilities.
High levels of security can be implemented using the new flexible DB access control features for database management. This section includes the following:
Oracle Enterprise Manager supports granting different levels of access to DBAs based on their roles and responsibilities in the organization. The following roles are recommended to implement security best practices for an organization.
Application DBA
An application DBA is a restricted database administrator who manages application schemas, application objects, and application performance in the database. An application DBA should be able to identify and fix application performance issues in the database. An application DBA is responsible for keeping the application up and running and in good performance.
Application Developer
An application developer is a person who develops an application. Application developers capture requirements from customers and develop the application according to customer requirements. Application developers use Oracle Enterprise Manager to tune SQL in their application modules for optimal performance in production environments. Application developers are responsible for the modules of the application in development, test, and production environments.
Monitoring User
The database monitoring user monitors the database for smooth functioning of the application in production environments. Monitoring users respond to alerts raised in the Enterprise Manager environment. Monitoring users can update the schedule of metrics and setup blackouts on the databases. Monitoring users are not allowed to make any changes to the production database. Monitoring users ensure that the application is up and running by responding to any issues reported and ensuring that the issues are assigned to the DBAs responsible for resolution.
Database Administrator
Database administrators performs full database lifecycle management including installation configuration, monitoring, backup, recovery, and performance tuning.
Application DBAs should have access to the Performance and Schema Management pages in Enterprise manager.
To create an application DBA account in Enterprise Manager:
Follow the instructions in "Creating a New Administrator" to create an Enterprise Manager administrator.
Grant the privilege Database Application DBA on the database target.
Grant the Full privilege on the database host target.
Using the Resource Privileges Page, grant the Create New Named Credentials privilege on the Named Credential Resource Type privilege page and Create Privilege on Job System Resource Type privilege.
To create named credentials, the database administrator can create their own named credential or it can be created by the super administrator (or a privileged Administrator with the system resource privilege) and then granted to the application DBA. The named Credential is granted the view privilege on the named credential so that the application DBA does not even know or see the contents of the named credential.
Application developers generally work on their development environments and have full access to their development databases. Application developers are not usually granted access to the production databases. However they might need access to a production database to see performance of application queries in a production environment. The access of the application developer to a production database must be READ ONLY access. Application developers should not be allowed to make any changes to the database. The application developer's access to a production database should be liberal enough to allow the developer to access performance management reports for a production database.
An Enterprise Manager administrator with the user management or grants management resource type privilege can grant application developer access on a database to an Enterprise administrator in Oracle Enterprise Manager 13c.
To grant application developer access on the database, follow the instructions in "Creating a New Administrator" to create an Enterprise Manager administrator then grant the privilege Database Application Developer on the database.
To access database management performance management pages the user must log in to the database using database named credentials. An application DBA should grant view credential access on a database named credential to the application developer in Enterprise Manager. The database named credential should have at least the SELECT_CATALOG_ROLE role on the database.
The application developer must not be given the user's password. The application developer should not be granted view access on the host, or any database host named credential.
The application developer account created in this way in Enterprise Manager will be able to view only the Performance Management and Schema Management pages in Enterprise Manager. The user will not be able to make any changes to the database.
The database monitoring user has user access and views the database monitoring pages in Enterprise Manager.
To grant view database performance access on the database, follow the instructions in "Creating a New Administrator" to create an Enterprise Manager administrator then grant the privilege View Database Performance on the database target.
To share credentials with the database monitoring user:
Create the database account for the database monitoring user.
Grant the SELECT_CATALOG_ROLE role to the database account.
Create a named credential in Enterprise Manager using the database account username and password.
Grant the view credential access privilege on the named credential to the database monitoring user in Enterprise Manager.
The database monitoring user can now use the database account to log in to the database in Enterprise Manager.
Users that do not have at least the SELECT_CATALOG_ROLE role cannot log in to the database in Enterprise Manager. If the database login user does not have at least the SELECT_CATALOG_ROLE role, the following error message is displayed:
The application requires more database privileges than you have currently been granted.Click on Help to get more version specific information.
The database administrator has full access on the database and can perform any operation on the database.
To create a database administrator account:
Follow the instructions in "Creating a New Administrator" to create an Enterprise Manager administrator
On the Create Administrator <Name>: Roles page
add the EM_PATCH_ADMINISTRATOR and EM_PROVISIONING_OPERATOR roles.
On the Target Privileges page grant the Add Any Target privilege from the Privileges Applicable to all Targets section.
To create named credentials the database administrator logs in to Enterprise Manager and creates a database named credential and a host named credential.
The database administrator provisions the database and adds the database to Enterprise Manager for management. Doing this makes the database administrator the owner of the database and listener targets in Enterprise Manager. The owner has FULL access on the targets in Enterprise Manager.
A similar level of access on the database to multiple users can be granted by creating Enterprise Manager roles. Privileges can be granted to roles and the roles can be granted to the Enterprise Manager administrators. Access changes made to the roles are reflected to the Enterprise Manager administrators granted that role.
To manage a similar level of access across multiple databases to a user, privilege propagating groups should be created. Individual databases should be added to a privilege propagating group. Privileges can be granted on the privilege propagating group. Privileges granted at the group level are automatically propagated to the group members. The user automatically receives the privileges to any database that is later added to the privilege propagating group.
Enterprise Manager roles are explained in Section 2.2.1 of the Oracle Enterprise Manager Cloud Control Security Guide.
See Also:
For more information about Enterprise Manager roles, see "Understanding Users, Privileges, and Roles"Flexible DB Access Control privileges are broadly categorized into 12 privilege groups for easy manageability. The following sections describe the 12 privilege groups available in Enterprise Manager:
Note:
The privilege groups listed in this section apply to the following target types:Database Instance
Cluster Database
Pluggable Database
The Database Application DBA can manage the application schema, application objects, and application performance in the database. In addition, the Database Application DBA can view and update the database to fix performance and other issues on the database.
Target Privileges | Menu Items |
---|---|
Manage the Database Performance Privilege group
Manage the Database Schema Privilege group |
Manage Database Performance Privilege Group
Manage Database Schema Privilege Group |
The Database Application Developer can view the database performance in Enterprise Manager but cannot make any changes to the database.
Target Privileges | Menu Items |
---|---|
View the Database Performance Privilege group
View the Database Schema Privilege group |
View Database Performance Privilege Group
View Database Schema Privilege Group |
The Manage Database High Availability Privilege group has the ability to manage database high availability pages in Enterprise Manager.
Target Privileges | Menu Items |
---|---|
View the database backup
View database advanced queues View database redo logs View recovery settings View the high availability console View database resources |
Availability>MAA AdvisorAdministration>Resource ManagerAvailability>Backup & Recovery>Backup ReportsAvailability>Backup & Recovery>Backup SettingsAvailability>Backup & Recovery>Recovery SettingsAvailability>Backup & Recovery>Recovery Catalog SettingsAvailability>Backup & Recovery>Transactions |
The Manage Database High Availability Privilege group has the ability to view database high availability pages in Enterprise Manager.
Target Privileges | Menu Items |
---|---|
View the database backup
View database advanced queues View database redo logs View recovery settings View the high availability console View database resources |
Availability>MAA AdvisorAdministration>Resource ManagerAvailability>Backup & Recovery>Backup ReportsAvailability>Backup & Recovery>Backup SettingsAvailability>Backup & Recovery>Recovery SettingsAvailability>Backup & Recovery>Recovery Catalog SettingsAvailability>Backup & Recovery>Transactions |
Members of this group have the ability to manage all database performance and advisory features including SQL Monitor, SQL Performance Analyzer, memory advisors, segment advisors, and so on.
Target Privileges | Menu Items |
---|---|
Use the database SQL Access Advisor
Manage the database SQL plan control Use the database SQL Tuning Advisor Manage the database SQL Tuning sets Database SPA administration Manage database sessions Database segment administration View database memory usage View the Database Performance Privilege Group Database optimizer statistics administration Connect target Database ADDM administration Database advisor tasks administration Automated maintenance tasks administration Manage database ASH reports Manage database automatic undo management Manage database AWR settings Manage database health checkers Manage database memory usage |
Performance>Performance Home
Performance>SQL>SQL Performance Analyzer Home Performance>SQL>Optimizer statistics Performance>Top Activity Performance>ASH Analytics Performance>SQL Monitor Performance>SQL>SQL Tuning Sets Performance>SQL>SQL Plan Control Performance>SQL>Cloud Control SQL History Performance>SQL>Search SQL Performance>Search Sessions Performance>Blocking Sessions Performance>Advisors Home Performance>Real-Time ADDM Administration>Storage>Automatic Undo Management Performance>AWR>AWR Report Performance>AWR>AWR Administration Performance>AWR>Compare Period ADDM Performance>AWR>Compare Period Reports Performance>SQL>SQL Performance Analyzer Setup Performance>SQL>SQL Tuning Advisor Performance>SQL>SQL Access Advisor Administration>Initialization Parameters |
Members of this group have the ability to view all database performance and advisory features including SQL Monitor, SQL Performance Analyzer, memory advisors, segment advisors, and so on.
Target Privileges | Menu Items |
---|---|
Connect to a target (read-only)
View database actions View database ADDM View Database Advisor Home View automated maintenance tasks View database ASH reports and analytics View database automatic undo management View database AWR reports View database health checkers View database clients View the database Data Recovery Advisor View the database in-memory setting Install database management packages View database modules View the Database Performance Home Page View Database Optimizer statistics View database segments View database services View database sessions View the database SQL Performance Analyzer View the Database SQL monitor View the database SQL plan control View the database SQL tuning sets View database SQL scripts View database top activity |
Performance>Performance Home
Performance>SQL>SQL Performance Analyzer Home Performance>SQL>Optimizer statistics Performance>Top Activity Performance>ASH Analytics Performance>SQL Monitor Performance>SQL>SQL Tuning Sets Performance>SQL>SQL Plan Control Performance>SQL>Cloud Control SQL History Performance>SQL>Search SQL Performance>Search Sessions Performance>Blocking Sessions Performance>Advisors Home Performance>Real-Time ADDM Administration>Storage>Automatic Undo Management Performance>AWR>AWR Report Performance>AWR>AWR Administration Performance>AWR>Compare Period ADDM Performance>AWR>Compare Period Reports |
Members of this group have the ability to manage database schema elements such as tables, views, indexes, packages, functions, and so on.
Target Privileges | Menu Items |
---|---|
Manage database directory objects
Manage database export Manage database import Manage database indexes Manage database Java content Manage database materialized views Manage database tables Manage database procedures and functions Reorganize database objects Manage database sequences Manage database synonyms Manage database workspaces Manage the XML database Manage database types Manage database triggers Manage database text Indexes View database table data Manage database dimensions Manage database links Manage database packages and package bodies |
Schema>Database Objects>Synonyms
Schema>Database Objects>Sequences Schema>Database Objects>Database Links Schema>Database Objects>Directory Objects Schema>Text Manager>Text Indexes Schema>Workspaces Schema>XML Database>Resources Schema>XML Database>XML Schemas Schema>XML Database>XMLType Views Schema>XML Database>XML Indexes Schema>XML Database>XML Repository Events Schema>XML Database>XMLType Tables Schema>Programs>Packages Schema>Programs>Package Bodies Schema>Programs>Java Sources Schema>Programs>Java Classes Schema>Materialized Views>Materialized Views Schema>Materialized Views>Materialized View Logs Schema>Materialized Views>Refresh Groups>Dimensions Schema>User Defined Types>Array Types Schema>User Defined Types>Object Types Schema>User Defined Types>Table Types Schema>Database Objects>Reorganize Objects Schema>Database Export/Import>Export to Export Files... Schema>Database Export/Import>Import from Export Files... Schema>Database Export/Import>Import from Database... Schema>Database Export/Import>Load Data from User Files... Schema>Text Manager>Query Statistics Schema>XML Database>Configuration Schema>Change Management>Data Comparisons Schema Change Plans Schema>Change Management>Schema Baselines Schema>Change Management>Schema Comparisons Schema>Change Management>Schema Change Plans Schema>Change Management>Schema Synchronizations |
Members of this group have the ability to view database schema elements such as tables, views, indexes, packages, functions, and so on.
Target Privileges | Menu Items |
---|---|
View the XML database
View database workspaces View database types View database triggers View database text indexes View database tables View database synonyms View database sequences View database procedures and functions View database packages and package bodies View database materialized views View database Java content View database indexes View database directory objects View database dimensions View database links |
Schema>Database Objects>Tables
Schema>Database Objects>Views Schema>Database Objects>Indexes Schema>Database Objects>Synonyms Schema>Database Objects>Sequences Schema>Database Objects>Database Links Schema>Database Objects>Directory Objects Schema>Text Manager>Text Indexes Schema>Workspaces Schema>XML Database>Resources Schema>XML Database>XML Schemas Schema>XML Database>XMLType Views Schema>XML Database>XML Indexes Schema>XML Database>XML Repository Events Schema>XML Database>XMLType Tables Schema>Programs>Packages Schema>Programs>Package Bodies Schema>Programs>Java Sources Schema>Programs>Java Classes Schema>Materialized Views>Materialized Views Schema>Materialized Views>Materialized View Logs Schema>Materialized Views>Refresh Groups>Dimensions Schema>User Defined Types>Array Types Schema>User Defined Types>Object Types Schema>User Defined Types>Table Types |
Members of this group have the ability to manage all database security features including users, roles, profiles, transparent data encryption, database vault, and so on.
Target Privileges | Menu Items |
---|---|
Manage database roles
Manage database audit settings Manage database audit trails Manage the database vault Manage database virtual private database policies Manage database users Manage database transparent data encryption settings View the Database Security Privilege group Manage the database scheduler Database redaction administration Manage database profiles Manage privilege analysis Manage database Oracle label security |
Security>Home
Security>Reports Security>Database Vault Administration>Oracle Scheduler>Jobs Administration>Oracle Scheduler>Job Classes Administration>Oracle Scheduler>Chains Administration>Oracle Scheduler>Schedules Administration>Oracle Scheduler>Programs Administration>Oracle Scheduler>Windows Administration>Oracle Scheduler>Window Groups Security>Roles Security>Users Security>Profiles Security>Audit Settings Security>Transparent Data Encryption Security>Data Redaction Security>Label Security Security>Application Contexts Security>Enterprise User Security Security>Virtual Private Database Security>Application Contexts Security>Enterprise User Security Security>Privilege Analysis |
Members of this group have the ability to view all database security features including users, roles, profiles, data encryption, data vault, audit vault, and so on.
Target Privileges | Menu Items |
---|---|
View database audit settingsView the database audit trailMonitor the database vaultView database feature usageView database Oracle label securityView privilege analysisView database profilesView database redactionView database rolesView the database schedulerView the Database Security HomeView database security reportsView database transparent data encryption settingsView database usersView database virtual private database policies | Security>Home
Security>Reports Security>Database Vault Administration>Oracle Scheduler>Jobs Administration>Oracle Scheduler>Job Classes Administration>Oracle Scheduler>Chains Administration>Oracle Scheduler>Schedules Administration>Oracle Scheduler>Programs Administration>Oracle Scheduler>Windows Administration>Oracle Scheduler>Window Groups Security>Roles Security>Users Security>Profiles Security>Audit Settings Security>Transparent Data Encryption Security>Data Redaction Security>Label Security Security>Application Contexts Security>Enterprise User Security Security>Virtual Private Database Security>Application Contexts Security>Enterprise User Security Security>Privilege Analysis |
The members of this group have the ability to manage database storage.
Target Privileges | Menu Items |
---|---|
Manage database control files
Manage database data files Manage database redo logs Manage database tablespaces Manage database transport tablespace |
Administration>Storage>Archive Logs
Administration>Storage>Datafiles Administration>Storage>Control Files Administration>Storage>Redo Log Groups Administration>Storage>Tablespaces Administration>Storage>Temporary Tablespace Groups Administration>Storage>Database File Systems Administration>Storage>Information Lifecycle Management |
Out of the box support is provided for discovering, monitoring, and administration of TCPS enabled listeners. All databases created through the Admin provisioning flow or Cloud Self Service portal support SSL enabled connection strings by default. Target databases can be monitored securely by configuring the TCPS connection protocol. By configuring secured access, data transport encryption is enabled between OMS and the database server target and between the Agent and the database server target.
As businesses look towards cloud solutions, secure user authentication is a key requirement of the product offering. Oracle's default authentication protocols O3LOGON and O5LOGON (introduced in Enterprise Manager 11g) have been revamped to enable user authentication using the TCPS protocol for Oracle Database Server instead of the not-so-secure TCP protocol.
Note:
Using secure authentication has no impact on normal database performance.To configure TCPS:
Enable the Oracle Advanced Security TLS setting on the target database.
Configure secure wallets with third party CA certificates.
Configure third party CA certificate wallets for OMS and Agent communication.
Set the connection protocol to TCPS in the monitoring configuration properties of target database.
You must set the following set of properties at the OMS server and Agent for secure target monitoring. Bounce the OMS and the agent to bring the changes into effect.
See Also:
For more information about emctl, see Appendix C, "Using emctl partool Utility" in the Oracle Enterprise Manager Lifecycle Management Administrator's Guide.Properties to be Set at the OMS Server:
#Client authority
emctl set property -sysman_pwd sysman -name em.targetauth.db.pki.KeyStore -value <…wallet..>
emctl set property -sysman_pwd sysman -name em.targetauth.db.pki.KeyStorePassword -value <…>
emctl set property -sysman_pwd sysman -name em.targetauth.db.pki.KeyStoreType -value <..>
#Server authority
emctl set property -sysman_pwd sysman -name em.targetauth.db.pki.TrustStorePassword -value <…>
emctl set property -sysman_pwd sysman -name em.targetauth.db.pki.TrustStoreType -value <..>
emctl set property -sysman_pwd sysman -name em.targetauth.db.pki.TrustStore -value <…wallet..>
Properties to be set at the Agent residing on the target database host:
#Client authority
emctl setproperty agent -name connectionKeyStoreLocation -value <…wallet..>
emctl setproperty agent -name connectionKeyStoreType -value <..>
emctl setproperty agent -name connectionTrustStoreType -value <..>
#Server authority
emctl setproperty agent -name connectionTrustStoreLocation -value <…wallet..>
emctl setproperty agent -name connectionTrustStorePassword -value <..>
emctl setproperty agent -name connectionTrustStoreType -value <..>
The following new EM CLI verbs have either been added or modified for target database account management:
See Also:
For more information about these verbs, including examples, see the "Verb Reference" chapter in the Oracle Enterprise Manager Command Line Interface.Displays the database profile details such as profile, resource name, resource type, and limits for a specific search criteria. The following example displays details about the profile with the name DEFAULT:
emcli get_db_profile -target_name=myDB -profile= DEFAULT -connect_as="DBNamedCreds:SYS_myDB"
Displays database account details such as username, profile, account status, and authentication type for a specific search criteria. The following example displays the details of the user Admin1:
emcli get_db_account -target_name=myDB -user_name=Admin1 -connect_as"DBNamedCreds:SYS_myDB"
Updates the database account status to LOCKED, OPEN, EXPIRED,or LOCKED & EXPIRED. The following example locks the account:
emcli update_db_account_status -target_name=myDB -user_name=Admin1 -action=LOCK -connect_as="DBNamedCreds:SYS_myDB"
Updates the target database password change in the Enterprise Manager Credential sub-system and can change the password on the target database as well. This verb also propagates the collection or monitoring credentials to Enterprise Manager Management Agents.
The EM CLI verb update_db_password can now change passwords for all users, including SYS/SYSDBA users. The verb can change passwords for the database target and all Enterprise Manager credentials. It syncs the password file across RAC and Dataguard instances. It accepts database named credentials to logon to database targets if it is used for changing other users' passwords. The following example updates the database password:
emcli update_db_password -target_name=myDB -user_name=Admin1