How do we access our files in computer lab? We login to computer with our login id and password. Then browse over computer to specific location where our files are stored. Correct? Here individual student have their own login id – password. But he will be doing same C/ C++/ PlSql lab exercise. Each user has their own set of files, folder etc. Similarly database can be considered as a program that individual students are exercising and each user is considered as schemas. Their files and folders are considered as objects of schema.
Database has objects like tables, views, indexes, constraints, procedure, Packages etc. All these objects are owned by particular user. This user is called as schema. Schema owns the ownership of all their objects. In a database, we can have multiple users – schemas with its own objects. The objects in one schema can be different from objects in other schema. Here individual schema will have specific role and purpose for task. One schema cannot access the objects of another schema. In order to access other schema objects, we need to give GRANTS to them. It is similar to how we cannot see other student’s lab files.
Look at below representation of schemas in a database. Here we have 3 schemas – STUDENT, STAFF and COURSE. Note that schema name need not be same as the objects within it. It can be anything. It can even be the name of the user has created/accessing individual schema. For example, we can have schema name as William, Albert and Ben in the below case. Schema acts like a login ID.
As you can see, each schema has its own set of objects. Student schema is the owner of STUDETNT and MARKS table, and it has one procedure and one function. Similarly STAFF schema has 2 tables – LECTURERS and STAFFS, and one procedure. Course schema has only 2 tables – CLASS and SUBJETCTS. Basically schema contains the structure and mapping of the object in it. It will remain same most of the time, unless we modify the structure of the objects in schema. It record value in the schema objects keeps changing and those structures with their value at any point in time is called instance of the schema.
How do we access the objects in the schema? We append schema name before the objects and fire the required query. Say, we need to see the records in STUDENT table.
SELECT * FROM SCHEMA.OBJECT; -- General Representation SELECT * FROM STUDENT.STUDENT; -- Retrieves all the STUDENT details SELECT * FROM STUDENT.MARKS; -- Retrieves all the records from MARKS CALL STUDENT.sp_getStudent; -- Calls the procedure sp_getStudent SELECT * FROM COURSE.SUBJECTS; --Retrieves all the subjects SELECT * FROM STAFF.LECTURERS; -- Retrieves all the lecturer details
Objects in STUDENT schema cannot access the objects in STAFF or COURSE schema. But some reason we need to know the details in COURSE schema. But how do we access it? We need to get the permission from COURSE schema – we give GRANTS to STUDENT schema to access the data. But access will be given only on the required tables or procedures. Also the access will be read only or read-write depending on the requirement.
Here say, STUDENT wants to know the SUBJECTS specified for his class. So STUDENT schema needs to access the SUBJECTS table from COURSE schema, and it needs only read only access. STUDENT schema does not have anything to modify SUBJECTS table. Hence we give SELECT grant on SUBJECTS table to STUDENT schema.
GRANT SELECT ON COURSE.SUBJECTS to STUDENT; -- Here STUDENT is the Schema, not table
Now, all the objects in STUDENT schema can access SUBJECTS table. Similarly, we can give grants on other objects at different levels, based on the requirement. This is how objects across different users/schema are accessed in database.
