Creating an Application Schema

Contact Us or call 1-877-932-8228
Creating an Application Schema

Creating An Application Schema

Code Sample:

Users-and-Schemas/Demos/create_user.sql
CREATE USER myusername IDENTIFIED BY mypassword;

DROP USER myusername CASCADE;

Code Explanation

Users are created by executing a CREATE USER statement as a privileged user. (Oracle users are assigned security related permissions based upon the requirements of a person's job and the sensitivity of the data. A privileged user is often a DBA - in this case it is a user who has the ability to create another user and grant the permissions listed. The simplest way to do this is to log on as a DBA user such as the system user).

You can drop a user by executing the DROP USER statement.

A user's characteristics can be modified using the ALTER statement. In the following example, we assign some permissions associated with a tablespace.

Code Sample:

Users-and-Schemas/Demos/alter_user.sql
ALTER USER myusername DEFAULT TABLESPACE users;

ALTER USER myusername QUOTA UNLIMITED ON users;

Code Explanation

A tablespace is a storage unit that contains data files that physically reside on a server. Each table and index created can be assigned to a specific tablespace. There are actually several types of table spaces - permanent, undo, and temporary. In this case, the USERS tablespace is a permanent tablespace where application user objects and data are stored.

DBAs have a challenging task of managing limited resources for a user base that frequently expresses unlimitted needs. One way a user can be limited is the amount of space they can use. This is done by limiting their "quota." By default, no space is allocated. The statement in question is used to allow a user the ability to take up space - and we are not concerned with using up this space in a classroom situation.

A user's access to the database itself and specific objects is set using the GRANT statement.

Code Sample:

Users-and-Schemas/Demos/grant_user.sql
GRANT CREATE SESSION, RESOURCE, CREATE SYNONYM, CREATE VIEW 
	TO myusername;

GRANT SELECT ON sometable TO myusername;

GRANT INSERT ON sometable TO myusername;

GRANT DELETE ON anothertable TO myusername;

Code Explanation

There are a number of other grants available, such as the ability to create database objects. These statements will be covered in the admin course.

There are numerous variations related to security depending upon your Oracle version and environment. Previous to Oracle 10g Release 2 you would also be required to "grant connect." Also note that additional privileges are needed to create and alter the objects described in this course. You will not need to be concerned with this in class, but should be aware of this if you plan to duplicate the HR environment at some later point.

A list of users associated with the database can be found by querying the ALL_USERS data dictionary table.

SELECT * FROM all_users;
Next