-
Notifications
You must be signed in to change notification settings - Fork 6
Expand file tree
/
Copy pathcheck_user.sql
More file actions
73 lines (64 loc) · 2.5 KB
/
Copy pathcheck_user.sql
File metadata and controls
73 lines (64 loc) · 2.5 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
--Stop and print an error message if the user is not logged on as the appropriate user.
--Parameters: &1 - Can be one of "must_run_as_sys", "must_not_run_as_sys_and_has_dba", or "must_be_m5_user".
whenever sqlerror exit;
set serveroutput on format truncated verify off feedback off;
prompt Checking user...;
declare
v_count number;
--Print an error message.
--This is a bit trickier than you might think. It's hard to get the text
--to always line up in SQL*Plus.
procedure print_error is
begin
dbms_output.put_line(' ______ _____ _____ ____ _____ ');
dbms_output.put_line('| ____|| __ \ | __ \ / __ \ | __ \ ');
dbms_output.put_line('| |__ | |__) || |__) || | | || |__) | ');
dbms_output.put_line('| __| | _ / | _ / | | | || _ / ');
dbms_output.put_line('| |____ | | \ \ | | \ \ | |__| || | \ \ ');
dbms_output.put_line('|______||_| \_\|_| \_\ \____/ |_| \_\ ');
end print_error;
begin
--Check for errors.
if '&1' = 'must_run_as_sys' then
if user <> 'SYS' then
print_error;
raise_application_error(-20000,
'This step must be run as SYS.'||chr(13)||chr(10)||
'Logon as SYS and re-run.');
end if;
elsif '&1' = 'must_not_run_as_sys_and_has_dba' then
--Ensure the user is not SYS.
if user = 'SYS' then
print_error;
raise_application_error(-20000,
'This step must not be run as SYS.'||chr(13)||chr(10)||
'Logon with a personal DBA account and re-run.');
end if;
--Ensure the user has DBA role.
select count(*) into v_count from dual where sys_context('SYS_SESSION_ROLES', 'DBA') = 'TRUE';
if v_count = 0 then
print_error;
raise_application_error(-20000,
'This step must be run as a user with the DBA role.'||chr(13)||chr(10)||
'Logon with a personal DBA account and re-run.');
end if;
elsif '&1' = 'must_be_m5_user' then
execute immediate 'select count(*) from method5.m5_user where upper(oracle_username) = user'
into v_count;
if v_count = 0 then
print_error;
raise_application_error(-20000,
'This step must be run as a user configured to use Method5.'||chr(13)||chr(10)||
'Logon with an account registered in METHOD5.M5_USER and re-run.');
end if;
else
print_error;
raise_application_error(-20000, 'Unexpected parameter.');
end if;
--Print success message.
dbms_output.put_line('PASS. The script is running as this user: '||user);
end;
/
whenever sqlerror continue;
set verify on feedback on;
set serveroutput on format wrapped;