Object Privileges
An object privilege allows a user to perform certain actions on database objects, such as executing DML statements on tables. For example, INSERT ON store.products allows a user to insert rows into the products table of the store schema. Some of the commonly used object privileges are shown in Table 9-3.
| Object Privilege | Allows a User to |
|---|---|
| SELECT | Perform a select |
| INSERT | Perform an insert |
| UPDATE | Perform an update |
| DELETE | Perform a delete |
| EXECUTE | Execute a stored procedure |
| Note | You can get the full list of object privileges in the Oracle SQL Reference manual. |
Granting Object Privileges to a User
You use GRANT to grant an object privilege to a user. The following example connects as store and grants the SELECT , INSERT , and UPDATE object privileges on the products table to steve along with the SELECT privilege on the employees table:
CONNECT store/store_password GRANT SELECT, INSERT, UPDATE ON store.products TO steve; GRANT SELECT ON store.employees TO steve;
The next example grants the UPDATE privilege on the last_name and salary columns to steve :
GRANT UPDATE (last_name, salary) ON store.employees TO steve;
You can also use the GRANT option to enable a user to grant a privilege to another user. The following example grants the SELECT privilege on the customers table with the GRANT option to steve :
GRANT SELECT ON store.customers TO steve WITH GRANT OPTION;
| Note | You use the GRANT option when enabling a user to grant an object privilege to another user, and you use the ADMIN option when enabling a user to grant a system privilege to another user. |
The SELECT ON store.customers privilege can then be granted to another user by steve . The following example connects as steve and grants this privilege to gail :
CONNECT steve/button GRANT SELECT ON store.customers TO gail;
Checking Object Privileges Made
You can check which table object privileges a user has made to other users by querying user_tab_privs_made . Table 9-4 documents the columns in user_tab_privs_made .
| Column | Type | Description |
|---|---|---|
| grantee | VARCHAR2(30) | User to whom the privilege was granted. |
| table_name | VARCHAR2(30) | Name of the object (such as a table) on which privilege was granted. |
| grantor | VARCHAR2(30) | User who granted the privilege. |
| privilege | VARCHAR2(40) | Privilege on the object. |
| grantable | VARCHAR2(3) | Whether the grantee can grant the privilege to another. Equal to YES or NO . |
| hierarchy | VARCHAR2(3) | Whether the privilege forms part of a hierarchy. Equal to YES or NO . |
The following example connects as store and queries user_tab_privs_made . Since there are so many rows, I ll limit the retrieved rows to those where table_name is PRODUCTS :
CONNECT store/store_password SELECT * FROM user_tab_privs_made WHERE table_name = 'PRODUCTS'; GRANTEE TABLE_NAME ---------------------- ------------------------------ GRANTOR PRIVILEGE GRA HIE ---------------------- ---------------------------- --- --- PRODUCT_MANAGER PRODUCTS STORE DELETE NO NO PRODUCT_MANAGER PRODUCTS STORE INSERT NO NO PRODUCT_MANAGER PRODUCTS STORE SELECT NO NO PRODUCT_MANAGER PRODUCTS STORE UPDATE NO NO STEVE PRODUCTS STORE INSERT NO NO STEVE PRODUCTS STORE SELECT NO NO STEVE PRODUCTS STORE UPDATE NO NO
You can check which column object privileges a user has made by querying user_col_privs_made . Table 9-5 documents the columns in user_col_privs_made .
| Column | Type | Description |
|---|---|---|
| grantee | VARCHAR2(30) | User to whom the privilege was granted. |
| table_name | VARCHAR2(30) | Name of the object on which privilege was granted. |
| column_name | VARCHAR2(30) | Name of the object on which privilege was granted. |
| grantor | VARCHAR2(30) | User who granted the privilege. |
| privilege | VARCHAR2(40) | Privilege on the object. |
| grantable | VARCHAR2(3) | Whether the grantee can grant the privilege to another. Equal to YES or NO . |
The following example queries user_col_privs_made :
SELECT * FROM user_col_privs_made; GRANTEE TABLE_NAME ------------------------------ ------------- COLUMN_NAME GRANTOR ------------------------------ ------------- PRIVILEGE GRA ---------------------------------------- --- STEVE EMPLOYEES LAST_NAME STORE UPDATE NO STEVE EMPLOYEES SALARY STORE UPDATE NO
Checking Object Privileges Received
You can check which object privileges on a table a user has received by querying the user_tab_privs_recd table. Table 9-6 documents the columns in user_tab_privs_recd .
| Column | Type | Description |
|---|---|---|
| owner | VARCHAR2(30) | User who owns the object. |
| table_name | VARCHAR2(30) | Name of the object on which the privilege was granted. |
| grantor | VARCHAR2(30) | User who granted the privilege. |
| privilege | VARCHAR2(40) | Privilege on the object. |
| grantable | VARCHAR2(3) | Whether the grantee can grant the privilege to another. Equal to YES or NO . |
| hierarchy | VARCHAR2(3) | Whether the privilege forms part of a hierarchy. Equal to YES or NO . |
The next example connects as steve and queries user_tab_privs_recd :
CONNECT steve/button SELECT * FROM user_tab_privs_recd; OWNER TABLE_NAME ------------------------------ ----------------- GRANTOR ------------------------------ PRIVILEGE GRA HIE ---------------------------------------- --- --- STORE CUSTOMERS STORE SELECT YES NO STORE PRODUCTS STORE INSERT NO NO STORE PRODUCTS STORE SELECT NO NO STORE PRODUCTS STORE UPDATE NO NO STORE EMPLOYEES STORE SELECT NO NO
You can check which column object privileges a user has received by querying user_col_privs_recd . Table 9-7 documents the columns in user_col_privs_recd .
| Column | Type | Description |
|---|---|---|
| owner | VARCHAR2(30) | User who owns the object. |
| table_name | VARCHAR2(30) | Name of the table on which the privilege was granted. |
| column_name | VARCHAR2(30) | Name of the column on which privilege was granted. |
| grantor | VARCHAR2(30) | User who granted the privilege. |
| privilege | VARCHAR2(40) | Privilege on the object. |
| grantable | VARCHAR2(3) | Whether the grantee can grant the privilege to another. Equal to YES or NO . |
The following example queries user_col_privs_recd :
SELECT * FROM user_col_privs_recd; OWNER TABLE_NAME ------------------------------ ------------- COLUMN_NAME GRANTOR ------------------------------ ------------- PRIVILEGE GRA ---------------------------------------- --- STORE EMPLOYEES LAST_NAME STORE UPDATE NO STORE EMPLOYEES SALARY STORE UPDATE NO
Making Use of Object Privileges
Once a user has been granted an object privilege, they can use it to perform the specified task. For example, steve has the SELECT privilege on store.customers :
CONNECT steve/button SELECT * FROM store.customers; CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE ----------- ---------- ---------- --------- ------------ 1 John Brown 01-JAN-65 800-555-1211 2 Cynthia Green 05-FEB-68 800-555-1212 3 Steve White 16-MAR-71 800-555-1213 4 Gail Black 800-555-1214 5 Doreen Blue 20-MAY-70
If steve were to attempt to retrieve from the purchases table ”for which he doesn t have any permissions ”the database will return the error ORA-00942: table or view does not exist :
SQL> SELECT * 2 FROM store.purchases; FROM store.purchases * ERROR at line 2: ORA-00942: table or view does not exist
Synonyms
In the examples in the previous section, you saw that you can access tables in another schema by specifying the schema name followed by the table. For example, when steve retrieved rows from the customers table in the store schema, he performed a SELECT from store.customers . You can avoid having to enter the schema name by creating a synonym for a table, which you create using the CREATE SYNONYM statement.
Let s take a look at an example. First, connect as system and grant the CREATE SYNONYM system privilege to steve :
CONNECT system/manager GRANT CREATE SYNONYM TO steve;
Next, connect as steve and perform a CREATE SYNONYM statement to create a synonym for the store.customers table:
CONNECT steve/button CREATE SYNONYM customers FOR store.customers;
To retrieve rows from store.customers , all steve has to do is to reference the customers synonym in the FROM clause of a SELECT statement. For example:
SELECT * FROM customers; CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE ----------- ---------- ---------- --------- ------------ 1 John Brown 01-JAN-65 800-555-1211 2 Cynthia Green 05-FEB-68 800-555-1212 3 Steve White 16-MAR-71 800-555-1213 4 Gail Black 800-555-1214 5 Doreen Blue 20-MAY-70
Public Synonyms
You can also create a public synonym for a table. When you do this, all users see the synonym. The following statements perform the following tasks :
-
Connect as system
-
Grant the CREATE PUBLIC SYNONYM system privilege to store
-
Connect as store
-
Create a public synonym named products for store.products
CONNECT system/manager GRANT CREATE PUBLIC SYNONYM TO store; CONNECT store/store_password CREATE PUBLIC SYNONYM products FOR store.products;
If you connect as steve , who has the SELECT privilege on store.products , you can retrieve from store.products through the products public synonym:
CONNECT steve/button SELECT * FROM products;
Even though a public synonym has been created for store.products , a user still needs object privileges on that table to actually access the table. For example, gail can see the products public synonym, but gail doesn t have any object privileges on store.products . Therefore, if gail attempts to retrieve rows from products , the database returns the error ORA-00942: table or view does not exist :
SQL> CONNECT gail/seymour Connected. SQL> SELECT * FROM products; SELECT * FROM products * ERROR at line 1: ORA-00942: table or view does not exist
If gail had the SELECT object privilege on the store.products table, the previous SELECT would succeed.
If a user has other object privileges, that user can exercise those object privileges through a synonym. For example, if gail had the INSERT object privilege on the store.products table, gail would be able to add a row to store.products through the products synonym.
Revoking Object Privileges
You revoke object privileges using REVOKE . The following example connects as store and revokes the INSERT privilege on the products table from steve :
CONNECT store/store_password REVOKE INSERT ON products FROM steve;
The next example revokes the SELECT privilege on the customers table from steve :
REVOKE SELECT ON store.customers FROM steve;
When you revoke SELECT ON store.customers from steve ”who has already passed on this privilege to gail ” gail also loses the privilege.
EAN: 2147483647
Pages: 217