ODBC Issues keep me up at night.

[dlangschied]

I have had numerous ODBC issues with Progress 10.1C! My current one is that I cannot seem to get the access assignment to work on a new database. I monkey around with until I get it to work, but it is a pain. I ran the following from the expsql command prompt:

create user 'sysadm','squirr1';
grant dba to 'sysadm';
commit;
quit

I got the user sysadm, but no dba rights. This is very frustrating when this happens because I can no longer get back the sqlexp command prompt as anyone, not even the database creator.

Please assist.

 

[dlangschied]

Slight correction. I is not that I cannot get in via command prompt. It is that I cannot do anything, including dropping the user that I created without rights. This is becoming quite a pickle as I need to get this database accessible via ODBC.

Help!!!!!

 

[Stefan]

Slight correction. I is not that I cannot get in via command prompt. It is that I cannot do anything, including dropping the user that I created without rights. This is becoming quite a pickle as I need to get this database accessible via ODBC.

Help!!!!!

You seem to have had a database in which no users (_user) existed. By creating the user (from the sql side) you have created an _user record. You should have simply granted the rights without creating the user - this creates an implicit user, see progress knowledge base.

The need to create records in the _user table for SQL users (and therefore for ABL users as well) can be avoided in the following ways: The database creator will always have DBA permissions, so this user can be used for SQL connections without needing to add to the _user table. Any password can be used when connecting as the database creator in this way; the password is not checked. See article 000001384, "Basic Guide to Defining Progress SQL-92 Database Permissions & Security". An implicit SQL user can be created in the database, which does not need a password. To create a SQL user implicitly, assign permissions to a user that does not exist in the _user table. For example: GRANT DBA TO user1​ ​ The above ssentially creates a new SQL user "user1" without creating a record in _user. The database can now be accessed by user1 in the same way as the database creator account, with the "user1" user name and any password.​ ​ Implicit users can be granted restricted access by granting or revoking specific privileges to specific tables, rather than full DBA rights. For example:​ ​ GRANT SELECT ON pub.Customer to user2;​ ​ user2 can connect to the database but can only query the Customer table.​

As to resolving your immediate issue. I think you should be able to connect to your database from data administration with the user password you provided. You should then be able to delete the user.

 

[dlangschied]

This is excellent information. I was unaware that I could simply grant dba to a user to create it with the rights that I want. This does not give me a password, but still ... Cool! I also fixed my problem with the rights not getting assigned. I had put the user in with single quotes as I did when I created the user. I granted to the user without quotes and it worked.

As far as granting to a table, this database user actually needs access to all table, so this is correct for my needs.