Skip to content
Advertisement

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

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;

Edit: Solution Created 3 views that I needed. GRANT-ed the new user SELECT and UPDATE on 2 of the views and SELET on the 3rd. For the new user I had to use the chema to refer to the views: chema.view

Advertisement

Answer

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.

Advertisement