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
|
#!/usr/bin/perl
#
# This script creates users for the RMC and LRC for a single VO.
#
# Author: James Casey <james.casey@cern.ch>
# Author: Miguel Anjo <miguel.anjo@cern.ch>
#
# 2004/12/14 : Changes made for the LCG File Catalog (LFC) by Sophie Lemaitre <Sophie.Lemaitre@cern.ch>
#
use strict;
use warnings;
use Getopt::Long;
use Env qw(ORACLE_SID ORACLE_HOME);
#
# Configuration constants. These could be changed if required
#
#################################################################
# start of script - do not change past here
#################################################################
#
# forwards
#
sub configuration();
sub usage($);
#
# check env variables
#
die "ORACLE_HOME environment variable not defined.\n"
if !defined($ORACLE_HOME);
die "ORACLE_SID environment variable not defined.\n"
if !defined($ORACLE_SID);
#
# get options
#
my ($name, $password);
my $tempTablespace="TEMP01";
my $verbose=0;
GetOptions("name=s" => \$name,
"password=s" => \$password,
"temp=s" => \$tempTablespace,
"v" => \$verbose);
usage("No user name specified") if !$name;
usage("No password specified") if !$password;
# capitalize
$name =~ tr/a-z/A-Z/;
$tempTablespace=~ tr/a-z/A-Z/;
# prefix the name with "LFC_"
$name="LFC_${name}";
my $logFile="/tmp/create-users.$$.log";
configuration() if $verbose;
print "Creating ROLE...\n" if $verbose;
# start sqlplus
open(SQLPLUS, "| $ORACLE_HOME/bin/sqlplus \"/ as sysdba\" > /dev/null")
or die("can't start SQLPLUS");
print SQLPLUS <<EOF;
-- setup error handling and logging
--
WHENEVER OSERROR EXIT FAILURE ROLLBACK
-- WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK
spool $logFile
CREATE ROLE lfcuser;
grant ALTER SESSION to lfcuser;
grant CREATE PROCEDURE to lfcuser;
grant CREATE SEQUENCE to lfcuser;
grant CREATE SESSION to lfcuser;
grant CREATE SNAPSHOT to lfcuser;
grant CREATE SYNONYM to lfcuser;
grant CREATE TABLE to lfcuser;
grant CREATE TRIGGER to lfcuser;
grant CREATE VIEW to lfcuser;
grant CREATE TYPE to lfcuser;
grant QUERY REWRITE to lfcuser;
EOF
close SQLPLUS;
print "Running SQLPLUS...\n" if $verbose;
# start sqlplus
open(SQLPLUS, "| $ORACLE_HOME/bin/sqlplus \"/ as sysdba\" > /dev/null")
or die("can't start SQLPLUS");
print SQLPLUS <<EOF;
-- setup error handling and logging
--
WHENEVER OSERROR EXIT FAILURE ROLLBACK
-- WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK
spool $logFile
-- DROP USER $name CASCADE;
CREATE USER $name IDENTIFIED BY "$password"
profile "DEFAULT"
DEFAULT TABLESPACE "${name}_DATA"
TEMPORARY TABLESPACE "$tempTablespace"
QUOTA UNLIMITED ON "${name}_DATA"
QUOTA UNLIMITED ON "${name}_IDX"
ACCOUNT UNLOCK;
GRANT "LFCUSER" to $name;
EOF
close SQLPLUS;
if($? != 0 ) {
die "Error while running sqlplus : see $logFile for more details";
}
unlink $logFile;
print "Done.\n" if $verbose;
exit;
#################################################################
# end of script
#################################################################
sub usage($) {
my $error = shift @_;
print <<EOF and die "Wrong usage of the script : $error\n";
usage : $0 --name=NAME --password=password
[--temp=tablespace] [--v]
Options
--name name The database user will be called LFC_<NAME>
--password password Password of the LFC_<NAME> account
--temp tablespace The name of the temp tablespace (defaults
to 'TEMP01')
--v verbose mode
EOF
}
sub configuration() {
print <<EOF;
Configuration :
ORACLE_HOME : $ORACLE_HOME
ORACLE_SID : $ORACLE_SID
Name : $name
Password : $password
Temp tablespace : $tempTablespace
EOF
}
|
