noAccessToProcedureBodies Keyword Exception

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for universal data access
Post Reply
idem84
Posts: 50
Joined: Thu 04 Apr 2013 19:12

noAccessToProcedureBodies Keyword Exception

Post by idem84 » Sat 09 May 2015 05:21

Hi!
I had to try to use noAccessToProcedureBodies = true in mysql connection string, because trying to control first exception:
"Can not the describe procedure <here spname>. You do not have enough privileges to get object metadata."

Second exception try:
Keyword is not supported: noAccessToProcedureBodies


Thanks in advance!

Shalex
Site Admin
Posts: 8245
Joined: Thu 14 Aug 2008 12:44

Re: noAccessToProcedureBodies Keyword Exception

Post by Shalex » Mon 11 May 2015 09:05

idem84 wrote:Keyword is not supported: noAccessToProcedureBodies
What MySQL provider are you using via interface of dotConnect Universal?
By default, dotConnect Universal Professional is shipped with the Standard edition of dotConnect for MySQL which does not include the noAccessToProcedureBodies connection string parameter: http://www.devart.com/dotconnect/mysql/ ... tring.html.
idem84 wrote:"Can not the describe procedure <here spname>. You do not have enough privileges to get object metadata."
Please send us a small test project with the corresponding DDL/DML script to reproduce the issue in our environment. Also specify the full call stack of the error.

idem84
Posts: 50
Joined: Thu 04 Apr 2013 19:12

Re: noAccessToProcedureBodies Keyword Exception

Post by idem84 » Mon 11 May 2015 13:30

Im using last version licensed dotconnect Universal (with mysql provider).
The code:
uniConnectionBuilder.Add("noAccessToProcedureBodies", "true");

I have dotconnect for MySql Pro too, but a dont have idea how to integrate this keyword in connectionstring, i cant find that documentation("noAccessToProcedureBodies")

==============================
"Can not the describe procedure <here spname>. You do not have enough privileges to get object metadata."
This issue happens when execute an SP that returns any resulset.
A temporary workaround solution(but not for me for security DB reasons) is to modify Db user permissions (grant select to mysql.proc table)

Thanks in advance!

Shalex
Site Admin
Posts: 8245
Joined: Thu 14 Aug 2008 12:44

Re: noAccessToProcedureBodies Keyword Exception

Post by Shalex » Wed 13 May 2015 12:12

idem84 wrote:Im using last version licensed dotconnect Universal (with mysql provider).
The code:
uniConnectionBuilder.Add("noAccessToProcedureBodies", "true");

I have dotconnect for MySql Pro too, but a dont have idea how to integrate this keyword in connectionstring, i cant find that documentation("noAccessToProcedureBodies")
noAccessToProcedureBodies is an option of the JDBC provider. It cannot be applied to dotConnect for MySQL.
idem84 wrote: "Can not the describe procedure <here spname>. You do not have enough privileges to get object metadata."
This issue happens when execute an SP that returns any resulset.
A temporary workaround solution(but not for me for security DB reasons) is to modify Db user permissions (grant select to mysql.proc table)
Please tell us how we should modify the following sample (it works in our environment) to reproduce the error you have encountered:

Code: Select all

-- root connected
CREATE TABLE DEPT (
  DEPTNO INT PRIMARY KEY,
  DNAME VARCHAR(14),
  LOC VARCHAR(13)
);
INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');

CREATE PROCEDURE TEST() 
BEGIN 
SELECT * from dept Limit 1; 
END;
/
CREATE USER 'idem84' IDENTIFIED BY 'idem84';
GRANT EXECUTE ON PROCEDURE TEST TO idem84@'%';

Code: Select all

    using (var conn = new Devart.Data.Universal.UniConnection())
    {
        conn.ConnectionString = "provider=mysql;server=db;port=3311;uid=idem84;pwd=idem84;database=alexsh;";
        conn.Open();
        var cmd = conn.CreateCommand();
        cmd.CommandText = "TEST";
        cmd.CommandType = System.Data.CommandType.StoredProcedure;
        using (var reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                object[] obj = new object[reader.FieldCount];
                reader.GetValues(obj);
            }
        }
    }

idem84
Posts: 50
Joined: Thu 04 Apr 2013 19:12

Re: noAccessToProcedureBodies Keyword Exception

Post by idem84 » Wed 13 May 2015 13:39

Nevermind, i got it.
I've noticed that when using "ParameterCheck = true" permission is required to table "mysql.proc".
ParameterCheck: It's a nice feature, so I found a way to grant permissions only in some columns of the table (no critical columns like sp body... etc):

It would be good if the documentation had this information (when "ParameterCheck = true"):

Code: Select all

GRANT SELECT (db,name,type,param_list,returns) ON mysql.proc TO 'user'@'host';
Solved!
Thanks in advance!

Post Reply