>>>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;
>
>
>
>Oh... My... God...
>
>Geez, maybe I should just take a class...
Don't panic!
In terms of the basic things you need to know, Mark' suggestions as above just about cover it.
I wouldn't bother with Oracle Education, unless you are going for certification. They are ferociously expensive and cover a lot of stuff you probably don't need to know.
Les
Previous
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only