New post
Question
· Jun 11, 2019

Alter table with %ALTER_TABLE doesnt works

#SQL #Caché

I tryinfg to alter a column definition data type with a user that seems that doesnt have priviledges, but the user has this priviliedge. 

Documentation: 

Privileges and Locking

The ALTER TABLE command is a privileged operation. Prior to using ALTER TABLE it is necessary for your process to have either %ALTER_TABLE administrative privilege or an %ALTER object privilege for the specified table. Failing to do so results in an SQLCODE -99 error (Privilege Violation). You can determine if the current user has %ALTER privilege by invoking the %CHECKPRIV command. You can determine if a specified user has %ALTER privilege by invoking the $SYSTEM.SQL.CheckPriv() method. You can use the GRANT command to assign %ALTER_TABLE or %ALTER privileges, if you hold appropriate granting privileges. In embedded SQL, you can use the $SYSTEM.Security.Login() method to log in as a user with appropriate privileges:

 

I execute this statement and returns "Have priviledge"

&sql(%CHECKPRIV %CREATE_TABLE,%ALTER_TABLE,%DROP_TABLE)  IF SQLCODE=0 {WRITE "Have privileges"}  ELSEIF SQLCODE=100 {WRITE "Do not have one or more privileges"}  ELSE {WRITE "Unexpected SQLCODE error: ",SQLCODE}

The user is the owner of the table, and has all the priviledges:

 

Privilegio SQLOpción ConcesiónConcedido por 
%CREATE_FUNCTIONDirecto
%DROP_FUNCTIONDirecto
%CREATE_METHODDirecto
%DROP_METHODDirecto
%CREATE_PROCEDUREDirecto
%DROP_PROCEDUREDirecto
%CREATE_QUERYDirecto
%DROP_QUERYDirecto
%CREATE_TABLEDirecto
%ALTER_TABLEDirecto
%DROP_TABLEDirecto
%CREATE_VIEWDirecto
%ALTER_VIEWDirecto
%DROP_VIEWDirecto
%CREATE_TRIGGERDirecto
%DROP_TRIGGERDirecto
%NOCHECKDirecto
%NOTRIGGERDirecto
%NOINDEXDirecto
%NOLOCKDirecto

 

What i doing wrong? 

Discussion (4)0
Log in or sign up to continue
Javier Llobet · Jun 12, 2019

Im executing inside a sql procedure the next sentence:

Set columnLength = ""
&SQL(SELECT character_maximum_length INTO :columnLength
     FROM information_schema.columns
     WHERE table_name = 'MYTABLE'
     AND   column_name = 'MYCOLUMN'
     AND   table_schema = 'MYSCHEMA')
If ((SQLCODE = 0) && (columnLength '= 32000))
{
    &SQL(ALTER TABLE MYTABLE ALTER COLUMN MYCOLUMN VARCHAR(32000))
    IF ((SQLCODE '= 0) && (SQLCODE '= 100))
    {
        SET %sqlcontext.%SQLCODE = SQLCODE
        SET %sqlcontext.%Message=%msg
        Quit
    }
}

And im getting the error:

%msg: <Insufficient Privilege For Operation at location: DDL ALTER TABLE Modify Column Datatype>]

And i try with NO Data and the same error. 

I added a %All rol to my user connected and then it's possible to alter the table. So i need one permission or priviledge that im missing....

0 0