| Hi Peter,
This is the expected behavior. I would recommend this customer (and everyone
else, for that matter), read section 2 of the 7.0 server configuration guide,
but specifically, sections 2.7, 2.8, 2.9, and 2.12, which describe security
aspects of the new server.
As for this particular situation, for services defined with database
authorization connect user:
- An executor for a universal service (on VMS) sets the operating system
process user name (plus all the other attributes) when a client connects.
This provides the correct default for the Rdb system and session user names
whenever the client application issues an ATTACH statement.
- An executor for a database service (on VMS) sets the operating system
process user name (plus all the other attributes) to the service owner
user name when the process first starts. This provides the correct
default for the Rdb system user name when the service ATTACH statement
is issued. Whenever a client connects to the service, a new SQL connect
is created with a session user name from the client; however, the
operating system process user name remains unchanged, as this is the
user name under which the original attach was made. In particular, this
has a direct impact on the execution of external functions, which are
described in section 2.9.
For folks on the net, the SQL/Services (plus Rdb and SQL) V7.0 manuals can
be obtained from WEORG::RDBDOC_V70:
Hope this helps,
Si
|
| > -< Expected behavior >-
As I expected :-)
>This is the expected behavior. I would recommend this customer (and everyone
>else, for that matter), read section 2 of the 7.0 server configuration guide,
>but specifically, sections 2.7, 2.8, 2.9, and 2.12, which describe security
>aspects of the new server.
I have just looked though those chapters. They are not clear to me
about this point.
It only mentions the rights list in connection with the process user
name, but wouldn't it be overridden if a username were specified on
the ATTACH?
Peter
|
| Hi Peter, plus everyone else who's interested in this,
Well, it turns out that question has uncovered a nice, though I suppose I
should say nasty, little can of worms.
The simple answer to the question is that, no, the process identifiers should
not change when a new client connects to a database service with database
authorization by connect user. The reason is that in this situation, we use SQL
CONNECTs and let the Rdb exec do the work of figuring out who can and can't
access the database. However, the fact that what's happening is expected
behavior (at least from my perspective) doesn't mean to say that its the
intended behavior... Which indeed it turns out it isn't: on VMS, Rdb should be
using the UIC and rights identifiers held by the session user name, but is in
fact using only the UIC of the session user name, plus the rights identifiers
of the current process.
First some background. SQL/Services only does user name impersonation/process
reconfiguration, to set up the correct enviroment for an ATTACH; whether the
ATTACH comes from a client application in the case of a universal service, or
is an attribute of the service definition in the case of a database service.
Once attached to a database, SQL/Services never changes the process profile by
changing such things as user name, privs, UIC, rights identifiers, etc. To do
so could conceivably render the attach unusable; for example, the new process
profile might not have write access to the .RUJ directory.
In the case of database service with database authorization by connect user,
SQL/Services creates a new SQL CONNECT whenever a new client connects to the
service. As it happens, you can very closely simulate what SQL/Services does
using interactive SQL; you can certainly reproduce this particular problem.
Consider the following senario, in which FRED is allowed to attach to the
ORDERS database, but isn't allowed to do much else, while JOE is allowed to
enter new orders:
Account Identifiers
------- ------------------------------
ORDERS ORDERS (UIC), ORDER_DB
FRED FRED (UIC), ORDER_DB
JOE JOE (UIC), ORDER_DB, NEW_ORDER
(Login as ORDERS)
SQL> CREATE DATABASE FILENAME ORDERS;
SQL> GRANT SELECT ON DATABASE ALIAS RDB$DBHANDLE TO ORDER_DB; ! Allow attaches
SQL> CREATE TABLE ORDERS_TABLE( ... );
SQL> GRANT ALL ON TABLE ORDERS_TABLE TP NEW_ORDER; ! Allow orders to be entered
(Login as FRED)
SQL> ATTACH 'ALIAS A FILENAME ORDERS';
SQL>
SQL> SELECT * FROM A.ORDERS_TABLE;
! Get correct error; FRED isn't allowed to access ORDERS_TABLE:
%RDB-E-NO_PRIV, privilege denied by database facility
SQL>
SQL> CONNECT TO 'ALIAS A' AS 'CONNECT1' USER 'JOE' USING 'JOES_PSWD';
SQL> SELECT * FROM A.ORDERS_TABLE;
! Get wrong error, JOE should be able to access ORDERS_TABLE:
%RDB-E-NO_PRIV, privilege denied by database facility
(Login as JOE)
SQL> ATTACH 'ALIAS A FILENAME ORDERS';
SQL>
SQL> SELECT * FROM A.ORDERS_TABLE;
! Get correct response, ie data in ORDERS_TABLE:
<<<<result set>>>>
SQL>
SQL> CONNECT TO 'ALIAS A' AS 'CONNECT1' USER 'FRED' USING 'FREDS_PSWD';
SQL> SELECT * FROM A.ORDERS_TABLE;
! Get correct or incorrect response, ie data in ORDERS_TABLE, based on
! one's point of view...
<<<<result set>>>>
There are a couple of differences in how this works inside SQL/Services, but
the end result is the same. (SQL/Services CONNECTs share the same database
attach, whereas each CONNECT using dynamic SQL results in a new attach. In
addition, SQL/Services doesn't have to use a password on the CONNECT statement
on VMS or UNIX. Both of these are because SQL/Services is in cahoots with both
SQL and the Rdb exec.)
The reason I said 'correct or incorrect response' above is that merging the
privs and rights of the underlying process with the rights of the session user
name can provide some quite powerful results. For example, in the SQL/Services
case:
- If you use an account with no elevated privs, and with a UIC and rights list
(or no additional rights), that allows only attach access, then, in theory,
bugs in Rdb excepted, access to the database will be based solely on the UIC
and rights identifiers held by the session user name.
- However, if one grants additional rights to the service owner account, then
one can impose further controls on what SQL/Services clients can do. For
example, one could grant a read-only identifier to the service owner account
and prevent anyone accessing the database with that identifier from updating
any tables. This means that someone using, say MS Access can't muck around
with the database. However, accessing the database via some other more
controlled means wouldn't impose the same restriction.
FYI - the reason why this behavior was expected from my perspective is that
this is how its always worked, ever since it was first implemented in Rdb 6.1.
Presumably the reason it wasn't spotted in V6.1 is that no one has used it
outside of the new version of SQL/Services which only ran on Unix, and Unix
doesn't have identifiers. However, having talked it over with a couple of exec
folks, it turns out what's there on VMS wasn't really what they intended.
So, in conclusion, consider it [to be] QAR'ed to Rdb 6.1 and I'll post a note
when I get some feedback on when and in which version(s) it'll be fixed.
Si
ps, if anyone has any comments on the question of whether or not to merge the
process rights with the session user's rights, then please let us know.
|
| >intended behavior... Which indeed it turns out it isn't: on VMS, Rdb should be
>using the UIC and rights identifiers held by the session user name, but is in
>fact using only the UIC of the session user name, plus the rights identifiers
>of the current process.
Is it really using the rights identifier's of the operating system's
process username or those of the system username? E.g. if SQL Services
on UNIX had a service that attached to a remote database on a VMS
system, specifying a username and password, would the
non-existant rights list of the UNIX process username or those of the
system username on the VMS system be used? In the reverse case, how
could a VMS process username rights list be honoured on a database on a
UNIX system? The same applies to process privileges. The manual is
clear that the operating systems username privileges should be used,
but doesn't explain how that would happen with the database on a
different operating system.
>The reason I said 'correct or incorrect response' above is that merging the
>privs and rights of the underlying process with the rights of the session user
>name can provide some quite powerful results. For example, in the SQL/Services
>case:
Is the 'underlying process' the executor process (as the manual says)
or the process accessing the database (which will be different when
using remote access).
>ps, if anyone has any comments on the question of whether or not to merge the
>process rights with the session user's rights, then please let us know.
Merging seems a good idea, but I suspect it is the system username's
rights that should be used.
Sorry if this seems to be nitpicking, but customers who worry about
security do not tolerate uncertainty.
Peter
|
| >> So the actual location of the database is not transparent to SQL
>> Services. That raises some other questions.
It depends what you mean by transparent to SQL/Services. All ATTACH statements
are [almost] completely transparent to the SQL/Services. [Almost, because we do
parse it sufficiently to see if there's an alias for a database service,
because we need to know that for the SQL CONNECT if its not RDB$DBHANDLE.]
However, even if the ATTACH works, trying to use the attach may fail at
run-time. Eg, if a client tries to connect to a database service with database
authorization by connect user, and the database is remote, then the SQL connect
will fail, resulting in the failure of the SQL/Services connect.
>> What if the database is not actually a Rdb database, but something
>> accessed via one of the Rdb Transparent Gateways?
As of today, only Rdb 6.1 and 7.0 support database authorization by connect
user, and then only for a local attach. I'll see about updating that
restriction to include that. (When Rdb moved to Oracle, but DBI and the Rdb
Transparent Gateways stayed behind, all references to them were removed from
out books, and they never got put back...)
>> What if it is a distributed database (using the Distributed Option for
>> Rdb)?
Sorry, but what is the Distributed Option for Rdb??
Si
|