Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted July 15, 2013

Using Oracle Enterprise Manager 12c Command Line Interface Part 2 – Auditing and User Management Commands

By Karen Reliford

Many of the activities that DBAs do through the Oracle Enterprise Manager 12 Cloud Control GUI interface can also be accomplished via the Command Line Interface (EM CLI).  Last month, we explored how to install and configure the EM CLI.  This article will explore three categories of commands – auditing and user account management.

EM CLI Auditing Commands

Being able to produce an audit trail of activities done via EM is required in many organizations.

We use the EM CLI Audit commands to enable or disable auditing of any or all operations done via EM.  The actual list of auditable operations is below, and it is exhaustive.

ADD_AGENT_REGISTRATION_PASSWORD

AGENT_REGISTRATION_PASSWORD_USAGE

AGENT_RESYNC

APPLY_TEMPLATE

AUDIT_EXPORT_SETTINGS

AUDIT_SETTINGS

CHANGE_PASSWORD

CHANGE_PREFERRED_CREDENTIAL

CREATE_PG_SCHED

CREATE_ROLE

CREATE_TEMPLATE

CREATE_UDP

CREATE_UDPG

CREATE_USER

DELETE_AGENT_REGISTRATION_PASSWORD

DELETE_JOB

DELETE_PG_EVAL

DELETE_PG_SCHED

DELETE_ROLE

DELETE_TEMPLATE

DELETE_UDP

DELETE_UDPG

DELETE_USER

EDIT_AGENT_REGISTRATION_PASSWORD

EDIT_JOB

EDIT_PG_SCHED

EDIT_TEMPLATE

EDIT_UDP

EDIT_UDPG

EVALUATE_UDP

FILE_TRANSFER

GET_FILE

GRANT_JOB_PRIVILEGE

GRANT_ROLE

GRANT_SYSTEM_PRIVILEGE

GRANT_TARGET_PRIVILEGE

IMPORT_UDP

JOB_OUTPUT

LOGIN

LOGOUT

MODIFY_METRIC_SETTINGS

MODIFY_POLICY_SETTINGS

MODIFY_ROLE

MODIFY_USER

PUT_FILE

REMOTE_OPERATION_JOB

REMOVE_PRIVILEGE_DELEGATION_SETTING

REPOSITORY_RESYNC

REVOKE_JOB_PRIVILEGE

REVOKE_ROLE

REVOKE_SYSTEM_PRIVILEGE

REVOKE_TARGET_PRIVILEGE

SAVE_MONITORING_SETTINGS

SET_PRIVILEGE_DELEGATION_SETTING

SUSPEND_JOB

There are five commands that affect EM operation auditing.  They are enable_audit, disable_audit, show_audit_settings, show_operations_list and update_audit_settings. 

emcli show_operations_list

– displays the list (see above) of all operations that may be audited

Use the enable_audit command to enable all operations or just login/logout operations.  There are two possible options for this command:

emcli enable_audit

-       this will enable auditing for all user operations

emcli enable_audit –level=basic

-       this will enable auditing for login/logout and db_login/db_logout

Use the disable_audit command to turn off all auditing.

emcli disable_audit

To see the current audit settings use the show_audit_settings command (which has the options of a detailed or summary output):

emcli show_audit_settings –view=”DETAIL|SUMMARY”

-       displays the following information

    • Audit Switch (enabled or disabled)
    • Externalization Switch (enabled or disabled)
    • Directory
    • File Prefix
    • File Size
    • Data Retention Period

In order to get specific audit settings, we use the update_audit_settings command.  It has the following options:

-audit_switch=”ENABLE|DISABLE”

-operations_to_enable=”name of operations|ALL”

(If auditing is disabled, this parameter is invalid, to list multiple operations separate the names with semi-colons).

-operations_to_disable=”name of operations|ALL”

(If auditing is disabled, this parameter is invalid; to list multiple operations separate the names with semi-colons).

-externalization_switch=”ENABLE|DISABLE”

(Writes the audit trail to an external file).

-directory=”directory object name”

-file_prefix=”name”

(Your specified file name, default value is em_audit).

-file_size=”bytes”

            (Your specified size in bytes, default is 5000000).

-data_retention_period=”days”

            (How long to store the audit records in EM, default is 365 days).

Let us imagine that we would like to turn on auditing for everything except a resync of the agents or repository.  We have a directory object created for us called EM_AUDIT_DIR and the files should be prefixed with my_audit.  Additionally we would like a size of 2500000 bytes, and we want to keep the data in EM for six months.

We would not be able to use the basic enable_audit command, so we would use the update_audit_settings command instead:

emcli update_audit_settings

-audit_switch=”ENABLE”

-operations_to_enable=”ALL”

-operations_to_disable=”AGENT_RESYNC;REPOSITORY_RESYNC”

-externalization_switch=”ENABLE”

-directory=”EM_AUDIT_DIR”

-file_prefix=”my_em_audit”

-file_size=”2500000”

-data_retention_period=”180”

EM CLI User Management Commands

DBAs who like to create scripts to perform tasks such as creating and managing user accounts may find it useful to know the EM CLI commands that focus on user and privilege management.  There are commands to manage users, roles, and privileges.  The list of commands is:

 

create_role

delete_role

modify_role

 

create_user

delete_user

modify_user

 

grant_privs

grant_roles

revoke_privs

revoke_roles

 

get_supported_privileges

Role Commands

The first of the role commands is used to create new roles via EM CLI.

create_role

-name=”name” (this is the only mandatory option)

-type=”EM_ROLE|EXTERNAL_ROLE” (default is EM_ROLE)

-description=”your description of the role”

-roles=”role_a;role_b”

-users=”user_a;user_b”

-privilege=”name[;secure_resource_details]”

-separator=privilege=”sep_string”

-subseparator=privilege=”sub_sep_string”

roles – list of other roles to assign to this role

users – list of users to assign this role to

privilege – privilege to grant this role

separator – what to use to separate the role list, default is ;

subseparator – what to use to separate value pairs for the privilege option, default is ;

As an example, we want to create a role called my_view_role.  It combines two other roles that were created earlier called role_a and role_b, it will be granted to the users Karen and Ron.  It will allow for viewing two hosts (host_01 and host_02 and one job with the ID of 12345ABCDE678910.

emcli create_role

-name=”my_view_role”

-type=”EM_ROLE”

-roles=”role_a;role;b”

-users=”Karen;Ron”

-privilege=”view_target;host_01.mycompany.com:host”

-privilege=”view_target;host_02.mycompany.com:host”

-privilege=”view_job; 12345ABCDE678910”

The second command is used to delete roles and it is simply:

emcli delete_role –name=”role name”

The third command is used to modify an existing role.  It is virtually identical to the create role command.  Any options that you specify will replace the existing settings.  Simply leave an option off if you do not wish to modify that particular setting for the role.

modify_role

-name=”role name”

-description=”your description of the role”

-roles=”role_a;role_b”

-users=”user_a;user_b”

-privilege=”name[;secure_resource_details]”

-separator=privilege=”sep_string”

-subseparator=privilege=”sub_sep_string”

To change “my_view_role” and add another existing role called role_c, simply modify the role option.

emcli modify_role

-name=”my_view_role”

-roles=”role_a;role_b;role_c”

User Commands

The create user command is used to create new EM Administrator user accounts.

emcli create_user

-name="name" (mandatory)

-password="password" (mandatory)

-type="type"

-roles="role(s)”

-email="email address(es)”

-privilege="name[;secure-resource-details]”

-separator=privilege=string"

-subseparator=privilege="string”

-profile="profile_name"

-desc="description

-expired="true|false"

-prevent_change_password="true|false"

-department="department_name"

-cost_center="cost_center"

-line_of_business="line_of_business"

-contact="contact"

-location="location"

-input_file="arg_name:file_path"

 

-type – can be EM_USER (default), EXTERNAL_USER or DB_EXTERNAL_USER

-roles – which roles should be assigned to the user

-email – email address(es) for the user – separate with semi-colons

-privilege – explicit privileges to grant to the user

-separator/subseparator – character to separate roles/privileges – default is ;

-profile – database profile to assign to the user, default is DEFAULT

-expired – set to true to expire password right away, default is FALSE

-prevent_change_password – if true, user cannot change the password, default is FALSE

-department – of the administrator

-cost_center – of the administrator

-line_of_business – of the administrator

-contact – contact information of the administrator

-location – of the administrator

-input_file – allows the use of a file for any of the options

To create a user named Karen with a password of Oracle1, with the roles role_a and role_c, full privileges on host_01 an email address of smilingpixie@yahoo.com with an immediately expired password and a profile of JR_ADMIN, enter the following:

emcli create_user

-name=”Karen”

-password=”Oracle1”

-roles=”role_a;role_b”

-email=smilingpixie@yahoo.com

-expired=”true”

-privilege=”full_target:host_01.mycompany.com:host”

-profile=”JR_ADMIN”

 

To delete an EM Administrator user account use delete_user.  If a user is deleted, by default objects created by that administrator would also be deleted.  Additionally any jobs would be stopped and deleted along with any blackouts that the user had set up.  If there were any active blackouts, the user would not be able to be removed.  There are three options for the delete user command:

            -name – name of user to be deleted

            -new_object_owner – name of user to become the owner of objects (instead of removing them)

            -force – remove the administrator even if they are logged in

 

To delete the user Karen and reassign all objecs to Ron – issue the following:

emcli delete_user

-name=”Karen”

-new_ojbect_owner=”Ron”

-force

 

To change an EM Administrator account – use the modify_user command.  Like the modify_role command, we simply specify the new values for any of the options to replace the current value for that particular attribute.

modify_user

-name="name" (mandatory)

-password="password" (mandatory)

-type="type"

-roles="role(s)”

-email="email address(es)”

-privilege="name[;secure-resource-details]”

-separator=privilege=string"

-subseparator=privilege="string”

-profile="profile_name"

-desc="description

-expired="true|false"

-prevent_change_password="true|false"

-department="department_name"

-cost_center="cost_center"

-line_of_business="line_of_business"

-contact="contact"

-location="location"

-input_file="arg_name:file_path"

 

For example, to change Karen’s profile from JR_ADMIN to SR_ADMIN:

modify_user

-name=”Karen”

-profile=”SR_ADMIN”

Privilege Commands

These commands are used to grant privileges to existing users or roles, to grant existing roles to existing users or to revoke the privileges and roles if necessary.

 

To grant privileges to a user or role:

grant_privs

-name=(user or role names)

-privilege=”name[;resource_details’”

-grant_all_targets_on_host=(yes/no)

-separator=”string”

-subseparator=”string”

 

In order to grant Karen full privileges on host_04 and all targets that reside on host_04:

grant_privs

-name=”Karen”

-privilege=”FULL_TARGET;host04.mycompany.com:host”

-grant_all_targets_on_host=”yes”

 

To grant roles to a user or another role:

grant_roles

-name=(user or role name to get the new role)

-roles=(roles to grant)

 

To grant a new role called my_new_role to my_existing_role and user Ron:

grant_roles

-name=”Ron;my_existing_role”

-roles=”my_new_role”

 

To revoke a privilege from a role or user:

revoke_privs

-name=(user or role names)

-privilege=”name[;resource_details’”

-separator=”string”

-subseparator=”string”

 

To revoke full target on host_04 from Karen:

revoke_privs

-name=”Karen”

-privilege=”FULL_TARGET;host04.mycompany.com:host”

 

To revoke a role from another role or user:

revoke_role

-name=(user or role to revoke from)

-roles=(roles to revoke)

 

To revoke my_new_role from Ron:

revoke_role

-name=”Ron”

-roles=”my_new_role”

 

Understanding the Supported Privileges

There is a command that will display the supported privilege options that can be used in the privilege attribute for the above commands. 

get_supported_privileges

-type=(priv type)

-noheader

-format=(format options)

-script=(same as format:script)

-type – which type of privilege to display, valid options are ALL, SYSTEM, TARGET and JOB

-noheader – prints a tabular output with no column headers

-format – determines which format to display the output in

-format=”name:pretty” – formats the output for screen display, but not for use in scripts

-format=”name:script” – sets the column separator is tabs and row separator to a newline

-format=”name:csv” – sets the column separator to a comma and the row separator to a newline

-format=column_separator:”string” – sets the comma separator to a character of your choosing

-format=row_separator.”string” – sets the row separator to a character of your choosing

 

Next month we will continue our exploration of the EM CLI commands with a look at the Credential Management commands.  Until then...

See all articles by Karen Reliford



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM