0
326
Question Javier Llobet · Jun 11, 2019

Alter table with %ALTER_TABLE doesnt works

#Caché #SQL

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 SQL Opción Concesión Concedido por  
%CREATE_FUNCTION Directo
%DROP_FUNCTION Directo
%CREATE_METHOD Directo
%DROP_METHOD Directo
%CREATE_PROCEDURE Directo
%DROP_PROCEDURE Directo
%CREATE_QUERY Directo
%DROP_QUERY Directo
%CREATE_TABLE Directo
%ALTER_TABLE Directo
%DROP_TABLE Directo
%CREATE_VIEW Directo
%ALTER_VIEW Directo
%DROP_VIEW Directo
%CREATE_TRIGGER Directo
%DROP_TRIGGER Directo
%NOCHECK Directo
%NOTRIGGER Directo
%NOINDEX Directo
%NOLOCK Directo

What i doing wrong? 

Discussion (4)0
Log in or sign up to continue

Comments

Eduard Lebedyuk · Jun 11, 2019

You need to check access to table.

Try

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

Replace <Table> with your table.

0
Javier Llobet  Jun 11, 2019 to Eduard Lebedyuk

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

0
Eduard Lebedyuk  Jun 12, 2019 to Javier Llobet

What error are you executing?

What error are you getting?

0
Javier Llobet  Jun 12, 2019 to Eduard Lebedyuk

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