-
Notifications
You must be signed in to change notification settings - Fork 10
/
Copy pathRecreateUsers.sql
1952 lines (1817 loc) · 78.7 KB
/
RecreateUsers.sql
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
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
set lines 180 trimspool on verify off
CREATE OR REPLACE PACKAGE gdp$uptime_migration
AS
-- Author: Geert De Paep - Uptime Technologies
procedure SetDebug(DebugA in boolean default true);
Procedure VerifyTargetDatabase(DbNameA in varchar2,
HostNameA in varchar2 default null,
InstanceNameA in varchar2 default null,
VersionLikeA in varchar2 default null);
procedure SetScriptOptions(GenSqlA in boolean default true,
LogFileNameA in varchar2 default null,
WheneverSqlErrorA in boolean default false,
AddInfoA in boolean default true,
CheckRoleExistanceA in boolean default false,
NrPausesA in number default 0,
DocFormatA in varchar2 default 'TXT',
IncDropUsersA in boolean default false,
IncDropUsersCascadeA in boolean default false,
LineSizeA in number default 200
);
procedure SetDatafileOptions(SrcSeparatorA in varchar2 default null,
DstSeparatorA in varchar2 default null,
FilesizeInitialPctA in number default 100,
FilesizeAutomaxPctA in number default 100,
ForceAutoextendA in boolean default true,
DefaultDirectoryA in varchar2 default null,
ConvertFilenamesToLowerA in boolean default false,
IncDropPermanentTablespacesA in boolean default false,
IncIncludeDropTSContentsA in boolean default false,
IncDropTemporaryTablespacesA in boolean default false
);
PROCEDURE ExcludeTablespace(TablespaceA IN VARCHAR2);
PROCEDURE IncludeTablespace(TablespaceA IN VARCHAR2);
PROCEDURE ExcludeSysPriv(PrivilegeA IN VARCHAR2);
PROCEDURE ExcludeRole(RoleA IN VARCHAR2);
PROCEDURE AddDatafileDirectory(IfLikeA IN VARCHAR2, DirA IN VARCHAR2);
PROCEDURE SetUserList(UserListA in varchar2);
procedure SetCreateOptions(CreateUsers in boolean,
SysPrivsUsers in boolean,
ObjPrivsUsers in boolean,
DependentTS in boolean,
PublicSynonyms in boolean,
SetQuotas in boolean,
CreateRoles in boolean,
SysPrivsRoles in boolean,
ObjPrivsRoles in boolean,
GrantRoles in boolean,
Contexts in boolean);
procedure SetCreateOptions(PrePostImportA in varchar2);
procedure Run(BigBannerA in varchar2 default null);
procedure Reset;
procedure CORP;
procedure MDB;
procedure MDBW;
procedure BORP;
procedure CORPSTAT;
procedure ZINT;
END gdp$uptime_migration;
/
CREATE OR REPLACE PACKAGE BODY gdp$uptime_migration
AS
-- Minimum extent size for tablespaces
-- Create-options:
OptCreateUsers boolean;
OptSysPrivsUsers boolean;
OptObjPrivsUsers boolean;
OptDependentTS boolean;
OptPublicSynonyms boolean;
OptSetQuotas boolean;
OptCreateRoles boolean;
OptSysPrivsRoles boolean;
OptObjPrivsRoles boolean;
OptGrantRoles boolean;
OptContexts boolean;
-- Script-options:
DEBUG BOOLEAN := false;
GenSql boolean := true; -- If true, generate SQL stmts, if false, only tell what will be done
LogFileName varchar2(256); -- If specified, generate "spool <LogFileName>" in the output
IncludeWheneverSqlError BOOLEAN; -- Enclose CREATE statements by "whenever sqlerror"
NrPauses NUMBER; -- Ask this number of times for confirmation
DocFormat VARCHAR2(3); -- Output format
AddInfo BOOLEAN; -- Add extra informational messages
IncDropUsers BOOLEAN; -- Generates 'DROP USER' statements
IncDropUsersCascade BOOLEAN; -- Adds CASCADE to DROP USER statements
CheckRoleExistance BOOLEAN; -- Skip role creation if it already exists
LineSize number;
-- Datafile-options:
SourceDirectorySeparator CHAR(1); -- Typically / on UNIX and \ on Windows
DestinationDirectorySeparator CHAR(1); -- Typically / on UNIX and \ on Windows
DefaultDatafileDirectory VARCHAR2(256); -- If specified, use this dir for all datafile names. If null, keep original directory
ConvertFilenamesToLower BOOLEAN; -- Convert all datafile-names to lower
IncDropPermanentTablespaces BOOLEAN; -- Generate DROP statements for permanent tablespaces
IncIncludeDropTSContents BOOLEAN; -- Adds 'including contents and datafiles' to drop tablespace
IncDropTemporaryTablespaces BOOLEAN; -- Generate DROP statements for temporary tablespaces
ForceAutoextend BOOLEAN; -- Create all datafiles with autoextend option
FilesizeInitialPct NUMBER; -- Create datafiles initially as FilesizeInitialPct percent from current size
FilesizeAutomaxPct NUMBER; -- Create datafiles maxsize as FilesizeAutomaxPct percent from current maxsize
FilesizeAutomaxMaxKb number; -- Create datafiles maxsize never bigger than FilesizeAutomaxMaxKb k
ForceAutoallocate BOOLEAN;
-- Internal Variables:
OptVerifyDbName varchar2(64); -- If specified, the output will contain a check on database name
OptVerifyHostName varchar2(128);
OptVerifyInstanceName varchar2(64);
OptVerifyVersionLike varchar2(32);
TYPE V256Tbl IS TABLE OF VARCHAR2(256) INDEX BY BINARY_INTEGER;
TYPE V30Tbl IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
TYPE V61Tbl IS TABLE OF VARCHAR2(61) INDEX BY BINARY_INTEGER;
DatafileLikeTable V256Tbl;
DatafileDirectoryTable V256Tbl;
CreatedTablespaces V30Tbl;
IncludedTablespaces V30Tbl;
ExcludedSysPrivs V30Tbl;
ExcludedRoles V30Tbl;
CreatedRoles V30Tbl; -- List of roles for which CREATE ROLE is already done
GrantedSysPrivsRoles V30Tbl; -- List of roles that have received sysprivs
GrantedObjPrivsRoles V30Tbl; -- List of roles that have received objprivs
GrantedRoles V61Tbl; -- List of roles that have been granted to user or other role
PausesPrinted NUMBER;
DbBlockSize NUMBER;
DbVersion varchar2(32);
DbName VARCHAR2(32);
QueuedLevel number;
QueuedMessage varchar2(4000);
UserTab Dbms_Utility.uncl_array;
------------------------------------------------------------------------------
------------------------------------------------------------------------------
------------------------------------------------------------------------------
------------------------------------------------------------------------------
FUNCTION AddPostIfNotNull(MsgA IN VARCHAR2, PostFixA IN varchar2) RETURN VARCHAR2
IS
-- Help function to append a postfix to a value, only when that value is not null
BEGIN
IF (MsgA IS NULL) THEN RETURN NULL;
ELSE RETURN MsgA||PostFixA;
END IF;
END;
------------------------------------------------------------------------------
FUNCTION AddPreIfNotNull(MsgA IN VARCHAR2, PreFixA IN varchar2) RETURN VARCHAR2
IS
-- Help function to prepend a prefix to a value, only when that value is not null
BEGIN
IF (MsgA IS NULL) THEN RETURN NULL;
ELSE RETURN PreFixA||MsgA;
END IF;
END;
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
procedure PutLineSplit(PrefixA in varchar2, MsgA in varchar2,
WidthA in number, SplitOnA in varchar2 default ' ',
AllowSplitA in boolean default true)
is
-- Put a line in the output. Take the global variable "linesize" into account for wrapping.
-- If the line is longer than linesize and wraps, the PrefixA will
-- be repeated on the next line.
-- Lines will only be wrapped after a character listed in SplitOnA
-- If AllowSpliA is false and line is longer than linesize, an error will be raised
SpacePos number;
Msg varchar2(20000);
Cntr number := 0; -- To avoid infinite loop
begin
if (LineSize < length(PrefixA)+10) then
Raise_Application_Error(-20999,'Error in PutLineSplit: Linesize too small for prefix '||PrefixA);
end if;
if (SplitOnA is null) then
Raise_Application_Error(-20999,'Error in PutLineSplit: SplitOnA may not be null');
end if;
if (MsgA is null) then
if (DocFormat = 'HTP') then
htp.p('<BR>');
else
dbms_output.put_line(MsgA);
end if;
else
Msg := MsgA;
while (Length(PrefixA||Msg) > WidthA and Cntr < 1000) loop
for i in 1 .. length(SplitOnA) loop
SpacePos := instr(PrefixA||Msg,substr(SplitOnA,i,1),-(length(PrefixA||Msg)-WidthA+1));
if (SpacePos <> 0) then exit; end if;
end loop;
if (SpacePos = 0 and NOT AllowSplitA) then -- No character found to split on
Raise_Application_Error(-20999,'Error in PutLineSplit: Linesize too small, unable to wrap data. You must increase linesize.');
elsif (SpacePos = 0) then -- No character found to split on
if (DocFormat = 'HTP') then
htp.p(substr(PrefixA||Msg,1,WidthA)||'<BR>');
elsif (DocFormat = 'HTM') then
dbms_output.put_line(substr(PrefixA||Msg,1,WidthA)||'<BR>');
else
dbms_output.put_line(substr(PrefixA||Msg,1,WidthA));
end if;
Msg := substr(PrefixA||Msg,WidthA+1);
else
if (DocFormat = 'HTP') then
htp.p(substr(PrefixA||Msg,1,SpacePos)||'<BR>');
elsif (DocFormat = 'HTM') then
dbms_output.put_line(substr(PrefixA||Msg,1,SpacePos)||'<BR>');
else
dbms_output.put_line(substr(PrefixA||Msg,1,SpacePos));
end if;
Msg := substr(PrefixA||Msg,SpacePos+1);
end if;
Cntr := Cntr + 1;
end loop;
if (DocFormat = 'HTP') then
htp.p(PrefixA||Msg);
elsif (DocFormat = 'HTM') then
dbms_output.put_line(PrefixA||Msg);
else
dbms_output.put_line(PrefixA||Msg);
end if;
end if;
exception
when OTHERS then
raise_application_error(-20000,'Error in PutLineSplit',true);
end;
------------------------------------------------------------------------------
PROCEDURE PrintDebug(MsgA IN VARCHAR2)
IS
BEGIN
IF (DEBUG) THEN
PutLineSplit('--DEBUG ',MsgA,200);
END IF;
END;
------------------------------------------------------------------------------
PROCEDURE PrintInfo(LevelA IN number, MsgA IN VARCHAR2,
ConditionalA in boolean default false)
-- This procedure prints an informational line in the output.
-- I.e. it will generate: prompt <MsgA>
-- It will alos take into account the pauses, so at the beginning of the
-- output, it will generate a number of: pause <MsgA>
-- If ConditionalA = true, the MsgA will only be printed if it is followed
-- by a PrintMessage(...) statement, and not if it is followed by another PrintInfo(...)
IS
BEGIN
if (ConditionalA) then
PrintDebug('Entering PrintInfo with Msg '||substr(MsgA,1,10)||'... and ConditionalA=true');
-- We don't know if this message really needs to be printed. It depends on
-- whether there will still be printed more data
QueuedLevel := LevelA;
QueuedMessage := MsgA;
else
PrintDebug('Entering PrintInfo with Msg '||substr(MsgA,1,10)||'... and ConditionalA=false');
IF (PausesPrinted < NrPauses AND GenSql) THEN
PutLineSplit('pause ',MsgA||' ... <RET>',LineSize);
PausesPrinted := PausesPrinted + 1;
IF (PausesPrinted = NrPauses AND NrPauses > 0) THEN
PutLineSplit('pause ','The rest of this script will run automatically (no more pauses) ...<RET>', LineSize);
END IF;
ELSE
IF (AddInfo) THEN
PutLineSplit('prompt ',MsgA, LineSize);
IF (LevelA <= 2) THEN
-- Print an additional horizontal line if level <= 2
PutLineSplit(null, LPad('-',Length('prompt '||MsgA),'-'), LineSize);
END IF;
IF (LevelA = 1) THEN
-- Print an additional blank line if level = 1
PutLineSplit(null, '', LineSize);
END IF;
END IF;
END IF;
QueuedMessage := null;
end if;
END;
------------------------------------------------------------------------------
PROCEDURE PrintMessage(MsgA IN VARCHAR2)
IS
-- This procedure puts the MsgA into the output
-- It will first print the Queued message, if there is any
BEGIN
if (QueuedMessage is not null) then
PrintDebug('Entering PrintMessage, BUT first need to print previously queued message');
PrintInfo(QueuedLevel, QueuedMessage, false);
end if;
PutLineSplit(null,MsgA,LineSize, AllowSplitA => false);
END;
------------------------------------------------------------------------------
PROCEDURE StartWheneverSqlError
IS
BEGIN
IF (IncludeWheneverSqlError) then
PrintMessage('whenever sqlerror exit sql.sqlcode');
END IF;
END;
------------------------------------------------------------------------------
PROCEDURE StopWheneverSqlError
IS
BEGIN
IF (IncludeWheneverSqlError) then
PrintMessage('whenever sqlerror continue');
END IF;
END;
------------------------------------------------------------------------------
Procedure VerifyTargetDatabase(DbNameA in varchar2,
HostNameA in varchar2 default null,
InstanceNameA in varchar2 default null,
VersionLikeA in varchar2 default null)
is
begin
OptVerifyDbName := DbNameA;
OptVerifyHostName := HostNameA;
OptVerifyInstanceName := InstanceNameA;
OptVerifyVersionLike := VersionLikeA;
end;
------------------------------------------------------------------------------
Procedure DoVerifyTargetDatabase
is
begin
if (OptVerifyDbName is not null) then
PrintMessage('prompt Checking if we are in the correct database...');
PrintMessage('whenever sqlerror exit');
PrintMessage('set serveroutput on format wrapped');
PrintMessage('declare');
PrintMessage(' DbName varchar2(30);');
PrintMessage(' HostName varchar2(30);');
PrintMessage(' InstanceName varchar2(30);');
PrintMessage(' Version varchar2(30);');
PrintMessage('begin');
PrintMessage(' select instance_name, host_name, version, d.name');
PrintMessage(' into InstanceName, HostName, Version, DbName');
PrintMessage(' from v$instance, v$database d;');
PrintMessage(' dbms_output.put_line(''Checking if we are in the requested database:'');');
PrintMessage(' if (upper(DbName) = upper('''||OptVerifyDbName||''')) then');
PrintMessage(' dbms_output.put_line(''Database name: ''||DbName);');
PrintMessage(' else');
PrintMessage(' raise_application_error(-20000,''WRONG DATABASE!!! Databasename (''||DbName||'') not correct'');');
PrintMessage(' end if;');
if (OptVerifyInstanceName is not null) then
PrintMessage(' if (upper(InstanceName) = upper('''||OptVerifyInstanceName||''')) then');
PrintMessage(' dbms_output.put_line(''Instance name: ''||InstanceName);');
PrintMessage(' else');
PrintMessage(' raise_application_error(-20000,''WRONG DATABASE!!! Instancename (''||InstanceName||'') not correct'');');
PrintMessage(' end if;');
end if;
if (OptVerifyHostName is not null) then
PrintMessage(' if (upper(HostName) = upper('''||OptVerifyHostName||''')) then');
PrintMessage(' dbms_output.put_line(''Host name: ''||HostName);');
PrintMessage(' else');
PrintMessage(' raise_application_error(-20000,''WRONG DATABASE!!! HostName (''||HostName||'') not correct'');');
PrintMessage(' end if;');
end if;
if (OptVerifyVersionLike is not null) then
PrintMessage(' if (Version like '''||OptVerifyVersionLike||''') then');
PrintMessage(' dbms_output.put_line(''Version : ''||Version);');
PrintMessage(' else');
PrintMessage(' raise_application_error(-20000,''WRONG DATABASE!!! Version (''||Version||'') not correct'');');
PrintMessage(' end if;');
end if;
PrintMessage(' dbms_output.put_line(''This is the correct database!'');');
PrintMessage('end;');
PrintMessage('/');
PrintMessage('whenever sqlerror continue');
PrintMessage('');
end if;
end;
------------------------------------------------------------------------------
FUNCTION FetchViewText(OwnerA IN VARCHAR2, ViewA IN VARCHAR2) RETURN varchar2
IS
Cur NUMBER;
Dummy NUMBER;
BufSize NUMBER := 20000;
Offset NUMBER := 0;
OutData VARCHAR2(20000);
OutSize NUMBER;
BEGIN
Cur := Dbms_Sql.open_cursor();
Dbms_Sql.parse(Cur,'select text from dba_views where owner = :owner and view_name = :view_name', Dbms_Sql.native);
Dbms_Sql.bind_variable(Cur, 'owner', Upper(OwnerA));
Dbms_Sql.bind_variable(Cur, 'view_name', Upper(ViewA));
Dbms_Sql.define_column_long(Cur, 1);
Dummy := Dbms_Sql.EXECUTE(Cur);
Dummy := Dbms_Sql.fetch_rows(Cur);
IF (Dummy = 0) THEN
RETURN '[View '||OwnerA||'.'||ViewA||' does not exist]';
END IF;
Dbms_Sql.column_value_long(Cur, 1, BufSize, Offset, OutData, OutSize);
Dbms_Sql.close_cursor(Cur);
OutData := replace(OutData,Chr(10));
RETURN OutData;
END;
------------------------------------------------------------------------------
FUNCTION FetchViewCollist(OwnerA IN VARCHAR2, ViewA IN VARCHAR2) RETURN varchar2
IS
Collist VARCHAR2(20000);
BEGIN
FOR c_rec IN (SELECT column_name FROM dba_tab_columns
WHERE owner = OwnerA
AND table_name = ViewA
ORDER BY column_id) LOOP
collist := collist || ','||c_rec.column_name;
END LOOP;
RETURN SubStr(collist,2);
END;
------------------------------------------------------------------------------
function RoleCreated(RoleA in varchar2) return boolean
IS
-- Function returns tue if the 'CREATE ROLE' statement has already been
-- generated during the current run of this procedure.
begin
FOR i IN 1 .. CreatedRoles.Count LOOP
IF (RoleA = CreatedRoles(i)) THEN
RETURN true;
END IF;
END LOOP;
return false;
end;
------------------------------------------------------------------------------
procedure MarkRoleAsCreated(RoleA in varchar2)
IS
-- Internally register that the 'CREATE ROLE' has been generated
begin
CreatedRoles(CreatedRoles.count+1) := RoleA;
end;
------------------------------------------------------------------------------
function GrantedSysPrivsToRole(RoleA in varchar2) return boolean
is
begin
FOR i IN 1 .. GrantedSysPrivsRoles.Count LOOP
IF (RoleA = GrantedSysPrivsRoles(i)) THEN
RETURN true;
END IF;
END LOOP;
return false;
end;
------------------------------------------------------------------------------
procedure MarkGrantedSysPrivsToRole(RoleA in varchar2)
is
begin
GrantedSysPrivsRoles(GrantedSysPrivsRoles.count+1) := RoleA;
end;
------------------------------------------------------------------------------
function GrantedObjPrivsToRole(RoleA in varchar2) return boolean
is
begin
FOR i IN 1 .. GrantedObjPrivsRoles.Count LOOP
IF (RoleA = GrantedObjPrivsRoles(i)) THEN
RETURN true;
END IF;
END LOOP;
return false;
end;
------------------------------------------------------------------------------
procedure MarkGrantedObjPrivsToRole(RoleA in varchar2)
is
begin
GrantedObjPrivsRoles(GrantedObjPrivsRoles.count+1) := RoleA;
end;
------------------------------------------------------------------------------
function RoleGranted(RoleA in VARCHAR2, ToA IN varchar2) return boolean
is
begin
FOR i IN 1 .. GrantedRoles.Count LOOP
IF (Upper(RoleA||'.'||ToA) = GrantedRoles(i)) THEN
RETURN true;
END IF;
END LOOP;
return false;
end;
------------------------------------------------------------------------------
procedure MarkRoleAsGranted(RoleA in varchar2, ToA IN varchar2)
is
begin
GrantedRoles(GrantedRoles.count+1) := Upper(RoleA||'.'||ToA);
end;
------------------------------------------------------------------------------
PROCEDURE RecreateTablespace(TablespaceA IN VARCHAR2, ReasonA in varchar2)
IS
t_rec dba_tablespaces%ROWTYPE;
NrFiles NUMBER := 0;
NewFilename VARCHAR2(512);
Prefix VARCHAR2(16);
DirectoryToUse VARCHAR2(256);
FilenameToUse VARCHAR2(256);
UNEXISTANT_TS exception;
Assm varchar2(32);
BEGIN
-- Exclude SYSTEM tablespace
IF (TablespaceA = 'SYSTEM') THEN RETURN; END IF;
-- Check if this tablespace was already created before
FOR i IN 1 .. CreatedTablespaces.Count LOOP
IF (TablespaceA = CreatedTablespaces(i)) THEN
RETURN;
END IF;
END LOOP;
begin
SELECT * INTO t_rec FROM dba_tablespaces WHERE tablespace_name = Upper(TablespaceA);
exception
when NO_DATA_FOUND then
raise UNEXISTANT_TS;
end;
if (NOT GenSql) then
PrintMessage('Tablespace '||t_rec.tablespace_name||' will be created because '||ReasonA);
else
-- Drop the tablespace first if requested to do so
IF (IncDropPermanentTablespaces AND t_rec.CONTENTS = 'PERMANENT') THEN
PrintInfo(3,'Dropping tablespace '||t_rec.tablespace_name);
IF (IncIncludeDropTSContents) then
PrintMessage('drop tablespace '||t_rec.tablespace_name||' including contents and datafiles;');
ELSE
PrintMessage('drop tablespace '||t_rec.tablespace_name||';');
END IF;
ELSIF (IncDropTemporaryTablespaces AND t_rec.CONTENTS = 'TEMPORARY') THEN
PrintInfo(3,'Dropping tablespace '||t_rec.tablespace_name);
PrintMessage('drop tablespace '||t_rec.tablespace_name||';');
END IF;
-- Write the first part of the CREATE statement (no datafiles yet)
PrintInfo(3,'Creating tablespace '||t_rec.tablespace_name||' because '||ReasonA);
StartWheneverSqlError;
IF (t_rec.CONTENTS = 'PERMANENT') then
PrintMessage('CREATE TABLESPACE '||t_rec.tablespace_name);
ELSIF (t_rec.CONTENTS = 'TEMPORARY') THEN
PrintMessage('CREATE TEMPORARY TABLESPACE '||t_rec.tablespace_name);
ELSIF (t_rec.CONTENTS = 'UNDO') THEN
PrintMessage('CREATE UNDO TABLESPACE '||t_rec.tablespace_name);
ELSE
PrintMessage('-- ERROR: this tablespace type is not supported '||t_rec.CONTENTS);
END IF;
-- Add all datafiles
FOR d_rec IN (SELECT * FROM dba_data_files
WHERE tablespace_name = Upper(TablespaceA)
ORDER BY file_id) LOOP
IF (NrFiles = 0 AND t_rec.CONTENTS in ('PERMANENT','UNDO')) THEN Prefix := ' DATAFILE ';
ELSIF (NrFiles = 0 AND t_rec.CONTENTS = 'TEMPORARY') THEN Prefix := ' TEMPFILE ';
ELSE Prefix := ' , ';
END IF;
PrintDebug('Adding file '||d_rec.file_name||' '||Ceil(d_rec.bytes/1024/1024)||'M --> '||Ceil(d_rec.maxbytes/1024/1024)||'M');
DirectoryToUse := NULL;
-- Search if a special mapping is defined for this datafile:
FOR i IN 1 .. DatafileLikeTable.Count LOOP
IF (d_rec.file_name LIKE DatafileLikeTable(i)) THEN
DirectoryToUse := DatafileDirectoryTable(i);
EXIT;
END IF;
END LOOP;
IF (DirectoryToUse IS NULL) THEN
DirectoryToUse := DefaultDatafileDirectory;
END IF;
IF (ConvertFilenamesToLower) then
FilenameToUse := Lower(d_rec.file_name);
ELSE
FilenameToUse := d_rec.file_name;
END IF;
IF (DirectoryToUse IS NULL) THEN
NewFilename := FilenameToUse;
ELSE
NewFileName := RTrim(DirectoryToUse,DestinationDirectorySeparator)||DestinationDirectorySeparator||
SubStr(FilenameToUse,InStr(d_rec.file_name,SourceDirectorySeparator,-1)+1);
end if;
PrintMessage(Prefix||''''||NewFilename||''' size '||ceil((d_rec.bytes*FilesizeInitialPct/100)/1024/1024)||'M');
IF (d_rec.autoextensible = 'YES') THEN
PrintMessage(' AUTOEXTEND on NEXT '||(d_rec.increment_by*DbBlockSize)/1024||'K MAXSIZE '||
Ceil(least(d_rec.maxbytes*FilesizeAutomaxPct/100/1024,FilesizeAutomaxMaxKb))||'K');
ELSIF (ForceAutoextend) THEN
PrintMessage(' AUTOEXTEND on NEXT '||Ceil((d_rec.bytes/20)/1024)||'K MAXSIZE '||
Ceil(least(d_rec.bytes*FilesizeAutomaxPct/100/1024,FilesizeAutomaxMaxKb))||'K');
END IF;
NrFiles := NrFiles + 1;
END LOOP;
-- Adding tempfiles
FOR d_rec IN (SELECT * FROM dba_temp_files
WHERE tablespace_name = Upper(TablespaceA)
ORDER BY file_id) LOOP
IF (NrFiles = 0) THEN Prefix := ' TEMPFILE ';
ELSE Prefix := ' , ';
END IF;
DirectoryToUse := NULL;
-- Search if a special mapping is defined for this datafile:
FOR i IN 1 .. DatafileLikeTable.Count LOOP
IF (d_rec.file_name LIKE DatafileLikeTable(i)) THEN
DirectoryToUse := DatafileDirectoryTable(i);
EXIT;
END IF;
END LOOP;
IF (DirectoryToUse IS NULL) THEN
DirectoryToUse := DefaultDatafileDirectory;
END IF;
IF (ConvertFilenamesToLower) then
FilenameToUse := Lower(d_rec.file_name);
ELSE
FilenameToUse := d_rec.file_name;
END IF;
IF (DirectoryToUse IS NULL) THEN
NewFilename := d_rec.file_name;
ELSE
NewFileName := RTrim(DirectoryToUse,DestinationDirectorySeparator)||DestinationDirectorySeparator||
SubStr(FilenameToUse,InStr(d_rec.file_name,SourceDirectorySeparator,-1)+1);
end if;
PrintMessage(Prefix||''''||NewFilename||''' size '||ceil((d_rec.bytes*FilesizeInitialPct/100)/1024/1024)||'M');
IF (d_rec.autoextensible = 'YES') THEN
PrintMessage(' AUTOEXTEND on NEXT '||(d_rec.increment_by*DbBlockSize)/1024||'K MAXSIZE '||
Ceil(least(d_rec.maxbytes*FilesizeAutomaxPct/100/1024,FilesizeAutomaxMaxKb))||'K');
ELSIF (ForceAutoextend) THEN
PrintMessage(' AUTOEXTEND on NEXT '||Ceil((d_rec.bytes/20)/1024)||'K MAXSIZE '||
Ceil(least(d_rec.bytes*FilesizeAutomaxPct/100/1024,FilesizeAutomaxMaxKb))||'K');
END IF;
NrFiles := NrFiles + 1;
END LOOP;
IF (t_rec.extent_management = 'LOCAL') THEN
IF (t_rec.CONTENTS = 'TEMPORARY') THEN --TEMP TS must have UNIFORM size
PrintMessage(' extent management LOCAL uniform size '||t_rec.next_extent);
ELSIF (t_rec.allocation_type = 'SYSTEM' OR ForceAutoallocate) THEN
PrintMessage(' extent management LOCAL autoallocate');
ELSIF (t_rec.CONTENTS = 'UNDO') THEN -- UNDO TS must have autoallocate
PrintMessage(' extent management LOCAL autoallocate');
ELSIF (t_rec.allocation_type = 'UNIFORM') THEN
PrintMessage(' extent management LOCAL uniform size '||t_rec.next_extent);
ELSE
Raise_application_error(-20000,'Error: Unsupported allocation type: '||t_rec.allocation_type);
END IF;
if (DbVersion not like '8.%') then
execute immediate 'select segment_space_management from dba_tablespaces '||
' where tablespace_name = '''||t_rec.tablespace_name||''''
into Assm;
PrintMessage(' segment space management '||Assm);
end if;
END IF;
IF (t_rec.CONTENTS NOT IN ('TEMPORARY','UNDO')) THEN
PrintMessage(' '||t_rec.LOGGING);
end if;
PrintMessage(';');
StopWheneverSqlError;
end if;
CreatedTablespaces(CreatedTablespaces.Count+1) := TablespaceA;
EXCEPTION
when UNEXISTANT_TS then
PrintMessage('prompt !! WARNING Unexistant tablespace '||TablespaceA||' found, check def_ts and temp_ts in dba_users !!');
WHEN OTHERS THEN
Raise_application_error(-20000,'Error in RecreateTablespace for '||TablespaceA,true);
END;
------------------------------------------------------------------------------
function IsExcludedSysPriv(PrivilegeA in varchar2) return boolean
is
begin
for i in 1 .. ExcludedSysPrivs.count loop
if (upper(ExcludedSysPrivs(i)) = upper(PrivilegeA)) then
return true;
end if;
end loop;
return false;
end;
------------------------------------------------------------------------------
FUNCTION IsPredefinedRole(RoleA IN VARCHAR2) RETURN BOOLEAN
IS
BEGIN
IF (RoleA IN ('CONNECT','RESOURCE','DBA','EXP_FULL_DATABASE','IMP_FULL_DATABASE',
'SNMPAGENT','SELECT_CATALOG_ROLE','HS_ADMIN_ROLE','EXECUTE_CATALOG_ROLE',
'DELETE_CATALOG_ROLE','GATHER_SYSTEM_STATISTICS','MONITOR_ROLE',
'ORA_OWNER','ORA_OWNER_SESSION','ORA_OWNER_SPECIAL','OLAP_DBA','WM_ADMIN_ROLE',
'AQ_ADMINISTRATOR_ROLE','AQ_USER_ROLE','JAVA_ADMIN','JAVA_DEPLOY',
'JAVASYSPRIV','JAVAUSERPRIV','JAVAIDPRIV','JAVADEBUGPRIV',
'OEM_MONITOR','RECOVERY_CATALOG_OWNER'
)) THEN
RETURN true;
ELSE
RETURN FALSE;
END IF;
END;
------------------------------------------------------------------------------
function IsExcludedRole(RoleA in varchar2) return boolean
is
begin
for i in 1 .. ExcludedRoles.count loop
if (upper(ExcludedRoles(i)) = upper(RoleA)) then
return true;
end if;
end loop;
return false;
end;
------------------------------------------------------------------------------
function IsUser(UserA in varchar2) return boolean
is
x number;
begin
select 1 into x from dba_users where username = upper(UserA);
return true;
exception
when no_data_found then return false;
end;
------------------------------------------------------------------------------
PROCEDURE CreateRole(RoleA IN VARCHAR2, ReasonA in varchar2)
IS
BEGIN
IF (GenSql) then
PrintInfo(3,'Creating role '||RoleA||' because '||ReasonA);
StartWheneverSqlError;
IF (CheckRoleExistance) then
PrintMessage('declare');
PrintMessage(' NrFound number;');
PrintMessage('begin');
PrintMessage(' select count(*) into NrFound from dba_roles where role = '''||RoleA||''';');
PrintMessage(' if (NrFound = 0) then');
PrintMessage(' execute immediate ''create role '||RoleA||''';');
PrintMessage(' dbms_output.put_line(''Created role '||RoleA||''');' );
PrintMessage(' else');
PrintMessage(' dbms_output.put_line(''Role '||RoleA||' already exists'');' );
PrintMessage(' end if;');
PrintMessage('end;');
PrintMessage('/');
ELSE
PrintMessage('create role '||RoleA||';');
END IF;
StopWheneverSqlError;
ELSE
PrintMessage('Role '||RoleA||' will be created because '||ReasonA);
END IF;
EXCEPTION
WHEN OTHERS THEN
Raise_application_error(-20000,'Error in CreateRoleWithSysPrivs for '||RoleA,true);
END;
------------------------------------------------------------------------------
PROCEDURE DoRolesGrantedToUser(GranteeA IN VARCHAR2, ActionA IN VARCHAR2,
LevelA IN NUMBER DEFAULT 0)
-- Do action on the roles that are granted to this user + parent roles
IS
UserHasUnlimitedTS CHAR(1);
NrRoles NUMBER;
NrGrants number;
Prefix VARCHAR2(25);
BEGIN
IF (ActionA NOT IN ('CREATE','ADDSYSPRIVS','ADDOBJPRIVS','GRANT')) THEN
Raise_Application_Error(-20000,'Internal error: DoRolesGrantedToUser called with invalid action');
END IF;
IF (LevelA > 25) THEN Raise_Application_Error(-20000,'Nested level too deep for roles granted to other roles'); ELSE Prefix := LPad(' ',LevelA+1); END IF;
PrintDebug(Prefix||'Entering DoRolesGrantedToUser for Grantee='||GranteeA||', Action='||ActionA||', Level '||LevelA);
-- IF (GenSql) THEN
FOR r_rec IN (SELECT granted_role, Decode(admin_option,'YES',' with admin option','') AS admin_option
FROM dba_role_privs
WHERE grantee = GranteeA
order by granted_role) LOOP
PrintDebug(Prefix||'Role '||r_rec.granted_role||' is granted to '||GranteeA||', processing...');
IF (ActionA = 'CREATE' AND NOT IsPredefinedRole(r_rec.granted_role)
and NOT IsExcludedRole(r_rec.granted_role)) then
if (NOT RoleCreated(r_rec.granted_role) ) then
CreateRole(r_rec.granted_role,'it was granted to '||GranteeA);
MarkRoleAsCreated(r_rec.granted_role);
end if;
ELSIF (ActionA = 'GRANT' and NOT IsExcludedRole(r_rec.granted_role)) then
if ( NOT RoleGranted(RoleA => r_rec.granted_role, ToA => GranteeA)) then
IF (GenSql) then
PrintInfo(3,'Granting '||r_rec.granted_role||' to '||GranteeA);
StartWheneverSqlError;
PrintMessage('grant '||r_rec.granted_role||' to '||GranteeA||r_rec.admin_option||';');
-- Revoke unlimited tablespace for RESOURCE and DBA
SELECT Decode(Count(*),0,'N','Y') INTO UserHasUnlimitedTS FROM dba_sys_privs WHERE grantee = GranteeA;
IF (r_rec.granted_role IN ('RESOURCE','DBA') AND UserHasUnlimitedTS = 'N' and IsUser(GranteeA)) THEN
PrintInfo(3,'Revoking UNLIMITED TABLESPACE from '||GranteeA);
PrintMessage('revoke unlimited tablespace from '||GranteeA||';');
END IF;
StopWheneverSqlError;
ELSE
PrintMessage('Role '||r_rec.granted_role||' will be granted to '||GranteeA);
END IF;
MarkRoleAsGranted(RoleA => r_rec.granted_role, ToA => GranteeA);
end if;
ELSIF (ActionA = 'ADDSYSPRIVS' AND NOT IsPredefinedRole(r_rec.granted_role)
and NOT IsExcludedRole(r_rec.granted_role)) THEN
if (NOT GrantedSysPrivsToRole(r_rec.granted_role) ) then
IF (GenSql) then
PrintInfo(3,'Adding system privileges to role '||r_rec.granted_role, true);
FOR p_rec IN (SELECT grantee, privilege, Decode(admin_option,'YES',' with admin option','') AS admin_option
FROM dba_sys_privs
WHERE grantee = r_rec.granted_role
order by grantee, privilege) LOOP
if (NOT IsExcludedSysPriv(p_rec.privilege)) then
PrintMessage('grant '||p_rec.privilege||' to '||r_rec.granted_role||p_rec.admin_option||';');
end if;
END LOOP;
-- Grants on objects of SYS are not imported in user-import
-- Should be given to roles in advance to avoid compilation errors due to insufficient privileges
PrintInfo(3,'Adding object privileges of SYS objects to role '||r_rec.granted_role, true);
FOR o_rec IN (SELECT owner, table_name, grantee, privilege, Decode(grantable,'YES',' with grant option','') AS grantable
FROM dba_tab_privs
WHERE grantee = r_rec.granted_role
and owner = 'SYS'
ORDER BY grantor, table_name, privilege) LOOP
PrintMessage('GRANT '||o_rec.privilege||' ON '||o_rec.owner||'.'||o_rec.table_name||
' TO '||r_rec.granted_role||o_rec.grantable||';');
END LOOP;
ELSE
SELECT Count(*) INTO NrGrants FROM dba_sys_privs
WHERE grantee = r_rec.granted_role;
IF (NrGrants > 0) then
PrintMessage(NrGrants||' system privileges will be granted to role '||r_rec.granted_role);
END IF;
SELECT Count(*) INTO NrGrants FROM dba_tab_privs
WHERE grantee = r_rec.granted_role
and owner = 'SYS';
IF (NrGrants > 0) then
PrintMessage(NrGrants||' object privileges on SYS objects will be granted to role '||r_rec.granted_role);
END IF;
END IF;
MarkGrantedSysprivsToRole(r_rec.granted_role);
end if;
ELSIF (ActionA = 'ADDOBJPRIVS' AND NOT IsPredefinedRole(r_rec.granted_role)
and NOT IsExcludedRole(r_rec.granted_role)) THEN
if (NOT GrantedObjPrivsToRole(r_rec.granted_role)) then
IF (GenSql) then
PrintInfo(3,'Adding object privileges to role '||r_rec.granted_role, true);
FOR o_rec IN (SELECT owner, table_name, grantee, privilege,
Decode(grantable,'YES',' with grant option','') AS grantable
FROM dba_tab_privs
WHERE grantee = r_rec.granted_role
ORDER BY grantor, table_name, privilege) LOOP
PrintMessage('GRANT '||o_rec.privilege||' ON '||o_rec.owner||'.'||o_rec.table_name||
' TO '||r_rec.granted_role||o_rec.grantable||';');
END LOOP;
ELSE
SELECT Count(*) INTO NrGrants FROM dba_tab_privs
WHERE grantee = r_rec.granted_role;
IF (NrGrants > 0) then
PrintMessage(NrGrants||' object privileges will be granted to role '||r_rec.granted_role);
END IF;
END IF;
MarkGrantedObjprivsToRole(r_rec.granted_role);
end if;
elsif (IsExcludedRole(r_rec.granted_role)) then
PrintDebug(Prefix||'Role '||r_rec.granted_role||' is in list of excluded roles, nothing done');
ELSE
PrintDebug(Prefix||'Nothing to do for role '||r_rec.granted_role);
END IF;
IF (NOT IsPredefinedRole(r_rec.granted_role) and NOT IsExcludedRole(r_rec.granted_role)) then
PrintDebug(Prefix||'Now recursively checking '||r_rec.granted_role);
DoRolesGrantedToUser(r_rec.granted_role, ActionA, LevelA + 1);
END IF;
END LOOP;
-- ELSE
-- IF (ActionA = 'CREATE') then
-- select count(distinct granted_role) into NrRoles
-- from dba_role_privs
-- connect by prior granted_role = grantee
-- start with grantee = upper(GranteeA);
-- if (NrRoles > 0) then
-- PrintMessage(NrRoles||' roles will be created for user '||GranteeA);
-- end if;
-- ELSIF (ActionA = 'GRANT') then
-- select count(*) into NrGrants
-- from dba_role_privs
-- connect by prior granted_role = grantee
-- start with grantee = upper(GranteeA);
-- if (NrRoles > 0) then
-- PrintMessage(NrGrants||' grants are needed for the roles of user '||GranteeA);
-- end if;
-- ELSIF (ActionA = 'ADDSYSPRIVS') THEN
-- PrintMessage('Todo...');
-- ELSIF (ActionA = 'ADDOBJPRIVS') THEN
-- PrintMessage('Todo...');
-- end if;
-- END IF;
PrintDebug(Prefix||'Finished DoRolesGrantedToUser for Grantee='||GranteeA||', Action='||ActionA||', Level '||LevelA);
EXCEPTION
WHEN OTHERS THEN
Raise_application_error(-20000,'Error in DoRolesGrantedToUser for '||GranteeA||' at level '||LevelA,true);
END;
------------------------------------------------------------------------------
PROCEDURE RolesToWhichObjPrivsAreGranted(GrantorA IN VARCHAR2)
IS
-- If the user has granted object privileges to a role, create that role as well
NrRoles number;
BEGIN
-- if (NOT GenSql) then
-- select count(distinct grantee) into NrRoles
-- FROM dba_tab_privs
-- WHERE grantor = GrantorA
-- AND grantee IN (SELECT ROLE FROM dba_roles);
-- if (NrRoles > 0) then
-- PrintMessage('User '||GrantorA||' will grant object privileges to '||NrRoles||' different roles');
-- end if;
-- else
FOR r_rec IN (SELECT DISTINCT grantee
FROM dba_tab_privs
WHERE grantor = GrantorA
AND grantee IN (SELECT ROLE FROM dba_roles)
) LOOP
if (NOT RoleCreated(r_rec.grantee)) then
CreateRole(r_rec.grantee,GrantorA||' gave obj privs to it');
MarkRoleAsCreated(r_rec.grantee);
end if;
END LOOP;
-- end if;
END;
------------------------------------------------------------------------------
PROCEDURE CreatePublicSynonyms(UsernameA IN VARCHAR2)
IS
NrSyn number;
BEGIN
PrintDebug('Entering CreatePublicSynonyms for user '||UsernameA);
if (NOT GenSql) then
select count(*) into NrSyn FROM dba_synonyms WHERE owner = 'PUBLIC' AND table_owner = UsernameA;
if (NrSyn > 0) then
PrintMessage(NrSyn||' public synonyms refer to user '||UsernameA);
end if;
else
StartWheneverSqlError;
FOR s_rec IN (SELECT * FROM dba_synonyms WHERE owner = 'PUBLIC' AND table_owner = UsernameA) LOOP
PrintInfo(3,'Creating public synonym '||UsernameA||'.'||s_rec.synonym_name);
PrintMessage('CREATE or replace public synonym '||s_rec.synonym_name||' for '||s_rec.table_owner||'.'||s_rec.table_name||';');
END LOOP;
StopWheneverSqlError;
end if;
END;
------------------------------------------------------------------------------
PROCEDURE CreateDependentTablespaces(UsernameA IN VARCHAR2)
IS
u_rec dba_users%ROWTYPE;
USER_NOT_FOUND EXCEPTION;
BEGIN
PrintDebug('Entering CreateDependentTablespaces for user '||UsernameA);
begin
SELECT * INTO u_rec FROM dba_users WHERE username = Upper(UsernameA);
EXCEPTION
WHEN No_Data_Found THEN RAISE USER_NOT_FOUND;
END;
IF (GenSql) THEN PrintInfo(3,'Creating dependent tablespaces for user '||UsernameA, true); END IF;
FOR t_rec IN (SELECT DISTINCT default_tablespace AS tablespace_name, 'is default TS for '||UsernameA as reason FROM dba_users WHERE username = Upper(UsernameA)
UNION ALL
SELECT DISTINCT temporary_tablespace AS tablespace_name, 'is temp TS for '||UsernameA FROM dba_users WHERE username = Upper(UsernameA)
UNION ALL
SELECT DISTINCT tablespace_name, 'user '||UsernameA||' has quota on it' FROM dba_ts_quotas WHERE username = Upper(UsernameA)
UNION all
SELECT DISTINCT tablespace_name, 'user '||UsernameA||' has data in it' FROM dba_segments WHERE owner = Upper(UsernameA)
) loop
RecreateTablespace(t_rec.tablespace_name,t_rec.reason);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
Raise_application_error(-20000,'Error in CreateDependentTS for '||UserNameA,true);
END;
------------------------------------------------------------------------------
procedure CreateContexts(UserNameA in varchar2)
is
Stmt varchar2(1024);
CtxType varchar2(64);
begin
PrintDebug('Entering CreateContexts for '||UsernameA);
IF (GenSql) THEN PrintInfo(3,'Creating contexts for user '||UsernameA, true); end if;
for c_rec in (select * from dba_context where schema = upper(UsernameA)) loop
Stmt := 'create context '||c_rec.namespace||' using '||c_rec.schema||'.'||c_rec.package;
if (NOT DbVersion like '8.%') then
-- Accessed globally was only introduced in Oracle 9i
execute immediate 'select type from dba_context where namespace = '''||c_rec.namespace||'''' into CtxType;
Stmt := Stmt || ' ' || CtxType;
end if;
if (GenSql) then
PrintMessage(Stmt);
else
PrintMessage('Context '||c_rec.namespace||' will be created');
end if;
end loop;
end;
------------------------------------------------------------------------------
PROCEDURE CreateOneUser(UsernameA IN VARCHAR2)
IS
u_rec dba_users%ROWTYPE;
USER_NOT_FOUND EXCEPTION;
NrPrivs number;
BEGIN
PrintDebug('Entering CreateOneUser for '||UsernameA);
begin
SELECT * INTO u_rec FROM dba_users WHERE username = Upper(UsernameA);