We are using SCO OpenServer 6 with PROGRESS V9.1E04 for our application. When the run-time RDBMS is installed on the server, we select the option to use both 4GL and SQL server groups, start the databases with the tcp option to expose them to the network. On the Windows client side, we install PROGRESS SQL-92 Client and configure it.
Now we want to set permissions (grant, revoke, etc), on tables. We want to charge a consulting fee for each table they want to access. This not only brings in revenue, but controls what they have access to. We would like to do this with the 'sqlexp' command supplied with PROGRESS RDBMS from the SCO 6 side. When you run the program, it doesn't do anything. Researching this issue, I found KB P122315 stating that although SQL Server support is available, there is no support for "native" SQL-92 clients. Upon further research I found out that PROGRESS made a business decision to not port it to the SCO 6 platform.
How are we supposed to set up the permissions and support this when we cannot grant SQL statements from the server side. Further, all the PROGRESS documents reference the fictitious "sysprogress" DBA user so anyone that knows their way around can access anything they want from the database using those credentials.
We have no access to the client side to issue these commands. In addition, we have very little control as to what the end user will do. We tried creating a Windows program to connect to each of the databases and issue the SQL statements, but ran into some issues with exclusive locks and it left the database in an unknown state. We have to be able to control this from the server side. Any suggestions besides move to another platform? Why can't the folks at PROGRESS port the sqlexp to run on SCO OS6?
According to the V9 Product Availability Guide ( http://www.progress.com/progress_software/products/docs/bu_sep/pavail.pdf ) page 5, there is no SQL-92 support on SCO OpenServer ... not client or server. I think this means you are out of luck.
Isn't it time for a switch to Linux? Might consider OE10 as well!
As for the sysprogress user, it isn't so much fictional as not pre-defined. Whenever I set up a database for SQL access, I typically create a sysprogress user and a user for the user that created the database and often a third user for use by the reporting tool or whatever application is using SQL. See http://www.oehive.org/node/954 for one person's way of doing things.
If you can connect to the database using ODBC from Windows, you should have no problem using sqlexp on the Windows machine. Whether you use sqlexp from the same machine as the database or not, it always needs to connect through the network socket. As such, if it's only sqlexp that doesn't work on SCO OpenServer, you will still be able to do what you want.
As far as SQL security goes, by default the Userid of the user who created the database have access to the database (with no password). Originally, it was the "sysprogress" user, but I'm not sure in which version that stopped being the case.
If you want to really secure the SQL side, you'll need to create users and give them passwords. Creating SQL users automatically creates 4GL users too. If you don't use database users (_user) today, you'll need to think long and hard about how you want to go about this. Given that you're concerned about people tinkering around through the SQL side, I guess you user 4GL users already. If not, you should also be worried about people tinkering around on the 4GL side.
If you don't have database users today and you decide to create SQL users, make sure that the first user you create gets DBA & RESOURCE privileges, otherwise you'll lock yourself out of the SQL engine!
If you're wanting to restrict access to bits of your database, you might want to consider creating SQL views and only grant access to them. This has the added advantage of allowing you to simplify the table relationships, give the views and their columns simple names and make the whole end user experience much better (you do want to earn that consulting fee, right )
...and then of course there's the question of dump & loads. Anything you do in the SQL engine is not part of the 4GL dictionary dump & load process, so you'd best make sure you have plans on how to dump & load the users, views, security, etc.
"Not supported" was my initial response too. The KBase entry made me unsure though. It would seem that the V9 Production Availability Guide hasn't been updated recently. Strangely, the OE10 Production Availability Guide mentions that SQL92 is supported on OpenServer 6 with 9.1E04 & OE10.0B05.
That said, it's still a little strange to me that the SQL engine would work, but sqlexp doesn't!?
I don't see any evidence that anything has actually worked yet. They have a client on Windows, but did they connect to the OpenSewer DB with it? If so, end of problem, but I'm guessing that is not the case.
I don't think it ever stopped being sysprogress. The SQL system tables are still owned by sysprogress. If I build a DB as root and then create a root and sysprogress user, then both show up with all capabilities in sysdbauth. Prior to creating users, one can get in as root with no password ... which one probably doesn't want for production, which is why I always start by creating the three users at a minimum.
SQL-92 is support for SCO OpenServer *6*. The client connects and you can view the tables in pub.tablename. The problem is the default permissions that you cannot set unless you are connected from the client.
The server and clients are at the end users site. They don't allow remote access to the client.
But you do have access to the server? Can you use your own Windows client to connect to the server?
How do you save the SQL92 table permissions?
I think you can do this with the sqldump script in $DLC/bin ... but then maybe you can't if you have no SQL client.
Are you sure it wouldn't just be simpler to switch to Linux?