Synchronizing ASE syslogins and sysusers after a database load

[Former Member]

Greetings,

This issue is as old as ASE itself. I have on multiple occasions dealt with it, and some employers had super secret scripts to perform the recociliation.

I have now been commissioned to resolve the problem once and for all at my current customer site.

Hence, racking my brain for previous experiences and solutions, brainstorming, asking for the feedback of colleagues in terms of how you have dealt with it.

Ideas, tips, suggestions, scripts? Any and all welcome!

Regards,

Jean-Pierre

[Former Member · Accepted solution]

here you go http://froebe.net/blog/2008/05/19/synchronize-a-sybase-ase-database-sysusers-with-mastersyslogins/

[jayrijnbergen · Accepted solution]

suid can be negative (valid values between -32768 and 2147483647)

your script only checks for suid > 1, should be:  between -32768 and -3 or > 1

With 15.7 and higher where you can specify suid manually with create login, it's getting more likely to have negative suid for logins.

[Former Member · Accepted solution]

Historically speaking negative suid numbers meant that there wasn't a matching master..syslogins record.  That being said, I'll update the script

jason

[Former Member · Accepted solution]

script updated

[former_member89972 · Accepted solution]

Excellent script Jason.

But I can see one area where just synchronizing logins + users using the script may fall short.

Groups + users + aliases that existed in the target database but were missing from source database have to be recreated.  So you have to provide some logic for this. 

Solution I suggested (using ddlgen + queries ) takes care of that.

Avinash

[Former Member · Accepted solution]

You are correct, I didn't include the aliases/roles code as the OP asked about syslogins and sysusers not roles/aliases.

I think both approaches are good.

If the database is restored into a similar system where all the logins exists (think production -> production) ddlgen will work fine.  If going to a dissimilar system (think production -> development) where only a few logins exist reconciling the suserid and userid using SQL may be preferred.

Use what works

[Former Member · Accepted solution]

Where do I find the update? On your site?

[Former Member · Accepted solution]

Jason,

Thank you!

What are the terms of use of procedure?

1. Personal use only?

2. Use at client sites as well?

Under what conditions?

1. Can we modify it? Like to quote the source? e.g. Jason Froebe, your site etc?

2. Or do you preffer that we use as is, report bugs and make feature requests

    to you so you can make the update and publish to the community at large?

3. Can we use it as the baseline for our own custom implementation?

Thank you Jason.

Jean-Pierre

Message was edited by: Jean-Pierre Dareys

[Former Member · Accepted solution]

Yes

[Former Member · Accepted solution]

Consider it public domain.  The procedure/method itself is so simple that anyone undertaking the task via SQL will come with it or something similar.  Attribution would be nice but isn't necessary.

[Former Member · Accepted solution]

Jason,

You are right...The task is rather common, and we all have at some point coded or worked with similar code.

Your procedure is the product of a great deal of experience and understanding of the problem at hand.

The result is a well thought out, beautifully simple, and therefore, easy to modify and maintain. Congratulations on a job well done.

I wil adopt it and of course, quote you as the author. If I alter it in any way, I will add my name to the list of people modifying the code, as has been done since the begining of time and as it should be.

Thank you and regards,

Jean-Pierre

P.S. I thank everyone else collaborating on the thread for their input, which is what I

        was looking for, given the fact that there are new issues to consider since the last time I

        looked at the problem.

Message was edited by: Jean-Pierre Dareys

[Former Member · Accepted solution]

Jason,

Upon further review of the code, and unless I am missing something, there could be an issue with the initialization of one of the variables in very large environments.

E.g.

declare @name char(30), @uid int, @suid int

select @suid = 9999

Later on in the body of the while loop processing the cursor, the variable is again reset to 9999. Presumably to wipe the previous "real" value, and provide some debugging capability.

However, in that case, why not let the variable retain its initial NULL value, and later reset it to NULL in the loop? I know that purists will argue that you cannot set a variable to NULL, but in T-SQL you can...

I would suggest initializing the variable to a value that will never occur... In other words, something that will not be assigned by ASE.

E.g.

select @suid = 0

FYI, in one of the test databases I am using right now, we already have 4000+ logins, and we are talking about not purging them... So over time, we could reach 10,000...

A bit far fetched, but, a potential bug nonetheless, don't you think?

Regards,

Jean-Pierre

Message was edited by: Jean-Pierre Dareys

Message was edited by: Jean-Pierre Dareys

Message was edited by: Jean-Pierre Dareys

[Former Member · Accepted solution]

Jason,

Consider i am refreshing the database from PROD - DEV.

Here my dev has some development ID' s  (This is to reduce DBA work ) . My prod is highly restricted . So i have created some additional user login account and assigned some additional privilege on dev application database  .  As per my understanding this script won't help me in that case . Please correct me if i am wrong .

Because

1) PROD doesn't have those additional accounts

[former_member89972]

Simple way is to use combination of

- ddglen (utility provided by SAP/Sybase for getting metadata out) and

- SQL generation for dropping users and groups (from system tables in the ASE database)

Before loading extract from target database

- as a precaution you can bcp out (character mode) all system tables from the target database

  this will be handy if things do not go as planned.

- extract group info (this includes grants for public + groups )

e.g. ddlgen -U sa -S <Server> -P<passw> -TGRP -N<DBName>.% -o  groups.ddl

- extract user info (includes group member ship for user )

e.g. ddlgen -U sa -S <Sever> -P<passw> -TUSR -N<DBName>.% -o  users.ddl

Before load but run on source side (or on target after the load)

- generate drop users (using  select on sysusers)

- generate drop NON-dbo aliases  (using select on sysalternates)

- generate drop user groups (use select on sysusers with gid = uid  and gid > last system gid)

On target after the load

- drop users, aliases and groups using the SQL generated

- add groups using ddlgen output

- add users using ddlgen output

This allows you have local sets of groups and users for each environment (PROD/QA/DEV)

Note that this method avoided updating system tables directly.

HTH

Avinash

[jayrijnbergen]

You'll have to synchronize multiple system tables

If you just load 1 user database and don't want to change syslogins in master database, then you have to check several tables in the user db, including: sysusers, sysroles, sysreferences

If you use column encryption and if you store your encryption keys in different db, then you also have to check syscolumns

If you load all user databases, it's easier to sync your master database, check tables:

- syslogins

- syssrvroles

- sysloginroles

- sysattributes (for remote logins, nested roles, some login options, etc.)

- sysroles

- sysusers

- sysprotects

Note: directly updating system tables is not supported...

[Former Member]

Jeroen,

Thank you for the response!

That is exactly what I am trying to do. Loading a single user database. It my come from another environment, so altering the local server syslogins table is out of the question. I had considered all the tables you mention except syscolumns, in the case of encryption.

While I investigate the topic, perhaps you can expand on exactly what would I have to check for in syscolumns. Would I have to extract that table before the load as well and then restore it? What about another database as you mention?

Encryption is a relatively new, and seldom used () ASE feature, which I have to revisit for the purpose. I did not remember that you could store encryption in a separate DB.

Reards,

Jean-Pierre

[jayrijnbergen]

For encryption keys the encryption key db name is stored in syscolumns.encrkeydb (only if the key is stored in a different db then the object using the encryption key)

As long as you restore the database containing the encryption keys with the same name, there's nothing you have to change for that in syscolumns for the encrypted table using these keys.

Only if you would like to restore the encryption key db with a different name, then you might have to update the encrkeydb name in syscolumns.