CONNECT BY PRIOR and ORDER SIBLINGS BY

Tested on an Oracle 9 database. Connect by prior was available in Oracle 8. It allows you to show recursive relationships such as managers and their employees or assemblies and their sub assemblies in a top-down fashion. The order by siblings clause is new. It lets you sort the siblings at each level. You can see the difference in the example below where the same data is displayed twice. The first time the siblings are not sorted whereas the second time they are:
  

SQL> col Manager format a15

SQL> col Employee format a15

SQL> create table emp

  2  (ename varchar2(10),

  3   mgr   varchar2(10));

 

Table created.

 

SQL> insert into emp values('Russell','King');

 

1 row created.

 

SQL> insert into emp values('Smith','King');

 

1 row created.

 

SQL> insert into emp values('Jones','King');

 

1 row created.

 

SQL> insert into emp values('Morrison','Russell');

 

1 row created.

 

SQL> insert into emp values('Matthews','Russell');

 

1 row created.

 

SQL> insert into emp values('Pearson','Russell');

 

1 row created.

 

SQL> insert into emp values('Williams','Smith');

 

1 row created.

 

SQL> insert into emp values('Brown','Smith');

 

1 row created.

 

SQL> insert into emp values('Fielder','Brown');

 

1 row created.

 

SQL> insert into emp values('Moir','Fielder');

 

1 row created.

 

SQL> select lpad(' ',level-1)||mgr Manager,

  2  lpad(' ',level-1)||ename Employee

  3  from emp

  4  start with mgr = 'King'

  5  connect by prior ename = mgr

  6  /

 

MANAGER         EMPLOYEE

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

King            Russell

 Russell         Morrison

 Russell         Matthews

 Russell         Pearson

King            Smith

 Smith           Williams

 Smith           Brown

  Brown           Fielder

   Fielder         Moir

King            Jones

 

10 rows selected.

 

SQL> select lpad(' ',level-1)||mgr Manager,

  2  lpad(' ',level-1)||ename Employee

  3  from emp

  4  start with mgr = 'King'

  5  connect by prior ename = mgr

  6  order siblings by ename

  7  /

 

MANAGER         EMPLOYEE

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

King            Jones

King            Russell

 Russell         Matthews

 Russell         Morrison

 Russell         Pearson

King            Smith

 Smith           Brown

  Brown           Fielder

   Fielder         Moir

 Smith           Williams

 

10 rows selected.

 

SQL>

Create User

This example was tested on an Oracle 9 database. When you create a user, you must either specify a password or make the user externally identified. Otherwise you will get an ORA-01938:
  

SQL> create user andrew;

create user andrew

                 *

ERROR at line 1:

ORA-01938: IDENTIFIED BY must be specified for CREATE USER

SQL> create user andrew identified by reid;

User created.

SQL> drop user andrew;

User dropped.

SQL> create user andrew identified externally;

User created.

SQL>

How to Change a Column from Number to Varchar2

This post, tested on Oracle 9, shows how to change a column definition from number to varchar2. First, create a test table:

SQL> col my_letter format a9
SQL> col my_number format 999999999
SQL> create table andrew1
  2  (my_letter varchar2(1),
  3   my_number number(1))
  4  /

Table created.

SQL> desc andrew1
Name                    Null?    Type
----------------------- -------- ----------------
MY_LETTER                        VARCHAR2(1)
MY_NUMBER                        NUMBER(1)

SQL>

Then add three rows of data:

SQL> insert into andrew1 values('A',1)
  2  /

1 row created.

SQL> insert into andrew1 values('B',2)
  2  /

1 row created.

SQL> insert into andrew1 values('C',3)
  2  /

1 row created.

SQL> select * from andrew1
  2  /

MY_LETTER  MY_NUMBER
--------- ----------
A                  1
B                  2
C                  3

SQL>

Next, try to modify the number column. This will fail and display an appropriate error message:

SQL> alter table andrew1 modify(my_number varchar2(1))
  2  /
alter table andrew1 modify(my_number varchar2(1))
                           *
ERROR at line 1:
ORA-01439: column to be modified must be empty to
change datatype

SQL>

Create another table and store the values in the column to be modified along with their rowids: 

SQL> create table andrew2 as
  2  select rowid my_rowid, my_number from andrew1
  3  /

Table created.

SQL>

Now remove the values from the column being modified and try the modify command again. This time it works:

SQL> update andrew1 set my_number = null
  2  /

3 rows updated.

SQL> alter table andrew1 modify(my_number varchar2(1))
  2  /

Table altered.

SQL> desc andrew1
Name                    Null?    Type
----------------------- -------- ----------------
MY_LETTER                        VARCHAR2(1)
MY_NUMBER                        VARCHAR2(1)

SQL>

Finally, using the rowid, reinstate the values in the modified column:

SQL> update andrew1 x
  2  set my_number =
  3  (select my_number from andrew2
  4   where my_rowid = x.rowid)
  5  /

3 rows updated.

SQL> col my_number format a9
SQL> select * from andrew1
  2  /

MY_LETTER MY_NUMBER
--------- ---------
A         1
B         2
C         3

SQL>

With Admin Option

This post was tested on Oracle 9. You can grant system privileges with or without the admin option. The default is to grant the system privilege without the admin option. As a first step, grant select any dictionary to public to allow the test users to look at dba_sys_privs:
 

SQL> col grantee format a10
SQL> col privilege format a15
SQL> conn / as sysdba
Connected.
SQL> grant select any dictionary to public
  2  /

Grant succeeded.

SQL>

Now create the first test user and allow it to connect to the database:

SQL> create user a identified by a
  2  /

User created.

SQL> grant create session to a
  2  /

Grant succeeded.

SQL>

You can see if a privilege has been granted with the admin option by looking at the admin_option column in dba_sys_privs: 

SQL> desc dba_sys_privs
Name                    Null?    Type
----------------------- -------- ----------------
GRANTEE                 NOT NULL VARCHAR2(30)
PRIVILEGE               NOT NULL VARCHAR2(40)
ADMIN_OPTION                     VARCHAR2(3)

SQL> select * from  dba_sys_privs
  2  where grantee in ('A', 'B', 'C')
  3  /

GRANTEE    PRIVILEGE       ADM
---------- --------------- ---
A          CREATE SESSION  NO

SQL> 

If a user has been granted a system privilege without the admin option, he cannot grant that privilege to other users:

SQL> create user b identified by b
  2  /

User created.

SQL> conn a/a
Connected.
SQL> grant create session to b
  2  /
grant create session to b
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL>

To get round this, you need to grant the system privilege to user A with the admin option:

SQL> conn / as sysdba
Connected.
SQL> grant create session to a with admin option
  2  /

Grant succeeded.

SQL> select * from  dba_sys_privs
  2  where grantee in ('A', 'B', 'C')
  3  /

GRANTEE    PRIVILEGE       ADM
---------- --------------- ---
A          CREATE SESSION  YES

SQL> conn a/a
Connected.
SQL> grant create session to b
  2  /

Grant succeeded.

SQL> select * from  dba_sys_privs
  2  where grantee in ('A', 'B', 'C')
  3  /

GRANTEE    PRIVILEGE       ADM
---------- --------------- ---
A          CREATE SESSION  YES
B          CREATE SESSION  NO

SQL>

User A can even grant create session to user B with the admin option:

SQL> grant create session to b with admin option
  2  /

Grant succeeded.

SQL> select * from  dba_sys_privs
  2  where grantee in ('A', 'B', 'C')
  3  /

GRANTEE    PRIVILEGE       ADM
---------- --------------- ---
A          CREATE SESSION  YES
B          CREATE SESSION  YES

SQL> 

Which might not be a great idea as user B can then revoke create session from user A:

SQL> conn b/b
Connected.
SQL> revoke create session from a
  2  /

Revoke succeeded.

SQL> select * from  dba_sys_privs
  2  where grantee in ('A', 'B', 'C')
  3  /

GRANTEE    PRIVILEGE       ADM
---------- --------------- ---
B          CREATE SESSION  YES

SQL> 

If you revoke a system privilege, the users it has passed that system privilege to will retain it:

SQL> conn / as sysdba
Connected.
SQL> create user c identified by c
  2  /

User created.

SQL> conn b/b
Connected.
SQL> grant create session to c with admin option
  2  /

Grant succeeded.

SQL> conn / as sysdba
Connected.
SQL> select * from  dba_sys_privs
  2  where grantee in ('A', 'B', 'C')
  3  /

GRANTEE    PRIVILEGE       ADM
---------- --------------- ---
B          CREATE SESSION  YES
C          CREATE SESSION  YES

SQL> revoke create session from b
  2  /

Revoke succeeded.

SQL> select * from  dba_sys_privs
  2  where grantee in ('A', 'B', 'C')
  3  /

GRANTEE    PRIVILEGE       ADM
---------- --------------- ---
C          CREATE SESSION  YES

SQL> 

Finally, to revoke the with admin option, you have to revoke the system privilege and grant it again without the admin option:

SQL> conn / as sysdba
Connected.
SQL> revoke create session from c
  2  /

Revoke succeeded.

SQL> grant create session to c
  2  /

Grant succeeded.

SQL> select * from  dba_sys_privs
  2  where grantee in ('A', 'B', 'C')
  3  /

GRANTEE    PRIVILEGE       ADM
---------- --------------- ---
C          CREATE SESSION  NO

SQL>

CONNECT Role

Think carefully before granting CONNECT as it has changed over the years.

In Oracle 7: The Complete Reference by George Koch, it says:

 

Versions of ORACLE before ORACLE7 had somewhat more limited privileges for the connect role.

 

It also says that the CONNECT role had the following system privileges in Oracle 7: ALTER SESSION, CREATE CLUSTER, CREATE DATABASE LINK, CREATE SEQUENCE, CREATE SESSION, CREATE SYNONYM, CREATE TABLE and CREATE VIEW.

 

In their Release 8.0 SQL Reference, Oracle say:

The CONNECT, RESOURCE and DBA roles are provided for compatibility with previous versions of Oracle. You should not rely on these roles; rather, Oracle recommends that you to (sic) design your own roles for database security. These roles may not be created automatically by future versions of Oracle.

Between Oracle 7 and Oracle 9, CONNECT did not change.

As mentioned above, CONNECT is a role, not a privilege:
  

SQL> select role from dba_roles

  2  where role = 'CONNECT'

  3  /

 

ROLE

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

CONNECT

 

SQL>

It had no roles granted to it in Oracle 9 and, as far as I can ascertain, it had none in other versions either:

SQL> select granted_role from dba_role_privs

  2  where grantee = 'CONNECT'

  3  /

 

no rows selected

 

SQL>

It had the following system privileges granted to it in Oracle 9:

SQL> select privilege from dba_sys_privs

  2  where grantee = 'CONNECT'

  3  /

 

PRIVILEGE

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

CREATE VIEW

CREATE TABLE

ALTER SESSION

CREATE CLUSTER

CREATE SESSION

CREATE SYNONYM

CREATE SEQUENCE

CREATE DATABASE LINK

 

8 rows selected.

 

SQL>

 

By Oracle 10 , it had changed and only had one privilege granted to it:

SQL> select privilege from dba_sys_privs

  2  where grantee = 'CONNECT'

  3  /

 

PRIVILEGE

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

CREATE SESSION

 

SQL>

So, what are the issues with granting CONNECT?

If you grant it in Oracle 9, you will have a problem when you upgrade, as users with CONNECT will lose 7 of the 8 privileges it provided.

If you grant it in Oracle 10 or above, you will get nothing more than the CREATE SESSION privilege. You may as well grant that instead, to avoid any confusion from people who are unaware of the change to CONNECT between Oracle 9 and Oracle 10.

GRANT CREATE ANY INDEX TO and GRANT INDEX ON

If you grant create any index to a user, this allows him to create an index on any table in any schema:

SQL> conn system/manager
Connected.
SQL> create table any_table
  2  (col1 varchar2(4))
  3  /

Table created.

SQL> create table specific_table
  2  (col1 varchar2(4))
  3  /

Table created.

SQL> create user andrew identified by reid
  2  default tablespace users
  3  quota unlimited on users
  4  /

User created.

SQL> grant create session, create any index to andrew
  2  /

Grant succeeded.

SQL> conn andrew/reid
Connected.
SQL> create index any_table_index
  2  on system.any_table(col1)
  3  /

Index created.

SQL> create index specific_table_index
  2  on system.specific_table(col1)
  3  /

Index created.

SQL> drop index any_table_index
  2  /

Index dropped.

SQL> drop index specific_table_index
  2  /

Index dropped.

SQL>

If you really need to allow users to create indexes in other schemas, you could consider limiting this privilege to specific tables with the grant index on command:

SQL> conn system/manager
Connected.
SQL> revoke create any index from andrew
  2  /

Revoke succeeded.

SQL> grant index on specific_table to andrew
  2  /

Grant succeeded.

SQL> conn andrew/reid
Connected.
SQL> create index any_table_index
  2  on system.any_table(col1)
  3  /
on system.any_table(col1)
          *
ERROR at line 2:
ORA-00942: table or view does not exist

SQL> create index specific_table_index
  2  on system.specific_table(col1)
  3  /

Index created.

SQL>

Drop User

This example was tested on an Oracle 10 database. You can remove a user from the database with the drop user command:

SQL> conn system/manager
Connected.
SQL> create user andrew identified by reid
  2  /

User created.

SQL> drop user andrew
  2  /

User dropped.

SQL>

But if the user owns any objects, you have to add the cascade option to the drop user command otherwise you will get an ORA-01922:

SQL> create user andrew identified by reid
  2  default tablespace users
  3  quota unlimited on users
  4  /

User created.

SQL> grant create session, create table to andrew
  2  /

Grant succeeded.

SQL> conn andrew/reid
Connected.
SQL> create table test_table (col1 number)
  2  /

Table created.

SQL> conn system/manager
Connected.
SQL> drop user andrew
  2  /
drop user andrew
*
ERROR at line 1:
ORA-01922: CASCADE must be specified to drop 'ANDREW'

SQL> drop user andrew cascade
  2  /

User dropped.

SQL>

Autocommit (Part 1)

(Tested on an Oracle 9 database.)

This is a worked example to show how the SQL*Plus autocommit command works. First show its default value:

SQL> show autocommit
autocommit OFF
SQL>

Show that when autocommit is OFF, transactions are not committed automatically:

SQL> create table test_autocommit (col1 varchar2(10))
  2  /

Table created.

SQL> insert into test_autocommit values('Andrew')
  2  /

1 row created.

SQL> select * from test_autocommit
  2  /

COL1
----------
Andrew

SQL> rollback
  2  /

Rollback complete.

SQL> select * from test_autocommit
  2  /

no rows selected

SQL>

Show that when autocommit is ON, transactions are committed automatically (the commit statement you can see was done by Oracle, not by me):

SQL> set autocommit on
SQL> show autocommit
autocommit IMMEDIATE
SQL> insert into test_autocommit values('Andrew')
  2  /

1 row created.

Commit complete.
SQL> select * from test_autocommit
  2  /

COL1
----------
Andrew

SQL> rollback
  2  /

Rollback complete.

SQL> select * from test_autocommit
  2  /

COL1
----------
Andrew

SQL>

If autocommit is set to 3 (for example) then Oracle commits automatically after 3 insert, update or delete statements (again, Oracle did the commit you can see):

SQL> set autocommit 3
SQL> show autocommit
AUTOCOMMIT ON for every 3 DML statements
SQL> update test_autocommit set col1 = 'Brian'
  2  /

1 row updated.

SQL> select * from test_autocommit
  2  /

COL1
----------
Brian

SQL> insert into test_autocommit values ('Colin')
  2  /

1 row created.

SQL> select * from test_autocommit
  2  /

COL1
----------
Brian
Colin

SQL> delete from test_autocommit where col1 = 'Brian'
  2  /

1 row deleted.

Commit complete.
SQL> select * from test_autocommit
  2  /

COL1
----------
Colin

SQL> rollback
  2  /

Rollback complete.

SQL> select * from test_autocommit
  2  /

COL1
----------
Colin

SQL>

With Grant Option

This post illustrates a problem I had recently while running some SQL, which failed because the person who wrote it did not understand the with grant option.

First create a user to own a table:

SQL> create user data_owner identified by data_owner
  2  quota unlimited on users
  3  /

User created.

SQL> grant create session, create table to data_owner
  2  /

Grant succeeded.

SQL>

Next, create a user who will have a view on that table:

SQL> create user view_owner identified by view_owner
  2  /

User created.

SQL> grant create session, create view to view_owner
  2  /

Grant succeeded.

SQL>

Then create a test user who will have access to the view:

SQL> create user tester identified by tester
  2  /

User created.

SQL>

Create the table and allow view_owner to look at it:

SQL> conn data_owner/data_owner
Connected.
SQL> create table test_table (col1 varchar2(5))
  2  /

Table created.

SQL> grant select on test_table to view_owner
  2  /

Grant succeeded.

SQL>

Now create the view:

SQL> conn view_owner/view_owner
Connected.
SQL> create view test_view as
  2  select * from data_owner.test_table
  3  /

View created.

SQL>

Try to allow tester to see test_view. This fails because although view_owner can see test_table, it does not have permission to allow other users to look at it:

SQL> grant select on test_view to tester
  2  /
grant select on test_view to tester
                *
ERROR at line 1:
ORA-01720: grant option does not exist for
'DATA_OWNER.TEST_TABLE'

SQL>

To get round this problem, data_owner needs to add with grant option to the end of the grant statement:

SQL> conn data_owner/data_owner
Connected.
SQL> grant select on test_table to view_owner
  2  with grant option
  3  /

Grant succeeded.

SQL> conn view_owner/view_owner
Connected.
SQL> grant select on test_view to tester
  2  /

Grant succeeded.

SQL>

Grant Create Index?

(This first part was tested on an Oracle 10 database.) I always thought that if you could create a table, you could create an index too:

SQL> create user andrew
  2  identified by reid
  3  default tablespace users
  4  quota unlimited on users;

User created.

SQL> grant create session, create table to andrew;

Grant succeeded.

SQL> conn andrew/reid
Connected.
SQL> create table test_table
  2  (col1 varchar2(4));

Table created.

SQL> create index test_index
  2  on test_table(col1);

Index created.

SQL>

But looking through a book about Oracle 11 on Google books, I came across a reference to granting a create index privilege. I never knew that such a privilege existed. I cannot show you the content as it is covered by copyright and I do not want to give the book's name as it comes from a series which are usually accurate.

I decided to try it out, this time on an Oracle 11 database, but I could not get it to work:

TEST11 > sqlplus /

SQL*Plus: Release 11.2.0.1.0 Production on Tue Jun 14 12:20:05 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create user andrew identified by reid;

User created.

SQL> grant create index to andrew;

grant create index to andrew

      *

ERROR at line 1:

ORA-00990: missing or invalid privilege

SQL>

Stored Procedures and Roles (Example 2)

Go back to example 1 

A colleague showed me another example of Oracle not recognising grants made to roles when creating stored procedures. I'm including it as it is slightly easier to follow than the first example. It was tested on an Oracle 9 database. First create a test user:

SQL> conn / as sysdba
Connected.
SQL> create user andrew identified by reid
  2  default tablespace user_data
  3  quota unlimited on user_data
  4  /

User created.

SQL> grant create session to andrew
  2  /

Grant succeeded.

SQL> 

Connect as the test user and try to create a table. This will fail as the user does not have the create table privilege:

SQL> conn andrew/reid
Connected.
SQL> create table test_table(one_col number)
  2  /
create table test_table(one_col number)
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> 

Create a role with the create table privilege and grant it to the test user:

SQL> conn / as sysdba
Connected.
SQL> create role table_creator
  2  /

Role created.

SQL> grant create table to table_creator
  2  /

Grant succeeded.

SQL> grant table_creator to andrew
  2  /

Grant succeeded.

SQL> 

Connect as the test user using SQL*Plus and show that this new role allows him to create a table:

SQL> conn andrew/reid
Connected.
SQL> create table test_table(one_col number)
  2  /

Table created.

SQL> drop table test_table
  2  /

Table dropped.

SQL>

Allow the test user to create stored code:

SQL> conn / as sysdba
Connected.
SQL> grant create procedure to andrew
  2  /

Grant succeeded.

SQL> 

Connect as the test user and create a procedure to recreate the same table. Run the procedure. It will fail as stored code ignores grant statements passed via roles:

SQL> conn andrew/reid
Connected.
SQL> create procedure role_tester
  2  is
  3  begin
  4  execute immediate
  5  ('create table test_table(one_col number)');
  6  end;
  7  /

Procedure created.

SQL> exec role_tester;
BEGIN role_tester; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "ANDREW.ROLE_TESTER", line 4
ORA-06512: at line 1

SQL> desc test_table
ERROR:
ORA-04043: object test_table does not exist

SQL>

Now grant the create table privilege directly to the user:

SQL> conn / as sysdba
Connected.
SQL> grant create table to andrew
  2  /

Grant succeeded.

SQL>

Connect as the test user. Run the procedure again. This time it will work:

SQL> conn andrew/reid
Connected.
SQL> exec role_tester;

PL/SQL procedure successfully completed.

SQL> desc test_table
Name                    Null?    Type
----------------------- -------- ----------------
ONE_COL                          NUMBER

SQL>