[ Pobierz całość w formacie PDF ]
.Now try to insert a new record into the table.INPUT/OUTPUT:SQL> INSERT INTO Bryan.SALARIES2 VALUES('JOE',85000,38);INSERT INTO Bryan.SALARIES*ERROR at line 1: ORA-01031: insufficient privilegesANALYSIS:This operation did not work because Jill does not have INSERT privileges on theSALARIES table.INPUT/OUTPUT:SQL> UPDATE Bryan.SALARIES2 SET AGE = 423 WHERE NAME = 'JOHN';UPDATE Bryan.SALARIES*ERROR at line 1:ORA-01031: insufficient privilegesANALYSIS: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.SALARIES2 SET SALARY = 350003 WHERE NAME = 'JOHN';1 row updated.SQL> SELECT *2 FROM Bryan.SALARIES;NAME SALARY AGE------------------------------ --------- ---------JACK 35000 29JILL 48000 42JOHN 35000 55ANALYSIS:You can see now that the update works as long as Jill abides by the privileges she hasbeen given.Using Views for Security PurposesAs we mentioned on Day 10, "Creating Views and Indexes," views are virtual tables that you can use to present a view of data that is different from the way it physically existsin the database.Today you will learn more about how to use views to implementsecurity measures.First, however, we explain how views can simplify SQL statements.Earlier you learned that when a user must access a table or database object thatanother user owns, that object must be referenced with a username.As you can imagine,this procedure can get wordy if you have to write writing several SQL queries in a row.More important, novice users would be required to determine the owner of a tablebefore they could select the contents of a table, which is not something you want allyour users to do.One simple solution is shown in the following paragraph.A Solution to Qualifying a Table or ViewAssume that you are logged on as Jack, your friend from earlier examples.You learnedthat for Jack to look at the contents of the SALARIES table, he must use the followingstatement:INPUT:SQL> SELECT *2 FROM Bryan.SALARIES;OUTPUT:NAME SALARY AGE------------------------------ --------- ---------JACK 35000 29JILL 48000 42JOHN 35000 55If you were to create a view named SALARY_VIEW, a user could simply select from thatview.INPUT/OUTPUT:SQL> CREATE VIEW SALARY_VIEW2 AS SELECT *3 FROM Bryan.SALARIES;View created.SQL> SELECT * FROM SALARY_VIEW;NAME SALARY AGE------------------------------ --------- ---------JACK 35000 29JILL 48000 42JOHN 35000 55 ANALYSIS:The preceding query returned the same values as the records returned fromBryan.SALARIES.Using Synonyms in Place of ViewsSQL also provides an object known as a synonym.A synonym provides an alias for a tableto simplify or minimize keystrokes when using a table in an SQL statement.There aretwo types of synonyms: private and public.Any user with the resource role can create aprivate synonym.On the other hand, only a user with the DBA role can create a publicsynonym.The syntax for a public synonym follows.SYNTAX:CREATE [PUBLIC] SYNONYM [schema.]synonymFOR [schema.]object[@dblink]In the preceding example, you could have issued the following command to achieve thesame results:INPUT/OUTPUT:SQL> CREATE PUBLIC SYNONYM SALARY FOR SALARIESSynonym created.Then log back on to Jack and type this:INPUT/OUTPUT:SQL> SELECT * FROM SALARY;NAME SALARY AGE------------------------------ --------- ---------JACK 35000 29JILL 48000 42JOHN 35000 55Using Views to Solve Security ProblemsSuppose you changed your mind about Jack and Jill and decided that neither of themshould be able to look at the SALARIES table completely.You can use views to changethis situation and allow them to examine only their own information. INPUT/OUTPUT:SQL> CREATE VIEW JACK_SALARY AS2 SELECT * FROM BRYAN.SALARIES3 WHERE NAME = 'JACK';View created.INPUT/OUTPUT:SQL> CREATE VIEW JILL_SALARY AS2 SELECT * FROM BRYAN.SALARIES3 WHERE NAME = 'JILL';View created.INPUT/OUTPUT:SQL> GRANT SELECT ON JACK_SALARY2 TO JACK;Grant succeeded.INPUT/OUTPUT:SQL> GRANT SELECT ON JILL_SALARY2 TO JILL;Grant succeeded
[ Pobierz całość w formacie PDF ]