Which has the higher priority in your organization: Deploying a new database or securing the ones you already have?
Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Purpose
Undocumented
AUTHID
CURRENT_USER
Dependencies
DBA_ROLES
DUAL
V$DATABASE
DBMS_ASSERT
GV$INSTANCE
V$OBJECT_PRIVILEGE
DBMS_REPORT
PLITBLM
V$PARAMETER
DBMS_SQL
PRVTEMX_DBHOME
V$PDBS
DBMS_SQLTUNE
PRVTEMX_RSRCMGR
V$SYSTEM_PARAMETER
DBMS_SQLTUNE_UTIL1
PRVTEMX_SQL
WRI$_REPT_CONFIG
DBMS_SQLTUNE_UTIL2
PRVT_EMX
WRI$_REPT_SECURITY
DBMS_STANDARD
PRVT_REPORT_TAGS
WRI$_REPT_STORAGE
DBMS_SYS_ERROR
PRVT_SMGUTIL
XMLTYPE
DBMS_UADV_ARR
SYSTEM_PRIVILEGE_MAP
XQSEQUENCE
DBMS_UNDO_ADV
Documented
No
First Available
12.1
Security Model
Owned by SYS with EXECUTE granted to EM_EXPRESS_BASIC
prvtemx_admin.add_redolog_group_member_xml(
p_group_number IN NUMBER,
p_file_name IN VARCHAR2,
p_show_sql IN NUMBER,
p_report_ref IN VARCHAR2)
RETURN XMLTYPE;
prvtemx_admin.create_redolog_group_xml(
p_group_number IN NUMBER,
p_thread_number IN NUMBER,
p_files IN XMLTYPE,
p_size IN VARCHAR2,
p_show_sql IN NUMBER,
p_report_ref IN VARCHAR2)
RETURN XMLTYPE;
prvtemx_admin.get_remote_undo_info(
p_is_summary IN NUMBER,
p_analysis_start_period IN DATE,
p_analysis_end_period IN DATE,
p_desired_retention IN NUMBER)
RETURN VARCHAR2;
prvtemx_admin.report_add_datafile_xml(
p_tbs_name IN VARCHAR2,
p_tbs_type IN VARCHAR2,
p_oracle_managed IN NUMBER,
p_datafiles IN XMLTYPE,
p_df_number IN NUMBER,
p_df_size IN VARCHAR2,
p_df_auto_extend IN NUMBER,
p_df_next_size IN VARCHAR2,
p_df_max_size IN VARCHAR2,
p_df_reuse IN NUMBER,
p_show_sql IN NUMBER)
RETURN XMLTYPE;
prvtemx_admin.report_alter_user(
p_username IN VARCHAR2,
p_auth_type IN VARCHAR2,
p_newpasswd IN VARCHAR2,
p_profile IN VARCHAR2,
p_passwd_expire IN NUMBER,
p_account_lock IN NUMBER,
p_default_tablespace IN VARCHAR2,
p_temp_tablespace IN VARCHAR2,
p_quota_tablespace IN VARCHAR2,
p_max_bytes IN VARCHAR2,
p_show_sql IN NUMBER)
RETURN XMLTYPE;
SELECT prvtemx_admin.report_alter_user('AUDSYS', 'PASSWORD', 'Orac1e', 'DEFAULT', p_show_sql=>1)
FROM dual;
prvtemx_admin.report_change_df_status_xml(
p_tbs_name IN VARCHAR2,
p_tbs_type IN VARCHAR2,
p_datafile IN VARCHAR2,
p_status IN VARCHAR2,
p_show_sql IN NUMBER)
RETURN XMLTYPE;
prvtemx_admin.report_change_tbs_status_xml(
p_name IN VARCHAR2,
p_type IN VARCHAR2,
p_status IN VARCHAR2,
p_offline_option IN VARCHAR2,
p_show_sql IN NUMBER)
RETURN XMLTYPE;
SELECT prvtemx_admin.report_change_tbs_status_xml('USERS', 'PERMANENT', 'OFFLINE', p_show_sql=>1)
FROM dual;
prvtemx_admin.report_create_tablespace_xml(
p_name IN VARCHAR2,
p_type IN VARCHAR2,
p_oracle_managed IN NUMBER,
p_datafileS IN XMLTYPE,
p_df_number IN NUMBER,
p_df_size IN VARCHAR2,
p_df_auto_extend IN NUMBER,
p_df_next_size IN VARCHAR2,
p_df_max_size IN VARCHAR2,
p_df_reuse IN NUMBER,
p_bigfile IN NUMBER,
p_block_size IN VARCHAR2,
p_logging IN VARCHAR2,
p_force_logging IN NUMBER,
p_encryption IN NUMBER,
p_enc_algorithm IN VARCHAR2,
p_enc_password IN VARCHAR2,
p_compression IN VARCHAR2,
p_status IN VARCHAR2,
p_tbs_group IN VARCHAR2,
p_ext_allocation IN VARCHAR2,
p_ext_allocation_size IN VARCHAR2,
P_auto_seg_mgmt IN NUMBER,
p_retention IN NUMBER,
p_is_default IN NUMBER,
p_show_sql IN NUMBER)
RETURN XMLTYPE;
prvtemx_admin.report_create_user_xml(
p_username IN VARCHAR2,
p_auth_type IN VARCHAR2,
p_passwd IN VARCHAR2,
p_profile IN VARCHAR2,
p_default_tablespace IN VARCHAR2,
p_temp_tablespace IN VARCHAR2,
p_passwd_expire IN NUMBER,
p_account_lock IN NUMBER,
p_privs IN CLOB,
p_show_sql IN NUMBER)
RETURN XMLTYPE;
SELECT prvtemx_admin.report_create_user_xml('C##ABC', 'PASSWORD', 'ZZYZX', 'DEFAULT', 'USERS', 'TEMP', p_show_sql=>1)
FROM dual;
prvtemx_admin.rpoert_df_auto_extend_xml(
p_tbs_name IN VARCHAR2,
p_tbs_type IN VARCHAR2,
p_datafile IN VARCHAR2,
p_df_auto_extend IN NUMBER,
p_df_next_size IN VARCHAR2,
p_df_max_size IN VARCHAR2,
p_show_sql IN NUMBER)
RETURN XMLTYPE;
prvtemx_admin.report_drop_tablespace_xml(
p_name IN VARCHAR2,
p_drop_contents IN NUMBER,
P_drop_datafiles IN NUMBER,
p_drop_constraints IN NUMBER,
p_show_sql IN NUMBER)
RETURN XMLTYPE;
prvtemx_admin.report_grant_priv_xml(
p_username IN VARCHAR2,
p_privs IN CLOB,
p_schema IN VARCHAR2,
p_objects IN CLOB,
p_grant_all IN NUMBER,
p_grant_option IN NUMBER,
p_show_sql IN NUMBER)
RETURN XMLTYPE;
prvtemx_admin.report_resize_datafile_xml(
p_tbs_name IN VARCHAR2,
p_tbs_type IN VARCHAR2,
p_datafile IN VARCHAR2,
p_df_size IN VARCHAR2,
p_show_sql IN NUMBER)
RETURN XMLTYPE;
prvtemx_admin.report_set_param_xml(
p_name IN VARCHAR2,
p_scope IN VARCHAR2,
p_sid IN VARCHAR2,
p_deferred IN NUMBER,
p_comment IN VARCHAR2,
p_type IN NUMBER,
p_value IN VARCHAR2,
p_value_list_xml IN XMLTYPE,
p_show_sql IN NUMBER)
RETURN XMLTYPE;
SELECT prvtemx_admin.report_set_param_xml('AUDIT_SYS_OPERATIONS', 'SPFILE', '*', 0, p_value=>'TRUE', p_show_sql=>1)
FROM dual;
-- parameters needed to run the show parameter query
var b_date_fmt varchar2(4000);
-- initialize parameter value
begin
:b_date_fmt := dbms_report.date_fmt;
end;
/
-- get information about control files
select
xmlelement(
"archlogs",
xmlagg(
xmlelement(
"file",
xmlattributes(
al.sequence# as "seq",
al.thread# as "thread",
al.blocks * block_size as "size",
al.first_change# as "lowest_scn",
to_char(al.first_time, :b_date_fmt) as "lowest_scn_time",
to_char(al.completion_time, :b_date_fmt) as "archive_time",
nls_initcap(al.creator) as "creator",
nls_initcap(al.registrar) as "registrar",
nls_initcap(al.status) as "status",
nls_initcap(al.is_recovery_dest_file) as "reco",
al.backup_count as "backup_count",
al.compressed as "compressed",
al.backed_by_vss as "vss"),
al.name)))
from sys.v_$archived_log al
-- parameters needed to run the show database properties query
/
-- get information about database properties
select
xmlelement(
"database_properties",
xmlagg(
xmlelement(
"prop",
xmlattributes(
p.property_name as "name",
p.property_value as "value",
p.description as "description"))))
from sys.database_properties p
prvtemx_admin.report_show_params_xml(
p_level IN VARCHAR2,
p_meta IN NUMBER,
p_name IN VARCHAR2,
p_inst_id IN NUMBER,
p_show_sql IN NUMBER,
p_reptag IN NUMBER)
RETURN XMLTYPE;
begin
:b_username := 'AUDSYS';
end;
/
select xmlelement("granted_privs",
xmlagg(
xmlelement("priv",
xmlattributes(
NAME as "name",
case ADMIN_OPTION
when 'YES' then '1'
else '0'
end as "adm",
case DEFAULT_ROLE
when 'YES' then '1'
else '0'
end as "default",
case COMMON
when 'YES' then '1'
else '0'
end as "common",
IS_ROLE as "is_role"
)
)
order by GRANTEE, NAME
)
)
from (
select GRANTEE,
PRIVILEGE as NAME,
ADMIN_OPTION,
null as DEFAULT_ROLE,
COMMON,
'0' as IS_ROLE
from SYS.dba_sys_privs
UNION ALL
select GRANTEE,
GRANTED_ROLE as NAME,
ADMIN_OPTION,
DEFAULT_ROLE,
COMMON,
'1' as IS_ROLE
from SYS.dba_role_privs
) p where grantee=:b_username
and COMMON='YES';]]></script>
</report>
begin
:b_profile := 'DEFAULT';
end;
/
select xmlelement("profiles",
xmlelement("profile",
xmlattributes(
PROFILE as "profile"
),
xmlelement("limits",
xmlagg(
xmlelement("limit",
xmlattributes(
RESOURCE_NAME as "resource_name",
RESOURCE_TYPE as "type",
LIMIT as "limit"
)
)
)
)
)
)
from SYS.dba_profiles
where PROFILE=:b_profile
group by PROFILE
;]]></script>
</report>
prvtemx_admin.report_show_profiles_xml(p_show_sql IN NUMBER) RETURN XMLTYPE;
SELECT prvtemx_admin.report_show_profiles_xml(1)
FROM dual;
<report db_version="19.0.0.0.0" elapsed_time="0.00" cpu_time="0.00" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-18000" packs="2">
<report_id><![CDATA[/orarep/security/show_profiles%3fshow_sql%3d1]]></report_id>
<script><![CDATA[
select xmlelement("profiles",
xmlagg(
xmlelement("profile",
xmlattributes(
p1.PROFILE as "profile",
p1.LIMIT as "connect_time",
p2.LIMIT as "sessions_per_user"
)
)
order by p1.PROFILE
)
)
from (
select PROFILE, LIMIT from SYS.dba_profiles
where RESOURCE_NAME='CONNECT_TIME'
) p1
inner join (
select PROFILE, LIMIT from SYS.dba_profiles
where RESOURCE_NAME='SESSIONS_PER_USER'
) p2 on p1.PROFILE = p2.PROFILE
inner join (
select PROFILE, count(distinct CON_ID) as CON_COUNT
from SYS.cdb_profiles
where CON_ID != 2
group by PROFILE
) pc on pc.PROFILE = p1.PROFILE
inner join (
select count(CON_ID) as CON_COUNT
from SYS.v_$containers c
where c.con_id != 2
) c on c.CON_COUNT=pc.CON_COUNT
;]]></script>
</report>
prvtemx_admin.report_show_redolog_files_xml(
p_group_number IN NUMBER,
p_show_sql IN NUMBER,
p_reptag IN NUMBER,
p_report_ref IN VARCHAR2)
RETURN XMLTYPE;
SELECT prvtemx_admin.report_show_redolog_files_xml(1, 1)
FROM dual;
prvtemx_admin.report_show_roles_xml(p_show_sql IN NUMBER) RETURN XMLTYPE;
SELECT prvtemx_admin.report_show_roles_xml(1)
FROM dual;
<report db_version="19.0.0.0.0" elapsed_time="0.00" cpu_time="0.00" cpu_cores="2" hyperthread="Y" con_id="1" con_name="CDB$ROOT" timezone_offset="-18000" packs="2">
<report_id><![CDATA[/orarep/security/show_roles%3fshow_sql%3d1]]></report_id>
<script><![CDATA[
select
xmlelement("privs",
xmlagg(
xmlelement("priv",
xmlattributes(
NAME as "name",
AUTHENTICATION_TYPE as "auth_type",
case COMMON
when 'YES' then '1'
else '0'
end as "common"
)
)
order by NAME
)
)
from (
select ROLE as NAME, AUTHENTICATION_TYPE, 1 as IS_ROLE, COMMON
from SYS.dba_roles
where COMMON='YES'
);]]></script>
</report>
prvtemx_admin.report_show_undo_details_xml(
p_inst_id IN NUMBER,
p_analysis_start_period IN DATE,
p_analysis_end_period IN DATE,
p_desired_retention IN NUMBER,
p_report_ref IN VARCHAR2,
p_show_sql IN NUMBER)
RETURN XMLTYPE;
SELECT prvtemx_admin.report_show_undo_details_xml(1, SYSDATE-30, SYSDATE-1/24, p_show_sql=>1)
FROM dual;
prvtemx_admin.report_tbs_auto_extend_xml(
p_name IN VARCHAR2,
p_type IN VARCHAR2,
p_auto_extend IN NUMBER,
p_next_size IN VARCHAR2,
p_max_size IN VARCHAR2,
p_show_sql IN NUMBER)
RETURN XMLTYPE;
