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.

--you need to be in this particular db to play with users
USE MyDb
GO

--raw output from table
SELECT * FROM sysusers

--To  have the names of the logins join the logins table
SELECT  l.name AS LoginName, u.name AS UserName,
        u.suid, u.uid, u.gid, u.environ, u.user_status
FROM sysusers u
JOIN master.dbo.syslogins l ON u.suid = l.suid

What usually happens if the login does not have a user is , that the login is not allowed to work with this specific database. There are some exceptions:

• The login can be a member of a sysadmin database role. Sysadmin is allowed to use any database independently if the login gas a user or not. We say that sysadmin Is mapped to database owner in every database.

USE master
GO

--who is member of which role? -  raw output
SELECT * FROM syssrvroles
GO

--login names and role names added
SELECT 
    l.name AS LoginName, r.name AS RoleName, lr.*
FROM sysloginroles lr
JOIN syslogins l ON lr.suid = l.suid
JOIN syssrvroles r ON lr.srid = r.srid
GO

• Login without user can access the database using the guest account. For this to work the guest account needs to be unlocked. One of the security best practices is that the guest account should be disabled.

sp_adduser guest

A login can also access all the databases where the login is a database owner. Each object in Sybase needs to have an owner. This is valid for a database itself. So for each database one specific user needs to be the owner of the database. As the owner, the database is accessible for him. Usually we set sa as the owner, so we can skip this one situation, when analyzing databases’ security.

sp_helpdb

The next way of accessing a database  is to create an alias user or proxy for a login.