![]() |
||||||
![]()
|
![]() |
|
![]() |
Teach Yourself SQL in 21 Days, Second Edition- Day 12 -
|
System Privilege | Operations Permitted |
ALTER ANY INDEX | Allows the grantees to alter any index in any schema. |
ALTER ANY PROCEDURE | Allows the grantees to alter any stored procedure, function, or package in any schema. |
ALTER ANY ROLE | Allows the grantees to alter any role in the database. |
ALTER ANY TABLE | Allows the grantees to alter any table or view in the schema. |
ALTER ANY TRIGGER | Allows the grantees to enable, disable, or compile any database trigger in any schema. |
ALTER DATABASE | Allows the grantees to alter the database. |
ALTER USER | Allows the grantees to alter any user. This privilege authorizes the grantee to change another user's password or authentication method, assign quotas on any tablespace, set default and temporary tablespaces, and assign a profile and default roles. |
CREATE ANY INDEX | Allows the grantees to create an index on any table in any schema. |
CREATE ANY PROCEDURE | Allows the grantees to create stored procedures, functions, and packages in any schema. |
CREATE ANY TABLE | Allows the grantees to create tables in any schema. The owner of the schema containing the table must have space quota on the tablespace to contain the table. |
CREATE ANY TRIGGER | Allows the grantees to create a database trigger in any schema associated with a table in any schema. |
CREATE ANY VIEW | Allows the grantees to create views in any schema. |
CREATE PROCEDURE | Allows the grantees to create stored procedures, functions, and packages in their own schema. |
CREATE PROFILE | Allows the grantees to create profiles. |
CREATE ROLE | Allows the grantees to create roles. |
CREATE SYNONYM | Allows the grantees to create synonyms in their own schemas. |
CREATE TABLE | Allows the grantees to create tables in their own schemas. To create a table, the grantees must also have space quota on the tablespace to contain the table. |
CREATE TRIGGER | Allows the grantees to create a database trigger in their own schemas. |
CREATE USER | Allows the grantees to create users. This privilege also allows the creator to assign quotas on any tablespace, set default and temporary tablespaces, and assign a profile as part of a CREATE USER statement. |
CREATE VIEW | Allows the grantees to create views in their own schemas. |
DELETE ANY TABLE | Allows the grantees to delete rows from tables or views in any schema or truncate tables in any schema. |
DROP ANY INDEX | Allows the grantees to drop indexes in any schema. |
DROP ANY PROCEDURE | Allows the grantees to drop stored procedures, functions, or packages in any schema. |
DROP ANY ROLE | Allows the grantees to drop roles. |
DROP ANY SYNONYM | Allows the grantees to drop private synonyms in any schema. |
DROP ANY TABLE | Allows the grantees to drop tables in any schema. |
DROP ANY TRIGGER | Allows the grantees to drop database triggers in any schema. |
DROP ANY VIEW | Allows the grantees to drop views in any schema. |
DROP USER | Allows the grantees to drop users. |
EXECUTE ANY PROCEDURE | Allows the grantees to execute procedures or functions (standalone or packaged) or reference public package variables in any schema. |
GRANT ANY PRIVILEGE | Allows the grantees to grant any system privilege. |
GRANT ANY ROLE | Allows the grantees to grant any role in the database. |
INSERT ANY TABLE | Allows the grantees to insert rows into tables and views in any schema. |
LOCK ANY TABLE | Allows the grantees to lock tables and views in any schema. |
SELECT ANY SEQUENCE | Allows the grantees to reference sequences in any schema. |
SELECT ANY TABLE | Allows the grantees to query tables, views, or snapshots in any schema. |
UPDATE ANY ROWS | Allows the grantees to update rows in tables. |
Object privileges are privileges that can be used against specific database objects. Table 12.2 lists the object privileges in Oracle7.
ALL |
ALTER |
DELETE |
EXECUTE |
INDEX |
INSERT |
REFERENCES |
SELECT |
UPDATE |
You can use the following form of the GRANT statement to give other users access to your tables:
GRANT {object_priv | ALL [PRIVILEGES]} [ (column [, column]...) ] [, {object_priv | ALL [PRIVILEGES]} [ (column [, column] ...) ] ] ... ON [schema.]object TO {user | role | PUBLIC} [, {user | role | PUBLIC}] ... [WITH GRANT OPTION]
To remove the object privileges you have granted to someone, use the REVOKE command with the following syntax:
REVOKE {object_priv | ALL [PRIVILEGES]} [, {object_priv | ALL [PRIVILEGES]} ] ON [schema.]object FROM {user | role | PUBLIC} [, {user | role | PUBLIC}] [CASCADE CONSTRAINTS]
Create a table named SALARIES with the following structure:
NAME, CHAR(30) SALARY, NUMBER AGE, NUMBER SQL> CREATE TABLE SALARIES ( 2 NAME CHAR(30), 3 SALARY NUMBER, 4 AGE NUMBER);
Table created.
Now, create two users--Jack and Jill:
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.
So far, you have created two users and granted each a different role. Therefore, they will have different capabilities when working with the database. First create the SALARIES table with the following information:
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.
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.
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.
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.
SQL> SELECT * FROM SALARIES; SELECT * FROM SALARIES *
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:
SQL> SELECT * 2 FROM Bryan.SALARIES; NAME SALARY AGE ------------------------------ --------- --------- JACK 35000 29 JILL 48000 42 JOHN 61000 55
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).
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.
SQL> INSERT INTO Bryan.SALARIES 2 VALUES('JOE',85000,38); INSERT INTO Bryan.SALARIES *
ERROR at line 1: ORA-01031: insufficient privileges
This operation did not work because Jill does not have INSERT privileges on the SALARIES table.
SQL> UPDATE Bryan.SALARIES 2 SET AGE = 42 3 WHERE NAME = 'JOHN'; UPDATE Bryan.SALARIES *
ERROR at line 1: ORA-01031: insufficient privileges
Once again, Jill tried to go around the privileges that she had been given. Naturally, Oracle caught this error and corrected her quickly.
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
You can see now that the update works as long as Jill abides by the privileges she has been given.
As 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 exists in the database. Today you will learn more about how to use views to implement security 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 that another 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 table before they could select the contents of a table, which is not something you want all your users to do. One simple solution is shown in the following paragraph.
Assume that you are logged on as Jack, your friend from earlier examples. You learned that for Jack to look at the contents of the SALARIES table, he must use the following statement:
SQL> SELECT * 2 FROM Bryan.SALARIES;
NAME SALARY AGE ------------------------------ --------- --------- JACK 35000 29 JILL 48000 42 JOHN 35000 55
If you were to create a view named SALARY_VIEW, a user could simply select from that view.
SQL> CREATE VIEW SALARY_VIEW 2 AS SELECT * 3 FROM Bryan.SALARIES; View created. SQL> SELECT * FROM SALARY_VIEW; NAME SALARY AGE ------------------------------ --------- --------- JACK 35000 29 JILL 48000 42 JOHN 35000 55
The preceding query returned the same values as the records returned from Bryan.SALARIES.
SQL also provides an object known as a synonym. A synonym provides an alias for a table to simplify or minimize keystrokes when using a table in an SQL statement. There are two types of synonyms: private and public. Any user with the resource role can create a private synonym. On the other hand, only a user with the DBA role can create a public synonym.
The syntax for a public synonym follows.
CREATE [PUBLIC] SYNONYM [schema.]synonym FOR [schema.]object[@dblink]
In the preceding example, you could have issued the following command to achieve the same results:
SQL> CREATE PUBLIC SYNONYM SALARY FOR SALARIES
Synonym created.
Then log back on to Jack and type this:
SQL> SELECT * FROM SALARY; NAME SALARY AGE ------------------------------ --------- --------- JACK 35000 29 JILL 48000 42 JOHN 35000 55
Suppose you changed your mind about Jack and Jill and decided that neither of them should be able to look at the SALARIES table completely. You can use views to change this situation and allow them to examine only their own information.
SQL> CREATE VIEW JACK_SALARY AS 2 SELECT * FROM BRYAN.SALARIES 3 WHERE NAME = 'JACK'; View created.
SQL> CREATE VIEW JILL_SALARY AS 2 SELECT * FROM BRYAN.SALARIES 3 WHERE NAME = 'JILL'; View created.
SQL> GRANT SELECT ON JACK_SALARY 2 TO JACK; Grant succeeded.
SQL> GRANT SELECT ON JILL_SALARY 2 TO JILL; Grant succeeded.
SQL> REVOKE SELECT ON SALARIES FROM JACK; Revoke succeeded.
SQL> REVOKE SELECT ON SALARIES FROM JILL; Revoke succeeded.
Now log on as Jack and test out the view you created for him.
SQL> SELECT * FROM Bryan.JACK_SALARY; NAME SALARY AGE ---------- ---------- ---- Jack 35000 29
SQL> SELECT * FROM PERKINS.SALARIES; SELECT * FROM PERKINS.SALARIES *
ERROR at line 1: ORA-00942: table or view does not exist
Log out of Jack's account and test Jill's:
SQL> SELECT * FROM Bryan.JILL_SALARY; NAME SALARY AGE ------------------ ------------- ---- Jill 48000 42
You can see that access to the SALARIES table was completely controlled using views. SQL enables you to create these views as you like and then assign permissions to other users. This technique allows a great deal of flexibility.
The syntax to drop a synonym is
SQL> drop [public] synonym synonym_name;
NOTE: By now, you should understand the importance of keeping to a minimum the number of people with DBA roles. A user with this access level can have complete access to all commands and operations within the database. Note, however, that with Oracle and Sybase you must have DBA-level access (or SA-level in Sybase) to import or export data on the database.
What do you think would happen if Jill attempted to pass her UPDATE privilege on to Jack? At first glance you might think that Jill, because she was entrusted with the UPDATE privilege, should be able to pass it on to other users who are allowed that privilege. However, using the GRANT statement as you did earlier, Jill cannot pass her privileges on to others:
SQL> GRANT SELECT, UPDATE(SALARY) ON Bryan.SALARIES TO Jill;
Here is the syntax for the GRANT statement that was introduced earlier today:
GRANT {object_priv | ALL [PRIVILEGES]} [ (column [, column]...) ] [, {object_priv | ALL [PRIVILEGES]} [ (column [, column] ...) ] ] ... ON [schema.]object TO {user | role | PUBLIC} [, {user | role | PUBLIC}] ... [WITH GRANT OPTION]
What you are looking for is the WITH GRANT OPTION clause at the end of the GRANT statement. When object privileges are granted and WITH GRANT OPTION is used, these privileges can be passed on to others. So if you want to allow Jill to pass on this privilege to Jack, you would do the following:
SQL> GRANT SELECT, UPDATE(SALARY) 2 ON Bryan.SALARIES TO JILL 3 WITH GRANT OPTION;
Grant succeeded.
Jill could then log on and issue the following command:
SQL> GRANT SELECT, UPDATE(SALARY) 2 ON Bryan.SALARIES TO JACK; Grant succeeded.
Security is an often-overlooked topic that can cause many problems if not properly thought out and administered. Fortunately, SQL provides several useful commands for implementing security on a database.
Users are originally created using the CREATE USER command, which sets up a username and password for a user. After the user account has been set up, this user must be assigned to a role in order to accomplish any work. The three roles available within Oracle7 are Connect, Resource, and DBA. Each role has different levels of access to the database, with Connect being the simplest and DBA having access to everything.
The GRANT command gives a permission or privilege to a user. The REVOKE command can take that permission or privilege away from the user. The two types of privileges are object privileges and system privileges. The system privileges should be monitored closely and should not be granted to inexperienced users. Giving inexperienced users access to commands allows them to (inadvertently perhaps) destroy data or databases you have painstakingly set up. Object privileges can be granted to give users access to individual objects existing in the owner's database schema.
All these techniques and SQL statements provide the SQL user with a broad range of tools to use when setting up system security. Although we focused on the security features of Oracle7, you can apply much of this information to the database system at your site. Just remember that no matter what product you are using, it is important to enforce some level of database security.
A No, especially in larger applications where there are multiple users. Because different users will be doing different types of work in the database, you'll want to limit what users can and can't do. Users should have only the necessary roles and privileges they need to do their work.
Q It appears that there is a security problem when the DBA that created my ID also knows the password. Is this true?
A Yes it is true. The DBA creates the IDs and passwords. Therefore, users should use the ALTER USER command to change their ID and password immediately after receiving them.
The Workshop provides quiz questions to help solidify your understanding of the material covered, as well as exercises to provide you with experience in using what you have learned. Try to answer the quiz and exercise questions before checking the answers in Appendix F, "Answers to Quizzes and Exercises."
SQL> GRANT CONNECTION TO DAVID;
3. What would happen if you created a table and granted select privileges on the table to public?
4. Is the following SQL statement correct?
SQL> create user RON identified by RON;
SQL> alter RON identified by RON;
SQL> grant connect, resource to RON;
© Copyright, Macmillan Computer Publishing. All rights reserved.
![]() |
|
Use of this site is subject certain Terms & Conditions. Copyright (c) 1996-1999 EarthWeb, Inc.. All rights reserved. Reproduction in whole or in part in any form or medium without express written permission of EarthWeb is prohibited. Please read our privacy policy for details. |