You are right, but...
USERNAME GRANTED_ROLE ADM DEF
OS_
------------------------------ ------------------------------ --- ---
---
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
----------------------------------------------------- --------
------------------------------------
USERNAME
VARCHAR2(30)
GRANTED_ROLE
VARCHAR2(30)
ADMIN_OPTION
VARCHAR2(3)
DEFAULT_ROLE
VARCHAR2(3)
OS_GRANTED
VARCHAR2(3)
-----Original Message-----
Sent: Thursday, October 17, 2002 1:15 PM
To: Grabowy, Chris; [EMAIL PROTECTED]
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; [EMAIL PROTECTED]
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 DEF
OS_
------------------------------ ------------------------------ --- ---
---
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 PRIVILEGE
ADM
------------------------------ ----------------------------------------
---
OPS$JNJDBA CREATE SESSION
YES
OPS$JNJDBA CREATE USER
NO
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: '[EMAIL PROTECTED]'
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 DEF
OS_
------------------------------ ------------------------------ --- ---
---
OPS$JNJDBA RESOURCE YES YES NO
11:54:52 SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production PL/SQL
Release 8.1.7.3.0 - Production
CORE 8.1.7.0.0 Production
TNS for Solaris: Version 8.1.7.3.0 - Production
NLSRTL Version 3.4.1.0.0 - Production
11:54:57 SQL> select username from all_users where username =
'JNJDSS_STG_T';
USERNAME
------------------------------
JNJDSS_STG_T
What the heck??? (shaking my head) What am I missing here?? Help?
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Grabowy, Chris
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
