All the statements are issued with the same user?
Usually this happens when trying to revoke a table-level privilege that your account name did not grant.
To find the correct grantee use:
SELECT a.grantee, a.grantor
FROM systabauth a, systables t
WHERE a.tabid = t.tabid
AND UPPER(t.tabname) = 'TEST';
Then it's possible to issue:
REVOKE INSERT ON TEST FROM 'test_user' AS '<GRANTEE>';
The other possibility that I didn’t mention, but @chris311 figure it out, is that you cannot revoke privileges from yourself.
What is happening “behind it”, take the next example, a database named chris311, owned by chris, bear in mind that I'm using the informix user:
[infx1210@tardis ~]$ id
uid=501(informix) gid=501(informix) groups=501(informix)
[infx1210@tardis ~]$ dbaccess chris311 -
Database selected.
> SELECT name, owner
> FROM sysmaster:sysdatabases
> WHERE name = DBINFO('dbname') ;
name chris311
owner chris
1 row(s) retrieved.
>
Both chris and informix have the DBA database-level privilege, and ricardo was granted the CONNECT privilege:
> SELECT username, usertype
> FROM sysusers;
username usertype
chris D
informix D
ricardo C
3 row(s) retrieved.
>
There is a table, tab1, owned by chris that ricardo was granted, by chris, the ALL table-level privilege:
> SELECT t.tabname, t.owner, a.grantee, a.tabauth, a.grantor
> FROM systabauth a, systables t
> WHERE a.tabid = t.tabid
> AND t.tabname= 'tab1';
tabname tab1
owner chris
grantee ricardo
tabauth su-idxar-
grantor chris
1 row(s) retrieved.
>
Then if informix whant's to revoke the INSERT privilege it must use the AS clause to specify chris as the revoker:
> REVOKE INSERT ON tab1 FROM ricardo;
580: Cannot revoke permission.
111: ISAM error: no record found.
Error in line 1
Near character position 33
> REVOKE INSERT ON tab1 FROM ricardo AS chris;
Permission revoked.
> SELECT t.tabname, t.owner, a.grantee, a.tabauth, a.grantor
> FROM systabauth a, systables t
> WHERE a.tabid = t.tabid
> AND t.tabname = 'tab1';
tabname tab1
owner chris
grantee ricardo
tabauth su--dxar-
grantor chris
1 row(s) retrieved.
>
If he tries to revoke the INSERT privilege from himself an error will return also:
> REVOKE INSERT ON tab1 FROM informix;
580: Cannot revoke permission.
111: ISAM error: no record found.
Error in line 1
Near character position 34
>
Now if we see the meaning of the 580 error we get:
[infx1210@tardis ~]$ finderr 580
-580 Cannot revoke permission.
This REVOKE statement cannot be carried out. Either it revokes a
database-level privilege, but you are not a Database Administrator in
this database, or it revokes a table-level privilege that your account
name did not grant. Review the privilege and the user names in the
statement to ensure that they are correct. To summarize the table-level
privileges you have granted, query systabauth as follows:
SELECT A.grantee, T.tabname FROM systabauth A, systables T
WHERE A.grantor = USER AND A.tabid = T.tabid
[infx1210@tardis ~]$
It doesn't says anything about revoking privileges from himself, but the documentations mentions it. Also, if we think about the 111: ISAM error: no record found. and associate it to the fact that the DBA doesn´t appear on the systabauth it makes sence, kind of.
The grants doesn´t return an error/warning because the DBA already have the privileges, the revoke returns it because the action didn't take effect.
Now let's take the DBA role from chris, let's do it twice:
> REVOKE DBA FROM chris;
Permission revoked.
> REVOKE DBA FROM chris;
Permission revoked.
> SELECT username, usertype
> FROM sysusers;
username usertype
chris C
informix D
ricardo C
3 row(s) retrieved.
> SELECT t.tabname, t.owner, a.grantee, a.tabauth, a.grantor
> FROM systabauth a, systables t
> WHERE a.tabid = t.tabid
> AND t.tabname= 'tab1';
tabname tab1
owner chris
grantee ricardo
tabauth su--dxar-
grantor chris
1 row(s) retrieved.
>
Again, the second REVOKE didn't return an error/warning because it was in effect. The user still doesn´t appear on the systabauth table.
But what table-level privileges it has?
[infx1210@tardis ~]$ dbaccess chris311 -
Database selected.
> INSERT INTO tab1 VALUES(1);
1 row(s) inserted.
> SELECT * FROM tab1;
col1
1
1 row(s) retrieved.
> DROP TABLE tab1;
Table dropped.
>
He isn't a DBA but he is the owner.
