>
OK, so even though user A has permission to a table in user B's schema, only the view of that table in user A's schema is considered to be in user A's schema, but not the table itself. Right?
>
>Yep, Mark has helped me out with that in the past too. I'm playing around trying to find out what the bare minimum rights should be given to a group of users and how many there should be (groups that is). I guess one "application" user, one "application" group, with CONNECT, INSERT, UPDATE, DELETE would be the simplest approach, but is it the best?You do not need a view of B.Table for user A. What you do is create roles:
create role schema_read;
grant connect to schema_read;
-- note the role Connect is a default Oracle role that grants
-- CREATE SESSION and ALTER SESSION privs to users
grant select on schema.table1name to schema_read;
grant select on schema.table2name to schema_read;
grant select on schema.table3name to schema_read;
grant select on schema.usertable to schema_read;
create role schema_edit;
grant schema_read to schema_edit;
grant insert, update, delete on schema.table1name to schema_edit;
grant insert, update, delete on schema.table2name to schema_edit;
grant insert, update, delete on schema.table3name to schema_edit;
create role schema_admin;
grant schema_edit to schema_admin;
grant insert, update, delete on schema.usertable to schema_admin;
The next job you have is to create PUBLIC SYNONYMS on these tables so you do not always have to precede the table name with the schema name. However, a PUBLIC SYNONYM can only point to 1 table in the entire Oracle instance.
create public synonym Table1Name for Schema.Table1Name;
You also have to grant select privileges on any SEQUENCES you create as well as a public synonym for the SEQUENCES. Grant the select privs to the SCHEMA_EDIT role.
grant select on S_SCHEMA_TABLE to SCHEMA_EDIT;
Mark McCasland
Midlothian, TX USA