Lesson 8
Managing Object Privileges Conclusion
In this module, you learned all about object privileges, including how to:
- Assign Oracle's object privileges
- Grant other users access to your tables
- Grant object privileges by using Security Manager
- List the privileges that have been granted on a table
- List the privileges that have been granted on columns and for users
- Use the
WITH GRANT option
- Revoke privileges once granted
There is much more to object privileges than simply understanding how to grant and revoke them. The design of a complete security schema for your data can be as challenging as the actual database design itself. But, armed with these essentials, you will be able to implement any type of object privileges you need to fit your security requirements.
What Users Can Grant
A user can grant privileges on any object he or she owns. The database administrator can grant any system privilege. Suppose that user Glenn owns the COMFORT table and is a database administrator. Create two new users, Tom and Sarah, with these privileges:
create user Sarah identified by sarah;
User created.
grant CREATE SESSION to Sarah;
Grant succeeded.
create user Tom identified by carolyn;
User created.
grant CREATE SESSION, CREATE TABLE, CREATE VIEW,
CREATE SYNONYM to Tom;
Grant succeeded.
alter user Tom
default tablespace users
quota 5m on users;
User altered.
This sequence of commands gives both Sarah and Tom the ability to connect to Oracle, and gives Tom some extra capabilities.
Question: But can either do anything with Glenn's tables? Not without explicit access. To give others access to your tables, use a second form of the grant command:
grant { object privilege | all [privileges] }
[(column [, column] . . .)]
[, { object privilege | all [privileges] }
[(column [, column] . . .)] ] . . .
on object to {user | role}
[with grant option]
[with hierarchy option];
The privileges a user can grant include these:
(*)On the user's tables, views, and materialized views:
FLASHBACK
INSERT
UPDATE (all or specific columns)
DELETE
SELECT
Note: The INSERT, UPDATE, and DELETE privileges can only be granted on materialized views if they are updateable.
(*) On tables, you can also grant:
ALTER (table-all or specific columns)
DEBUG
REFERENCES
INDEX (columns in a table)
ON COMMIT REFRESH
QUERY REWRITE
ALL (of the items previously listed)
(*)On procedures, functions, packages, abstract datatypes, libraries, indextypes, and
operators:
EXECUTE
DEBUG
(*)On sequences:
SELECT
ALTER
(*) On directories (for BFILE LOB datatypes and external tables):
READ
WRITE
On object types and views:
UNDER (for the ability to create subviews under a view, or subtypes under a type)
When you execute another user's procedure or function, it is typically executed using the privileges of its owner. This means you do not need explicit access to the data the procedure or function uses; you see only the result of the execution, not the underlying data.
You can also create stored procedures that execute under the privileges of the invoking user instead of the procedure owner. Glenn gives Tom SELECT access to the COMFORT table:
New Terms introduced in this module
In this module, you learned the following glossary terms:
- (DML) Data Manipulation Language: The portion of the SQL language that is used to manipulate the actual data in the database.
- Privilege: The capability to perform some type of database action.
In the next module, you will learn how to use roles to make the process of implementing a security scheme much easier.
Grant Option Revoke - Quiz
Ad Oracle DBA
