insufficient priveleges when loggen in as sysdba

[ronnie]

Hi,
I logged in as sys as sysdba from DBA studio.

I wanted to create a Role companyuser which had execute priveleges on procedures/packages and functions on the company schema.

I was able to select everything i needed but when i hit the apply button it said that you have insufficient priveleges. Isnt the sys as sysdba supposed to have all the priveleges.

Please suggest
Ronnie

[molonede]

It kind of does, you need to assign privileges as the company user. So login as the company user and then assign the privileges to the role.

[gandolf989]

You can try logging in as internal/oracle.
You can also try adding a few privileges at a time to see which ones are giving you the error.
Or you can click on the show sql button and execute the commands in sql plus as the schema owner.

[ronnie]

Logging in as internal user does not help. It still gives the same error.

I am really surprised why the sys as sysdba which is a SUPERUSER cannot perform this operation. why in the world does somebody has to login as the user who created the objects to assign object priveleges. If there are 50 users and 1000 objects each then the DBA will have a very tough time logging in as the users who created it to do this job.

Then why the DBA account :-)

Ronnie

[ronnie]

Still waiting for an answer.

Please suggest.

Ronnie

[amar]

It has already been suggested that To grant an object privilege, you must own the object or the owner of the object must have granted you the object privileges with the GRANT OPTION. This rule applies to users with the DBA role.

This is the RULE by which Oracle plays. You CAN'T change it (not till 9i atleast).

Here is a suggestion though:

create a script like the following -

set feedback off
..... all other off's

spool gr.sql
select 'grant all on '||tname||' to sys with grant option;' from tab where tabtype in ('TABLE','VIEW');
spool off

Execute this script from each individual user login owing schema objects and then run the gr.sql while still being logged in as the same user. You may substitute SYS with any other user. After you are done executing this script for all users, you would be able to perform the operation from a single user account.

Hope this helps

As for the question "Why the DBA account ?" - well do I need to give an answer to that ?

-amar

[jrpm]

There are some privileges that can only be granted if the cartridge/add on /feature that uses it is installed.

Doing a few privs at a time to skip the one's that are bad (until you find the one's that don't work) is the best way.