IST 318 3/27/2003
Lab 8
Database Security
0. Connect as a Different User. You need to connect the Oracle server as different users in the lab when creating users and assigning privileges to them. The following SQL shows the syntax for this purpose. In this case, you can connect as user system identified by a password of manager. As you may have already noticed, user logins and passwords are not case-sensitive.
CONNECT system/manager
Run the following SQL to display the name and status of tablespaces available with the Oracle server we connect to.
select tablespace_name, status
from DBA_TABLESPACES
Part I Managing Users
1. Connect to the server instance ORACLEDB as yourself.
CONN <username>/<password>@oracledb
Use the following SQL to create user Bob (prefix your initials) with a password of CRUSADER.
CREATE USER bob
IDENTIFIED BY crusader
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE temp
QUOTA 1M ON USERS
QUOTA 1M ON INDX;
This statement can make sure that any objects created by Bob are not created in the system tablespace. Also, it ensures that Bob can log in and create objects up to one megabyte in size in the USERS and INDX tablespaces. ( It assigns Bob the default tablespace, USERS, and the temporary tablespace, TEMP.)
2 The following SQL creates a user Emi with a password of MARY and makes sure that any objects created by Emi are not created in the system tablespace.
CREATE USER emi
IDENTIFIED BY mary
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;
3 Run the following SQL to display the information on Bob and Emi from the data dictionary.
SELECT username, default_tablespace,
temporary_tablespace
FROM dba_users
WHERE username IN ('BOB', 'EMI');
4 From the data dictionary, display the information on the amount of space that Bob can use in tablespaces.
SELECT *
FROM dba_ts_quotas
WHERE username = 'BOB';
5 Connect as user Bob, and attempt to change his temporary tablespace. What happens?
CONNECT bob/crusader@oracledb
ALTER USER bob
TEMPORARY TABLESPACE users;
6 As user Bob, change his password to SAM.
ALTER USER bob
IDENTIFIED BY sam;
Part II Managing Privileges
7. As user SYSTEM, give user Emi the capability to log on to the database and create objects in her schema.
GRANT create session, create table TO emi;
8. Run the following SQL to connect as Emi and create the tables CUSTOMERS1 and ORDERS1.
CONNECT emi/mary@oracledb
CREATE TABLE customers1 (cust_code VARCHAR2(3),
name VARCHAR2(50),
region VARCHAR2(5) )
TABLESPACE users;
CREATE TABLE orders1 (ord_id NUMBER(3),
ord_date DATE,
cust_code VARCHAR2(3),
date_of_dely DATE )
TABLESPACE users;
Connect as SYSTEM and copy the data from SYSTEM.CUSTOMERS to Emi’s CUSTOMERS1 table. Verify that the records have been inserted.
CONNECT system/manager
INSERT INTO emi.customers1
SELECT *
FROM system.customers;
As user SYSTEM give Bob the ability to select from Emi's CUSTOMERS1 table. What happens?
GRANT select ON emi.customers1 TO bob;
9. Reconnect as Emi and give Bob the ability to select from Emi’s CUSTOMERS1 table. Also, enable Bob to give the select capability to other users.
GRANT select ON customers1
TO bob WITH GRANT OPTION;
As user SYSTEM, examine the data dictionary views (DBA_TAB_PRIVS) that record these actions.
CONNECT system/manager
SELECT *
FROM dba_tab_privs
WHERE grantee='BOB';
Deliverables
Submit your scripts by April 3, at beginning of class.
2