Authorization in Oracle Part 1

Authorization in OraclePart 1 Ji-Won Mahesh

Sources • Starting source: • Oracle Database – Security Guide • 10g Release 1 (10.1) (Dec 2003) • Other sources: • Oracle SQL, PL/SQL Ref. • Hacking via SQL*Plus sessions • Random webpages… • Have not yet looked through ANSI SQL standard.

Approaches • Oracle Enterprise Security Manager • LDAP v3 compliant Oracle thing • Can store “ACLs” • SQL*Plus – grant/revoke • PL/SQL – Encapsulation of privileges • VPD

RBAC • Orthogonal/complementary feature – Roles • How roles can be defined (e.g., can adopt UNIX groups as DB roles) • “Secure application roles” – “Multi-tier authorization” • Restricted use of inherited privileges • E.g., need explicitly granted rights on base objects to include in view • Subtleties when it comes to stored procedures • named and anonymous PL/SQL blocks • E.g. cannot set roles within a definer’s rights procedure

Privileges • System Privilege • E.g., create view • Schema object privilege • E.g., select • Includes table, view, procedure, type • Two kinds of table privileges: • DML – select, insert, update, delete • DDL – alter, index, references

Overview of “demo” • Base table privileges: • Overlapping rights • Cascading and coarse-grained revoke • “grant any object privilege” • Views: • Minimum privileges • Operations on join views • Revocation – dichotomy

Overview (contd.) • Stored Procedures • Definer/invoker rights does not seem to apply to “execute” privilege

Demo << Onto the live demo >>

Authorization in Oracle Part 1