grant permissions on 11.2.0.3, dbms_scheduler permission are not granted Options

[vishalb jayce]

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

[burleson]

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 . . . .

[vishalb jayce]

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

[burleson]

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?

[vishalb jayce]

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

[burleson]

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!

[vishalb jayce]

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