Alter table with %ALTER_TABLE doesnt works

Primary tabs

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. 


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 


What i doing wrong? 



  • 0
  • 0
  • 98
  • 3
  • 1


You need to check access to table.


write $SYSTEM.SQL.CheckPriv($username,"1,<TABLE>","a")

Replace <Table> with your table.

the result is true for this command, so i have access but doesnt work.

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))
    IF ((SQLCODE '= 0) && (SQLCODE '= 100))
        SET %sqlcontext.%SQLCODE = SQLCODE
        SET %sqlcontext.%Message=%msg

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....