1 PACKAGE bsc_security AS
2 /* $Header: BSCSSECS.pls 120.2 2006/01/24 16:07:29 calaw noship $ */
3
4 /*-------------------------------------------------------------------------+
5 | PUBLIC CONSTANTS |
6 +-------------------------------------------------------------------------*/
7
8 -- User Types
9
10 DB_USER_TYPE CONSTANT NUMBER(1) := 0;
11 BSCADMIN_TYPE CONSTANT NUMBER(1) := 1;
12 SYSADMIN_TYPE CONSTANT NUMBER(1) := 2;
13
14 -- Error Message Types (from BSC_MESSAGE_LOGS)
15
16 DB_ERR_MSG CONSTANT NUMBER := 0;
17 APP_ERR_MSG CONSTANT NUMBER := 1;
18 WARN_ERR_MSG CONSTANT NUMBER := 2;
19 INFO_ERR_MSG CONSTANT NUMBER := 3;
20 DEBUG_ERR_MSG CONSTANT NUMBER := 4;
21
22 CRLF CONSTANT VARCHAR2(1) := '
23 ';
24
25 --
26 -- This package-level pragma means that the initialization section of this
27 -- package cannot write any DB or package status
28 --
29 -- Currently, this package has no initialization section
30 --
31
32 pragma restrict_references (bsc_security, WNPS, WNDS);
33
34 --
35 -- Name
36 -- get_user_info
37 -- Purpose
38 -- Gets user_pwd, user_type and scheme un/pwd for OBSC and UBSC users.
39 --
40 -- Arguments
41 -- x_user_name
42 -- x_user_pwd
43 -- x_user_id
44 -- x_user_type
45 -- x_obsc_un
46 -- x_obsc_pwd
47 -- x_ubsc_un
48 -- x_ubsc_pwd
49 -- x_debug_flag
50 -- x_status
51 -- x_calling_fn
52 --
53
54 PROCEDURE get_user_info(
55 x_user_name IN VARCHAR2,
56 x_user_pwd IN OUT NOCOPY VARCHAR2,
57 x_user_id IN OUT NOCOPY NUMBER,
58 x_user_type IN OUT NOCOPY NUMBER,
59 x_obsc_un IN OUT NOCOPY VARCHAR2,
60 x_obsc_pwd IN OUT NOCOPY VARCHAR2,
61 x_ubsc_un IN OUT NOCOPY VARCHAR2,
62 x_ubsc_pwd IN OUT NOCOPY VARCHAR2,
63 x_debug_flag IN VARCHAR2 := 'NO',
64 x_status IN OUT NOCOPY BOOLEAN,
65 x_calling_fn IN VARCHAR2);
66
67 -- Overloaded procedure for VB code, since VB cannot read from PL/SQL
68 -- parameters.
69
70 PROCEDURE get_user_info(
71 x_sid IN VARCHAR2,
72 x_user_name IN VARCHAR2,
73 x_debug_flag IN VARCHAR2 := 'NO',
74 x_calling_fn IN VARCHAR2);
75
76 --
77 -- Name
78 -- Check_System_Lock
79 -- Purpose
80 -- Enforce system locking for all OBSC models
81 -- Note
82 -- OBSC client applications should call this procedure identify
83 -- and register itself at v$session view, and to obtain the lock
84 -- of the system after it connects to database.
85 -- The following rules are enforced in the locking:
86 -- 1. Loader, Optimizer, Security Wizard, Designer, Builder:
87 -- a. only one process can run on the system at any given
88 -- point of time.
89 -- b. no other process can run while this process is running
90 --
91 -- 2. iViewer, Viewer (in user mode):
92 -- a. multiple Viewer processes can run on the system at the
93 -- same time
94 -- b. no other process can run while Viewer is running
95 --
96 -- Parameter:
97 -- x_program_id - program identifier, has the following value
98 -- a. Loader = -100
99 -- b. Metadata Optimizer = -200
100 -- c. Security Wizard = -300
101 -- d. KPI Designer = -400
102 -- e. BSC Builder = -500
103 -- f. iViewer or VB Viewer (in user mode) = -600
104 -- x_debug_flag - debug flag
105 -- x_user_id - Session Management, passed by OA Fwk for user name
106 -- x_icx_session_id - Session Management, passed by OA Fwk from IBuilder only
107 -- Other OBSC clients will the first 3 parms.
108
109 Procedure Check_System_Lock(
110 x_program_id IN Number,
111 x_debug_flag IN Varchar2 := 'NO',
112 x_user_id IN Number :=NULL,
113 x_icx_session_id IN Number :=NULL
114 );
115
116
117 --
118 -- Name
119 -- Refresh_System_Lock
120 -- Purpose
121 -- Cleanup BSC_CURRENT_SESSIONS table before acquiring locks
122 -- Called by BSC_SECURITY.CHECK_SYSTEM_LOCK and BSC_LOCKS_PUB.GET_SYSTEM_LOCK
123 -- 1) Delete all orphan the sessions
124 -- 2) Delete all the session not being reused by FND
125 -- 3) Delete all sessions, which have their concurrent programs in invalid or hang status
126 -- 4) Kill IViewer Sessions that have been INACTIVE more than 20 minutes
127 -- 5) Delete all the Killed Sessions
128 --
129 -- Parameter:
130 -- p_program_id - program identifier
131
132 Procedure Refresh_System_Lock(
133 p_program_id IN Number
134 );
135
136
137 --
138 -- Name
139 -- Check_Source_System_Lock
140 -- Purpose
141 -- Enforce system locking for all OBSC models in the source system
142 -- This is issued by Migration (-800)
143 --
144 -- Parameter:
145 -- x_debug_flag - debug flag
146
147 Procedure Check_Source_System_Lock(
148 x_debug_flag IN Varchar2 := 'NO'
149 );
150
151
152 --
153 -- Name
154 -- Delete_Bsc_Session
155 -- Purpose
156 -- Delete the current session from BSC_CURRENT_SESSIONS table.
157
158 PROCEDURE Delete_Bsc_Session;
159
160 --
161 -- Name
162 -- Delete from BSC_CURRENT_SESSION using ICX Session ID
163
164 PROCEDURE Delete_Bsc_Session_ICX(
165 p_icx_session_id IN NUMBER
166 );
167
168
169 --
170 -- Name
171 -- user_has_lock
172 -- Purpose
173 -- Return Y if user holds locks
174 -- else return N
175 -- Parameter:
176 -- x_SID - Sessuib ID that user currently belongs
177
178 FUNCTION user_has_lock(
179 X_SID in NUMBER) RETURN VARCHAR2;
180
181 --
182 -- Name
183 -- user_has_lock
184 -- Purpose
185 -- Return Y if user holds locks
186 -- else return N
187 -- Parameter:
188 -- x_SID - Sessuib ID that user currently belongs
189 -- x_Schema - BSC schema name performance improvement
190
191 FUNCTION user_has_lock (
192 X_SID in NUMBER, X_SCHEMA IN VARCHAR2) RETURN VARCHAR2;
193
194 --
195 -- Name
196 -- can_meta_run
197 -- Purpose
198 -- Return Y if no user holds any locks and Meta Optimizer can start running
199 -- else return N
200 --
201
202 FUNCTION can_meta_run RETURN VARCHAR2;
203
204 --
205 -- Name
206 -- is_meta_inside
207 -- Purpose
208 -- Return Y if Meta Optimizer is inside the system
209 -- else return N
210
211 FUNCTION is_meta_inside RETURN VARCHAR2;
212
213 END bsc_security;
[Home] [Help]