Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Need to grant privileges to object but says ORA-01031: insufficient privileges even logged in as sysdba

Posted on 2009-02-20
15
Medium Priority
?
820 Views
Last Modified: 2012-05-06
Need to grant privileges to object but says ORA-01031: insufficient privileges even logged in as sysdba... please help... I don't have the password for the account that owns the schema/objects.
0
Comment
Question by:la_colibri
15 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 23693763
what version?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 23693779
and what objects are you trying to grant privileges to?

if the sys.x$ views you can't.  Those are "special" and can't have any grants on them.  Only SYS can query those.
0
 

Author Comment

by:la_colibri
ID: 23693861
It's Oracle 8i.... the objects are on the Deltek schema
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 23693871
Which schema owns the objects?  Is it an Oracle-supplied schema, or a user-created schema?
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 1000 total points
ID: 23693913
1) select password from dba_users where username = 'DELTEK';  
-- save this result it will be a 16 character Hex value, it it's not, don't proceed

2) alter user DELTEK identified by newpa55w0rd;

3) connect DELTEX/newpa55w0rd@yourdb

4) grant xxxx on yyyyyy to zzzz; -- repeat as needed for all grants and objects

5) login as privileged user again

6) alter user DELTEX identified by values 'your_saved_password_from_step_1';

0
 
LVL 48

Assisted Solution

by:schwertner
schwertner earned 1000 total points
ID: 23693917
If you have no password to some account but have sysdba account you can log as that user:
1. Copy/paste the password from dba_users view
2. Change the password
3. do the job
4. recreate the old passwrd using
alter user x identified by value "that_password'

Of course do exercise for the correct syntax and order of steps with an experimental account. But nevertheles I will not recommend to do this ...

Possibly you have sysdba privelege, but you do not use SYS account and this is the cause of your trouble.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 23693919
oops,  DELTEK not DELTEX  above
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 23693977
the reason the version matters is 9i introduced the "GRANT ANY OBJECT PRIVILEGE'  system privilege

which addresses this very problem.  however on 8i,  you have to be the user who owns the object
0
 

Author Closing Comment

by:la_colibri
ID: 31549316
thanks!
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 23693987
glad I could help
0
 

Author Comment

by:la_colibri
ID: 23694194
Arg.... I don't have the password from step #1 anymore so can't revert back to the old password... anyway I can retrieve this 16 digit password???
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 23694208
do a point in time recovery to before you changed it
0
 

Author Comment

by:la_colibri
ID: 23694224
If not how can I view a list of scheduled jobs to check to see if Deltek has any jobs running periodically?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 23694292
select * from dba_jobs
0
 

Author Comment

by:la_colibri
ID: 23694433
thanks
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to recover a database from a user managed backup
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question