Granting access to database (user or guest)

It is not enough to create a login to have access to the database. Each login requires additionally a user in the database where the login should have access to.  You can compare it to entering a building and an office in the building. Login allows only to enter the building and some public accessible places (in Sybase those "public places" are master and tempdb databases). To enter an office (user database) you need additionally to have user account.

So to create the user in the database MyDb for a login named myAppLogin you just issue command like this:

USE MyDb
sp_adduser myAppLogin

Starting from now the login myAppLogin can start using the database. The user can be removed using the following code:

USE MyDb
sp_dropuser myAppLogin

What is very important - you cannot remove a user when it is "in use", so there is an active session in the database using this user, however when the session was not found, the user will be removed.

Creating new login. Locking and unlocking the login

To gain access to the Sybase, you need to have a valid login. To create a login for your application type:

sp_addlogin myAppLogin, 'Passw0rd', MyDb, null, 'Application ABC', null, 8, 3

The parameters of the commands are:

• name of the login
• the password
• the name of a default database (warning: if you create a login with the default database, the user in the database is not created automatically. This step needs to be done manually)
• default language
• the description of the account
• time (in days) after that the password will expire. Leave null to have a never expiring password
• number of chars that the password should have. This is important for future password changes that will be done by the login
• number of failed logins, after which the account will be locked

After the login has been created you can test in syslogins whether the information about a new login should be available.

Sybase security fundamentals: logins vs users

Sybase and MS SQL are like an older and younger sisters, so if you know how the access in organized on one of the platforms, you know immediately how it in general works in another database system.
So, starting from beginning:

• To access the Sybase’s server databases you don’t need direct access to operating system where the server is installed, nor the files, where the database’s files are stored. The reason for this is, that Sybase server just listens on requests from clients and then gets data from the system, however this is done by the Sybase processes, not directly by the client.
• To connect to Sybase, you need to have a valid login. This login can be a locally created login, so just user name with password, but this login can be taken from Kerberos, LDAP or PAM. Usually dba’s decide to use the locally defined logins, but using external directory of users have huge advantages.

USE master
GO
SELECT * FROM syslogins

• The login alone is not enough. To access a specific database you need to have a user account as well. The user account is created per database and you can think about this like about an association of a login name with a database. If the login has such association, than is allowed to access the database. We say in such case that the login has a user in the database.

Making external backups: QUIESCE DATABASE

One of more advanced backup options in Sybase is, that Sybase supports making database dumps using external tools. Generally the problem should not be very complicated. Sybase's databases are at the end of it's implementation just files on disk! However....:

• When you copy database files all of them need to be from exactly the same time, and it will be very complicated/impossible to copy hundrets of Gigabytes in the same moment
• All the transactions should be hold in the time when the external backup is run

That is why in Sybase there is a command allowing to freeze all the transactions, so that the files can be easily copied using the external tool. Sybase has found a way this conditions can be met - just all the transactions should be frozen for the time of external copy process. In the same time data can be read, however if you decide to initiate the transaction, that this transaction will hang until the freeze will be finished. Unfortunately since that time also all read operations will hang waiting for the freeze end. 

Take a look at this example:

QUIESCE DATABASE Maintenance1 HOLD MyDb
GO
USE MyDb
GO
SELECT * FROM TEST
GO
INSERT TEST VALUES('A new value')

Here administrator plans to make a copy of MyDb files. So he initiates quiesceing of that database. Such operation needs to have a name, that is here Maintenance1. After this operation INSERT will stop working. The first SELECT will be executed with no additional delay, but the INSERT will hang.

In the same time in a second session similar activities can be initiated:

USE MyDb
GO
SELECT * FROM TEST
GO

In this example, the SELECT will hang as well!. That is caused by a still not executed insert from the first session:

However, when the maintenance will be finished with a command like:

QUIESCE DATABASE Maintenance1 RELEASE
GO

All the other frozen INSERTs or SELECTs will be executed in the correct order:

Such copied files can be later taken to another server, put into correct location, and when data_server will be started with the -q option, than all the quiesced databases will be treated as "in recovery" allowing to:

• restore them
• or
• leave as "in restore" and with time apply additional log dumps, making the second server up-to-date.