Re: schema visibility

From: Tim X <timx_at_nospam.dev.null>
Date: Fri, 16 Oct 2009 18:09:44 +1100
Message-ID: <87k4yvvmpz.fsf_at_lion.rapttech.com.au>

student4life <student4lifer_at_gmail.com> writes:

> Could someone tell me if person A adds one column to a table in the
> database in person A's schema, will person B see the change from
> person B's schema in the same database? TIA

Generally speaking, schemas are independent and the data in once schema is not visible to another schema. However,

1. A schema owner controls the 'grants' for their own schema objects. and can give read, write and create privileges to another schema.
2. The DBA can also control the access rights of a user and can give various privileges to other schemas/users
3. Through the use of grants and synonyms, things can be setup so that an object from schema A can look like an normal object in schema B i.e. the schema B user can access the schema A object without having to qualify the object name with the schema name or by qualifying its name with its schema name

Often, you will see an application where all the tables and other objects live in one schema, such as schema A and then specific objects will have grants applied to give various levels of access to another schema, such as schema B. You might do this when you have a web application that accesses your data and you want to control what it does. For example, you might only grant read access to tables in schema A to schema B and have your web front-end connect to the database via schema B. Should your web security be somehow compromised, the access to data is still restricted. On the other hand, if your web front-end connected directly to schema A, it would have the full access that the schema A user has i.e. write/create privs in addition to read privs.

PL/SQL further supports this seperation of privs via its author/invoker privilege mechanism. If you grant execute privs to another schema for a pl/sql package, when that package is executed by the second schema, it will execute with the privs of the package author i.e. the first schema that granted the execute privs. The package will be able to access objects as if it was being exeucted by the author of the package, avoiding the need to give access privs for all the objecs to the second schema. At other times, you may only want the second schema to be able to execute things if it has the necessary grants for the objects accessed by the package, in which case, you can define it to run with invoker's privs.

Tim

-- 
tcross (at) rapttech dot com dot au

Received on Fri Oct 16 2009 - 02:09:44 CDT