newbie wrote:
> You are absolutely correct....I do not have the password for the user
> abc
>
> Of course, I am not going to change the password for user abc
> indiscriminately
>
> However I kind of thought that sys and system were kinda superusers and
> if this is not possible to somehow overcome this issue.
>
> And of course I do not want to say "Do You want fries with your order"
> I kind of like my current job !!!!!
>
>
> Thanks
>
>
> fitzjarrell_at_cox.net wrote:
>> newbie wrote:
>>> I have a schema called 'abc'
>>> I do not know the password for the user 'abc'
>>>
>>> I know the password for the user 'sys' and user 'system'
>>>
>>> I tried the following when logged in as user 'sys' as sysdba and user
>>> 'system' and user 'sys' (normal)
>>>
>>> select * from abc.pqr where 'pqr' is a table in schema 'abc'
>>>
>>> I get the result
>>>
>>> However If I create another user 'test' and try the following
>>> select * from abc.pqr it gives me the result
>>>
>>> ORA-00942: table or view does not exist
>>>
>>>
>>> I tried the following when I am logged in as user 'sys' as sysdba or
>>> sysoper or normal or user system
>>>
>>> grant select on abc.pqr to test;
>>>
>>> The following is the error message
>>>
>>> ORA-01031: insufficient privileges
>>>
>>>
>>> How do I resolve this...Please help
>> You use the user 'abc' to grant privileges on the objects OWNED by
>> 'abc'. It's that simple. Possibly you created this 'abc' user to test
>> things; since you DO have access as SYS as SYSDBA you can CHANGE the
>> password for 'abc':
>>
>> SQL> alter user abc identified by somepasswordicanremember;
>>
>> And, then, by the magic of Oracle you can connect as 'abc':
>>
>> SQL> connect abc/somepasswordicanremember
>> Connected.
>> SQL> select * from pqr;
>>
>> [all sorts of results here]
>>
>> SQL> grant select on pqr to test;
>>
>> Grant succeeded.
>>
>> SQL> connect test/somepasswordidontknow
>> Connected.
>> SQL> select * from abc.pqr;
>>
>> [all sorts of results here]
>>
>> If this is some 'real world' problem which you've oversimplified in
>> order to get an answer then the above instructions won't do you any
>> good as you cannot change the application user password without
>> breaking the application. In that case I'd go to whoever created the
>> application user and ask, pretty please, for the password. Of course,
>> if you have SYS as SYSDBA privileges without authorization you should
>> update your resume as you won't be long for this job.
>>
>>
>> David Fitzjarrell
>
What version of Oracle? The DBA role since version 9i includes the
"GRANT ANY OBJECT PRIVILEGE" system privilege which lets the DBA grant
privs on objects he does not own to other users.
Example - 9.2.0.5 - run by a user with DBA role.
SQL> CREATE USER u1 IDENTIFIED BY u1 DEFAULT TABLESPACE users TEMPORARY
TABLESPACE temp
User created.
SQL> ALTER USER u1 QUOTA UNLIMITED ON users
User altered.
SQL> CREATE TABLE u1.t1 TABLESPACE users AS SELECT * FROM dba_tables
Table created.
SQL> CREATE USER u2 IDENTIFIED BY u2 DEFAULT TABLESPACE users TEMPORARY
TABLESPACE temp
User created.
SQL> GRANT SELECT ON u1.t1 TO u2
Grant complete.
Received on Wed Jul 12 2006 - 13:58:51 CDT
