will have different capabilities when working with the database...

Pokaż mi serce nie opętane zwodniczymi marzeniami, a pokażę ci człowieka szczęśliwego.

First create the
SALARIES table with the following information:
INPUT/OUTPUT:
SQL> SELECT * FROM SALARIES;
NAME SALARY AGE
------------------------------ --------- ---------
JACK 35000 29
JILL 48000 42
JOHN 61000 55
You could then grant various privileges to this table based on some arbitrary reasons
for this example. We are assuming that you currently have DBA privileges and can
grant any system privilege. Even if you do not have DBA privileges, you can still grant
object privileges on the SALARIES table because you own it (assuming you just created it).
Because Jack belongs only to the Connect role, you want him to have only SELECT
privileges.
INPUT/OUTPUT:
SQL> GRANT SELECT ON SALARIES TO JACK;
Grant succeeded.
Because Jill belongs to the Resource role, you allow her to select and insert some data
into the table. To liven things up a bit, allow Jill to update values only in the SALARY
field of the SALARIES table.
INPUT/OUTPUT:
SQL> GRANT SELECT, UPDATE(SALARY) ON SALARIES TO Jill;
Grant succeeded.
Now that this table and these users have been created, you need to look at how a user
accesses a table that was created by another user. Both Jack and Jill have been granted
SELECT access on the SALARIES table. However, if Jack tries to access the SALARIES table, he will be told that it does not exist because Oracle requires the username or schema
that owns the table to precede the table name.
Qualifying a Table
Make a note of the username you used to create the SALARIES table (mine was Bryan).
For Jack to select data out of the SALARIES table, he must address the SALARIES table with that username.
INPUT:
SQL> SELECT * FROM SALARIES;
SELECT * FROM SALARIES
*
OUTPUT:
ERROR at line 1:
ORA-00942: table or view does not exist
Here Jack was warned that the table did not exist. Now use the owner's username to
identify the table:
INPUT/OUTPUT:
SQL> SELECT *
2 FROM Bryan.SALARIES;
NAME SALARY AGE
------------------------------ --------- ---------
JACK 35000 29
JILL 48000 42
JOHN 61000 55
ANALYSIS:
You can see that now the query worked. Now test out Jill's access privileges. First log
out of Jack's logon and log on again as Jill (using the password Jill).
INPUT/OUTPUT:
SQL> SELECT *
2 FROM Bryan.SALARIES;
NAME SALARY AGE
------------------------------ --------- ---------
JACK 35000 29
JILL 48000 42
JOHN 61000 55
That worked just fine. Now try to insert a new record into the table.
INPUT/OUTPUT:
SQL> INSERT INTO Bryan.SALARIES
2 VALUES('JOE',85000,38);
INSERT INTO Bryan.SALARIES
*
ERROR at line 1:
ORA-01031: insufficient privileges
ANALYSIS:
This operation did not work because Jill does not have INSERT privileges on the SALARIES
table.
INPUT/OUTPUT:
SQL> UPDATE Bryan.SALARIES
2 SET AGE = 42
3 WHERE NAME = 'JOHN';
UPDATE Bryan.SALARIES
*
ERROR at line 1:
ORA-01031: insufficient privileges
ANALYSIS:
Once again, Jill tried to go around the privileges that she had been given. Naturally, Oracle caught this error and corrected her quickly.
INPUT/OUTPUT:
SQL> UPDATE Bryan.SALARIES
2 SET SALARY = 35000
3 WHERE NAME = 'JOHN';
1 row updated.
SQL> SELECT *
2 FROM Bryan.SALARIES;
NAME SALARY AGE
------------------------------ --------- ---------
JACK 35000 29
JILL 48000 42
JOHN 35000 55
ANALYSIS:
You can see now that the update works as long as Jill abides by the privileges she has
been given.
Using Views for Security Purposes