[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1967
  • Last Modified:

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.
0
bslorence
Asked:
bslorence
  • 11
  • 6
1 Solution
 
mirtheilCommented:
A few questions:
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.  
0
 
Bill BachPresidentCommented:
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.
0
 
bslorenceAuthor Commented:
@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;pwd=<password_here>;

  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][ODBC Engine Interface][Data Record Manager]Invalid user authorization specification.

4. Yes; I shouldn't have left out "log in as Master" from step 3 in my original post; sorry.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
bslorenceAuthor Commented:
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?
0
 
bslorenceAuthor Commented:
@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.
0
 
bslorenceAuthor Commented:
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.
0
 
Bill BachPresidentCommented:
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.
0
 
bslorenceAuthor Commented:
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.
0
 
Bill BachPresidentCommented:
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.
0
 
bslorenceAuthor Commented:
OK, I'm going to give v8.7 a shot over the weekend. I'll follow up on Monday.
0
 
bslorenceAuthor Commented:
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.
0
 
bslorenceAuthor Commented:
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
0
 
Bill BachPresidentCommented:
This should be done in the PCC.

However, please note that you need to look in the X$File table for the X$Rights table name.  Then, you can issue a query like this:
    SELECT * FROM X$Field INNER JOIN X$File ON Xe$file = xf$id WHERE xf$Name = 'X$Rights'
This should show you the rights DDF definition.  Then do this one:
    SELECT * FROM X$Index INNER JOIN X$File ON Xi$file = xf$id WHERE xf$Name = 'X$Rights'
And you should see the same number of records.  Again, the ID column s won't matter4, but the number of rows for each query should be the same.

0
 
bslorenceAuthor Commented:
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.
0
 
Bill BachPresidentCommented:
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.
0
 
bslorenceAuthor Commented:
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
0
 
bslorenceAuthor Commented:
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.
0
 
Bill BachPresidentCommented:
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.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 11
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now