Wednesday, September 7, 2011

Sybase user ‘alias’

In the last post I stated that a Sybase ‘login’ is mapped to a ‘database user’. In this post, I’ll elaborate on it further and introduce the concept of an ‘alias’.

One ‘login’ can be mapped to only one ‘database user’ in the given database. Obviously, at the server-level one ‘login’ can, and generally is mapped to many database users in different databases. For example, the ‘sa’ login is mapped to ‘dbo’ users in all databases. But how about the relationship in the other direction i.e. from ‘database user’ to a ‘login’?

Each ‘database user’ is also associated with one ‘login’ – except when there exists an ‘alias’. It is possible for two or more logins to be mapped to the same ‘database user’ by creating an ‘alias’. This allows both the logins to act as the same ‘database user’. This is typically done to allow multiple logins to act as database owners (i.e. user ‘dbo’). For example, as I mentioned earlier, ‘sa’ is always mapped to the ‘dbo’ user. If you wish to make someone else, lets say, ‘president’ login act as the ‘dbo’ database user, you create an alias

sp_addalias ‘president’, ‘dbo’

This would make both ‘sa’ and ‘president’ act as ‘dbo’. Note that you can have many aliases for a given ‘login’. You could very well also alias ‘vice_president’ to ‘sa’

sp_addalias ‘vice_president’, ‘dbo’

Even though multiple logins now act as the same ‘database user’, their activity can still be audited individually. This would not be possible if you simply gave the password for ‘sa’ login to everyone who you wish to make ‘dbo’, for example.

No comments:

Post a Comment