Re: Privilege of 'internal' user

Internal is not, never has been, and most certainly never will be, a User. Select * from dba_users if you don't believe me. He's not there. Internal is simply a keyword, used in Oracle 7, and carried through to other versions for backward compatibility reasons, that means 'let me on as a Privileged User' -that is, one with the "sysdba" system privilege, which then permits that user to issue startup, shutdown, backup, recover and create dataabse commands. Therefore (as you discovered), simply issuing a 'grant sysdba to fred' allows fred to be a Privileged User. (And the replacement set of keywords that does the same thing in 8.0 and above is 'connect fred/password AS SYSDBA'). Access to the dba_ views has nothing to do with possessing the SYSDBA privilege, but everything to do with having the DBA *role*. So a 'grant dba to fred' should allow him access to those views. And as SYSTEM has the DBA role, it should work with him, too -unless someone has been doing various revokes for him.

The "ALL_" views will only show tables to which the User has some form of access (that is, the tables he or she actually owns outright, plus any to which a select, update or delete object privilege) has been granted. Therefore, a 'grant select any table to fred' will allow Fred to see every table in the database in the ALL_TABLES view. He doesn't even, in other words, need the grant of the DBA role.

Finally, if someone has the 'select any table' privilege, that would (in Oracle 7) have meant that they *could* see SYS's tables. That was a potential security nightmare, so in Oracle 8, they invented a new init.ora parameter, o7_dictionary_accessibility (and that's an "oh" at the front of that paramter, not a zero). It defaults to TRUE, meaning that a 'select any table' privilege still, in 8.0 and 8.1, grants acess to the SYS tables as before. But if it is set to FALSE, then the grant is for select rights on all tables *except* for the SYS ones (and the default value has changed in 9i to FALSE, causing no end of fun for many people).

If you want someone in 8 or 8i to still have rights to see the SYS tables, without granting them major security risk roles like DBA or privileges like SYSDBA, then a new role was specially created for just that taks: SELECT_FROM_CATALOG. So a 'grant select_from_catalog to fred' should allow fred to see the SYS tables, even without possessing the DBA role or SYSDBA privilege.

Just to put all that together, I issued the following commands:

create user fred identified by fred;
grant conenct, select_any_table to fred; connect fred/fred
<issue your query>
..everything worked fine... 191 rows selected....

That only works because my 'o7_dictionary_accessibility' is set to TRUE. Set it to false, and whilst I don't get the error you were getting, a mere 120 rows were returned, because access to the data dictionary tables is now denied.

Regards
HJR

--
Resources for Oracle: http://www.hjrdba.com
===============================


"Christophe" <christophe.cavelier_at_template-software.fr> wrote in message
news:jcLO7.45$G02.47662_at_monolith.news.easynet.net...


> Hi,

>

> I found the problem with your indication.

> The user need 'sysdba' privilege. It wasn't necesary with Oracle 8.0.5 !

> It's strange but it's works.

>

> Thanks very much for your help.

> Christophe

>

>

> "Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> a écrit dans le

> message news: 3c0b70fd$0$8508$ed9e5944_at_reading.news.pipex.net...

> > "Christophe" <christophe.cavelier_at_template-software.fr> wrote in message

> > news:qlJO7.15$G02.20392_at_monolith.news.easynet.net...

> > > Hi,

> > >

> > > I'm a beginer with Oracle and i have the following problem.

> > >

> > > I use a development tool which make a request for retrieving all

> available

> > > oracle table.

> > > Oracle don't accept this request (Error ORA-01039) when i use 'system'

> > user

> > > for example (dba user) but accept only with 'internal' user.

> > > I have this problem with Oracle 8.1.7 only (with Oracle 8.0.5, it work

> !).

> > > The request is :

> > > SELECT DISTINCT TABLE_NAME FROM ALL_CATALOG WHERE OWNER=USER OR (

> > > OWNER='PUBLIC' AND TABLE_NAME NOT IN ( SELECT SYNONYM_NAME FROM

> > ALL_SYNONYMS

> > > WHERE TABLE_OWNER = 'SYS' OR TABLE_OWNER = 'SYSTEM'))

> >

> > This works fine under 8.1.7 for me. Internal is of course connecting as

> > sysdba. Is the database in a special state? not up or startup restricted

> or

> > some such?

> >

> >

> > --

> > Niall Litchfield

> > Oracle DBA

> > Audit Commission UK

> > *****************************************

> > Please include version and platform

> > and SQL where applicable

> > It makes life easier and increases the

> > likelihood of a good answer

> >

> > ******************************************

> >

> >

>

>