Alter table with %ALTER_TABLE doesnt works
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?
You need to check access to table.
Try
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.
What error are you executing?
What error are you getting?
Im executing inside a sql procedure the next sentence:
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....