From Support Wiki
3 December 2002
Eliminating "Ghost" Users in SYSADM.SYSCOLAUTH
by Kevin Watts, December 3rd, 2002
Summary
Defects were reported in SQLBase 7.6.1 which may incorrectly cause entries in the SYSADM.SYSCOLAUTH table that indicate UPDATE authority has been granted on columns to users where there are no corresponding users in SYSADM.SYSUSERAUTH. The biggest problem associated with this phenomenon is an inability to LOAD a database from a file that was created with the SQLBase UNLOAD DATABASE statement.
This document provides details on how to circumvent this problem if you are affected by it.
The Problem
Defects were reported in 7.6.1 which may incorrectly cause entries in the SYSADM.SYSCOLAUTH table that indicate UPDATE authority has been granted on columns to users where there are no corresponding users in SYSADM.SYSUSERAUTH. You can test for this condition by executing the following query in SQLTalk:
select grantee from syscolauth
where grantee not in (select name from sysuserauth);
You should have 0 rows returned from this query. If instead you have 1 or more rows returned, you will have to work around the SYSCOLAUTH table problem so you will be able to successfully UNLOAD and LOAD your database. If your database has this problem, you will be able to UNLOAD your data, but will not be able to successfully LOAD a new database using the unload file.
Solutions
There are two known workarounds to this problem. One, you may edit the unload file to remove all GRANT statements referencing users who do not exist in the SYSUSERAUTH table. If you only have a few incorrect entries in SYSCOLAUTH, this can be an easy and effective solution. Two, you can scrub the SYSCOLAUTH table to remove the problem. This is a more complex process, but may be appropriate if you have a large number of incorrect entries.
Scrubbing the SYSCOLAUTH table is an 8-step process in SQLTalk. All of the steps are described below. Note that you should be logged in as SYSADM for each step. You might want to print these instructions out and tick off the steps as you complete them.
The Eight-Step Procedure
STEP 1:
Create a script in the SQLTalk output window using this query in the input window:
select distinct 'grant connect to '||grantee||' identified by '||grantee||';'
from syscolauth where grantee in (
select distinct grantee from syscolauth where grantee not in
(select name from sysuserauth)) order by grantee;
Save it as Script1.sql.
The output from SQLTalk will look something similar to this:
'GRANT CONNECT TO '||GRANTEE||' IDENTIFIED BY '||GRANTEE||';'
=============================================================
grant connect to FRANK identified by FRANK;
grant connect to JEAN identified by JEAN;
grant connect to JIM identified by JIM;
3 ROWS SELECTED
Copy and Paste this result into Script1.sql so the entire contents of the file look like this:
grant connect to FRANK identified by FRANK;
grant connect to JEAN identified by JEAN;
grant connect to JIM identified by JIM;
STEP 2:
Create a script using this query:
select distinct 'select distinct ''revoke all on ''||ttname||'' from '||grantee|| ';'' ' ||
' from systabauth where grantee = '''||grantee||''';'
from syscolauth where grantee in(
select distinct grantee from syscolauth where grantee not in
(select name from sysuserauth)) order by grantee;
Save it as Script2.sql.
In our sample case, the content of Script2.sql should be something like:
select distinct 'revoke all on '||ttname||' from FRANK;' from systabauth where grantee = 'FRANK';
select distinct 'revoke all on '||ttname||' from JEAN;' from systabauth where grantee = 'JEAN';
select distinct 'revoke all on '||ttname||' from JIM;' from systabauth where grantee = 'JIM';
STEP 3:
Create a script using this query:
select distinct 'revoke connect from '||grantee||';'
from syscolauth where grantee in (
select distinct grantee from syscolauth where grantee not in
(select name from sysuserauth)) order by grantee;
Save it as Script3.sql.
In our sample case, the content of Script3.sql should be something like:
revoke connect from FRANK;
revoke connect from JEAN;
revoke connect from JIM;
STEP 4:
Execute Script1.sql. COMMIT your work.
Note: this step assumes you prudently backed up all your files, etc.
You have to GRANT CONNECT to the incorrect users in the SYSCOLAUTH table for two reasons. One, you will not be able to create a new database using the LOAD command if this is not done. Two, these users will need to have permissions in order to scrub the invalid entries in SYSCOLAUTH in the new database.
STEP 5:
UNLOAD the database.
STEP 6:
Create a new database and LOAD the new database using the file created in STEP 5.
Note: This newly loaded database still has the invalid entries in SYSCOLAUTH. It is very important to finish the scrubbing process by performing steps 7 and 8.
STEP 7:
Run each element of Script2.sql separately. Each element will produce a new script that needs to be run. Copy and paste each new script to the input window and execute it.
Example. Our Script2.sql looks like this:
select distinct 'revoke all on '||ttname||' from FRANK;' from systabauth where grantee = 'FRANK';
select distinct 'revoke all on '||ttname||' from JEAN;' from systabauth where grantee = 'JEAN';
select distinct 'revoke all on '||ttname||' from JIM;' from systabauth where grantee = 'JIM';
We would run the first select and get output similar to this:
revoke all on TABLE01 from FRANK;
revoke all on TABLE02 from FRANK;
revoke all on TABLE03 from FRANK;
revoke all on TABLE04 from FRANK;
revoke all on TABLE05 from FRANK;
revoke all on TABLE06 from FRANK;
revoke all on TABLE07 from FRANK;
revoke all on TABLE08 from FRANK;
Copy all of this into the bottom of the input window and execute the script by setting the cursor in the 1st row and pressing F5. Then do the same with line 2 of Script2.sql and again with line 3, until all lines have been processed.
STEP 8:
Run Script3.sql, and then COMMIT your work. After committing your work, the SYSCOLAUTH table should be completely scrubbed.
Semi-automated Alternatives
If you choose, you can also run these steps programmatically. Below is an example of incorporating some of the 8 steps into four C programs. This is only an example, you can also do this in SAL, VB, Java or any language you choose that has access to SQLBase and the resident file system. Advanced users can even create two stored procedures to execute the needed SQL at the proper times. Note that in this example, steps 5 and 6 (unloading and loading the database) are left as manual operations.
Sample program 1 - ColAuth1.cpp. This program performs steps 1 through 3 of the 8-step process. That is, it creates all 3 of the scripts needed to scrub the SYSCOLAUTH table.
// ColAuth1.cpp : Creates 3 scripts to help scrub SYSCOLAUTH.
//
// You must be to only user connected to the database
//
#include "stdafx.h"
#include "sql32.h"
#include <string.h>
#include <stdio.h>
#include <stdlib.h>
#define DBNAME "Island"
#define PASSWD "SYSADM"
int main(int argc, char* argv[])
{
char database[] = DBNAME; // Database name
char userid[] = "SYSADM"; // Must connect as SYSADM
char password[] = PASSWD; // SYSADM password
int rc = -1; // Generic return code
SQLTCUR cur1; // A cursor
char ConnectString[60]; // Database connection string
char SQLstatement[511]; // Generic SQL statement buffer
char buffer[511]; // Work buffer
int numErrors = 0; // Number of bad rows in SYSCOLAUTH
FILE *fp; // Handle for three scripts to be written
// Begin program
printf("ColAuth1 - Scrub SYSCOLAUTH\n\n");
// Connect to database DBNAME
strcpy(ConnectString, database); strcat(ConnectString, "/"); strcat(ConnectString, userid);
strcat(ConnectString, "/"); strcat(ConnectString, password);
printf("Connecting as SYSADM to %s\n", database);
rc = sqlcnc(&cur1, (SQLTDAP)ConnectString, 0);
if (rc != 0) {
printf("Connection error %i\n", rc);
exit(1);
}
/*************************************************
P a r t I C r e a t e S c r i p t 1 . s q l
Script1 will grant connect to the users stranded
in SYSCOLAUTH. This is necessary in order to
successfully unload and load your database.
*************************************************
*/
// See if there is anything in SYSCOLAUTH to clean up
strcpy(SQLstatement, "select distinct 'grant connect to '||grantee||' identified by '");
strcat(SQLstatement, "||grantee||';' ");
strcat(SQLstatement, "from syscolauth where grantee in(");
strcat(SQLstatement, "select distinct grantee from syscolauth where grantee not in");
strcat(SQLstatement, "(select name from sysuserauth)) order by grantee");
printf("Checking SYSCOLAUTH. Be patient, this could take some time ...");
rc = sqlcom(cur1, (SQLTDAP)SQLstatement, 0);
if (rc
|| (rc = sqlssb(cur1, 1, SQLPSTR, (SQLTDAP)buffer, (SQLTPDL)sizeof(buffer), 0, SQLNPTR, SQLNPTR))
|| (rc = sqlexe(cur1))) {
printf("\n\nFatal error on compile or SSB or execute (%i)\n", rc);
exit(1);
}
// Fetch the rows that should not appear in SYSCOLAUTH
printf("Check complete.\nNow fetching inappropriate rows.\n");
fp = fopen("Script1.sql", "w"); // prepare Script1.sql
while ((rc = sqlfet(cur1)) == 0) {
numErrors++; // Increment error counter if fetch successful
fputs(buffer, fp); // Write the entry to Script1.sql
putc((int)'\n', fp); // Add newline. Script is built to run in SQLTalk
}
printf("There are %i inappropriate rows in SYSCOLAUTH.\n", numErrors);
if (numErrors == 0) {
printf("\nYOU HAVE NOTHING FURTHER TO DO.\n");
fclose(fp);
exit(0);
}
fputs("commit;", fp);
putc((int)'\n', fp);
fclose(fp);
/**************************************************
P a r t II C r e a t e S c r i p t 2 . s q l
Script2 will revoke table privileges that contain
unwanted column privileges IN THE NEW DATABASE.
Do not run this script in the "old" database, it
will not solve the problem.
**************************************************
*/
strcpy(SQLstatement, "select distinct 'select distinct ''revoke all on ''||ttname||'' from '||grantee|| ';'' ' ||");
strcat(SQLstatement, "' from systabauth where grantee = '''||grantee||''';'");
strcat(SQLstatement, "from syscolauth where grantee in(");
strcat(SQLstatement, "select distinct grantee from syscolauth where grantee not in");
strcat(SQLstatement, "(select name from sysuserauth)) order by grantee");
printf("\nBuilding second script. Be patient, this could take some time ...");
rc = sqlcom(cur1, (SQLTDAP)SQLstatement, 0);
if (rc
|| (rc = sqlssb(cur1, 1, SQLPSTR, (SQLTDAP)buffer, (SQLTPDL)sizeof(buffer), 0, SQLNPTR, SQLNPTR))
|| (rc = sqlexe(cur1))) {
printf("\n\nFatal error on compile or SSB or execute (%i)\n", rc);
exit(1);
}
// Fetch the rows that represent the bad grants
printf("Done.\nNow fetching rows.\n");
fp = fopen("Script2.sql", "w"); // prepare Script2.sql
numErrors = 0; // reset error counter
while ((rc = sqlfet(cur1)) == 0) {
numErrors++; // Increment error counter if fetch successful
fputs(buffer, fp); // Write the entry to Script2.sql
putc((int)'\n', fp);
}
printf("There are %i grants to revoke.\n", numErrors);
fputs("commit;", fp);
putc((int)'\n', fp);
fclose(fp);
/***************************************************
P a r t III C r e a t e S c r i p t 3 . s q l
Script3 will revoke connect privileges from the
unwanted users IN THE NEW DATABASE. Do not run this
script in the "old" database, it will not solve the
problem. Must be run AFTER Script2.
***************************************************
*/
strcpy(SQLstatement, "select distinct 'revoke connect from '||grantee||';'");
strcat(SQLstatement, "from syscolauth where grantee in(");
strcat(SQLstatement, "select distinct grantee from syscolauth where grantee not in");
strcat(SQLstatement, "(select name from sysuserauth)) order by grantee");
printf("\nBuilding third script. Be patient, this could take some time ...");
rc = sqlcom(cur1, (SQLTDAP)SQLstatement, 0);
if (rc
|| (rc = sqlssb(cur1, 1, SQLPSTR, (SQLTDAP)&buffer[0], (SQLTPDL)sizeof(buffer), 0, SQLNPTR, SQLNPTR))
|| (rc = sqlexe(cur1))) {
printf("\n\nFatal error on compile or SSB or execute (%i)\n", rc);
exit(1);
}
// Fetch the rows that represent the bad grants
printf("Done.\nNow fetching rows.\n");
fp = fopen("Script3.sql", "w"); // prepare Script3.sql
numErrors = 0; // reset error counter
while ((rc = sqlfet(cur1)) == 0) {
numErrors++; // Increment error counter if fetch successful
fputs(buffer, fp); // Write the entry to Script3.sql
putc((int)'\n', fp);
}
printf("There are %i grants to revoke.\n\n", numErrors);
fputs("commit;", fp);
putc((int)'\n', fp);
fclose(fp);
/***************************************************
E n d o f C o l A u t h 1
This program creates 3 scripts, but does not attempt
to execute any of them. This is to allow the user
maximum flexibility in the manner in which they
scrub the SYSCOLAUTH table.
***************************************************
*/
return 0;
}
Sample program 2 - ColAuth2.cpp. This program performs step 4 of the 8-step process. That is, it performs the same actions as running Script1.sql.
// ColAuth2.cpp : Run Script1.sql. You may also run Script1.sql in SQLTalk.
//
// You must be to only user connected to the database
//
#include "stdafx.h"
#include "sql32.h"
#include <string.h>
#include <stdio.h>
#include <stdlib.h>
#define DBNAME "Island"
#define PASSWD "SYSADM"
int main(int argc, char* argv[])
{
char database[] = DBNAME; // Database name
char userid[] = "SYSADM"; // Must connect as SYSADM
char password[] = PASSWD; // SYSADM password
int rc = -1; // Generic return code
SQLTCUR cur1; // A cursor
char ConnectString[60]; // Database connection string
char buffer[511]; // Work buffer
int numLines = 0; // Number of lines in script
FILE *fp; // Handle to read Script1.sql
int i = 0; // Work integer
// Begin program
printf("ColAuth2 - Run Script1.sql\n\n");
// Connect to database DBNAME
strcpy(ConnectString, database); strcat(ConnectString, "/"); strcat(ConnectString, userid);
strcat(ConnectString, "/"); strcat(ConnectString, password);
printf("Connecting as SYSADM to %s\n", database);
rc = sqlcnc(&cur1, (SQLTDAP)ConnectString, 0);
if (rc != 0) {
printf("Connection error %i\n", rc);
exit(1);
}
// See if you can open Script1.sql
if ((fp = fopen("Script1.sql", "r")) == NULL) {
printf("Cannot open script file Script1.sql\nProgram terminating\n");
exit(1);
}
// read all SQL statements and execute them
while ((fgets(buffer, sizeof(buffer), fp)) != NULL) {
++numLines; // increment number of lines read
for (i = 0; buffer[i] != ';'; i++) ;
buffer[i] = '\0'; // strip semicolon from script
if ((rc = sqlcex(cur1, (SQLTDAP)buffer, 0))) {
printf("Could not execute statement %i\nRolling back and terminating\n", numLines);
sqlrbk(cur1);
fclose(fp);
exit(1);
}
printf("%s\n", buffer);
}
printf("%i users granted connect\n", numLines-1);
fclose(fp);
return 0;
}
Sample program 3 - ColAuth3.cpp. This program performs step 7 of the 8-step process. That is, it performs the same actions as running Script2.sql.
// ColAuth3.cpp : Read Script2.sql; create a new script for
// each entry; execute each script.
//
#include "stdafx.h"
#include "sql32.h"
#include <string.h>
#include <stdio.h>
#include <stdlib.h>
#define DBNAME "Island"
#define PASSWD "SYSADM"
int main(int argc, char* argv[])
{
char database[] = DBNAME; // Database name
char userid[] = "SYSADM"; // Must connect as SYSADM
char password[] = PASSWD; // SYSADM password
int rc = -1; // Generic return code
SQLTCUR cur1; // A cursor
SQLTCUR cur2; // A cursor
char ConnectString[60]; // Database connection string
char SQLstatement[511]; // Generic SQL statement buffer
char buffer[511]; // Work buffer
int numErrors = 0; // Number of bad rows in SYSCOLAUTH
int i = 0; // Work integer
FILE *in; // Handle for Script2.sql
// Begin program
printf("ColAuth3 - Revoke table rights\n\n");
// Connect to database DBNAME
strcpy(ConnectString, database); strcat(ConnectString, "/"); strcat(ConnectString, userid);
strcat(ConnectString, "/"); strcat(ConnectString, password);
printf("Connecting as SYSADM to %s\n", database);
rc = sqlcnc(&cur1, (SQLTDAP)ConnectString, 0);
if (rc == 0)
rc = sqlcnc(&cur2, (SQLTDAP)ConnectString, 0);
if (rc != 0) {
printf("Connection error %i\n", rc);
exit(1);
}
/* Script2 is a metaScript. Each line represents a new script that must be
generated and executed. Each line will generate a script that revokes table rights from the "bogus" users originally found in SYSCOLAUTH. The tables are those that contain the columns in the bad SYSCOLAUTH entries. When these are all executed, the SYSCOLAUTH table should be scrubbed.
*/
// See if you can open Script2.sql
if ((in = fopen("Script2.sql", "r")) == NULL) {
printf("Cannot open script file Script2.sql\nProgram terminating\n");
exit(1);
}
// Outer loop to read the input script, Script2.sql
while ((fgets(buffer, sizeof(buffer), in)) != NULL) {
for (i = 0; buffer[i] != '\n'; i++); // find newline
buffer[i-1] = '\0'; // translate last ';' to null
if (strcmp(buffer, "commit") == 0)
break;
printf("%s\n", buffer);
rc = sqlcom(cur1, (SQLTDAP)buffer, 0);
if (rc
|| (rc = sqlssb(cur1, 1, SQLPSTR, (SQLTDAP)SQLstatement, (SQLTPDL)sizeof(SQLstatement), 0, SQLNPTR, SQLNPTR))
|| (rc = sqlexe(cur1))) {
printf("\n\nFatal error on compile or SSB or execute (%i)\n", rc);
fclose(in);
exit(1);
}
// inner loop: make and execute the new script
while ((rc = sqlfet(cur1)) == 0) {
for (i = 0; SQLstatement[i] != ';'; i++);
SQLstatement[i] = '\0'; // Strip trailing semicolon
printf("..........%s\n", SQLstatement);
// Got the statement, now execute it
if ((rc = sqlcex(cur2, (SQLTDAP)SQLstatement, 0))) {
printf("Inner loop failure.\n");
fclose(in);
sqlrbk(cur2);
exit(1);
}
}
sqlcmt(cur1); // Commit the entire transaction
}
fclose(in);
return 0;
}
Sample program 4 - ColAuth4.cpp. This program performs step 8 of the 8-step process. That is, it performs the same actions as running Script3.sql.
// ColAuth4.cpp : Run Script3.sql
//
// You must be to only user connected to the database
//
#include "stdafx.h"
#include "sql32.h"
#include <string.h>
#include <stdio.h>
#include <stdlib.h>
#define DBNAME "Island"
#define PASSWD "SYSADM"
int main(int argc, char* argv[])
{
char database[] = DBNAME; // Database name
char userid[] = "SYSADM"; // Must connect as SYSADM
char password[] = PASSWD; // SYSADM password
int rc = -1; // Generic return code
SQLTCUR cur1; // A cursor
char ConnectString[60]; // Database connection string
char buffer[511]; // Work buffer
int numLines = 0; // Number of lines in script
FILE *fp; // Handle to read Script1.sql
int i = 0; // Work integer
// Begin program
printf("ColAuth4 - Run Script3.sql\n\n");
// Connect to database DBNAME
strcpy(ConnectString, database); strcat(ConnectString, "/"); strcat(ConnectString, userid);
strcat(ConnectString, "/"); strcat(ConnectString, password);
printf("Connecting as SYSADM to %s\n", database);
rc = sqlcnc(&cur1, (SQLTDAP)ConnectString, 0);
if (rc != 0) {
printf("Connection error %i\n", rc);
exit(1);
}
// See if you can open Script3.sql
if ((fp = fopen("Script3.sql", "r")) == NULL) {
printf("Cannot open script file Script3.sql\nProgram terminating\n");
exit(1);
}
// read all SQL statements and execute them
while ((fgets(buffer, sizeof(buffer), fp)) != NULL) {
++numLines; // increment number of lines read
for (i = 0; buffer[i] != ';'; i++) ;
buffer[i] = '\0'; // strip semicolon from script
if ((rc = sqlcex(cur1, (SQLTDAP)buffer, 0))) {
printf("Could not execute statement %i\nRolling back and terminating\n", numLines);
sqlrbk(cur1);
fclose(fp);
exit(1);
}
printf("%s\n", buffer);
}
printf("%i users had connect revoked\n", numLines-1);
fclose(fp);
return 0;
}