[ Pobierz całość w formacie PDF ]
.GRANT ANY ROLEAllows the grantees to grant any role in thedatabase.INSERT ANY TABLEAllows the grantees to insert rows into tablesand views in any schema.LOCK ANY TABLEAllows the grantees to lock tables and views inany schema. SELECT ANY SEQUENCEAllows the grantees to reference sequences inany schema.SELECT ANY TABLEAllows the grantees to query tables, views, orsnapshots in any schema.UPDATE ANY ROWSAllows the grantees to update rows in tables.Object privileges are privileges that can be used against specific database objects.Table12.2 lists the object privileges in Oracle7.Table 12.2.Object privileges enabled under Oracle7.ALLALTERDELETEEXECUTEINDEXINSERTREFERENCESSELECTUPDATEYou can use the following form of the GRANT statement to give other users access toyour tables:SYNTAX:GRANT {object_priv | ALL [PRIVILEGES]} [ (column[, column].) ][, {object_priv | ALL [PRIVILEGES]} [ (column[, column].) ] ].ON [schema.]objectTO {user | role | PUBLIC} [, {user | role | PUBLIC}].[WITH GRANT OPTION]To remove the object privileges you have granted to someone, use the REVOKE commandwith the following syntax:SYNTAX:REVOKE {object_priv | ALL [PRIVILEGES]}[, {object_priv | ALL [PRIVILEGES]} ]ON [schema.]object FROM {user | role | PUBLIC} [, {user | role | PUBLIC}][CASCADE CONSTRAINTS]From Creating a Table to Granting RolesCreate a table named SALARIES with the following structure:INPUT:NAME, CHAR(30)SALARY, NUMBERAGE, NUMBERSQL> CREATE TABLE SALARIES (2 NAME CHAR(30),3 SALARY NUMBER,4 AGE NUMBER);OUTPUT:Table created.Now, create two users--Jack and Jill:INPUT/OUTPUT:SQL> create user Jack identified by Jack;User created.SQL> create user Jill identified by Jill;User created.SQL> grant connect to Jack;Grant succeeded.SQL> grant resource to Jill;Grant succeeded.ANALYSIS:So far, you have created two users and granted each a different role.Therefore, theywill have different capabilities when working with the database.First create theSALARIES table with the following information:INPUT/OUTPUT: SQL> SELECT * FROM SALARIES;NAME SALARY AGE------------------------------ --------- ---------JACK 35000 29JILL 48000 42JOHN 61000 55You could then grant various privileges to this table based on some arbitrary reasonsfor this example.We are assuming that you currently have DBA privileges and cangrant any system privilege.Even if you do not have DBA privileges, you can still grantobject privileges on the SALARIES table because you own it (assuming you just createdit).Because Jack belongs only to the Connect role, you want him to have only SELECTprivileges.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 datainto the table.To liven things up a bit, allow Jill to update values only in the SALARYfield 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 useraccesses a table that was created by another user.Both Jack and Jill have been grantedSELECT access on the SALARIES table.However, if Jack tries to access the SALARIEStable, he will be told that it does not exist because Oracle requires the username orschema that owns the table to precede the table name.Qualifying a TableMake 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 tablewith that username.INPUT: SQL> SELECT * FROM SALARIES;SELECT * FROM SALARIES*OUTPUT:ERROR at line 1:ORA-00942: table or view does not existHere Jack was warned that the table did not exist.Now use the owner's username toidentify the table:INPUT/OUTPUT:SQL> SELECT *2 FROM Bryan.SALARIES;NAME SALARY AGE------------------------------ --------- ---------JACK 35000 29JILL 48000 42JOHN 61000 55ANALYSIS:You can see that now the query worked.Now test out Jill's access privileges.First logout of Jack's logon and log on again as Jill (using the password Jill).INPUT/OUTPUT:SQL> SELECT *2 FROM Bryan [ Pobierz całość w formacie PDF ]
  • zanotowane.pl
  • doc.pisz.pl
  • pdf.pisz.pl
  • agnieszka90.opx.pl