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.
fitzjarr...@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':
> 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.
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
> fitzjarr...@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':
>> 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.
fitzjarr...@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':
> 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
Just adding to the excellent tip above, before changing the user's password, execute the following query: SELECT USERNAME, PASSWORD FROM DBA_USERS WHERE USERNAME='ABC';
When finished, update the user with the original encrypted password: ALTER USER ABC IDENTIFIED BY VALUES 'ENCRYPTEDPASSWORD';
For obvious reasons, this should be used as a last resort.
Charles Hooper PC Support Specialist K&M Machine-Fabricating, Inc.
Yes, The changing password method is my last resort.
I would like to attempt any other method to enable either user sys or system to grant permission to user test select permission to objects of schema abc
Charles Hooper wrote: > fitzjarr...@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':
> > 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
> Just adding to the excellent tip above, before changing the user's > password, execute the following query: > SELECT > USERNAME, > PASSWORD > FROM > DBA_USERS > WHERE > USERNAME='ABC';
> When finished, update the user with the original encrypted password: > ALTER USER ABC IDENTIFIED BY VALUES 'ENCRYPTEDPASSWORD';
> For obvious reasons, this should be used as a last resort.
> Charles Hooper > PC Support Specialist > K&M Machine-Fabricating, Inc.
On 12 Jul 2006 15:49:32 -0700, "newbie" <rjngh2...@gmail.com> wrote:
>Oracle Version 8.1.7.4.1
>Yes, The changing password method is my last resort.
>I would like to attempt any other method to enable either user sys or >system >to grant permission to user test select permission to objects of schema >abc
>Thanks.....I look forward to more suggestions
First suggestion: stop top-posting Second suggestion : stop top-posting Third suggestion: Always mention your version in order to avoid wasting people's time, as in 8i you can't use David Fitzjarell's suggestion.
There is a very nasty tric to help you do it.
As sys or system create or replace procedure <table_owner>.do_sql(sqlstr in varchar2) is begin execute immediate sqlstr; end; /
Now as sys or system execute <table_owner>.do_sql('grant .... etc')
Works like a charm, and demonstrates whoever know the SYS or SYSTEM password (or any other privileged account) can control the entire database.
Sybrand Bakker wrote: > Works like a charm, and demonstrates whoever know the SYS or SYSTEM > password (or any other privileged account) can control the entire > database.
I always thought that was pretty much the main purpose of having those accounts in the first place :)
> >Yes, The changing password method is my last resort.
> >I would like to attempt any other method to enable either user sys or > >system > >to grant permission to user test select permission to objects of schema > >abc
> >Thanks.....I look forward to more suggestions
> First suggestion: stop top-posting > Second suggestion : stop top-posting > Third suggestion: Always mention your version in order to avoid > wasting people's time, as in 8i you can't use David Fitzjarell's > suggestion.
Hmmm, MY suggestion was this:
"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] "
Explain how 'alter user xxx identified by yyyy;' isn't available in 8i, please, as I've been using it since Oracle 6 to change user passwords (yes, even in the latest releases as the user community can't wrap their minds around the 'password' function).
Chuck offered the solution which is release-specific (any release from 9i onwards); possibly you misread the thread and attributed comments to me which should have been attributed elsewhere.
>First suggestion: stop top-posting >Second suggestion : stop top-posting >Third suggestion: Always mention your version in order to avoid >wasting people's time, as in 8i you can't use David Fitzjarell's >suggestion.
Excellent reply
Sorry, I did not understand the phrase ... top posting
Yes....Next time I shall give Oracle version first.
The reason I was hesitating to change the password is that this is a production database and as such I did not want to
a) change password to new password. b) run grant sql c) revert old password
I would do the above at the last resort
Your trick of creating a stored procedure is an excellent trick
> > >Yes, The changing password method is my last resort.
> > >I would like to attempt any other method to enable either user sys or > > >system > > >to grant permission to user test select permission to objects of schema > > >abc
> > >Thanks.....I look forward to more suggestions
> > First suggestion: stop top-posting > > Second suggestion : stop top-posting > > Third suggestion: Always mention your version in order to avoid > > wasting people's time, as in 8i you can't use David Fitzjarell's > > suggestion.
> Hmmm, MY suggestion was this:
> "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':
> Explain how 'alter user xxx identified by yyyy;' isn't available in 8i, > please, as I've been using it since Oracle 6 to change user passwords > (yes, even in the latest releases as the user community can't wrap > their minds around the 'password' function).
> Chuck offered the solution which is release-specific (any release from > 9i onwards); possibly you misread the thread and attributed comments to > me which should have been attributed elsewhere.
> > There is a very nasty tric to help you do it.
> > As sys or system > > create or replace procedure <table_owner>.do_sql(sqlstr in varchar2) > > is > > begin > > execute immediate sqlstr; > > end; > > /
> > Now as sys or system > > execute <table_owner>.do_sql('grant .... etc')
> > Works like a charm, and demonstrates whoever know the SYS or SYSTEM > > password (or any other privileged account) can control the entire > > database.
> fitzjarr...@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':
>> 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
> Just adding to the excellent tip above, before changing the user's > password, execute the following query: > SELECT > USERNAME, > PASSWORD > FROM > DBA_USERS > WHERE > USERNAME='ABC';
> When finished, update the user with the original encrypted password: > ALTER USER ABC IDENTIFIED BY VALUES 'ENCRYPTEDPASSWORD';
> For obvious reasons, this should be used as a last resort.
> Charles Hooper > PC Support Specialist > K&M Machine-Fabricating, Inc.
And of course, when you use this workaround and change the password, the whole application stops working because db connections will still use the old password ... Unless this user isn't used at all for db connections, and then I see no problems using this solution.
> >> 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
> > Just adding to the excellent tip above, before changing the user's > > password, execute the following query: > > SELECT > > USERNAME, > > PASSWORD > > FROM > > DBA_USERS > > WHERE > > USERNAME='ABC';
> > When finished, update the user with the original encrypted password: > > ALTER USER ABC IDENTIFIED BY VALUES 'ENCRYPTEDPASSWORD';
> > For obvious reasons, this should be used as a last resort.
> > Charles Hooper > > PC Support Specialist > > K&M Machine-Fabricating, Inc.
> And of course, when you use this workaround and change the password, the > whole application stops working because db connections will still use the > old password ... Unless this user isn't used at all for db connections, and > then I see no problems using this solution.
Explore the @ command in SQLPlus. Throw the command to be executed into a text file: * Change password * Log off * Connect as the user * Perform grant * Log off * Connect as SYS * Reset user password by values
Unless it takes five minutes to log into the database, there is little chance that any one would notice if performed in off hours.
I like the other solution that was proposed earlier.
Charles Hooper PC Support Specialist K&M Machine-Fabricating, Inc.
> > >> 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
> > > Just adding to the excellent tip above, before changing the user's > > > password, execute the following query: > > > SELECT > > > USERNAME, > > > PASSWORD > > > FROM > > > DBA_USERS > > > WHERE > > > USERNAME='ABC';
> > > When finished, update the user with the original encrypted password: > > > ALTER USER ABC IDENTIFIED BY VALUES 'ENCRYPTEDPASSWORD';
> > > For obvious reasons, this should be used as a last resort.
> > > Charles Hooper > > > PC Support Specialist > > > K&M Machine-Fabricating, Inc.
> > And of course, when you use this workaround and change the password, the > > whole application stops working because db connections will still use the > > old password ... Unless this user isn't used at all for db connections, and > > then I see no problems using this solution.
> Explore the @ command in SQLPlus. Throw the command to be executed > into a text file: > * Change password > * Log off > * Connect as the user > * Perform grant > * Log off > * Connect as SYS > * Reset user password by values
> Unless it takes five minutes to log into the database, there is little > chance that any one would notice if performed in off hours.
> I like the other solution that was proposed earlier.
> Charles Hooper > PC Support Specialist > K&M Machine-Fabricating, Inc.
|
