bslorence
asked on
Pervasive SQL v8: GRANT executed from Data Manager doesn't stick
I'm running Pervasive SQL v8.60 on Windows Server 2003. When I use the Control Center to open a table in the Data Manager, and execute a "GRANT" statement to give another user column-level privileges, those privileges seem to last only until I close the Control Center.
Step-by-step example:
1. Start Control Center on the server
2. Navigate to the "Tables" control of the database in question.
3. Double click any table to open the Data Manager.
4. Execute "GRANT SELECT (column_a) ON table_b TO user_x" -- Data Manager reports that the SQL statement executed successfuly
5. From a different computer, run a test script which connects, via ODBC, to the database in question as user_x, and executes a simple query: "SELECT column_a FROM table_b" -- script succeeds.
6. Close Control Center on the server
7. From the same computer mentioned in step 5, run the same test script again. The script fails with an ODBC error: "you are not authorized to perform this operation".
If I start over from step 1, I get the same results, every time. Is there some sort of a "commit" or "flush" that I need to do on the server to get this GRANT to take effect permanently? I tried connecting to the database and executing the GRANT statement over ODBC, but Pervasive doesn't seem to permit the "Master" user to log in via ODBC.
Step-by-step example:
1. Start Control Center on the server
2. Navigate to the "Tables" control of the database in question.
3. Double click any table to open the Data Manager.
4. Execute "GRANT SELECT (column_a) ON table_b TO user_x" -- Data Manager reports that the SQL statement executed successfuly
5. From a different computer, run a test script which connects, via ODBC, to the database in question as user_x, and executes a simple query: "SELECT column_a FROM table_b" -- script succeeds.
6. Close Control Center on the server
7. From the same computer mentioned in step 5, run the same test script again. The script fails with an ODBC error: "you are not authorized to perform this operation".
If I start over from step 1, I get the same results, every time. Is there some sort of a "commit" or "flush" that I need to do on the server to get this GRANT to take effect permanently? I tried connecting to the database and executing the GRANT statement over ODBC, but Pervasive doesn't seem to permit the "Master" user to log in via ODBC.
The Pervasive.SQL V8.6 engine is a bit old now, having been replaced directly by Pervasive.SQL V8.7 (Service Pack 3) and then Pervasive PSQL v9. However, I can assure you that the GRANT syntax does work, and no flush is required to save this information, although sometimes DDF's can get damaged and become unusable.
Your first step may be to patch to the Service Pack 3 code. You can get this from www.pervasive.com/support/updates.
After that, please follow these steps:
1) From the Pervasive Control Center, open the database on the server. You should be prompted for a login. If not, then security is NOT yet enabled. To correct this, right-click the database and select Properties, then go to the Security tab and enter a Master password (twice). Clicking OK should enable security, but you must be the ONLY one in the system for this to take effect. If you are prompted for a login, then provide the Master user and password to log in.
2) Open the Tables item and look for the system tables X$User and X$Rights (with red checkmarks). Verify that both exist. Double-click on the X$User table and verify that the users that you want in there are actually there. If not, execute this statement: "GRANT LOGIN TO USER username:password" Re-run the SELECT query from X$User and note the Xu$Id of the user name that you want.
3) Now, run this query: "SELECT * FROM X$Rights where Xr$User = #" where the # is the number from Xu$Id above. This should show you rights to the tables that this user has rights to. If you don't see anything, try this query: "GRANT SELECT ON tablename TO username" and re-run the SELECT again. You should see something now.
4) At this point, things should be set up correctly. If you get any error messages, or cannot get the data expected, then something is wrong, and we'll know what steps need to be done to fix it. It it DOES fail, then please try the same steps on the DEMODATA database, and see if that one works. If it does, and yours does not, then the DDF's were either built incorrectly or they were damaged. In this case, we'll need to manually strip out the security records from the database and start over again.
Your first step may be to patch to the Service Pack 3 code. You can get this from www.pervasive.com/support/updates.
After that, please follow these steps:
1) From the Pervasive Control Center, open the database on the server. You should be prompted for a login. If not, then security is NOT yet enabled. To correct this, right-click the database and select Properties, then go to the Security tab and enter a Master password (twice). Clicking OK should enable security, but you must be the ONLY one in the system for this to take effect. If you are prompted for a login, then provide the Master user and password to log in.
2) Open the Tables item and look for the system tables X$User and X$Rights (with red checkmarks). Verify that both exist. Double-click on the X$User table and verify that the users that you want in there are actually there. If not, execute this statement: "GRANT LOGIN TO USER username:password" Re-run the SELECT query from X$User and note the Xu$Id of the user name that you want.
3) Now, run this query: "SELECT * FROM X$Rights where Xr$User = #" where the # is the number from Xu$Id above. This should show you rights to the tables that this user has rights to. If you don't see anything, try this query: "GRANT SELECT ON tablename TO username" and re-run the SELECT again. You should see something now.
4) At this point, things should be set up correctly. If you get any error messages, or cannot get the data expected, then something is wrong, and we'll know what steps need to be done to fix it. It it DOES fail, then please try the same steps on the DEMODATA database, and see if that one works. If it does, and yours does not, then the DDF's were either built incorrectly or they were damaged. In this case, we'll need to manually strip out the security records from the database and start over again.
ASKER
@mirtheil:
1. The control center itself doesn't require a login. I'm logged into the computer that the control center is running on, as domain administrator. When I open a database table (in step 3 of my original post) I have to log in as 'Master'.
2. An account in the Domain Admins group.
3. Here is my VBScript connect string:
DSN=sample_rw;uid=Master;p
The line in the script that should open the connection fails with:
Microsoft OLE DB Provider for ODBC Drivers: [Pervasive][ODBC Client Interface][LNA][Pervasive]
4. Yes; I shouldn't have left out "log in as Master" from step 3 in my original post; sorry.
1. The control center itself doesn't require a login. I'm logged into the computer that the control center is running on, as domain administrator. When I open a database table (in step 3 of my original post) I have to log in as 'Master'.
2. An account in the Domain Admins group.
3. Here is my VBScript connect string:
DSN=sample_rw;uid=Master;p
The line in the script that should open the connection fails with:
Microsoft OLE DB Provider for ODBC Drivers: [Pervasive][ODBC Client Interface][LNA][Pervasive]
4. Yes; I shouldn't have left out "log in as Master" from step 3 in my original post; sorry.
ASKER
Is it possible that the Master account's password is limited to a certain number of characters, and that the Control Center silently discards the extra characters, but ODBC doesn't?
ASKER
@BillBach:
My most recent posts should clarify that security is enabled for this database; sorry for leaving that ambiguous in the original question.
I'm doing maintenance this weekend and should be able to install the v8.7 patch. In the meantime I can tell you that the X$Rights table is always empty, right now. Doesn't matter how many GRANT statements execute successfully, a "SELECT * FROM X$Rights" always returns an empty result set.
I don't have the DEMODATA database in my Control Center, so I went to add it. In the Create Database Wizard I specified "DEMODATA" for the "Name" and "C:\PVSW\Demodata" for the "Directory" -- this directory exists and is full of mkd and ddf files. When I clicked "Next" I got this error message:
"Cannot retrieve database names. You do not have sufficient access rights for the operation."
But again, I"m logged into that computer as Domain Administrator, and the Administrators group has full control over that directory and all the files in it. Weirder and weirder.
I should mention that we have Pervasive because a third-party CRM app requires it. I didn't install the Pervasive server originally; a consultant did two years ago.
My most recent posts should clarify that security is enabled for this database; sorry for leaving that ambiguous in the original question.
I'm doing maintenance this weekend and should be able to install the v8.7 patch. In the meantime I can tell you that the X$Rights table is always empty, right now. Doesn't matter how many GRANT statements execute successfully, a "SELECT * FROM X$Rights" always returns an empty result set.
I don't have the DEMODATA database in my Control Center, so I went to add it. In the Create Database Wizard I specified "DEMODATA" for the "Name" and "C:\PVSW\Demodata" for the "Directory" -- this directory exists and is full of mkd and ddf files. When I clicked "Next" I got this error message:
"Cannot retrieve database names. You do not have sufficient access rights for the operation."
But again, I"m logged into that computer as Domain Administrator, and the Administrators group has full control over that directory and all the files in it. Weirder and weirder.
I should mention that we have Pervasive because a third-party CRM app requires it. I didn't install the Pervasive server originally; a consultant did two years ago.
ASKER
I just confirmed that there is an 8-character limit on the Master password. That's why the ODBC connection was failing. So now I can use ODBC to execute a "GRANT" statement as Master, from the remote computer, in VBScript. But... the GRANT still doesn't take effect, and X$Rights still has no data related to the user in question. The user record does exist in X$User, though.
You are correct, there is an 8-character limit on the password. I did know that the PCC chopped the password, but apparently the ODBC driver does not. I'm not sure if this counts as a bug or not, but I'll definitely test in PSQLv9.5 and PSQLv10 and recommend a change if it is still the case.
You still have a few problems with this server, though. First off, it was likely installed from a Remote Desktop (i.e. Terminal Services) connection. This is NOT a valid install, and is mentioned in the manual and (I think) the ReadMe file as well. A proper install will create DemoData (the test database) and DefaultDB (the default security database) for you automatically. Second, it sounds like you are trying to configure it from Remote Desktop to create the Database now, getting the same error that would have killed the installer.
Best suggestion: Remove the older V8.6 install completely, delete the C:\WINDOWS\DBNAMES.CFG file and anything left in C:\PVSW or C:\PVSWARCH, and THEN do the PSQLV8.7 install from the server console itself. (A remote control tool like VNC, RAdmin, or PCAnywhere will work fine, just don't use Terminal Services.)
If you must admin the box through TS _after_ the install, the flip the registry flag that allows such access, called "Restricted Access on WTS Client". Once done, restart the tools on the server, and you should be OK from there. Note, though, that *ALL* TS users will then have administrative rights on the box. Once we have the install working, this will likely be a cleaner config.
The next step will be to figure out the Rights issue. If the User record has been created, but the Rights records have not been created, then something is likely wrong with your Rights table. Try these next steps:
1) Verify that RIGHTS.DDF exists in the DDF directory. It should, as you didn't indicate that you got an error when doing the SELECT * FROM X$Rights.
2) Do a SELECT * FROM X$File WHERE Xf$Name = 'X$Rights' and verify that the entry for looks like the following:
Xf$Id Xf$Name Xf$Loc Xf$Flags Xf$Reserved
----- -------------------- --------------------------
15 X$Rights rights.ddf 16
3) You may also wish to check out the X$Field and X$Index records relating to the Rights table (restrict on Xe$File = the Xf$Id shown above). Compare what you see with the same from DemoData, and it may give you an idea of what is wrong.
I have seen some cases whereby the DDF's are mangled, or else they were created by an ancient tool that did not do it properly, and they simply need to be recreated and reloaded. You should be able to do this by creating a new database, then drag&dropping all user tables from one to another. (Of course, keep a GOOD backup of all files, just in case!!!) We have other tools of our own design that allow moving tables from one set of DDF's to another, but the drag&drop should work for most databases.
In short, doing ANY type of GRANT statement on a user or group should give you database writes into RIGHTS.DDF. If you are not seeing anything, there must be something wrong with that table that is losing the inserts to it, and rebuilding a new DDF set is the way to fix it.
You still have a few problems with this server, though. First off, it was likely installed from a Remote Desktop (i.e. Terminal Services) connection. This is NOT a valid install, and is mentioned in the manual and (I think) the ReadMe file as well. A proper install will create DemoData (the test database) and DefaultDB (the default security database) for you automatically. Second, it sounds like you are trying to configure it from Remote Desktop to create the Database now, getting the same error that would have killed the installer.
Best suggestion: Remove the older V8.6 install completely, delete the C:\WINDOWS\DBNAMES.CFG file and anything left in C:\PVSW or C:\PVSWARCH, and THEN do the PSQLV8.7 install from the server console itself. (A remote control tool like VNC, RAdmin, or PCAnywhere will work fine, just don't use Terminal Services.)
If you must admin the box through TS _after_ the install, the flip the registry flag that allows such access, called "Restricted Access on WTS Client". Once done, restart the tools on the server, and you should be OK from there. Note, though, that *ALL* TS users will then have administrative rights on the box. Once we have the install working, this will likely be a cleaner config.
The next step will be to figure out the Rights issue. If the User record has been created, but the Rights records have not been created, then something is likely wrong with your Rights table. Try these next steps:
1) Verify that RIGHTS.DDF exists in the DDF directory. It should, as you didn't indicate that you got an error when doing the SELECT * FROM X$Rights.
2) Do a SELECT * FROM X$File WHERE Xf$Name = 'X$Rights' and verify that the entry for looks like the following:
Xf$Id Xf$Name Xf$Loc Xf$Flags Xf$Reserved
----- -------------------- --------------------------
15 X$Rights rights.ddf 16
3) You may also wish to check out the X$Field and X$Index records relating to the Rights table (restrict on Xe$File = the Xf$Id shown above). Compare what you see with the same from DemoData, and it may give you an idea of what is wrong.
I have seen some cases whereby the DDF's are mangled, or else they were created by an ancient tool that did not do it properly, and they simply need to be recreated and reloaded. You should be able to do this by creating a new database, then drag&dropping all user tables from one to another. (Of course, keep a GOOD backup of all files, just in case!!!) We have other tools of our own design that allow moving tables from one set of DDF's to another, but the drag&drop should work for most databases.
In short, doing ANY type of GRANT statement on a user or group should give you database writes into RIGHTS.DDF. If you are not seeing anything, there must be something wrong with that table that is losing the inserts to it, and rebuilding a new DDF set is the way to fix it.
ASKER
Interesting... the Terminal Services limitation probably explains why, when we briefly installed Pervasive on a different computer to test a major upgrade of the app that uses it, things looked a lot different in the Control Center from the way they look on our live server.
I'd like to do that fresh installation you've describe, but It looks like Service Pack 3 is a patch, so I'll need to install from my CD first, right? And then patch Service Pack 3 over that installation?
Will upgrading the server to v8.7 require me to do anything with the client installations? I've got about 25 XP machines with the Pervasive client installed.
I'd like to do that fresh installation you've describe, but It looks like Service Pack 3 is a patch, so I'll need to install from my CD first, right? And then patch Service Pack 3 over that installation?
Will upgrading the server to v8.7 require me to do anything with the client installations? I've got about 25 XP machines with the Pervasive client installed.
Probably would explain the differences.
The Service Pack 3 installation is *ALSO* a full installation. (It does double-duty, so Pervasive only needed one download.) Just download it, and you can either patch or do a full install. If you remove and delete everything FIRST, then your reinstall should go smoothly.
You should (ideally) update the clients to PSQLV8.7 as well, as bugs were fixed in both the server AND client, but this is not immediately critical -- just get to it when you can for best results.
The Service Pack 3 installation is *ALSO* a full installation. (It does double-duty, so Pervasive only needed one download.) Just download it, and you can either patch or do a full install. If you remove and delete everything FIRST, then your reinstall should go smoothly.
You should (ideally) update the clients to PSQLV8.7 as well, as bugs were fixed in both the server AND client, but this is not immediately critical -- just get to it when you can for best results.
ASKER
OK, I'm going to give v8.7 a shot over the weekend. I'll follow up on Monday.
ASKER
Sorry, didn't follow up on Monday. The re-installation went smoothly. I'm going to back and try your other suggestions now and I'll follow up again afterward.
ASKER
OK, I compared the relevant rows in the X$File, X$Field, and X$Index tables in the DEMODATA database with the ones in my database, and I don't see any discrepancies. The values in the 'Xi$Field' column in 'X$Index' were different between the two databases, but I'm guessing that's to be expected.
Sounds like I should re-create the DDFs. The dragging-and-dropping of tables that you describe above: is that in PCC, or should I be dragging-and-dropping the underlying files in Windows?
Thanks, Ben
Sounds like I should re-create the DDFs. The dragging-and-dropping of tables that you describe above: is that in PCC, or should I be dragging-and-dropping the underlying files in Windows?
Thanks, Ben
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I ran the two queries above and they did not have the same number of rows. So I created a new database and started dragging data tables from the corrupt one to the new one. After I had copied three tables or so, I tried to copy the next one and got the error message "Unable to open table", followed by "You are not authorized to perform this operation", followed by "The operation completed successfully".
But I'm logged into the PCC as the Master user of the original database, and I'm using the console on the server this time, not Remote Desktop, and I'm logged into the server as domain administrator. Just another sign that the DDFs are corrupt?
I'm starting to think that maybe I should just skip column-level security and content myself with table-level.
But I'm logged into the PCC as the Master user of the original database, and I'm using the console on the server this time, not Remote Desktop, and I'm logged into the server as domain administrator. Just another sign that the DDFs are corrupt?
I'm starting to think that maybe I should just skip column-level security and content myself with table-level.
It is most likely that the DDF's are damaged, and perhaps to a point where the "normal" tools can't handle it. Goldstar Software can do a repair of the DDF's, for a small fee, if you want. However, I would rather go back to the original question -- with the newly created DDF's and one or two tables dropped in, is the Rights issue resolved? If so, then this would close your current question, noting that the DDF's are damaged and needed to be fixed, but leaving a secondary issue of fixing up your original DDF's such that they work properly again.
ASKER
I just tested the new database and the Rights table works beautifully. GRANT and REVOKE statements insert and delete rows from the table, respectively, and the permissions "stick". So I guess you're right, that should settle it for this question. I'll keep Goldstar in mind for the other. Thanks, Ben
ASKER
BUT... I just went back and ran those INNER JOIN queries again, this time in the newly-created database, and in that database I get different numbers of rows for each query also.
There are 4 fields in the X$Rights table, so the first query should always yield 4 rows. You should see field names (Xe$Name) of Xr$User, Xr$Table, Xr$Column, Xr$Rights. Then, for the query from X$Index, you should get 6 rows out, with records for each of the following segments: 0/0, 1/0, 1/1, 1/2, 2/0, 2/1. BTW, the system tables are documented in the online manuals (search for "System Tables"), so you can always verify the results directly with this documentation.
1. what user are you logged into the Control Center with?
2. What user are you logged into the "different computer" (step 5) with?
3. What do you mean by "but Pervasive doesn't seem to permit the "Master" user to log in via ODBC."? Are you getting an error?
4. Does the database have security enabled?
Control Center uses ODBC.