oracle 安全性配置文件,ORACLE PROFILE(配置文件)查询



配置文件定义

SELECT profile_option_name 配置文件名,

user_profile_option_name 用户配置文件名,

description 说明,

hierarchy_type 层次结构类型,

site_enabled_flag 地点可见,

site_update_allowed_flag 地点可更新,

app_enabled_flag 应用产品可见,

app_update_allowed_flag 应用产品可更新,

resp_enabled_flag 责任可见,

resp_update_allowed_flag 责任可更新,

server_enabled_flag 服务器可见,

server_update_allowed_flag 服务器可更新,

serverresp_enabled_flag 服务器职责可见,

serverresp_update_allowed_flag 服务器职责可更新,

org_enabled_flag 组织可见,

org_update_allowed_flag 组织可更新,

user_enabled_flag 用户可见,

user_update_allowed_flag 用户可更新,

start_date_active 有效起始日期,

end_date_active 有效截止日期,

user_visible_flag 用户访问可查看,

user_changeable_flag 用户访问可更新,

read_allowed_flag 可读,

write_allowed_flag 可写,

sql_validation sql验证,

profile_option_id 配置文件配置情况id

FROM fnd_profile_options_vl

WHERE (user_profile_option_name = 'CUX_用户机台编号')

配置文件设置

SELECT op.profile_option_id,

tl.profile_option_name,

tl.user_profile_option_name,

lv.level_id,

lv.文件安全性,

va.level_value,

CASE

WHEN va.level_id = 10001 THEN

'地点'

WHEN va.level_id = 10002 THEN

(SELECT fav.application_name

FROM fnd_application_vl fav

WHERE fav.application_id = va.level_value)

WHEN va.level_id = 10003 THEN

(SELECT /* $HEADER$ */

t.responsibility_name

FROM fnd_responsibility_tl t, fnd_responsibility b

WHERE t.responsibility_id = va.level_value

AND t.responsibility_id = b.responsibility_id

AND b.application_id = t.application_id

AND nvl(b.end_date, SYSDATE + 1) > SYSDATE

AND nvl(b.start_date, SYSDATE - 1) < SYSDATE

AND t.language = 'ZHS')

WHEN va.level_id = 10004 THEN

(SELECT user_name

FROM fnd_user

WHERE user_name NOT IN

('*ANONYMOS*',

'CONVERSION',

'INITIAL SETUP',

'FEEDER SYSTEM',

'CONCURRENT MANAGER',

'STANDALONE BATCH PROCESS')

AND user_id = va.level_value

AND nvl(end_date, SYSDATE + 1) > SYSDATE

AND nvl(start_date, SYSDATE - 1) < SYSDATE)

WHEN va.level_id = 10005 THEN

(SELECT node_name FROM fnd_nodes WHERE node_id = va.level_value)

WHEN va.level_id = 10006 THEN

(SELECT NAME

FROM hr_operating_units

WHERE organization_id = va.level_value)

ELSE

''

END AS profile_level_value,

va.profile_option_value

FROM fnd_profile_options_tl tl,

fnd_profile_options op,

fnd_profile_option_values va,

(SELECT 10001 level_id, '地点' 文件安全性

FROM dual

UNION

SELECT 10002 level_id, '应用产品' 文件安全性

FROM dual

UNION

SELECT 10003 level_id, '责任' 文件安全性

FROM dual

UNION

SELECT 10004 level_id, '用户' 文件安全性

FROM dual

UNION

SELECT 10005 level_id, '服务器' 文件安全性

FROM dual

UNION

SELECT 10006 level_id, '组织' 文件安全性

FROM dual) lv

WHERE tl.language = 'ZHS'

AND tl.profile_option_name = op.profile_option_name

AND va.profile_option_id = op.profile_option_id

AND va.level_id = lv.level_id

--AND TL.PROFILE_OPTION_NAME like '%'

AND tl.user_profile_option_name = 'CUX_用户机台编号'