Oracle Database

Oracle 10g XE comes with 3 predefined roles:

* CONNECT - Enables a user to connect to the database. Grant this role to any user or application that needs database access.

* RESOURCE - Enables a user to create certain types of schema objects in his own schema. Grant this role only to developers and to other users that must create schema objects. This role grants a subset of the create object system privileges.

* DBA - Enables a user to perform most administrative functions, including creating users and granting privileges; creating and granting roles; creating and dropping schema objects in other users' schemas; and more. It grants all system privileges, but does not include the privileges to start up or shut down the database. It is by default granted to user SYSTEM.

An internal user account is a system predefined user account. Oracle 10g XE comes with a number of internal accounts:

* SYSTEM - This is the user account that you log in with to perform all administrative functions other than starting up and shutting down the database. SYSTEM is automatically created when you install the server. It's password is the one you specified during the installation process.

* SYS - This is another user account automatically created when you install the server. It's password is the one you specified during the installation process. All base tables and views for the database data dictionary are stored in the SYS schema. So avoid log in as user SYS as much as possible to reduce the risk of damaging those important data objects. User SYSTEM is preferred for all administrative tasks except starting up and shutting down.

* Other internal user accounts - Other special user accounts are predefined for special purposes. For example, CTXSYS is a special user account used by the Oracle Text product.

During the installation process, 10g XE will create a special Windows user group called ORA_DBA, and put your Windows user into this group. Any Windows users in this group can be connected to Oracle server with SYSDBA privilege without any Oracle server user account. This process is called connecting the server as SYSDBA with OS Authentication. Here is how to do this with a special form of the "connect" command:

(Log in with the same user you used to install 10g XE)

>cd (OracleXE home directory)

>.instartdb

>.insqlplus /nolog

SQL> connect / AS SYSDBA

Connected.

SQL> quit

So if "connect" is used without user name and password, the current Windows user will be trusted if he/she is in the ORA_DBA user group on the Windows system.

User accounts can be accessed through a system view called ALL_USERS. A simple SELECT statement can be used to get a list of all user accounts. Try the following script:

>.insqlplus /nolog

SQL> connect SYSTEM/globalguideline

Connected.

SQL> SELECT * FROM ALL_USERS;

USERNAME     USER_ID CREATED

------------------------------ ---------- ---------

FLOWS_020100     35 07-FEB-06

FLOWS_FILES     34 07-FEB-06

HR       33 07-FEB-06

MDSYS      32 07-FEB-06

ANONYMOUS     28 07-FEB-06

XDB      27 07-FEB-06

CTXSYS      25 07-FEB-06

DBSNMP      23 07-FEB-06

TSMSYS      20 07-FEB-06

DIP      18 07-FEB-06

OUTLN      11 07-FEB-06

SYSTEM      5 07-FEB-06

SYS       0 07-FEB-06

To be able to create tables in a user's own schema, the user needs to have the CREATE TABLE privilege, or the CREATE ANY TABLE privilege, which is more powerful, and allows the user to create tables in other user's schema. The following tutorial exercise gives you a good example on CREATE TABLE privilege:

>.insqlplus /nolog

SQL> CONNECT DEV/developer

SQL> CREATE TABLE ggl (id NUMBER);

ORA-01031: insufficient privileges

SQL> disconnect

SQL> connect SYSTEM/globalguideline

SQL> GRANT CREATE TABLE TO dev;

Grant succeeded.

SQL> disconnect

SQL> CONNECT DEV/developer

SQL> CREATE TABLE ggl (id NUMBER);

ORA-01950: no privileges on tablespace 'SYSTEM'

The above error message tells that user "dev" is not allowed to use the tablespace "SYSTEM". See the next question for answers.

When you create a new user, Oracle will assign the SYSTEM tablespace to the user by default. If you want to change this, you can assign a different table space to a user using the ALTER USER command. The following tutorial exercise changes user dev's default tablespace, and assigns 4MB of space to dev:

>.insqlplus /nolog

SQL> CONNECT DEV/developer

SQL> ALTER USER dev DEFAULT TABLESPACE USERS;

User altered.

SQL> ALTER USER dev QUOTA 4M ON USERS;

User altered.

SQL> disconnect

SQL> CONNECT DEV/developer

SQL> CREATE TABLE ggl (id NUMBER);

Table created.

SQL> DROP TABLE ggl;

Table dropped.

SQL> CREATE TABLE ggl (id NUMBER);

Table created.

As you can see, "dev" can create and drop tables now. You can also let "dev" to create tables in any tablespace without any restriction by granting him the UNLIMITED TABLESPACE system privilege.

To be able to create views in a user's own schema, the user needs to have the CREATE VIEW privilege, or the CREATE ANY VIEW privilege, which is more powerful, and allows the user to create views in other user's schema. The following tutorial exercise gives you a good example on CREATE VIEW privilege:

>.insqlplus /nolog

SQL> CONNECT DEV/developer

SQL> CREATE VIEW ggl_view AS SELECT * FROM ggl;

ORA-01031: insufficient privileges

SQL> disconnect

SQL> connect SYSTEM/globalguideline

SQL> GRANT CREATE VIEW TO dev;

Grant succeeded.

SQL> disconnect

SQL> CONNECT DEV/developer

SQL> CREATE VIEW ggl_view AS SELECT * FROM ggl;

View created.

SQL> DROP VIEW ggl_view;

View dropped.

SQL> CREATE VIEW ggl_view AS SELECT * FROM ggl;

View created.

As you can see, "dev" can create and drop views now.

For a user to run queries (SELECT statements) on tables of someone else's schema, he/she needs the SELECT ANY TABLE privilege. The following tutorial exercise gives you a good example of granting "dev" to query tables in "hr" schema:

>.insqlplus /nolog

SQL> CONNECT DEV/developer

SQL> SELECT COUNT(*) FROM hr.employees;

ORA-01031: insufficient privileges

SQL> disconnect

SQL> connect SYSTEM/globalguideline

SQL> GRANT SELECT ANY TABLE TO dev;

Grant succeeded.

SQL> disconnect

SQL> CONNECT DEV/developer

SQL> SELECT COUNT(*) FROM hr.employees;

COUNT(*)

----------

107

As you can see, "dev" can query tables in any schema now.

You also need to remember that table name must be prefixed with the schema name (same as owner user name).

For a user to insert rows into tables of someone else's schema, he/she needs the INSERT ANY TABLE privilege. The following tutorial exercise gives you a good example of granting "dev" to insert rows in "hr" schema:

>.insqlplus /nolog

SQL> CONNECT DEV/developer

SQL> INSERT INTO hr.jobs

VALUES ('DV.ggl', 'Dev ggl Consultant', 7700, 8800);

ORA-01031: insufficient privileges

SQL> disconnect

SQL> connect SYSTEM/globalguideline

SQL> GRANT INSERT ANY TABLE TO dev;

Grant succeeded.

SQL> disconnect

SQL> CONNECT DEV/developer

SQL> INSERT INTO hr.jobs

VALUES ('DV.ggl', 'Dev ggl Consultant', 7700, 8800);

1 row created.

As you can see, "dev" can insert rows in any schema now. But you should be careful when giving this privilege to a regular developer.

For a user to delete rows from tables of someone else's schema, he/she needs the DELETE ANY TABLE privilege. The following tutorial exercise gives you a good example of granting "dev" to delete rows in "hr" schema:

>.insqlplus /nolog

SQL> CONNECT DEV/developer

SQL> DELETE FROM hr.jobs WHERE job_id = 'DV.ggl';

ORA-01031: insufficient privileges

SQL> disconnect

SQL> connect SYSTEM/globalguideline

SQL> GRANT DELETE ANY TABLE TO dev;

Grant succeeded.

SQL> disconnect

SQL> CONNECT DEV/developer

SQL> DELETE FROM hr.jobs WHERE job_id = 'DV.ggl';

1 row deleted.

As you can see, "dev" can delete rows in any schema now. But you should be careful when giving this privilege to a regular developer.

Privileges granted to users are listed in two system views: DBA_SYS_PRIVS, and USER_SYS_PRIVS. You can find out what privileges a user currently has by running a query on those views as shown in the tutorial exercise below:

>.insqlplus /nolog

SQL> CONNECT DEV/developer

SQL> SELECT username, privilege FROM USER_SYS_PRIVS;

USERNAME     PRIVILEGE

------------------------------ ----------------------

DEV     SELECT ANY TABLE

DEV     INSERT ANY TABLE

DEV     CREATE SESSION

DEV     CREATE VIEW

DEV     DELETE ANY TABLE

DEV     CREATE ANY TABLE

SQL> disconnect

SQL> connect SYSTEM/globalguideline