Friday, August 14, 2009

Oracle 10g database authentication

This is my attempt to get my collective knowledge of authorization in the Oracle 10g Database world in one location. The following steps and information assume you have access to a super-user (i.e. AS SYSDBA) and a database with a "USERS" tablespace.

First, create the schema owner user, read and update roles and then grant built-in roles with necessary system privileges to the users. Also grant the read role to the write role.

CREATE USER test_schema IDENTIFIED BY "t3sting"
PROFILE DEFAULT
DEFAULT TABLESPACE users
QUOTA UNLIMITED ON users
ACCOUNT UNLOCK;
CREATE USER test_user IDENTIFIED BY "t3sting"
PROFILE DEFAULT
DEFAULT TABLESPACE users
QUOTA 5M ON users
ACCOUNT UNLOCK;
CREATE ROLE test_READ NOT IDENTIFIED;
CREATE ROLE test_CRUD IDENTIFIED BY "supercomplicatedpassword";
GRANT CONNECT TO test_schema,test_user;
GRANT RESOURCE TO test_schema;
GRANT test_READ TO test_CRUD;

Next, connect as schema owner to create objects and then grant object privileges to them to the read and write roles

SQL> CONN test_schema
Enter password:
Connected.
CREATE TABLE test_table
COMPRESS
AS
SELECT
TO_CHAR(time_date,'YYYYMMDD')||'.01' AS time_id
, time_date
, EXTRACT(YEAR FROM time_year_beg_date) AS time_year
, time_year_beg_date
, ADD_MONTHS(time_year_beg_date-1,12) AS time_year_end_date
, EXTRACT(MONTH FROM time_date) AS time_month
, TRUNC(time_date,'MONTH') AS time_month_beg_date
, ADD_MONTHS(time_date-1,12) AS time_month_end_date
, TO_CHAR(time_date,'Day') AS time_day_of_week
, CASE time_date
WHEN time_year_beg_date THEN 'New Years Day'
WHEN NEXT_DAY(time_year_beg_date+30,'MON')+14 THEN 'Presidents Day'
WHEN NEXT_DAY(ADD_MONTHS(time_year_beg_date,5)-1,'MON')-7 THEN 'Memorial Day'
WHEN ADD_MONTHS(time_year_beg_date,6)+3 THEN 'Independance Day'
WHEN NEXT_DAY(ADD_MONTHS(time_year_beg_date,8)-1,'MON') THEN 'Labor Day'
WHEN ADD_MONTHS(time_year_beg_date,10)+10 THEN 'Veterans Day'
WHEN NEXT_DAY(ADD_MONTHS(time_year_beg_date,10)-1,'THU')+21 THEN 'Thanksgiving'
WHEN ADD_MONTHS(time_year_beg_date,11)+24 THEN 'Christmas'
ELSE NULL
END AS time_holiday_description
FROM (
SELECT
TRUNC(SYSDATE,'YEAR')+(LEVEL - 1) AS time_date
, TRUNC(TRUNC(SYSDATE,'YEAR')+(LEVEL - 1),'YEAR') AS time_year_beg_date
FROM DUAL
CONNECT BY ROWNUM BETWEEN 1 AND ADD_MONTHS(TRUNC(SYSDATE,'YEAR'),36)-TRUNC(SYSDATE,'YEAR')
);
Table created.

SQL> DESC test_table
Name Null? Type
----------------------------------------- -------- ----------------------------
TIME_ID VARCHAR2(11)
TIME_DATE DATE
TIME_YEAR NUMBER
TIME_YEAR_BEG_DATE DATE
TIME_YEAR_END_DATE DATE
TIME_MONTH NUMBER
TIME_MONTH_BEG_DATE DATE
TIME_MONTH_END_DATE DATE
TIME_DAY_OF_WEEK VARCHAR2(9)
TIME_HOLIDAY_DESCRIPTION VARCHAR2(16)

SQL> SELECT COUNT(*) FROM test_table;

COUNT(*)
----------
1095
GRANT SELECT ON test_table TO test_READ;
GRANT INSERT,UPDATE,DELETE ON ON test_table TO test_CRUD;

The test_user USER has CREATE SESSION system privileges so test_user can create a session and connect to Oracle and see PUBLIC objects. The test_user cannot see or access test_schema.test_table

SQL> CONN test_user
Enter password:
Connected.

SQL> DESC test_schema.test_table
ERROR:
ORA-04043: object test_schema.test_table does not exist

Authorization to schema objects can be granted: directly to the USER, indirectly through a ROLE, or through granted execute privilege on a source object (PROCEDURE, FUNCTION or PACKAGE) created with AUTHID DEFINER.

GRANT SELECT ON test_table TO test_user;

Privileges granted directly to the USER are recognized immediately

SQL> DESC test_schema.test_table
Name Null? Type
----------------------------------------- -------- ----------------------------
TIME_ID VARCHAR2(11)
TIME_DATE DATE
TIME_YEAR NUMBER
TIME_YEAR_BEG_DATE DATE
TIME_YEAR_END_DATE DATE
TIME_MONTH NUMBER
TIME_MONTH_BEG_DATE DATE
TIME_MONTH_END_DATE DATE
TIME_DAY_OF_WEEK VARCHAR2(9)
TIME_HOLIDAY_DESCRIPTION VARCHAR2(16)

SQL> SELECT DISTINCT time_holiday_description FROM TEST_SCHEMA.test_table;

TIME_HOLIDAY_DES
----------------
Veterans Day
New Years Day
Independance Day
Memorial Day
Presidents Day
Labor Day
Thanksgiving

8 rows selected.

As schema owner user revoke the privilege from the USER and then grant the read ROLE to USER test_user.

REVOKE SELECT ON test_table FROM test_user;
GRANT test_READ TO test_user;
*
ERROR at line 1:
ORA-01919: role 'TEST_READ' does not exist

Unless granted the privilege to do so, the schema owner USER cannot GRANT a ROLE to a USER; a super-user must do it.

GRANT test_READ TO test_user;

The ROLE privileges are not available until the USER reconnects or sets the ROLE

SQL> DESC test_schema.test_table
ERROR:
ORA-04043: object test_schema.test_table does not exist

SQL> SET ROLE test_READ;

Role set.

SQL> DESC test_schema.test_table
Name Null? Type
----------------------------------------- -------- ----------------------------
TIME_ID VARCHAR2(11)
TIME_DATE DATE
TIME_YEAR NUMBER
TIME_YEAR_BEG_DATE DATE
TIME_YEAR_END_DATE DATE
TIME_MONTH NUMBER
TIME_MONTH_BEG_DATE DATE
TIME_MONTH_END_DATE DATE
TIME_DAY_OF_WEEK VARCHAR2(9)
TIME_HOLIDAY_DESCRIPTION VARCHAR2(16)

SQL> UPDATE TEST_SCHEMA.test_table
SET time_holiday_description = 'Special Day'
WHERE time_id LIKE '____1107%';
UPDATE TEST_SCHEMA.test_table
*
ERROR at line 1:
ORA-01031: insufficient privileges

The test_user USER cannot update test_schema.test_table without privileges. The test_schema USER granted the privileges to the test_CRUD ROLE so a super-user can GRANT that ROLE to test_user.

GRANT test_CRUD TO test_user;

Connect as test_user and try again.

SQL> CONN test_user
Enter password:
Connected.
SQL> UPDATE TEST_SCHEMA.test_table
SET time_holiday_description = 'Special Day'
WHERE time_id LIKE '____1107%';

3 rows updated.

SQL> ROLLBACK;

Rollback complete.

Notice how test_user was able to update the table but did not need the password for the test_CRUD role? When a ROLE is granted to a USER or another ROLE it is as a DEFAULT ROLE and all default roles are applied when the session is created. The USER must be altered by a super-user to indicate what roles are default (or not default).

ALTER USER test_user DEFAULT ROLE ALL EXCEPT test_CRUD;

The test_user USER will not have the test_CRUD ROLE when it creates a session.

SQL> CONN test_user
Enter password:
Connected.

SQL> UPDATE TEST_SCHEMA.test_table
SET time_holiday_description = 'Special Day'
WHERE time_id LIKE '____1107%';

UPDATE TEST_SCHEMA.test_table
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> SET ROLE test_CRUD;
SET ROLE test_CRUD
*
ERROR at line 1:
ORA-01979: missing or invalid password for role 'TEST_CRUD'

SQL> SET ROLE test_CRUD IDENTIFIED BY "supercomplicatedpassword";

Role set.

SQL> UPDATE TEST_SCHEMA.test_table
SET time_holiday_description = 'Special Day'
WHERE time_id LIKE '____1107%';

3 rows updated.

SQL> ROLLBACK;

Rollback complete.

Source types assume the privileges of the source type definer or invoker (definer rights and invoker rights). Create a PROCEDURE as USER test_schema:

CREATE OR REPLACE PROCEDURE set_special_day AUTHID DEFINER
AS
BEGIN
UPDATE TEST_SCHEMA.test_table
SET time_holiday_description = 'Special Day'
WHERE time_id LIKE '____1107%';
COMMIT;
END;
/
GRANT EXECUTE ON set_special_day TO test_user;

Reconnect as USER test_user. When the PROCEDURE is invoked the rights and privileges of the owner are used, not the current user (invoker). A schema owner by default has privileges to all objects created in their schema.

SQL> conn test_user
Enter password: *******
Connected.

SQL> EXEC TEST_SCHEMA.set_special_day

PL/SQL procedure successfully completed.

SQL> SELECT DISTINCT time_holiday_description FROM TEST_SCHEMA.test_table;

TIME_HOLIDAY_DES
----------------
Veterans Day
New Years Day
Independance Day
Memorial Day
Presidents Day
Special Day
Labor Day
Thanksgiving

9 rows selected.

Replace the PROCEDURE as USER test_schema but with AUTHID CURRENT_USER

CREATE OR REPLACE PROCEDURE set_special_day AUTHID CURRENT_USER
AS
BEGIN
UPDATE TEST_SCHEMA.test_table
SET time_holiday_description = 'Special Day 2'
WHERE time_id LIKE '____1107%';
COMMIT;
END;
/

Reconnect as USER test_user.

SQL> CONN test_user
Enter password: *******
Connected.
SQL> EXEC TEST_SCHEMA.set_special_day
BEGIN TEST_SCHEMA.set_special_day; END;
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "TEST_SCHEMA.SET_SPECIAL_DAY", line 4
ORA-06512: at line 1

There are no privileges granted directly or through default roles to the current USER, test_user. Grant all roles as default to USER test_user using a super-user:

ALTER USER test_user DEFAULT ROLE ALL;

Reconnect as USER test_user.

SQL> conn test_user
Enter password: *******
Connected.

SQL> EXEC TEST_SCHEMA.set_special_day

PL/SQL procedure successfully completed.

SQL> SELECT DISTINCT time_holiday_description FROM TEST_SCHEMA.test_table;

TIME_HOLIDAY_DES
----------------
Veterans Day
New Years Day
Independance Day
Memorial Day
Presidents Day
Special Day 2
Labor Day
Thanksgiving

9 rows selected.

The confusion for me was with AUTHID DEFINER. What if USER test_user were granted temporary privileges to create objects in the test_schema schema?

GRANT DBA TO test_user;

Now create the PROCEDURE as USER test_user using the recently granted built-in DBA ROLE:

SET ROLE DBA;
CREATE OR REPLACE PROCEDURE TEST_SCHEMA.set_special_day AUTHID DEFINER
AS
BEGIN
UPDATE TEST_SCHEMA.test_table
SET time_holiday_description = 'Special Day 3'
WHERE time_id LIKE '____1107%';
COMMIT;
END;
/

Now have super-user REVOKE all the fun roles from USER test_user:

REVOKE dba, test_CRUD FROM test_user;

Reconnect as USER test_user and try to execute the PROCEDURE:

SQL> CONN test_user
Enter password: *******
Connected.
SQL> EXEC TEST_SCHEMA.set_special_day

PL/SQL procedure successfully completed.

SQL> SELECT DISTINCT TIME_HOLIDAY_DESCRIPTION FROM TEST_SCHEMA.test_table;

TIME_HOLIDAY_DES
----------------
Veterans Day
New Years Day
Independance Day
Memorial Day
Special Day 3
Presidents Day
Labor Day
Thanksgiving

9 rows selected.

Confused? I was until I looked at the owner of the procedure.

SQL> SELECT owner, authid FROM dba_procedures WHERE object_name = 'SET_SPECIAL_DAY';

OWNER AUTHID
------------------------------ ------------
TEST_SCHEMA DEFINER

When defining source types the DEFINER for AUTHID DEFINER is really the USER that is owner of the schema where the source type was created; not the USER doing the CREATE. When the test_schema USER created the PROCEDURE it by coincidence was also the owner.