Oracle Grant Security -- In-Class Exercise
Before you start
If you are using SQL*Plus...
Before you can see the output of PL/SQL programs in SQL*Plus,
you need to set the size of the output buffer. Do this by
typing the following command into SQL*Plus.
Notice that there is no semi-colon at the end of the command.
SET SERVEROUTPUT ON SIZE 4096
If you are using Oracle SQL Developer...
Enter your PL/SQL programs into the window where you normally
enter your SQL statements.
To enable output, click on the "DBMS Output" tab and then click
the "Enable DBMS Output" button (to the left of the pencil eraser
button).
Run the PL/SQL program by clicking
the "Run Script" button or pressing F5.
After the script is run,
in the lower part of the screen, the "Script Output" window will
show "anonymous block completed" to indicate that the script
has finished running.
To see the output, click on the "DBMS Output" tab.
Start by creating a table
We will start this exercise by creating a simple table
that stores information about books:
create table books (bid number, title varchar(40),
author varchar(40), price number);
You will need to populate the table with some values:
insert into books values(1,'The Sorcerers Stone','JK Rowling',9.95);
insert into books values(2,'The Chamber of Secrets','JK Rowling',9.95);
insert into books values(3,'The Prisoner of Azkaban','JK Rowling',9.95);
insert into books values(4,'The Goblet of Fire','JK Rowling',9.95);
insert into books values(5,'The Hobbit','JRR Tolkien',9.95);
insert into books values(6,'The Lord of the Rings','JRR Tolkien',9.95);
insert into books values(7,'The Two Towers','JRR Tolkien',9.95);
insert into books values(8,'The Return of the King','JRR Tolkien',9.95);
You also need to insert one more book that is likely to be unique.
You can make up a book name, or pick a favorite book.
Allow your neighbor to SELECT (read) the table
For this part of the exercise, everyone in class needs a partner.
Tell your partner what your Oracle username is (e.g. db2_19) but
do NOT tell them your password!
Once you know your partner's username, grant them SELECT privileges
on the books table you just created (replace db2_19 below with the
username of your partner):
grant SELECT on books to db2_19;
If you make a mistake, you can remove the privilege using the
revoke command:
revoke SELECT on books from db2_19;
Once your partner has granted you permission on their books table,
you should be able to query it as follows:
select * from db2_19.books;
Note that you have to include your partner's username followed by a dot and
then the table name. Otherwise, Oracle will look for the table
name in your set of tables rather than in your partner's.
Controlling access using a view
Create a view of your books table that only includes the JK Rowling books:
create view jkbooks as
select * from books
where author = 'JK Rowling';
Now grant your partner SELECT and UPDATE access to the view:
grant select, update on jkbooks to db2_19;
Now, not only can your partner issue SELECT queries, but they can
update items:
update db2_19.jkbooks set price=12.95 where title='The Goblet of Fire';
You can verify that you don't have permission to DELETE items from your
partner's table:
delete from db2_19.jkbooks where title='The Goblet of Fire';
You can also be more selective and only grant permissions on specific
columns in a table:
grant update (title, price) on books to db2_19;
You can also grant privileges to all current and future users:
grant SELECT on books to public;
Revoking Privileges
Use the revoke command to revoke privileges:
revoke UPDATE on books from db2_19;
Checking User Privileges
USER_USERS -- information about your account (and all accounts)
USER_TAB_PRIVS -- table privileges
USER_TAB_PRIVS_MADE -- table grants you have made
USER_TAB_PRIVS_RECD -- table grants you have received
USER_COL_PRIVS -- column grants
USER_COL_PRIVS_MADE -- column grants you have made
USER_COL_PRIVS_RECD -- column grants you have received
USER_SYS_PRIVS -- system privileges for the user
References and Links
- Oracle8: The Complete Reference
|