Page 1 of 1

noAccessToProcedureBodies Keyword Exception

Posted: Sat 09 May 2015 05:21
by idem84
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!

Re: noAccessToProcedureBodies Keyword Exception

Posted: Mon 11 May 2015 09:05
by Shalex
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.

Re: noAccessToProcedureBodies Keyword Exception

Posted: Mon 11 May 2015 13:30
by idem84
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!

Re: noAccessToProcedureBodies Keyword Exception

Posted: Wed 13 May 2015 12:12
by Shalex
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);
            }
        }
    }

Re: noAccessToProcedureBodies Keyword Exception

Posted: Wed 13 May 2015 13:39
by idem84
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!