Database Reference
In-Depth Information
The LOWPRIVS account has merely been granted the right to connect to the database. The API is owned by the
APPOWNER schema. The following procedure is part of the API and allows any application owner to enter data into
the system. Crucially the LOWPRIVS account does not have any grants to objects in the application owner's schema.
SQL> CREATE OR REPLACE PROCEDURE appowner.insert_critical_data(
2 pi_id appowner.critical_table.id%type,
3 pi_secret appowner.critical_table.secretData%type)
4 authid current_user
5 as
6 begin
7 insert into appowner.critical_table (
8 id, secretData)
9 values (
10 pi_id,
11 pi_secret);
12* end;
SQL>
Notice that the procedure is an invoker rights procedure. The next step is to create a role that allows the execution
of this stored procedure. In order to insert into a table the select and insert privileges are needed.
SQL> create role critical_table_role;
Role created.
SQL> grant insert on appowner.critical_table to critical_table_role;
Grant succeeded.
SQL> grant select on appowner.critical_table to critical_table_role;
Grant succeeded.
With the grants sorted the role can be granted to the procedure as shown:
SQL> grant critical_table_role to procedure insert_critical_data;
Grant succeeded.
With the execute privilege on the stored procedure granted to the LOWPRIVS user either directly or better via
another role, the low privileged user can make use of the API:
SQL> exec appowner.insert_critical_data(1, 'test')
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
Search WWH ::




Custom Search