amis definer invoker rights
DESCRIPTION
Deze presentatie is gegeven tijdens de KScope conferentie 2012Spreker: Patrick BarelTitel: Should Invoker Rights Be Used?Onderwerp: Developers Toolbox - Coding Deze presentatie gaat over de vraag of het Invoker Rights model van de Oracle Database, voor verschillende gebruikers binnen dezelfde database, kan helpen bij het scheiden van de zichtbaarheid van de data. Door gebruik te maken van de techniek deze techniek kun je op een relatief eenvoudige wijze ervoor zorgen dat gebruikers alleen werken op hun eigen data en niet op die van anderen. Als het bijvoorbeeld gaat om een hosted applicatie, dan hoeft er nog maar één codebase te zijn, waardoor alle gebruikers direct profiteren van verbeteringen die aangebracht worden. Daarnaast leer je in deze sessie hoe je één set code kunt onderhouden voor verschillende gebruikers van de applicatie en hoe je je ‘gedeeltelijk’ kunt beschermen tegen SQL Injection.TRANSCRIPT
Patrick Barel , AMIS, The Netherlands
Monday, June 25, 2012
ODTUG KScope 12
San Antonio, Texas, USA
Developers Toolbox – Coding Should invoker rights be used?
Definer Rights vs Invoker Rights
Prior to Oracle8i, whenever you executed a stored
program, it ran under the privileges of the account in
which the program was defined.
This is called the …
With Oracle8i, you can now decide at compilation time
whether your program or package will execute in the
definer's schema (the default) or the schema of the invoker
of the code.
This is called the …
Definer Rights Model
Invoker Rights Model
Patrick Mitchell
Definer Rights
Code Invoke
Ref
Relations Relations
Patrick Mitchell
Invoker Rights
Code Invoke
Relations Relations
Invoker Rights
Allows you to centralize access to and control of underlying data structures.
Uses roles and doesn’t rely on directly-granted privileges.
But it can be a source of confusion and architectural problems.
Note: Oracle built-in packages have
long had the capability of running under the invoker's authority.
Deployment & maintenance
Must install module in all schemas where needed
In some databases, each user has own copy of
table(s), requiring copy of stored module
Security
No declarative way to restrict privileges on certain
modules in a package -- it's all or nothing, unless
you write code in the package to essentially
recreate roles programmatically.
Difficult to audit privileges
Sure would be nice to have a choice...and now you do!
What’s wrong with Definer Rights
Invoker Rights
For top level modules:
For modules with separate spec and body,
AUTHID goes only in spec, and must be at the
package level.
Holds true for packages and object types.
CREATE [ OR REPLACE ] <module type>
[ AUTHID { DEFINER | CURRENT_USER } ]
AS ...
Overview of Definer Rights
Emp
begin
x.foo;
end; package x
authid
definer
Emp Emp
package y
authid
definer
package z
authid
definer
Overview of Invoker Rights
Emp
begin
x.foo;
end; package x
authid
current_user
Emp Emp
package y
authid
definer
package z
authid
current_user
Overview of Invoker Rights
Emp
begin
x.foo;
end;
package x
authid
current_user
Emp Emp
package y
authid
definer
package z
authid
current_user
Emp
begin
x.foo;
end;
Mock objects
To compile code you still need the structure of the
objects.
Mock objects
begin
x.foo;
end;
begin
x.foo;
end; package x
authid
current_user
Col1` Col2 Col3 Col4
Col1 Col2 Col3 Col4
A.val1 A.val2 A.val3 A.val4
A.val5 A.val6 A.val7 A.val8
A.val9 A.val10 A.val11 A.val12
A.val13 A.val14 A.val15 A.val16
Col1 Col2 Col3 Col4
B.val1 B.val2 B.val3 B.val4
B.val5 B.val6 B.val7 B.val8
B.val9 B.val10 B.val11 B.val12
B.val13 B.val14 B.val15 B.val16
Execute
Execute
Definer Rights
Use a single codebase for multiple users
(a bit of) Protection from SQL Injection
Single codebase
User1 User2
App
Mock objects
Single codebase
User1 User2
App
Code
Single codebase
User1 User2
App
Single codebase
User1 User2
App
Application code in a central schema (with mock objects)
Single codebase
User1 User2
App
Each user has it’s own set of tables, views and sequences
Single codebase
User1 User2
App
Columns can be different in each schema
Advantages
One time development
Specific code in user schema
(partial) Protection from
SQL Injection
Drawbacks
Debugging can be hard
Support can be hard
SQL Injection
Dynamic SQL
Modification (drop) of objects
You cannot drop what is not there
Modification of records
Will only affect current users data
You should always use binding
instead of concatenating in
Dynamic SQL Statements
Rules and Restrictions
AUTHID DEFINER
Uses directly granted
privileges
Default, so no need to change current code
AUTHID CURRENT_USER
Uses ROLEs
On entire objects
Need for ‘mock’ objects (at compile time it’s Definer Rights)
Definer Rights Model
Invoker Rights Model