Re: Administration.

From: ddf <oratune_at_msn.com>
Date: Mon, 1 Jun 2009 07:16:31 -0700 (PDT)
Message-ID: <31fe8d62-8fc6-4330-be3b-4bf4266a116e_at_o14g2000vbo.googlegroups.com>

On Jun 1, 8:03�am, "Markus Gronotte" <maqq..._at_gmx.de> wrote:
> I do this Code as System:
>
> Create Table T_Controlling (
> � �ControllingID number,
> � �Mitarbeiter number,
> � �Verkaeufe number,
> � �Constraint PK_Control Primary Key (ControllingID),
> � �Constraint FK_Control Foreign Key (Mitarbeiter) References Mitarbeiter(MitarbeiterID)
> );
>
> Create Role R_Einkauf
> Create Role R_Verkauf
>
> Grant Connect to R_Einkauf
> Grant Connect to R_Verkauf
>
> Grant Select On T_Controlling to R_Einkauf
> Grant Select On T_Controlling to R_Verkauf
>
> Grant Insert On T_Controlling to R_Einkauf
> Grant Insert On T_Controlling to R_Verkauf
>
> Grant Update On T_Controlling to R_Einkauf
> Grant Update On T_Controlling to R_Verkauf
>
> Grant Select On T_Mitarbeiter to R_Einkauf
> Grant Select On T_Mitarbeiter to R_Verkauf
>
> Grant Select On T_Waren to R_Einkauf
> Grant Select On T_Lieferanten to R_Einkauf
>
> Grant Select On T_Rechnung to R_Verkauf
> Grant Select On T_Kunden to R_Verkauf
>
> Create User Meyer � Identified By "abc"
> Create User Moeller Identified By "abc"
> Create User Mueller Identified By "abc"
> Create User Schmidt Identified By "abc"
>
> Grant R_Einkauf to Meyer
> Grant R_Einkauf to Moeller
> Grant R_Verkauf to Mueller
> Grant R_Verkauf to Schmidt
>
> Then I log in with "Schmidt" and if i try to do
> select * from T_Controlling Oracle says
> "ORA-00942: Table or View not found.
>
> Does anybody know why?
>
> Yours,
> Markus

You have no synonym for T_Controlling; if you're not using owner.table_name syntax you will get that error. I expect if you change your query to

select * from system.t_controlling;

you'll see data.

So, either create private synonyms in each schema for system.t_controlling or create a public synonym for that table and your query should work as expected.

SQL> connect system/#%#%#%#%#
Connected.
SQL>
SQL> create table Mitarbeiter(MitarbeiterID number primary key) ;

Table created.

SQL>
SQL> Create Table T_Controlling (

  2  		ControllingID number,
  3  		Mitarbeiter number,
  4  		Verkaeufe number,
  5  		Constraint PK_Control Primary Key (ControllingID),
  6  		Constraint FK_Control Foreign Key (Mitarbeiter) References

Mitarbeiter(MitarbeiterID)
  7 );

Table created.

SQL>
SQL>
SQL> Create Role R_Einkauf ;

Role created.

SQL> Create Role R_Verkauf ;

Role created.

SQL>
SQL>
SQL> Grant Connect to R_Einkauf ;

Grant succeeded.

SQL> Grant Connect to R_Verkauf ;

Grant succeeded.

SQL>
SQL>
SQL> Grant Select On T_Controlling to R_Einkauf ;

Grant succeeded.

SQL> Grant Select On T_Controlling to R_Verkauf ;

Grant succeeded.

SQL>
SQL>
SQL> Grant Insert On T_Controlling to R_Einkauf ;

Grant succeeded.

SQL> Grant Insert On T_Controlling to R_Verkauf ;

Grant succeeded.

SQL>
SQL>
SQL> Grant Update On T_Controlling to R_Einkauf ;

Grant succeeded.

SQL> Grant Update On T_Controlling to R_Verkauf ;

Grant succeeded.

SQL>
SQL>
SQL> Create User Meyer	 Identified By "abc" ;

User created.

SQL> Create User Moeller Identified By "abc" ;

User created.

SQL> Create User Mueller Identified By "abc" ;

User created.

SQL> Create User Schmidt Identified By "abc" ;

User created.

SQL>
SQL>
SQL> Grant R_Einkauf to Meyer ;

Grant succeeded.

SQL> Grant R_Einkauf to Moeller ;

Grant succeeded.

SQL> Grant R_Verkauf to Mueller ;

Grant succeeded.

SQL> Grant R_Verkauf to Schmidt ;

Grant succeeded.

SQL>
SQL> connect schmidt/abc
Connected.
SQL>
SQL> select * from t_controlling;
select * from t_controlling

              *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL>
SQL> connect system/#%#%#%#%#
Connected.
SQL>
SQL> create synonym schmidt.t_controlling for system.t_controlling;

Synonym created.

SQL> create synonym moeller.t_controlling for system.t_controlling;

Synonym created.

SQL> create synonym mueller.t_controlling for system.t_controlling;

Synonym created.

SQL> create synonym meyer.t_controlling for system.t_controlling;

Synonym created.

SQL>
SQL> connect schmidt/abc
Connected.
SQL>
SQL> select * from t_controlling;

no rows selected

SQL> David Fitzjarrell Received on Mon Jun 01 2009 - 09:16:31 CDT