I wanted to know what privileges can be granted to PL/SQL so first in 18c XE I can make a simple query and see what's already granted to PL/SQL in a clean XE database:
SQL> select distinct privilege
2 from dba_tab_privs
3 where type in ('PROCEDURE','FUNCTION','TYPE','PACKAGE','PACKAGE BODY','TYPE BODY');
PRIVILEGE
----------------------------------------
EXECUTE
SQL>
Now connect to my sample schema CRM and grant all privileges to the user TEST:
SQL> connect crm/crm@//192.168.56.78:1523/xepdb1
Connected.
SQL> grant all on crm_proc to test;
Grant succeeded.
SQL>
Now connect as SYSTEM and have a look at the privileges actually granted:
SQL> connect system/oracle1@//192.168.56.78:1523/xepdb1
Connected.
SQL> set serveroutput on
SQL> @print 'select * from dba_tab_privs where table_name=''''CRM_PROC'''''
old 33: lv_str:=translate('&&1','''','''''');
new 33: lv_str:=translate('select * from dba_tab_privs where table_name=''CRM_PROC''','''','''''');
Executing Query [select * from dba_tab_privs where table_name='CRM_PROC']
GRANTEE : TEST
OWNER : CRM
TABLE_NAME : CRM_PROC
GRANTOR : CRM
PRIVILEGE : EXECUTE
GRANTABLE : NO
HIERARCHY : NO
COMMON : NO
TYPE : PROCEDURE
INHERITED : NO
-------------------------------------------
GRANTEE : TEST
OWNER : CRM
TABLE_NAME : CRM_PROC
GRANTOR : CRM
PRIVILEGE : DEBUG
GRANTABLE : NO
HIERARCHY : NO
COMMON : NO
TYPE : PROCEDURE
INHERITED : NO
-------------------------------------------
PL/SQL procedure successfully completed.
SQL>
Just DEBUG and EXECUTE are granted; not much then!. Lets see the system privileges that affect PL/SQL:
SQL> select name from system_privilege_map where name like '%PROCED%';
NAME
----------------------------------------
DEBUG ANY PROCEDURE
EXECUTE ANY PROCEDURE
DROP ANY PROCEDURE
ALTER ANY PROCEDURE
CREATE ANY PROCEDURE
CREATE PROCEDURE
6 rows selected.
SQL>
As you can see from the SYSTEM_PRIVILEGE_MAP there are 6 system privileges that relate to PROCEDURE (PL/SQL) but only one that is a direct grant to a single user that affects his own rights in relation to PL/SQL; thats CREATE PROCEDURE; obviously this only affects new PL/SQL - i.e. you can create new ones or do CREATE OR REPLACE and overwrite a new one.
Of course there are multiple %ANY% privileges that do not exist at the single use level; There are DROP, ALTER, CREATE, EXECUTE and DEBUG ANY rights. This means that the grantee of these rights can affect other users code (except SYS as its protected by O7_DICTIONARY_ACCESSIBILITY - deprecated in 18c and desupported in 19c).
so we have ALTER ANY PROCEDURE not no ALTER PROCEDURE privilege. So we cannot grant ALTER PROCEDURE to TEST to use on CRMs procedures.
SQL> connect crm/crm@//192.168.56.78:1523/xepdb1
Connected.
SQL> grant alter on crm_proc to test
2 /
grant alter on crm_proc to test
*
ERROR at line 1:
ORA-02225: only EXECUTE and DEBUG privileges are valid for procedures
SQL>
This confirms that there are only two possible rights for a piece of PL/SQL, DEBUG and EXECUTE but CRM can ALTER his own PL/SQL:
SQL> sho user
USER is "CRM"
SQL> alter procedure crm_proc compile;
Procedure altered.
SQL>
This is the so called "Object owner principle". An objects owner can issue all sorts of commands on his own objects where those commands are valid for the object type. So for a piece of PL/SQL the owner can issue ALTER PROCEDURE.... or DROP PROCEDURE.... or AUDIT PROCEDURE... so an example could be:
SQL> audit execute on crm_proc;
Audit succeeded.
SQL>
Thee abilities do not show up in a list of grants to CRM_PROC and others cannot do this to CRMs objects unless they have an %ANY% right. So this is a statement not a privilege - In standard Oracle audit we have STATEMENT privileges and SYSTEM PRIVILEGES, seen in the views DBA_STMT_AUDIT_OPTS and DBA_PRIV_AUDIT_OPTS.
The object privileges in the database can be seen here:
SQL> select * from table_privilege_map;
PRIVILEGE NAME
---------- ----------------------------------------
0 ALTER
1 AUDIT
2 COMMENT
3 DELETE
4 GRANT
5 INDEX
6 INSERT
7 LOCK
8 RENAME
9 SELECT
10 UPDATE
PRIVILEGE NAME
---------- ----------------------------------------
11 REFERENCES
12 EXECUTE
16 CREATE
17 READ
18 WRITE
20 ENQUEUE
21 DEQUEUE
22 UNDER
23 ON COMMIT REFRESH
24 QUERY REWRITE
26 DEBUG
PRIVILEGE NAME
---------- ----------------------------------------
27 FLASHBACK
28 MERGE VIEW
29 USE
30 FLASHBACK ARCHIVE
26 rows selected.
SQL>
Lots of possible object privileges but only 2 that relate to PL/SQL as privileges that can be granted; DEBUG and EXECUTE. The one system privilege that relates to a single schema and PL/SQL is the CREATE PROCEDURE and to prove that its needed not just to CREATE a procedure but also for CREATE OR REPLACE - i.e. reinstall a procedure we will revoke CREATE PROCEDURE from the CRM user and try and recreate his CRM_PROC PL/SQL:
SQL> connect crm/crm@//192.168.56.78:1523/xepdb1
Connected.
SQL> select * from session_roles;
ROLE
--------------------------------------------------------------------------------
CONNECT
RESOURCE
SODA_APP
SQL> select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
ALTER SESSION
CREATE TABLE
CREATE CLUSTER
CREATE VIEW
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE
PRIVILEGE
----------------------------------------
SET CONTAINER
12 rows selected.
SQL> connect system/oracle1@//192.168.56.78:1523/xepdb1
Connected.
SQL> revoke resource from crm;
Revoke succeeded.
SQL> connect crm/crm@//192.168.56.78:1523/xepdb1
Connected.
SQL> select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
ALTER SESSION
CREATE VIEW
SET CONTAINER
SQL> select * from session_roles;
ROLE
--------------------------------------------------------------------------------
CONNECT
SQL>
we cannot do a replace:
SQL> create or replace procedure crm_proc(lv_text in varchar2) is
2 begin
3 dbms_output.put_line(lv_text);
4 end;
5 /
create or replace procedure crm_proc(lv_text in varchar2) is
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL>
So even though a user (CRM here) has an object CRM_PROC he cannot CREATE OR REPLACE it again as he no longer has CREATE PROCEDURE. So only two actual privileges that can be granted by the owner of a piece of PL/SQL code to another user but 5 SYSTEM %ANY% privileges that would allow others to CREATE, ALTER, DROP, DEBUG and EXECUTE. The owner can of course do all of these except CREATE and also other less obvious grants are things like AUDIT:
SQL> select name from system_privilege_map where name like '%AUDIT%';
NAME
----------------------------------------
AUDIT ANY
AUDIT SYSTEM
SQL>
Audit SYSTEM allows someone to issue audit commands on privileges. AUDIT ANY allows the grantee to issue audit commands on others objects. From an attacker perspective then he could also issue NOAUDIT and turn off audit settings on objects that are already set.
More soon on grants to PL/SQL.
