Home » SQL & PL/SQL » SQL & PL/SQL » Proxy Granted But Unable to Modified a Table owned by the Proxy Account (Oracle Database 12c Enterprise Edition Release 12.2.0.1.0)
Proxy Granted But Unable to Modified a Table owned by the Proxy Account [message #674121] Fri, 04 January 2019 13:54 Go to next message
wtolentino
Messages: 303
Registered: March 2005
Senior Member
i created an account dtest to replicate a problem that our developers is having issue with proxy.

i granted the dtest proxy to account CCFAPP.
alter user ccfapp grant connect through dtest;

now i login using dtest and tried this command:
SQL> show user;
USER is "DTEST"

SQL> comment on column ccfapp.tcodes.prog_type is 'program types';
comment on column ccfapp.tcodes.prog_type is 'program types'
                         *
ERROR at line 1:
ORA-01031: insufficient privileges

please help. thank you.


Re: Proxy Granted But Unable to Modified a Table owned by the Proxy Account [message #674122 is a reply to message #674121] Fri, 04 January 2019 14:33 Go to previous messageGo to next message
John Watson
Messages: 8293
Registered: January 2010
Location: Global Village
Senior Member
Well, how did you connec? Was it with
connect dtest[ccfapp]
Re: Proxy Granted But Unable to Modified a Table owned by the Proxy Account [message #674123 is a reply to message #674121] Fri, 04 January 2019 14:37 Go to previous messageGo to next message
BlackSwan
Messages: 26720
Registered: January 2009
Location: SoCal
Senior Member
Consider to learn how to simply Read The Fine Manual yourself.

GRANT CONNECT in & of itself does NOT all you to manipulate any OBJECT; only to connect to this instance.

In Oracle EVERYTHING is forbidden; except that which is explicitly GRANTED.

https://docs.oracle.com/database/121/SQLRF/statements_4010.htm#SQLRF01109

"Prerequisites

The object about which you are adding a comment must be in your own schema or:

To add a comment to a table, view, or materialized view, you must have COMMENT ANY TABLE system privilege."
Re: Proxy Granted But Unable to Modified a Table owned by the Proxy Account [message #674124 is a reply to message #674121] Fri, 04 January 2019 15:11 Go to previous messageGo to next message
Michel Cadot
Messages: 67227
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

An example of using proxy user there.

Re: Proxy Granted But Unable to Modified a Table owned by the Proxy Account [message #674132 is a reply to message #674122] Sat, 05 January 2019 09:23 Go to previous messageGo to next message
wtolentino
Messages: 303
Registered: March 2005
Senior Member
yes i tried that

connect dtest[ccfapp]

did not work

[Updated on: Sat, 05 January 2019 09:23]

Report message to a moderator

Re: Proxy Granted But Unable to Modified a Table owned by the Proxy Account [message #674133 is a reply to message #674121] Sat, 05 January 2019 09:49 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2963
Registered: January 2010
Location: Connecticut, USA
Senior Member
wtolentino wrote on Fri, 04 January 2019 14:54
i created an account dtest to replicate a problem that our developers is having issue with proxy.

yes i tried that

connect dtest[ccfapp]

SQL> show user;
USER is "DTEST"
This doesn't add up. If you login as dtest[ccfapp] you are logging as ccfapp through dtest and therefore SHOW USER should return CCFAPP, not DTEST. For example, I create user U1 and grant it proxy access as scott:

SQL> drop user u1 cascade;

User dropped.

SQL> create user u1 identified by u1 default tablespace users;

User created.

SQL> grant create session to u1;

Grant succeeded.

SQL> grant select on emp1 to u1;

Grant succeeded.

SQL> connect u1@pdb1sol122/u1
Connected.
SQL> comment on column scott.emp1.ename is 'employee name';
comment on column scott.emp1.ename is 'employee name'
                        *
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> connect scott@pdb1sol122
Enter password: *****
Connected.
SQL> alter user scott grant connect through u1;

User altered.

SQL> connect u1[scott]@pdb1sol122/u1
Connected.
SQL> show user
USER is "SCOTT"
SQL> comment on column scott.emp1.ename is 'employee name';

Comment created.

SQL> 

As you can see, SHOW USER returns SCOTT, not U1.

SY.
Re: Proxy Granted But Unable to Modified a Table owned by the Proxy Account [message #674134 is a reply to message #674132] Sat, 05 January 2019 10:45 Go to previous messageGo to next message
Michel Cadot
Messages: 67227
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

wtolentino wrote on Sat, 05 January 2019 16:23
yes i tried that

connect dtest[ccfapp]

did not work

This is not true, see the example in the link I posted.

[Updated on: Sun, 06 January 2019 01:50]

Report message to a moderator

Re: Proxy Granted But Unable to Modified a Table owned by the Proxy Account [message #674135 is a reply to message #674134] Sat, 05 January 2019 20:01 Go to previous messageGo to next message
wtolentino
Messages: 303
Registered: March 2005
Senior Member
found out that ccfapp account was locked. all is working now when unlocked it. thanks so much all.
Re: Proxy Granted But Unable to Modified a Table owned by the Proxy Account [message #674136 is a reply to message #674135] Sun, 06 January 2019 01:54 Go to previous messageGo to next message
Michel Cadot
Messages: 67227
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

In this case you should have another message:
SQL> create user act1 identified by act1;

User created.

SQL> grant create session to act1;

Grant succeeded.

SQL> create user proxyone identified by proxy1;

User created.

SQL> grant create session to proxyone;

Grant succeeded.

SQL> alter user act1 grant connect through proxyone ;

User altered.

SQL> alter user act1 account lock;

User altered.

SQL> connect proxyone[act1]/proxy1
ERROR:
ORA-28000: the account is locked


Warning: You are no longer connected to ORACLE.
Re: Proxy Granted But Unable to Modified a Table owned by the Proxy Account [message #674156 is a reply to message #674136] Mon, 07 January 2019 09:04 Go to previous message
wtolentino
Messages: 303
Registered: March 2005
Senior Member
right it is shows the error ORA-28000. i didn't see the error at first (my mistake) and maybe i was in hurry trying to resolve the issue. thank you all.

[Updated on: Mon, 07 January 2019 09:04]

Report message to a moderator

Previous Topic: What does @! mean in SQL?
Next Topic: wm_concat to listagg
Goto Forum:
  


Current Time: Sun Jul 05 02:04:59 CDT 2020