Table access

#1
HI Team,

We have Progress Database which is running on 9.1e version.
Today we have received incident where user dont have access to vo_mstr table while they are connecting from SQL explorer tool they facing "Access Denied error".
We checked using below command they dont have access to table and we have provided grant access to vo_mstr;
But before giving access we run select command and they have access table it worked fine but when we ran this command select * from sysprogress.systabauth where TBL='<vo_mstr>'; it showing as they dont have access.

SQLExplorer>select * from sysprogress.systabauth where TBL='<vo_mstr>';
GRANTOR GRANTEE TBLOWNER TBL INS DEL UPD SEL EXE NDX ALT REF
-------------------------------- -------------------------------- -------------------------------- -------------------------------- --- --- --- --- --- --- --- ---

When we checked in Progress Editor table has full access permissions for all users.
I'm confused where user informed they have already access on this table weekly once 1 job is scheduled.

Due to below full access to table are their jobs are working fine?
But why they are not able to connect from SQL explorer when they have select command is working?
How to check user has proper permissions on table from SQL explorer tool ?

lqqqqqqqqqqqqqqqqqqqqqqqqqq Table Name: "vo_mstr" qqqqqqqqqqqqqqqqqqqqqqqqqqqk
x Can-Read: * x
x Can-Write: * x
x Can-Create: * x
x Can-Delete: * x
x Can-Dump: * x
x Can-Load: * x
x x
x Examples: x
x * - All users (login Ids) are allowed access. x
x <user>,<user>,etc. - Only these users have access. x
x !<user>,!<user>,* - All except these users have access. x
x acct* - Only users that begin with "acct" allowed. x
x Do not use spaces in the string (they will be taken literally).

$DLC/bin/sqlexp -db slc -S tslc -H p5lp4 -user biuser -password
PROGRESS Version 9.1E as of Tue Oct 12 17:19:22 EDT 2004

SQLExplorer>select count(*) from pub.vo_mstr;
count(*)
-----------
425994

SQLExplorer>select * from pub.vo_mstr;
0000001 P010 8201 1766.6100000000 1998-12-18 1998-12-18 0 1999-01-07 0.0000000000 0.0000000000 0.0000000000 0.0000000000 0 USD 1.0000000000 0 3600 0.0000000000 0.0000000000 0 0;0;0 1 y550ekn 1 0.00000


SQLExplorer>select * from sysprogress.sysdbauth;
GRANTEE DBA_ACC RES_ACC
-------------------------------- ------- -------
BIUSER y
PGRESDBA y y
SYSPROGRESS y y
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
#2
select * from sysprogress.systabauth where TBL='<vo_mstr>';
Why do you have the table name within angle brackets?

Due to below full access to table are their jobs are working fine?
But why they are not able to connect from SQL explorer when they have select command is working?
How to check user has proper permissions on table from SQL explorer tool ?

lqqqqqqqqqqqqqqqqqqqqqqqqqq Table Name: "vo_mstr" qqqqqqqqqqqqqqqqqqqqqqqqqqqk
x Can-Read: * x
x Can-Write: * x
x Can-Create: * x
x Can-Delete: * x
x Can-Dump: * x
x Can-Load: * x
The CAN-* permissions are fields in PUB._file. They apply to 4GL clients only, not SQL clients. Table-level permissions for SQL users are in sysprogress.systabauth.

select * from sysprogress.sysdbauth;
This shows you any users who have DBA privilege. These users inherit all privileges on current and new tables. SQL users who are not DBAs start with no table-level privileges and must be GRANTed those privileges by a privileged user. This process must be repeated after schema additions.

For example, if a non-DBA user has been granted SELECT privilege on all tables, and then you add five new tables to the schema, that user will not be able to access those new tables until they have been granted SELECT on the new ones.

How to check user has proper permissions on table from SQL explorer tool ?
Try this:
Code:
SELECT
sysprogress.Systabauth.Grantor,
sysprogress.Systabauth.Grantee,
sysprogress.Systabauth.Tbl,
sysprogress.Systabauth.Ins,
sysprogress.Systabauth.Del,
sysprogress.Systabauth.Upd,
sysprogress.Systabauth.Sel,
sysprogress.Systabauth.Exe,
sysprogress.Systabauth.Ndx,
sysprogress.Systabauth.Alt,
sysprogress.Systabauth.Ref
FROM sysprogress.Systabauth
WHERE (sysprogress.Systabauth.Tblowner='PUB') AND (sysprogress.Systabauth.Tbl<'ZZZ')
ORDER BY sysprogress.Systabauth.Tbl;
If you have a lot of users, you might want to filter that further by Grantee.
 
#3
HI Rob,

thanks for information..
i'm able to generate your query and it helped me alot.

After that we have provided access for below table but when we run below select command it showing an error.
As per progress knowledge base it stating "to avoid errors from data in a field exceeding its SQL width".
How to increase this max length from db tool.

1533151819579.png
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
#4
Every field in every table has a corresponding record in PUB._field. While character fields in tables in the PUB schema can store data of arbitrary length, up to a limit (a little less than 32,000 bytes), SQL clients have a different expectation. They expect that the amount of data returned in a field in a query will not exceed the field's "SQL width". This width is stored in _field._width and it defaults to two times the default ABL display width. For example, if a field's default display format is "x(8)", its SQL width will default to 16. If one of the records returned in a query of that table has a value in that field that exceeds 16 bytes in length, the SQL client will throw the error about exceeding max length or precision.

The value of the SQL width can be changed. One way to do that is to run the dbtool utility. It will scan the database, using the options you select, and read the lengths of all the character fields in all the records, comparing them to the fields' SQL widths. It can be configured to just report on problems, or to report and fix them (i.e. adjust the SQL width accordingly).

The syntax is dbtool <dbname>. The option you want is option 2 ("SQL Width Scan w/Fix Option"). Example:
Code:
                     DATABASE TOOLS MENU - 11.7
                     ---------------------------

                 1. SQL Width & Date Scan w/Report Option
                 2. SQL Width Scan w/Fix Option
                 3. Record Validation
                 4. Record Version Validation
                 5. Read or Validate Database Blocks
                 6. Record Fixup
                 7. Schema Validation
                 8. Disable Object Locking Protection
                 9. Enable/Disable File Logging
                10. Index Space Validation
                11. Index Space Fixup
                12. Schema Fixup

                Q. Quit

                Choice: 2

 The option will process all records including template records in the specified range.

        <connect>:    (0=single-user 1=self-service >1=#threads)? 1

        Padding % above current max: 0

        <table>:      (Table number or all)? all

        <area>:       (Area number or all)? all

        <display>:    (verbose level 0-4)? 1
You can select the options that make sense for you. And while this is an interactive example, you can also run it from a script; search the KB for "dbtool script".
 
#5
Thanks Rob..

Error we are receiving on below highlighted filed.
Before doing it we will test in test server.
Please suggest what value we can define and also can we do it when users using this table.

1533215378813.png
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
#6
Please suggest what value we can define and also can we do it when users using this table.
Again, we aren't talking about the "Format"; that doesn't need to change. We are talking about the SQL width (_field._width). I don't know what value you need for _width for code_value. That depends on the maximum length of the values in that field in code_mstr.

If you run dbtool against that table, it will scan the values and adjust _width as appropriate, for all character fields in the table. You can do this online while users are connected, however be advised that it does read the entire table. If it is a very large table that could be a lot of I/O. I suggest you try it out in a non-production copy of the database to get a feel for how to do it, how long it takes, etc.
 
Top