{"id":73357,"date":"2011-12-08T15:26:07","date_gmt":"2011-12-08T15:26:07","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/uncategorized\/invoker-rights-part-1\/"},"modified":"2021-07-14T13:08:06","modified_gmt":"2021-07-14T13:08:06","slug":"invoker-rights-part-1","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/oracle-databases\/invoker-rights-part-1\/","title":{"rendered":"Invoker Rights Part 1"},"content":{"rendered":"
\n

Truth suffers from too much analysis.
\nFrank Herbert<\/p>\n<\/blockquote>\n

In the Oracle database you have a choice of two models for executing the PL\/SQL code. The
\ndefault model (and the only one available until Oracle 8i (8.1.7)) is the Definer Rights model.
\nWhen using this model the program executes under the authority of the owner (or creator) of the
\nprogram. The other model is the Invoker Rights model. When using this model the program
\nexecutes under the authority of the invoker of the program. This means data and objects that
\nare available to the user are also available to the program. When running the code using the
\nDefiner Rights model, the data and objects available to the definer of the code are available to
\nthe code. This might mean a user may have access to data he is not supposed to have
\naccess to. There are some differences between both models and your programs may run using a
\ncombination of the two.<\/p>\n

Definer Rights<\/h2>\n

A PL\/SQL program must be compiled and stored in the database before it can be executed. Any
\nreference in the code is resolved at compile time. This means the program must have directly
\ngranted access to these objects. They are not resolved using roles granted to the definer or
\nowner of the program. Although the references are checked against the rights the owner has,
\nyou can still grant execute rights to different schemas or users. Keep in mind though that when
\nanother user executes the code, it runs under the authority of the definer of the code. This
\nmeans this user has access to all the objects needed by the code, while the user might not have
\naccess to these objects either through directly granted privileges or through a role. This may be
\na good thing when you want to make sure the user accesses tabledata only through a defined
\ninterface (Table API) but if you want the user to see certain parts of the data, when different
\nparts should be hidden from him\/her it means either building more code to hide parts of the data
\nor resort to Virtual Private Databases.<\/p>\n

Invoker Rights<\/h2>\n

The other model available to you is the Invoker Rights model. In this model the code is executed
\nunder the authority of the schema (or user) that started the code. This means that all objects
\nneeded by the code need to be available to the invoker of the code. References are checked to
\nthe references available to the rights the invoker has, instead of the rights the definer of the
\ncode has. This means that every user that executes the code can have different objects
\navailable than the ones available to the definer. If the code, for instance, references a table then
\nthis table can be a completely different object than the one used by the compiling schema. This
\nway you can make sure the user only has access to his own data. If multiple users use the
\nsame code to reference, for instance, a clients table, the different users will see different results.
\nThis way you can easily implement a model where users have access to only their own data,
\nwithout the need for Virtual Private Databases or more code. Be aware though that when you
\ncall a Definer Rights defined program from within an Invoker Rights program; from that time
\non you will be using the rights for that Definer. If you are to call another Invoker rights program it
\nwill be executed under the authority of that Definer, rather then the Invokers authority. If this
\nprogram calls an Invoker Rights program it will be executed under the authority of the Definer of
\nthe program that was just called rather than the authority of the original invoker.<\/p>\n

Ref:
\nOracle PL\/SQL Programming 5th Edition – Steven Feuerstein
\ntahiti.oracle.com<\/a> – all Oracle Documentation online<\/p>\n","protected":false},"excerpt":{"rendered":"

Truth suffers from too much analysis. Frank Herbert In the Oracle database you have a choice of two models for executing the PL\/SQL code. The default model (and the only one available until Oracle 8i (8.1.7)) is the Definer Rights model. When using this model the program executes under the authority of the owner (or creator) of the program. T…<\/p>\n","protected":false},"author":316181,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143533],"tags":[],"coauthors":[],"class_list":["post-73357","post","type-post","status-publish","format-standard","hentry","category-oracle-databases"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73357","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/316181"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=73357"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73357\/revisions"}],"predecessor-version":[{"id":91796,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73357\/revisions\/91796"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=73357"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=73357"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=73357"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=73357"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}