Many times, little thought or planning goes into the actual production phase of the application. What happens when many users are allowed to use the application across a wide area network (WAN)? With today's powerful personal computer software and with technologies such as Microsoft's Open Database Connectivity (ODBC), any user with access to your network can find a way to get at your database. (We won't even bring up the complexities involved when your company decides to hook your LAN to the Internet or some other wide-ranging computer network!) Are you prepared to face this situation?
Fortunately for you, software manufacturers provide most of the tools you need to handle this security problem. Every new release of a network operating system faces more stringent security requirements than its predecessors. In addition, most major database vendors build some degree of security into their products, which exists independently of your operating system or network security. Implementation of these security features varies widely from product to product.
TIP: Keep the following questions in mind as you plan your security system:
CREATE USER user IDENTIFIED {BY password | EXTERNALLY} [DEFAULT TABLESPACE tablespace] [TEMPORARY TABLESPACE tablespace] [QUOTA {integer [K|M] | UNLIMITED} ON tablespace] [PROFILE profile]If the BY password option is chosen, the system prompts the user to enter a password each time he or she logs on. As an example, create a username for yourself:
SQL> CREATE USER Bryan IDENTIFIED BY CUTIGER; User created.Each time I log on with my username Bryan, I am prompted to enter my password: CUTIGER.
If the EXTERNALLY option is chosen, Oracle relies on your computer system logon name and password. When you log on to your system, you have essentially logged on to Oracle.
As you can see from looking at the rest of the CREATE USER syntax, Oracle also allows you to set up default tablespaces and quotas. You can learn more about these topics by examining the Oracle documentation.
NOTE: Some implementations allow you to use the external, or operating system, password as a default when using SQL (IDENTIFIED externally). However, we recommend that you force the user to enter a password by utilizing the IDENTIFIED BY clause (IDENTIFIED BY password).
As with every other CREATE command you have learned about in this book, there is also an ALTER USER command. It looks like this:
ALTER USER user [IDENTIFIED {BY password | EXTERNALLY}] [DEFAULT TABLESPACE tablespace] [TEMPORARY TABLESPACE tablespace] [QUOTA {integer [K|M] | UNLIMITED} ON tablespace] [PROFILE profile] [DEFAULT ROLE { role [, role] ... | ALL [EXCEPT role [, role] ...] | NONE}]You can use this command to change all the user's options, including the password and profile. For example, to change the user Bryan's password, you type this:
SQL> ALTER USER Bryan 2 IDENTIFIED BY ROSEBUD; User altered.To change the default tablespace, type this:
SQL> ALTER USER RON 2 DEFAULT TABLESPACE USERS; User altered.To remove a user, simply issue the DROP USER command, which removes the user's entry in the system database. Here's the syntax for this command:
DROP USER user_name [CASCADE];If the CASCADE option is used, all objects owned by username are dropped along with the user's account. If CASCADE is not used and the user denoted by user_name still owns objects, that user is not dropped. This feature is somewhat confusing, but it is useful if you ever want to drop users.
GRANT role TO user [WITH ADMIN OPTION];If WITH ADMIN OPTION is used, that user can then grant roles to other users. Isn't power exhilarating?
To remove a role, use the REVOKE command:
REVOKE role FROM user;When you log on to the system using the account you created earlier, you have exhausted the limits of your permissions. You can log on, but that is about all you can do. Oracle lets you register as one of three roles:
NOTE: If you have the appropriate privileges, you can create your own role, grant privileges to your role, and then grant your role to a user for further security.
SQL> GRANT CONNECT TO Bryan; Grant succeeded.The Connect role enables the user to select, insert, update, and delete records from tables belonging to other users (after the appropriate permissions have been granted). The user can also create tables, views, sequences, clusters, and synonyms.
SQL> GRANT RESOURCE TO Bryan; Grant succeeded.
SQL> GRANT DBA TO Bryan; Grant succeeded.After the three preceding steps, user Bryan was granted the Connect, Resource, and DBA roles. This is somewhat redundant because the DBA role encompasses the other two roles, so you can drop them now:
SQL> REVOKE CONNECT FROM Bryan; Revoke succeeded. SQL> REVOKE RESOURCE FROM Bryan; Revoke succeeded.Bryan can do everything he needs to do with the DBA role.
System privileges apply systemwide. The syntax used to grant a system privilege is as follows:
GRANT system_privilege TO {user_name | role | PUBLIC} [WITH ADMIN OPTION];WITH ADMIN OPTION enables the grantee to grant this privilege to someone else.
SQL> GRANT CREATE VIEW 2 TO PUBLIC;
Grant succeeded.
WARNING: Use caution when granting privileges to public. Granting public gives all users with access to the database privileges you may not want them to have.
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. |
ALL |
ALTER |
DELETE |
EXECUTE |
INDEX |
INSERT |
REFERENCES |
SELECT |
UPDATE |
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]
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.
SQL> SELECT * FROM SALARIES; NAME SALARY AGE ------------------------------ --------- --------- JACK 35000 29 JILL 48000 42 JOHN 61000 55You 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.
SQL> SELECT * FROM SALARIES; SELECT * FROM SALARIES *
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 to identify the table:
SQL> SELECT * 2 FROM Bryan.SALARIES; NAME SALARY AGE ------------------------------ --------- --------- JACK 35000 29 JILL 48000 42 JOHN 61000 55
SQL> SELECT * 2 FROM Bryan.SALARIES; NAME SALARY AGE ------------------------------ --------- --------- JACK 35000 29 JILL 48000 42 JOHN 61000 55That 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
SQL> UPDATE Bryan.SALARIES 2 SET AGE = 42 3 WHERE NAME = 'JOHN'; UPDATE Bryan.SALARIES *
ERROR at line 1: ORA-01031: insufficient privileges
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
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.
SQL> SELECT * 2 FROM Bryan.SALARIES;
NAME SALARY AGE ------------------------------ --------- --------- JACK 35000 29 JILL 48000 42 JOHN 35000 55If 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 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
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 existLog out of Jack's account and test Jill's:
SQL> SELECT * FROM Bryan.JILL_SALARY; NAME SALARY AGE ------------------ ------------- ---- Jill 48000 42
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.
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.
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.
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;