Database Programming with PL/SQL
9-5: Review of Object Privileges
Practice Activities
Vocabulary
Allows the grantee to invoke and execute a PL/SQL subprogram. - EXECUTE privilege
Allows the grantee to create indexes on the table. - INDEX privilege
Allows the use of a specific database object, such as a table, a view, or a PL/SQL procedure, by one or more database users. - Object privilege
Allows the grantee to ALTER the table. - ALTER privilege
Allows the grantee to check for the existence of rows in a table using foreign key constraints. - REFERENCES privilege
Try It / Solve It
1. If you wanted user SUSAN to be able to execute SELECT and all DML statements on your
COUNTRIES table, what SQL statement would you execute to give her the required privileges?
GRANT INSERT, UPDATE, SELECT, DELETE
ON countries
TO SUSAN;
2. User TOM creates a table called TOMTAB, but does not grant you any privileges on it.
A. If you try to execute the following statement, will it work?
INSERT INTO tom.tomtab (…) VALUES (…);
No, it will not work because I don't have the privileges.
B. Examine the following code. The INSERT statement has been included in a procedure which
you have created. Will it work now?
CREATE OR REPLACE PROCEDURE my_ins_proc
IS
BEGIN
INSERT INTO tom.tomtab (…)
VALUES (…);
END;
It will not work.
C. TOM now executes the following statement:
GRANT INSERT ON tomtab TO <your user name>;
Will your my_ins_proc procedure work now? Why or why not?
It will work now because I have the privileges
D. TOM now REVOKEs your INSERT privilege on tomtab. TOM then writes the following
procedure. Which privilege must TOM grant to you to allow you to execute his tom_ins_proc
procedure? With this privilege, will the INSERT work when you invoke TOM’s procedure?
CREATE OR REPLACE PROCEDURE tom_ins_proc
IS
BEGIN
INSERT into tom.tomtab (…)
VALUES (…);
END;
You will need EXECUTE privilege on that procedure. The INSERT will work, only the creator must hold the appropriate privileges.
3. Create a SELECT statement to display all the procedures and functions which you can execute?
SELECT * FROM sys.all_objects
WHERE (OBJECT_TYPE = 'FUNCTION' OR OBJECT_TYPE = 'PROCEDURE') AND EDITIONABLE = 'Y';
4. In this question, you will need to pair up with one other student. You will then grant access to one
of your procedures to the other student, and he or she will do the same to you. Each of you will
then execute the other student’s procedure and see the effects on the other student’s table. For
the rest of this question, we call one of you Student A and the other Student B.
a. (Both students): create a table called NEW_DEPT_TAB using the DEPARTMENT table.
CREATE TABLE new_dept_tab AS
(SELECT * FROM departments);
b. (Both students): create a procedure called ins_new_dept which inserts a record into the
new_dept_tab table.
describe new_dept_tab; so we know what we have to insert
CREATE OR REPLACE PROCEDURE ins_new_dept IS
BEGIN
INSERT INTO new_dept_tab
VALUES (15, 'TestName', 15, 15);
END ins_new_dept;
c. (Both students): grant EXECUTE privilege on your ins_new_emp procedure to the other
student.
GRANT EXECUTE ON ins_new_dept TO STUDENT2;
d. (Both students): check that you can see the definition of the other student’s procedure.
describe ins_new_dept;
e. (Student A): insert an employee record using Student B’s ins_new_emp procedure.
f. (Student B): insert an employee record using Student A’s ins_new_emp procedure.
BEGIN
ins_new_dept;
END;
g. (Both students): Query your own new_dept_tab table and check that you can see the
new row inserted by the other student.
SELECT *
FROM new_dept_tab
WHERE department_id = 15;
h. (Both students): remove the right to execute your procedure from the other student.
REVOKE EXECUTE ON ins_new_dept FROM STUDENT2;
i. (Both students): check that you can no longer see the other student’s ins_new_emp
procedure.
describe ins_new_dept;
0 Comments