| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: ORA-01031: insufficient privileges
You are right, but...
USERNAME GRANTED_ROLE ADM DEFOS_
OPS$JNJDBA CONNECT YES YES NO
OPS$JNJDBA RESOURCE YES YES NO
^^^ (with
ADMIN option = YES)
The RESOURCE role with ADMIN option was granted to OPS$JNJDBA. That's my point, it should work, it doesn't.
Here's the description of USER_ROLE_PRIVS...
13:05:48 SQL> desc user_role_privs
Name Null? Type ----------------------------------------------------- --------
------------------------------------
Chris,
According to the docs:
Prerequisites
To grant a system privilege, you must either have been granted the
system privilege with the ADMIN OPTION or have been granted the GRANT
ANY PRIVILEGE system privilege.
To grant a role, you must either have been granted the role with the ADMIN OPTION or have been granted the GRANT ANY ROLE system privilege, or you must have created the role.
What you are trying to do is to allow the user priv's to grant RESOURCE
to another user. The user doing the granting does not have the priv to
do this
- right? I think RESOURCE is a ROLE, so you could try granting GRANT
ANY ROLE or GRANT RESOURCE WITH ADMIN option to OPS$JNJDBA.
I just tried this and it works.
Hope this helps.
Tom Mercadante
Oracle Certified Professional
-----Original Message-----
Sent: Thursday, October 17, 2002 1:08 PM
To: Mercadante, Thomas F; ORACLE-L_at_fatcity.com
It's a developer DBA account, and the DBA role has too much, so it's only...
12:12:23 SQL> select * from user_role_privs;
USERNAME GRANTED_ROLE ADM DEFOS_
OPS$JNJDBA CONNECT YES YES NO OPS$JNJDBA RESOURCE YES YES NO OPS$JNJDBA SELECT_CATALOG_ROLE NO YES NO
Elapsed: 00:00:00.01
12:56:32 SQL> select * from user_sys_privs;
USERNAME PRIVILEGEADM
OPS$JNJDBA CREATE SESSION YES OPS$JNJDBA CREATE USERNO
Elapsed: 00:00:00.00
13:02:02 SQL> sho user
USER is "OPS$JNJDBA"
13:03:06 SQL>
-----Original Message-----
Sent: Thursday, October 17, 2002 12:55 PM
To: 'ORACLE-L_at_fatcity.com'
Cc: Grabowy, Chris
Chris,
Does the OPS$JNJDBA account have DBA privs?
Tom Mercadante
Oracle Certified Professional
-----Original Message-----
Sent: Thursday, October 17, 2002 1:28 PM
To: Multiple recipients of list ORACLE-L
11:54:32 SQL> GRANT RESOURCE TO JNJDSS_STG_T;
GRANT RESOURCE TO JNJDSS_STG_T
*
ERROR at line 1:
ORA-01031: insufficient privileges
11:54:39 SQL> sho user
USER is "OPS$JNJDBA"
11:54:45 SQL> select * from user_role_privs where granted_role =
'RESOURCE';
USERNAME GRANTED_ROLE ADM DEFOS_
OPS$JNJDBA RESOURCE YES YES NO
11:54:52 SQL> select * from v$version;
BANNER
11:54:57 SQL> select username from all_users where username = 'JNJDSS_STG_T'; USERNAME
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Grabowy, Chris INET: cgrabowy_at_fcg.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting servicesReceived on Thu Oct 17 2002 - 13:25:33 CDT
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Grabowy, Chris INET: cgrabowy_at_fcg.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
 |
 |