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
|
<html>
<head>
<title>firstworks Using SQL Relay With Oracle 8i/9i/10g n-Tiered Authentication</title>
<link href="css/styles.css" rel="stylesheet">
</head>
<body>
<span class="heading1">Using SQL Relay With Oracle 8i/9i/10g n-Tiered Authentication</span><br><br>
<span class="heading2">Background</span>
<p>Ordinarily, <b>SQL Relay</b> logs into the database as a particular user
several times and hands those sessions off to <b>SQL Relay</b> clients, thus
avoiding the cost of connecting to and disconnecting from the database over and
over. A sometimes undesirable side-effect of this approach is that it is
impossible to distinguish which queries were run by which <b>SQL Relay</b>
users from within the database since <b>SQL Relay</b> uses the same
database user to run all queries.</p>
<p>Oracle n-tiered authentication provides a way around this side-effect.</p>
<p>If you set up a proxy role, a proxy user and a set of users that can be
proxied by that proxy user in Oracle and configure <b>SQL Relay</b> to use the
"database" authentication tier, <b>SQL Relay</b> users will map to Oracle
users.</p>
<span class="heading2">Setting Up Oracle</span>
<p>First, make sure your database's compatibility mode is set to a version
equal to or higher than "8.1.0". Log in as the sys user and run:</p>
<blockquote>
<b>select name,value from v$parameter where name='compatible'</b>
</blockquote>
<p>(Note: In 8i, you can log in as sys using <i>sqlplus sys/syspass</i>
where syspass is replaced with the sys user's password. In 9i/10g, you
must use <i>sqlplus sys/syspass as sysdba</i> to log in as the sys user.)</p>
<p>If the compatibility mode is not 8.1.0 or greater, then follow these
steps:</p>
<p>If you have an $ORACLE_HOME/dbs/init$ORACLE_SID.ora file, change the
"compatible" parameter to a version equal to or higher than "8.1.0".</p>
<p>If you have an $ORACLE_HOME/dbs/spfile$ORACLE_SID.ora then you can log in as
the sys user and run:</p>
<blockquote>
<b>ALTER SYSTEM SET compatible='8.1.0' SCOPE=spfile</b>
</blockquote>
<p>(Note: In 8i, you can log in as sys using <i>sqlplus sys/syspass</i>
where syspass is replaced with the sys user's password. In 9i/10g, you
must use <i>sqlplus sys/syspass as sysdba</i> to log in as the sys user.)</p>
<p>After changing the compatibility mode, restart the database.</p>
<p>Next, log into the database as system and create a set of users:</p>
<blockquote><b>CREATE USER user1 IDENTIFIED BY user1;<br>
GRANT CREATE SESSION TO user1;<br>
CREATE USER user2 IDENTIFIED BY user2;<br>
GRANT CREATE SESSION TO user2;<br>
CREATE USER user3 IDENTIFIED BY user3;<br>
GRANT CREATE SESSION TO user3;</b></blockquote>
<p>You may also need to run:</p>
<blockquote><b>ALTER USER user1 QUOTA UNLIMITED ON <i>tablespace_name</i>;<br>
ALTER USER user2 QUOTA UNLIMITED ON <i>tablespace_name</i>;<br>
ALTER USER user3 QUOTA UNLIMITED ON <i>tablespace_name</i>;<br></b></blockquote>
<p>Where <i>tablespace_name</i> is replaced with the name of the tablespace that
the user's schema was created in. This appears to be necessary with
Oracle 10g, but I don't remember it being necessary prior to 10g.</p>
<p>Now, create a proxy role and give the users access to it:</p>
<blockquote><b>CREATE ROLE proxyrole;<br>
GRANT proxyrole TO user1;<br>
GRANT proxyrole TO user2;<br>
GRANT proxyrole TO user3;</b></blockquote>
<p>At this point, you'll need to perform grants to the proxyrole to give it
whatever permissions that the users that may use it will need. Grants to roles
are the performed like grants to users. For example if proxyrole needs random
access to "publictable" and read access to "readonlytable":</p>
<blockquote><b>GRANT all ON publictable TO proxyrole;<br>
GRANT select ON readonlytable TO proxyrole;<br>
</b></blockquote>
<p>Create a proxy user:</p>
<blockquote><b>CREATE USER proxyuser IDENTIFIED BY proxyuser;<br>
GRANT CREATE SESSION TO proxyuser;</b></blockquote>
<p>Give the users access through the proxy user:</p>
<blockquote>
<b>ALTER USER user1 GRANT CONNECT THROUGH proxyuser WITH ROLES proxyrole;<br>
ALTER USER user2 GRANT CONNECT THROUGH proxyuser WITH ROLES proxyrole;<br>
ALTER USER user3 GRANT CONNECT THROUGH proxyuser WITH ROLES proxyrole;</b>
</blockquote>
<p>Note that you cannot do this with the SYSTEM user. Ie. you cannot run:</p>
<blockquote>
<b>ALTER USER SYSTEM GRANT CONNECT THROUGH proxyuser WITH ROLES proxyrole;</b><br>
</blockquote>
<p>If you need to access the database through SQL Relay as the SYSTEM user, you
must set up an instance of SQL Relay which logs in as the SYSTEM user and use
that instance for accessing the database.</p>
<p>If auditing is enabled in your database, you can enable auditing of the
queries that the users have run through the proxyrole as follows:</p>
<blockquote><b>AUDIT SELECT ANY TABLE BY proxyuser ON BEHALF OF user1;<br>
AUDIT SELECT ANY TABLE BY proxyuser ON BEHALF OF user2;<br>
AUDIT SELECT ANY TABLE BY proxyuser ON BEHALF OF user3;</b></blockquote>
<p>Queries will show up in the audit table as having been run by user1, user2
or user3, rather than as the proxyuser.</p>
<span class="heading2">Setting Up SQL Relay</span>
<p><b>SQL Relay</b> should be set up to use the database authentication tier
and to log into Oracle as the proxy user. Below is an sqlrelay.conf file
that does this. Note the authtier attribute of the instance tag. Note also
that there are no users defined as they are unnecessary for this kind of
configuration.</p>
<blockquote>
<PRE>
<FONT color=#0000f8><?</FONT><B><FONT color=#288850>xml</FONT></B><B><FONT color=#288850> </FONT></B><B><FONT color=#288850>version</FONT></B>=<FONT color=#f800f8>"1.0"</FONT><FONT color=#0000f8>?></FONT>
<FONT color=#008888><!</FONT><B><FONT color=#a02828>DOCTYPE</FONT></B> instances <B><FONT color=#a02828>SYSTEM</FONT></B> <FONT color=#f800f8>"sqlrelay.dtd"</FONT><FONT color=#008888>></FONT>
<FONT color=#008888><instances></FONT>
<FONT color=#008888><instance </FONT><B><FONT color=#288850>id</FONT></B>=<FONT color=#f800f8>"proxyuser"</FONT><FONT color=#008888> </FONT><B><FONT color=#288850>port</FONT></B>=<FONT color=#f800f8>"9000"</FONT><FONT color=#008888> </FONT><B><FONT color=#288850>socket</FONT></B>=<FONT color=#f800f8>"/tmp/proxyuser.socket"</FONT><FONT color=#008888> </FONT><B><FONT color=#288850>dbase</FONT></B>=<FONT color=#f800f8>"oracle8"</FONT><FONT color=#008888> </FONT><B><FONT color=#288850>connections</FONT></B>=<FONT color=#f800f8>"1"</FONT><FONT color=#008888> </FONT><B><FONT color=#288850>maxconnections</FONT></B>=<FONT color=#f800f8>"3"</FONT><FONT color=#008888> </FONT><B><FONT color=#288850>maxqueuelength</FONT></B>=<FONT color=#f800f8>"0"</FONT><FONT color=#008888> </FONT><B><FONT color=#288850>growby</FONT></B>=<FONT color=#f800f8>"1"</FONT><FONT color=#008888> </FONT><B><FONT color=#288850>ttl</FONT></B>=<FONT color=#f800f8>"60"</FONT><FONT color=#008888> </FONT><B><FONT color=#288850>endofsession</FONT></B>=<FONT color=#f800f8>"commit"</FONT><FONT color=#008888> </FONT><B><FONT color=#288850>sessiontimeout</FONT></B>=<FONT color=#f800f8>"600"</FONT><FONT color=#008888> </FONT><B><FONT color=#288850>runasuser</FONT></B>=<FONT color=#f800f8>"nobody"</FONT><FONT color=#008888> </FONT><B><FONT color=#288850>runasgroup</FONT></B>=<FONT color=#f800f8>"nobody"</FONT><FONT color=#008888> </FONT><B><FONT color=#288850>cursors</FONT></B>=<FONT color=#f800f8>"5"</FONT><FONT color=#008888> </FONT><B><FONT color=#288850>authtier</FONT></B>=<FONT color=#f800f8>"database"</FONT><FONT color=#008888>></FONT>
<FONT color=#008888><users></FONT>
<FONT color=#008888></users></FONT>
<FONT color=#008888><connections></FONT>
<FONT color=#008888><</FONT><FONT color=#008888>connection </FONT><B><FONT color=#288850>connectionid</FONT></B>=<FONT color=#f800f8>"proxyuser"</FONT><FONT color=#008888> </FONT><B><FONT color=#288850>string</FONT></B>=<FONT color=#f800f8>"user=proxyuser;password=proxyuser;oracle_sid=ora1;"</FONT><FONT color=#008888> </FONT><B><FONT color=#288850>metric</FONT></B>=<FONT color=#f800f8>"1"</FONT><FONT color=#008888>/></FONT>
<FONT color=#008888></connections></FONT>
<FONT color=#008888></instance></FONT>
<FONT color=#008888></instances></FONT>
</PRE>
</blockquote>
<span class="heading2">Running SQL Relay</span>
<p>Now that Oracle and <b>SQL Relay</b> are configured, you can run
<b>SQL Relay</b> as follows:</p>
<blockquote>sqlr-start -id proxyuser</blockquote>
<p>You can use sqlrsh to access it as any of the database level users that
you created earlier:</p>
<blockquote>sqlrsh localhost 9000 "/tmp/proxyuser.socket" user1 user1<br>
or<br>
sqlrsh localhost 9000 "/tmp/proxyuser.socket" user2 user2<br>
or<br>
sqlrsh localhost 9000 "/tmp/proxyuser.socket" user3 user3</blockquote>
</body>
</html>
|
