Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: ORA-01031: insufficient privileges

Re: ORA-01031: insufficient privileges

From: Ben Ryan <benryan_at_my-deja.com>
Date: Sat, 13 Nov 1999 07:41:47 GMT
Message-ID: <80j4nr$3vd$1@nnrp1.deja.com>


On Fri, 12 Nov 1999 07:21:27 [by e-mail] Thomas Kyte wrote:
>
>> -----Original Message-----
>> From: Ben Ryan [mailto:benryan_at_my-deja.com]
>> Sent: Thursday, November 11, 1999 8:15 PM
>> To: tkyte_at_us.oracle.com
>> Subject: Re: ORA-01031: insufficient privileges
>>
>>
>> In article <+isrONyRyVd3dbZX5sfaR8DcrvEW_at_4ax.com>,
>> tkyte_at_us.oracle.com wrote:
>> > A copy of this was sent to Nandakumar <N.Kumar_at_rocketmail.com>
>> > (if that email address didn't require changing)
>> > On Thu, 11 Nov 1999 20:22:20 GMT, you wrote:
>> >
>> > >why's it like that?
>>
>> The trouble with questions like the one posed, is if you go back
>> far enough the answer becomes "because it just is.".
>>
>> For instance, since the creators of the database invented
>> the 'SELECT ANY TABLE' privilege, thereby bypassing the need
>> for explict grants of select privilege on individual tables,
>> then why can't they invent 'CREATE ANY TABLE PRIVILEGE' system
>> privilege?
>>
>
>???
>
>there is a create any table priv:

Woops! Sorry for the confusion by giving my imaginary system privilege a virtually identical name to an existing system privilege.

My imaginary system privilege would give you the ability to grant a table privilege on any object regardless of whether the object owner had granted you the table privilege (with admin option) or not. If such a privilege was granted to system, then system would have been able to do what the original e-mailer was requesting.

Put another way, a result of using the system privilege .. 'CREATE ANY TABLE' is a row in dba_tables 'CREATE ANY TABLE PRIVILEGE' is a row in dba_tab_privs

(Could of called it 'GRANT ANY TABLE PRIVILEGE'? Imagine the command 'grant GRANT ANY TABLE PRIVILEGE to someone' just as confusing - probably a good thing it does not exist!)

>
>The reason for "why is security the way it is" is actually explainable.
 A
>person 'owns' a table -- even if they had it created for them by
someone
>else. They are responsible for that table. The priv's on that table
cannot
>be transferred from person to person UNLESS the owner permits it, these
are
>the rules of DAC (discrentionary access control). If usera "owns" a
table
>and userb wants to grant on it, userb must have been granted the priv
on
>that table with the "grant option". now they have been explicity told
"you
>may grant on this object".

Agreed. That was basically the same as the best answer I could up with. It is directly applicable to the situation in my company where I am the company DBA and there are a bunch of application administrators who have Oracle accounts which own the tables needed by each application. I do not look after individual applications (generally speaking). When I need to log into Oracle, I do so as system. I have no business whatsoever granting application table privileges to ordinary end users.

Trouble is, I do not buy it. But I gave it try anyway..

So I tested the line of reasoning with one of the application administrators. He like the original e-mailer, was surprised to discover that system did not have the capability. He had assumed that system was all
powerful. He was not impressed with my explanation and pointed out that I
could always change the password on his account and then log in as him and
do the grant anyway. (i.e. not having the capability just slows me down a
bit). I pointed out that I would need to know what his original password was, so that I could cover my tracks when I was finished. To which he said, that if I had told him something like "there was a database problem
forcing me to reset your password" he would been perfectly happy!

Your own equivalent to "ALTER USER x IDENTIFIED BY newpass" is more elegant in that you do not need to make up some story at the end of it. (Come to think of it - is the password in dba_users encrypted or just in hex?).

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sat Nov 13 1999 - 01:41:47 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US