Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> grant permissions on 11.2.0.3, dbms_scheduler permission are not granted
vishalb jayce
post Apr 20 2012, 08:19 AM
Post #1


Member
**

Group: Members
Posts: 29
Joined: 20-April 12
From: Hyderabad India
Member No.: 47,109



sad.gif Hi team,

i wanted to grant dbms_scheduler permission to the system user in oracle 11.2.0.3,
but it is showing insufficient privileges, but my System has Sysdba rights.

can you please help me..
PFB......

SQL> SELECT * FROM v$pwfile_users WHERE sysdba='TRUE';

USERNAME SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS TRUE TRUE FALSE

SQL> conn system as sysdba;
Enter password:
Connected.
SQL> GRANT EXECUTE ON DBMS_SCHEDULER TO vishal;

Grant succeeded.


SQL> revoke execute on dbms_scheduler from vishal;

Revoke succeeded.

SQL> conn system
Enter password:
Connected.
SQL> GRANT EXECUTE ON DBMS_SCHEDULER TO vishal;
GRANT EXECUTE ON DBMS_SCHEDULER TO vishal
*
ERROR at line 1:
ORA-01031: insufficient privileges

while i ran this query

"
SQL> select * from session_privs;"

for system user i have 202 rows of different privileges,
but the same query for sysdba has 208 rows...


-jayce
Go to the top of the page
 
+Quote Post
burleson
post Apr 20 2012, 08:28 AM
Post #2


Advanced Member
***

Group: Members
Posts: 11,722
Joined: 26-January 04
Member No.: 13



Hi Jayce,

>> i wanted to grant dbms_scheduler permission to the system user in oracle 11.2.0.3

But SYS already has the DBA role, which includes everything!

***************************************
>> ORA-01031: insufficient privileges

Please read this: http://www.dba-oracle.com/t_ora_01031.htm


***************************************

>> for system user i have 202 rows of different privileges, but the same query for sysdba has 208 rows


See what the other 6 are . . . .


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
vishalb jayce
post Apr 20 2012, 08:38 AM
Post #3


Member
**

Group: Members
Posts: 29
Joined: 20-April 12
From: Hyderabad India
Member No.: 47,109



QUOTE (burleson @ Apr 20 2012, 09:28 AM) *
Hi Jayce,

>> i wanted to grant dbms_scheduler permission to the system user in oracle 11.2.0.3

But SYS already has the DBA role, which includes everything!

***************************************
>> ORA-01031: insufficient privileges

Please read this: http://www.dba-oracle.com/t_ora_01031.htm


***************************************

>> for system user i have 202 rows of different privileges, but the same query for sysdba has 208 rows


See what the other 6 are . . . .



the other 6 are

1)sysdba
2)sysoper
3)exempt.access.policy
4)exempt.identity.policy
5)alter.public.database.link
6)alter.database.link

i also observered the difference while i ran these queries..

SQL> SELECT dbms_scheduler.check_sys_privs
2 FROM dual;

CHECK_SYS_PRIVS
---------------
1048543

SQL> conn system as sysdba
Enter password:
Connected.
SQL> SELECT dbms_scheduler.check_sys_privs from dual;

CHECK_SYS_PRIVS
---------------
2147483647

please let me know if any info is needed..


http://www.digipedia.pl/usenet/thread/11551/16602/



-jayce
Go to the top of the page
 
+Quote Post
burleson
post Apr 20 2012, 05:25 PM
Post #4


Advanced Member
***

Group: Members
Posts: 11,722
Joined: 26-January 04
Member No.: 13



Hi Jayce,

OK, you have established this. Correct me if I'm mistaken:

- SYSTEM user gets an ORA-01031 on granting the dbms_scheduler privilege

- SYS has these six additional privileges over SYSTEM:

1)sysdba
2)sysoper
3)exempt.access.policy
4)exempt.identity.policy
5)alter.public.database.link
6)alter.database.link

Can you grant sysdba to your SYSTEM user?


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
vishalb jayce
post Apr 21 2012, 01:54 PM
Post #5


Member
**

Group: Members
Posts: 29
Joined: 20-April 12
From: Hyderabad India
Member No.: 47,109



QUOTE (burleson @ Apr 20 2012, 06:25 PM) *
Hi Jayce,

OK, you have established this. Correct me if I'm mistaken:

- SYSTEM user gets an ORA-01031 on granting the dbms_scheduler privilege

- SYS has these six additional privileges over SYSTEM:

1)sysdba
2)sysoper
3)exempt.access.policy
4)exempt.identity.policy
5)alter.public.database.link
6)alter.database.link

Can you grant sysdba to your SYSTEM user?





Hi ,

i have granted sysdba to system ,
but still throwing the same error.

i have see an article saying that in oracle 11.2.0.3, there is no effect on granting privileges.

FYI..

http://comments.gmane.org/gmane.comp.db.oracle.general/16618

-jayce
Go to the top of the page
 
+Quote Post
burleson
post Apr 22 2012, 08:43 AM
Post #6


Advanced Member
***

Group: Members
Posts: 11,722
Joined: 26-January 04
Member No.: 13



Hi Jayce,

>> i have granted sysdba to system , but still throwing the same error.

This must be a bug, open a service request on MOSC:

http://support.oracle.com

Please advise on the bug number!


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
vishalb jayce
post May 17 2012, 02:48 AM
Post #7


Member
**

Group: Members
Posts: 29
Joined: 20-April 12
From: Hyderabad India
Member No.: 47,109



QUOTE (burleson @ Apr 22 2012, 09:43 AM) *
Hi Jayce,

>> i have granted sysdba to system , but still throwing the same error.

This must be a bug, open a service request on MOSC:

http://support.oracle.com

Please advise on the bug number!


Hi burleson

we have logged the bug in oracle, they have given the solution as below :

Kindly apply this patch 13555974 on top of 11.2.0.3 or apply
latest patch 11.2.0.3 bundle patch 5 #13885389


This has worked for us.


Thanks,
vish
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 26th November 2014 - 11:14 PM