User's Guide
PART 1. Working with Databases
CHAPTER 2. Connecting to a Database
The integrated login feature allows you to maintain a single user ID and password for both database connections and operating system and/or network logins. This section describes the integrated login feature.
Integrated login capabilities are available for the Windows NT server only. It is possible for Windows 95 clients as well as Windows NT clients to use integrated logins to connect to a network server running on Windows NT.
An integrated login is a mapping from one or more Windows NT user profiles to an existing user in a database. A user who has successfully navigated the security for that user profile and logged in to their machine can connect to a database without providing an additional user ID or password.
To accomplish this, the database must be enabled to use integrated logins and a mapping must have been granted between the user profile used to log in to the machine and/or network, and a database user.
Using an integrated login is more convenient for the user and permits a single security system for database and network security. Its advantages include:
When connecting to a database using an integrated login, the user does not need to enter a user ID or password.
If you use an integrated login, the user authentication is done by the operating system, not the database: a single system is used for database security and machine or network security.
Multiple user profiles can be mapped to a single database user ID.
The name and password used to login to the Windows NT machine do not have to match the database user ID and password.
Caution
Integrated logins offer the convenience of a single security system but there are important security implications which database administrators should be familiar with.
For more information about security and integrated logins, see Security concerns: unrestricted database access.
Using integrated loginsSeveral steps must be implemented in order to connect successfully via an integrated login.
To use an integrated login:Enable the integrated login feature in a database by setting the value of the LOGIN_MODE database option to either Mixed or Integrated (the option is case insensitive), in place of the default value of Standard. This step requires DBA authority).
Create an integrated login mapping between a user profile and an existing database user. This can be done using a SQL statement or a wizard in Sybase Central.
Connect from a client application in such a way that the integrated login facility is triggered.
Each of these steps is described in the sections below.
Enabling the integrated login featureThe LOGIN_MODE database option determines whether the integrated login feature is enabled. As database options apply only to the database in which they are found, different databases can have a different integrated login setting even if they are loaded and running within the same server.
The LOGIN_MODE database option accepts one of following three values (which are case insensitive).
Standard This is the default setting, which does not permit integrated logins. An error occurs if an integrated login connection is attempted.
Mixed With this setting, both integrated logins and standard logins are allowed.
Integrated With this setting, all logins to the database must be made using integrated logins.
Caution
Setting the LOGIN_MODE database option to Integrated restricts connections to only those users who have been granted an integrated login mapping. Attempting to connect using a user ID and password generates an error. The only exception to this are users with DBA authority (full administrative rights).
The following SQL statement sets the value of the LOGIN_MODE database option to Mixed, allowing both standard and integrated login connections:
SET OPTION Public.LOGIN_MODE = Mixed
Creating an integrated loginUser profiles can only be mapped to an existing database user ID. When that database user ID is removed from the database, all integrated login mappings based on that database user ID are automatically removed.
A user profile does not have to exist for it to be mapped to a database user ID. More than one user profile can be mapped to the same user ID.
Only users with DBA authority are able to create or remove an integrated login mapping.
An integrated login mapping is made either using a wizard in Sybase Central or a SQL statement.
To map an integrated login from Sybase Central:Connect to a database as a user with DBA authority.
Open the Integrated Logins folder for the database, and double-click Add Integrated Login. The Integrated Login wizard is displayed.
On the first page of the wizard, enter the name of the system (computer) user for whom the integrated login is to be created. You can either select a name from the list or enter a name.
Also, select the database user ID this user maps to. The wizard displays the available database users. You must select one of these. You cannot add a new database user ID.
Follow the remaining instructions in the Wizard.
To map an integrate login using a SQL statement:The following SQL statement allows Window NT users dmelanso and bhay to log in to the database as the user DBA, without having to know or provide the DBA user ID or password.
GRANT INTEGRATED LOGIN TO dmelanso, bhay AS USER dba
Connecting from a client applicationA client application can connect to a database using an integrated login in one of the following ways:
Set the INTEGRATED parameter in the list of connection parameters to yes.
Specify neither a user ID nor a password in the connection string or connection dialog. This method is available only for Embedded SQL applications, including the Adaptive Server Anywhere administration utilities.
If INTEGRATED=yes is specified in the connection string, an integrated login is attempted. If the connection attempt fails and the LOGIN_MODE database option is set to Mixed, the server attempts a standard login.
If an attempt to connect to a database is made without providing a user ID or password, an integrated login is attempted. The attempt succeeds or fails depending on whether the current user profile name matches an integrated login mapping in the database.
For example, a connection attempt using the following Interactive SQL statement will succeed, providing the user has logged on with a user profile name that matches a integrated login mapping in a default database of a server:
CONNECT USING 'INTEGRATED=yes'
The following Interactive SQL statement...
CONNECT
...can connect to a database if all the following are true:
A server is currently running.
The default database on the current server is enabled to accept integrated login connections.
An integrated login mapping has been created that matches the current user's user profile name.
If the user is prompted with a dialog box by the server for more connection information (such as occurs when using the Interactive SQL utility), the user clicks OK without providing more information.
A client application connecting to a database via ODBC can use an integrated login by including the Integrated parameter among other attributes in its Data Source configuration.
Setting the attribute Integrated=yes in an ODBC data source causes database connection attempts using that DSN to attempt an integrated login. If the LOGIN_MODE database option is set to Standard, the ODBC driver prompts the user for a database user ID and password.
Security concerns: unrestricted database accessThe integrated login feature works by using the login control system of Windows NT in place of the Adaptive Server Anywhere security system. Essentially, the user passes through the database security if they can log in to the machine hosting the database, and if other conditions, outlined in Using an integrated login, are met.
If the user successfully logs in to the Windows NT server as "dsmith", they can connect to the database without further proof of identification provided there is either an integrated login mapping or a default integrated login user ID.
When using integrated logins, database administrators should give special consideration to the way Windows NT enforces login security in order to prevent unwanted access to the database.
In particular, be aware that by default a "Guest" user profile is created and enabled when Windows NT Workstation or Server is installed.
Caution
Leaving the user profile Guest enabled can permit unrestricted access to a database being hosted by that server.
If the Guest user profile is enabled and has a blank password, any attempt to log in to the server will be successful. It is not required that a user profile exist on the server, or that the login ID provided have domain login permissions. Literally any user can log in to the server using any login ID and any password: they are logged in by default to the Guest user profile.
This has important implications for connecting to a database with the integrated login feature enabled.
Consider the following scenario, which assumes the Windows NT server hosting a database has a "Guest" user profile that is enabled with a blank password.
An integrated login mapping exists between the user dsmith and the database user ID DBA. When the user dsmith connects to the server with her correct login ID and password, she connects to the database as DBA, a user with full administrative rights.
But anyone else attempting to connect to the server as "dsmith" will successfully log in to the server regardless of the password they provide because Windows NT will default that connection attempt to the "Guest" user profile. Having successfully logged in to the server using the "dsmith" login ID, the unauthorized user successfully connects to the database as DBA using the integrated login mapping.
Disable the Guest user profile for security |
Setting temporary public options for added securitySetting the value of the LOGIN_MODE option for a given database to Mixed or Integrated using the following SQL statement permanently enables integrated logins for that database.
SET OPTION Public.LOGIN_MODE = Mixed
If the database is shut down and restarted, the option value remains the same and integrated logins are still enabled.
Changing the LOGIN_MODE option temporarily will still allow user access via integrated logins. The following statement will change the option value temporarily:
SET TEMPORARY OPTION Public.LOGIN_MODE = Mixed
If the permanent option value is Standard, the database will revert to that value when it is shut down.
Setting temporary public options can be considered an additional security measure for database access since enabling integrated logins means that the database is relying on the security of the operating system on which it is running. If the database is shut down and copied to another machine (such as a user's machine) access to the database reverts to the Adaptive Server Anywhere security model and not the security model of the operating system of the machine where the database has been copied.
For more information on using the SET OPTION statement see SET OPTION statement.
Network aspects of integrated loginsIf the database is located on a network server, then one of two conditions must be met for integrated logins to be used:
The user profile used for the integrated login connection attempt must exist on both the local machine and the server. As well as having identical user profile names on both machines, the passwords for both user profiles must also be identical.
For example, when the user jsmith attempts to connect using an integrated login to a database loaded on network server, identical user profile names and passwords must exist on both the local machine and application server hosting the database. jsmith must be permitted to login to both the local machine and the server hosting the network server.
If network access is controlled by a Microsoft Domain, the user attempting an integrated login must have domain permissions with the Domain Controller server and be logged in to the network. A user profile on the network server matching the user profile on the local machine is not required.
Creating a default integrated login userA default integrated login user ID can be created so that connecting via an integrated login will be successful even if no integrated login mapping exists for the user profile currently in use.
For example, if no integrated login mapping exists for the user profile name JSMITH, an integrated login connection attempt will normally fail when JSMITH is the user profile in use.
However, if you create a user ID named Guest in a database, an integrated login will successfully map to the Guest user ID if no integrated login mapping explicitly identifies the user profile JSMITH.
The default integrated login user permits anyone attempting an integrated login to successfully connect to a database if the database contains a user ID named Guest. The permissions and authorities granted to the newly-connected user are determined by the authorities granted to the Guest user ID.