Conference orarep::nomahs::sql_services

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

>    Is it really using the rights identifier's of the operating system's
>    process username or those of the system username? 

Operating system process (username) on VMS, Rdb system user name on Unix. The
following is from section 2.12.1, with additions to account for remote access
and to clarify its use on Unix (changes indicated by |):

   To understand how Oracle SQL/Services implements database
   authorization by connect user name and by service owner, it
   is first necessary to understand the four user names that are
   involved in accessing an Oracle Rdb database in the Oracle
   SQL/Services environment. Following is an explanation of the
   four user names:

    �   The operating system process user name

{Changed:-}

|	 The process user name is the user name under which an
|        Oracle SQL/Services executor process runs in the case of
|        a local attach or the user name of the Rdb remote server 
|        process in the case of a remote attach.

 <OpenVMS>

{Changed:-}

|        On OpenVMS, the process user name is set based on the
|        SERVICE OWNER service attribute for local attaches, whereas
|        it is based on the ATTACH statement and the configuration of
|        the remote Rdb server node for remote attaches. Associated with 
|        the process user name are a number of process attributes,
|        including:

         -  UIC
         -  Privileges
         -  Rights list
         -  Account name
         -  Default directory
         -  Logical names, including
                    SYS$DISK
                    SYS$LOGIN_DEVICE
                    SYS$LOGIN
                    SYS$SCRATCH
                    LNM$GROUP (for group logical name table)    <end>

 <Unix>

        On Digital UNIX, the process user name is always    'db-
        smgr'. This is necessary for Oracle Rdb to authorize
        Oracle SQL/Services executors access to Oracle Rdb
        databases. No other attributes of the process are relevant
        to this discussion.  <end>

[Note that the above is always true for Unix]

   �   The Oracle Rdb system user name

        Each attached database in an executor process has a
        value for the system user name. The Oracle Rdb system
        user name is used to determine if the process is autho-
        rized to attach to the database and also serves as the
        default value for the Oracle Rdb session user name.

{Added:-}

| <Unix>
|
|	On Digital Unix, the Rdb system user name has an additional
|	role of specifying the conceptual equivalent of an operating
|	system privilege mask. The signifance of the Oracle Rdb system 
|	user name On Digital Unix is described below.	    <end>

[That's what the Rdb system user name is used for: 
- to determine if the process is authorized to attach to the database, and
- to provide a default for the Rdb session user name
Plus on Unix:
- to provide the conceptual equivalent of an operating system privilege mask

However, note that the same bug exists for the attach authorization check for
the system user name as for the request execution authorization check for the
session user name. In other words, it uses only the system user name's UIC,
not the rights list, when doing the attach authorization.]

...a bit later on...

{Changed:-}

|   Whenever a database request is started, Oracle Rdb must determine if the 
|   process issuing the request is authorized to execute the request. To 
|   perform this check, Oracle Rdb first merges the system privileges of
|   the process accessing the database with the current user name's database 
|   privileges. For a local attach, the process accessing the database is
|   the SQL/Services executor process. For a remote attach, the process
|   accessing the database is the Rdb server process.

 <OpenVMS>

   On OpenVMS, the operating system's process privilege mask
   is used as the executor process' system privileges. <end>

[Given the current bug, this should read, but will be release-noted instead:

   On OpenVMS, the operating system's process privilege mask
   and current rights identifiers are used as the system privileges.]

 <Digital Unix>

   There is no concept of a process privilege mask on the Digital
   UNIX operating system. If the Oracle Rdb system user
   name is 'dbsmgr', then Oracle Rdb considers an executor
   process to have full system privileges and full access to all
   databases. If the Oracle Rdb system user name is anything
   other than  'dbsmgr', then an executor process is considered
   to have no system privileges.  <end>

>    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.

Given these alterations to the docs to account for remote access and to better
account for the Rdb system user name on Unix, does the preceeding answer
the above anwer question? 

>    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).

Process accessing the database; see changes for details.
    
>    Merging seems a good idea, but I suspect it is the system username's
>    rights that should be used.

Unfortunately, that's just not the way Rdb works on VMS - its still VMS-centric
and performs authorization checks in the context of the VMS process
environment. Unix doesn't have privs, hence that bit about special-casing
certain values of the Rdb system user name. Other than that, Rdb doesn't use
the system username other than to authorize attach access to the database and
to provide a default value for the session user name. As to what it should do,
I've no idea - best to direct that question to the SQL standard expects and the
Rdb exec folks.

Just to clarify one point that I suspect makes all this a lot less of an issue:
SQL/Services doesn't support remote access using a database service with
database authorization by connect user (At least not in SQL/Services 7.0 using
Rdb 6.1 or 7.0; there's a chance this may change in 8.0.) This is described in
section 2.8.1:

   �   Provide database services if:

         -  Your system has a number of frequently accessed
              databases that can be managed easily by using
              database services.

         -  You want to provide fast client connection times using
              prestarted executor processes that are preattached to
              the appropriate database.

         -  You have one or more databases with suitable trans-
              action workload characteristics that can take ad-
              vantage of the performance gains made possible with
              transaction reusable database services.

        ------------------------- Note ------------------------
        You cannot use database services with database ac-
        cess authorization set to the connect user name
        to provide access to remote Oracle Rdb databases.
        However, Oracle SQL/Services does allow you to pro-
        vide a database service for a remote Oracle Rdb
        database if you create the service with database access
        authorization set to the service owner.
        -------------------------------------------------------

In other words, if you go remote, then SQL/Services is effectively out of the
picture and what you get is what you would get if you were to access the
database from any other SQL client application.

Hope this helps,

Si

>Given these alterations to the docs to account for remote access and to better
>account for the Rdb system user name on Unix, does the preceeding answer
>the above anwer question? 
    
    Yes. Thanks.
    
>to provide a default value for the session user name. As to what it should do,
>I've no idea - best to direct that question to the SQL standard expects and the
>Rdb exec folks.
    
    It does seem rather pointless :-), but knowing what it actually does is
    the important bit.
    
>Just to clarify one point that I suspect makes all this a lot less of an issue:
>SQL/Services doesn't support remote access using a database service with
>database authorization by connect user (At least not in SQL/Services 7.0 using
>Rdb 6.1 or 7.0; there's a chance this may change in 8.0.) This is described in
>section 2.8.1:
    
    So the actual location of the database is not transparent to SQL
    Services. That raises some other questions.
    
    What if the database is not actually a Rdb database, but something
    accessed via one of the Rdb Transparent Gateways?
    
    What if it is a distributed database (using the Distributed Option for
    Rdb)?
    
    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

>>>  Sorry, but what is the Distributed Option for Rdb??
    
    Distributed Option is the Oracle name for DBI.
    
    /Regina