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.

Tuesday, September 6, 2011

Sybase ‘guest’

A little background on Sybase first. Each instance of Sybase server typically contains many databases. TCP/IP is usually enabled and 5000 is the default port, but this can obviously be changed. One logons to the Sybase instance and thereby gets potential access to all databases residing at the instance. Some of the databases like ‘master’, ‘tempdb’, ‘model’ etc get created at install time by the Sybase installer and are required for Sybase to run properly.

Logins - A ’login’ gives you access to the instance and is a server-level concept. You can add a ‘login’ using
sp_addlogin ‘login_name’, ‘password’

This adds an entry to the ‘master.dbo.syslogins’ table. Although a ‘login’ gives you access to the instance, to get access to a particular database, it must be mapped to an existing ‘database user’.

Database Users - Each database maintains its own set of database users. When creating a ‘database user’, the associated ‘login’ has to be supplied – like this
sp_adduser ‘login_name’, ‘database_user’

This adds an entry to the ‘<database_name>.dbo.sysusers’ table. Here the ‘login_name’ is mapped to ‘database_user’. Now, one thing to note is that one may add a ‘login’ but never map it to any particular ‘database user’ – what happens then is a little tricky and depends on the existence of a special user called ‘guest’.

Special ‘guest’ - If an explicit mapping does not exist between the ‘login’ and any of the existing database users, then the ‘login’ is implicitly mapped to the special ‘guest’ user, if one exists. If the 'guest’ user does not exist, the ‘login’ is denied access to the database. One can easily add the ‘guest’ user if it does not exist by using
sp_adduser guest

When creating new database (using ‘create database’ for example), the ‘model’ database gets used as a template. In the versions of Sybase (12.5, 15, 15.5 on Linux/Windows) I played with I did not see the user ‘guest’ defined in the ‘model’ database. So the new databases based on ‘model’ will also not have the ‘guest’ user by default.

Monday, September 5, 2011

MacPorts

I was looking for an easy way to upgrade an outdated version 2.6.9 of rsync on my OS X Lion MacBook. I'm not too good with UNIX to begin with and finding required dependencies etc on my OS X Lion would be a time consuming task. So I was looking around for any ready-to-use installers or binaries of the latest 3.0.8, when I stumbled upon this neat project called MacPorts. It provides an easy to use system for getting binaries for such open-source tools on the Mac OS X systems. Installation was simple - this is from the MacPorts website

- “dmg” disk images for Lion and Snow Leopard. These contain pkg installers for use with the Mac OS X Installer. This is the simplest installation procedure that most users should follow after meeting the requirements listed below. An installer forLeopard, as a legacy platform, is also available.

Before installing enable the root user and install Xcode from the AppStore (on Lion) or Apple dev connection website (Snow Leopard/Leopard). You would need X11 as well for ports that depend on it. Use the package installer and then simply run this to update to the latest of MacPorts

sudo port -v selfupdate

Now, if you know the program you want to install is supported by MacPorts, like rsync for example, all you do to install it is

sudo port install rsync

 

Sunday, September 4, 2011

Enable root user on OS X Lion

1. Launch the Directory Utility. It is located in /System/Library/CoreServices.
2. Click the padlock icon on the bottom left corner and enter your Administrator login credentials to unlock the application
3. In the Edit menu, select Enable Root User
Again from the Edit menu, select Change Root Password. A dialog window will apear prompting you to enter a root password