Conference orarep::nomahs::sql_services

When an SQL/Services executor is started, it is given the UIC and privs of the
STARTUP user name. (This username must be configured to start the executor
correctly; typically, a SQLSRV$SRVnn account is used, since they all have the
correct LGICMD specified, etc. If you don't use one of these accounts, you must
ensure the account you do use is configured in a  simmilar way to an
SQLSRV$SRVnn account. If you don't, the executor won't start.) The STARTUP user
name is also used to determine the quotas of the executor process.

However, and this is the catch, VMS does NOT provide any method to specify a
user name when creating a new process, nor does it provide any way to specify
rights list information. Therefore, the user name of an executor process will
be that of the comm server (<= V6.1) or monitor (>= V7.0), while its rights
list will be empty as the comm server/monitor removes all rights when they
start up. This is not normally an issue as the startup user name is used only
to start the executor process by invoking RDB$SETVER, then running the executor
process image. (It can be an issue if the executor tries to access an
ACL-protected file during process startup, and there's not a lot you can do
about that right now.) Process logical names can be defined by editing the
LOGIN.COM of the STARTUP account (<= V6.1) or creating a process initialization
file (>= V7.0); however, there's not a lot else that can use be usefully
accomplished during the startup of an executor process. Note that any security
checks that are required during this stage are made using the UIC and rights
list of the process. (The user name is not actually used, unless a SPAWN or
SUBMIT command gets used, in which case the resulting process will have the
user name of the executor process.)

When the executor image itself for a database service first starts, it
reconfigures the executor process as the USER user name. It is the USER user
name that is used to access the database. As part of reconfiguring the process,
SQL/Services sets the process user name, account, UIC, privs, rights list,
default directory and so forth. 

Note that the IDENT in a service definition specifies a VMS identifier that
must be held by any client-specified user name wishing to connect to the
server. It is NOT granted to the executor process as a means by which it can
access the database. If that is required, the necessary identifier(s) must be
granted to the USER account, as that is the account used to access the
database.

In summary:

- Using <= V6.1, its best to specify a SQLSRV$SRV(nn) account as the STARTUP
  user name for all services. If you need a service with large quotas, then
  make sure the account you use is configured in a simmilar way to a
  SQLSRV$SRV(nn) account, that it can write to its default directory, that 
  its LGICMD is correct (a SQL/Services start up file), etc, etc.

- Make sure the LOGIN.COM of the STARTUP account doesn't do any fancy stuff;
  logical names are typically all that should be done here. Bear in mind
  that the process will be reconfigured once the executor image itself
  starts running. 

- Make sure the USER user name has the necessary privs and/or rights
  identifiers to attach to the specified database and to perform the 
  necessary data access operations. 

Hope this helps,

Si

ps, this is all much simpler in 7.0 (eg, no more SQLSRV$SRV(nn) accounts), but 
    it is different, so reading the 7.0 server guide when it comes time to 
    upgrade is a must.

Thanks for your quick response, Si.

>correctly; typically, a SQLSRV$SRVnn account is used, since they all have the
>correct LGICMD specified, etc. If you don't use one of these accounts, you must
>ensure the account you do use is configured in a  simmilar way to an
>SQLSRV$SRVnn account. If you don't, the executor won't start.) 

Will changing the STARTUP user name for this class to SQLSRV$SRV make 
this connection work correctly?  I am puzzled by the fact that the
security alarm is on a directory lookup request for [PDA]WRITE.DIR

>process image. (It can be an issue if the executor tries to access an
>ACL-protected file during process startup, and there's not a lot you can do
>about that right now.) 

Isn't this my problem?  Is it failing because it doesn't have the
necessary identifier to get to the directory in which it should
create the log file? ie [PDA.WRITE.IN]

I am using SQLSRV version 6.0-0

Carol.

    re .1
    
    To "specify" a username when creating a process on VMS you can
    first do a $persona_create followed by a $persona_assume followed
    by a $creprc before you return to your original personna.
    
    Regards Richard Maher.

Re: .-2

>Isn't this my problem?  Is it failing because it doesn't have the
>necessary identifier to get to the directory in which it should
>create the log file? ie [PDA.WRITE.IN]

Yep, now I've looked in more detail, the fact that the process doesn't have any
identifiers when it starts, may indeed mean that it won't be able to create its
log file. (I have to confess I always get very confused when I it comes to
mixing ACLs, UICs and rights lists.) 

The best way to think of the STARTUP user name for a database service in
SQL/Services <= V6.1 is that it supplies the quotas for the process (directly)
and specifies the version of Rdb the executor should use (indirectly, via the 
LGICMD attribute, which invokes a SYS$SYSTEM:SQLSRV$LOGIN_LOGIN(nn).COM
procedure, which in turn invokes SYS$LOGIN:LOGIN.COM, if it exists). In
contrast, the USER user name specifies the account (plus UIC, privs, rights
list and so on) which is used to attach and to access the database. Typically,
the STARTUP user name has no special privs and just gets the process going,
while the USER user name is responsible for doing most of the work.

Not knowing the details of your particular setup, I'm not sure exactly what to
do to fix it. If you don't need quotas that are different from SQLSRV$SRV(nn)
and don't need any special setup like logicals, then just modify the service to
use SQLSRV$SRV(nn) as the STARTUP user name. If you do need different quotas or
special setup like logicals, then:

- MODIFY pda_proxy/LGICMD=SYS$SYSTEM:SQLSRV$SERVER_LOGIN(nn).COM
- MODIFY pda_proxy/DEV=<a-disk>/DIR=[a-dir] ! to which pda_proxy can write
- REVOKE/ID PDA_USER pda_proxy ! not needed/doesn't do any good

Create a_disk:[a-dir]LOGIN.COM:
$ @DISK$APPL_04:[PDA.COM]PDA$USER_LOGIN.COM
$ ! assuming PDA_PROXY has execute access or @ some other command procedure 
$ ! which does the minimum necessary, in terms of defining logicals and
$ ! the like, so PDA_PROXY can find the database.

If you need PDA_PROXY for other uses, such as remote Rdb access, then create
a new account, eg PDA_SQS, which does the same sort of thing and leave
PDA_PROXY alone.

Hope that helps...

Re: .-1

While I was typing in .1, I was vaguely wondering if we could use the existing
SQL/Services process reconfiguration [impersonation] mechanism, currently used
only in executor's, to temporarily change the identity of the comm
server/monitor process while creating the executor process. We don't want to
use the new VMS impersonation code, because its not supported in our base
version of VMS, which is still V5.2-something. But neither do we need to,
because the SQL/Services code does all we need. I don't work on SQL/Services
any more, but I'll pass this on to Sue as a request.a

Si

>do to fix it. If you don't need quotas that are different from SQLSRV$SRV(nn)
>and don't need any special setup like logicals, then just modify the service to
>use SQLSRV$SRV(nn) as the STARTUP user name. 

OK, I tried this and got an error on SQLSRV$STARTUP (in sqlsrv$.log) saying

0318 16481556  %SQLSRV-F-GETACCINF, SQL Services authorization failed
0318 16481556  -SQLSRV-F-NO_MCHRHT, Account does not have rights access to class
0318 16481557  -SQLSRV-I-LOGINFAILINFO, Username: SQLSRV$SRV; client: 16.37.80.8
4

Is this because SQLSRV$SRV doesn't have access to the database specified
in the EXECUTE statement?

NB This may be unrelated, but I notice the address 16.37.80.84 mentioned in
the log file is not the address of this node (which is 16.37.80.83).

Carol.

    Ooops, I think I might have told a fib a little while back... 
    
    From memory, I thought it was only client-supplied accounts that needed
    the IDENT identifier (to authorize access to the service), but it looks
    like the USER user name account needs it too.
    
    Sorry about that.
    
    Si

    
>    From memory, I thought it was only client-supplied accounts that needed
>    the IDENT identifier (to authorize access to the service), but it looks
>    like the USER user name account needs it too.

I presume you mean the STARTUP user name here, as my USER user name 
already has the IDENT identifier.


The good news is that I have got it working.  Thanks very much for all
your help, Si.


For future reference, here's what I did:-

I don't want to grant the IDENT identifer to SQLSRV$SRV, partly for
security reasons, and partly in case it was lost next time SQL services
was upgraded, so I have copied SQLSRV$SRV to a new account (PDA_SQLSRV),
and created a top level home directory (with no ACL) and a login.com 
which defines the database logical (PDA$DATABASE).  I granted the 
IDENT identifer (PDA_USER) to the new account (PDA_SQLSRV).

I modified the STARTUP in the class definition use the new account:-

TYPE    "BEST"
STARTUP "PDA_SQLSRV"
EXECUTE "ATTACH 'FILENAME PDA$DATABASE'"
USER    "PDA_PROXY"
IDENT   "PDA_USER"
MIN     0
MAX     50
IDLE    1

and shut down SQL services, waited ~10 minutes and started it up (any
sooner gave me a DUPLNAM error).  The next connection worked fine.

 Thanks again,

Carol.