How to give access to columns to new user when current user does not see the tables?-CodePudding

Using SYSTEM user I create a new user. After that I try to give this user access to specific columns only in 1 table, but get the error that this table does not exist. Meaning that SYSTEM user does not have access to this table. Unfortunately, the user that has access to the normal production tables cannot manage user privileges and access. What are my options?

CREATE USER test1 IDENTIFIED BY 123456;
GRANT UPDATE (extinvno, invoiceno) ON invoice TO test1;

CodePudding user response：

If SYSTEM doesn't own the table then you need to specify who does; for example if the table was in the HR schema you would do:

GRANT UPDATE (extinvno, invoiceno) ON HR.invoice TO test1;

... using the real owning schema name, of course.

It isn't that SYSTEM doesn't have access to the table; it's that by default it's looking for SYSTEM.invoice, which doesn't exist.

The table owner could also grant the update privilege to test1.