SQL> select * from system_privilege_map where name like '%PROCEDURE%';
PRIVILEGE NAME PROPERTY
---------- ---------------------------------------- ----------
-140 CREATE PROCEDURE 0
-141 CREATE ANY PROCEDURE 0
-142 ALTER ANY PROCEDURE 0
-143 DROP ANY PROCEDURE 0
-144 EXECUTE ANY PROCEDURE 0
-241 DEBUG ANY PROCEDURE 0
6 rows selected.
SQL>
If we just show those that are not %ANY% then:
SQL> select * from system_privilege_map where name like '%PROCEDURE%' and name not like '%ANY%';
PRIVILEGE NAME PROPERTY
---------- ---------------------------------------- ----------
-140 CREATE PROCEDURE 0
SQL>
There is only one permission for PL/SQL and that is CREATE PROCEDURE. Why is there not ALTER PROCEDURE or DROP PROCEDURE or EXECUTE PROCEDURE or DEBUG PROCEDURE?
There are %ANY% versions of these so that a user granted the ANY can operate DDL on another users PL/SQL except SYS because of O7_dictionary_accessibility or the new 23c dictionary protection if its 23c.
So, if we create a user and grant CREATE PROCEDURE that user can of course create a procedure. So lets do that, note we must also grant CREATE SESSION:
C:\>sqlplus sys/oracle@//192.168.56.18:1521/freepdb1 as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Dec 28 09:13:35 2023
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
SQL> create user plsql identified by plsql;
User created.
SQL> grant create procedure to plsql;
Grant succeeded.
SQL> grant create session to plsql;
Grant succeeded.
SQL>
Now create that procedure:
SQL> connect plsql/plsql@//192.168.56.18:1521/freepdb1
Connected.
SQL> create procedure test is begin null; end;
2 /
Procedure created.
SQL>
OK, we have a PL/SQL procedure and only CREATE PROCEDURE but we can do other actions such as ALTER or DROP. Lets show ALTER PROCEDURE:
SQL> sho user
USER is "PLSQL"
SQL> alter procedure test compile;
Procedure altered.
SQL>
This is because of the OBJECT OWNER PRINCIPAL.
If an Oracle user has an object then it can do ACTIONS on that object without specific database permissions. This is the OBJECT OWNER PRINCIPAL.
We see something similar in database auditing. We we can for instance in standard auditing audit a permission such as ALTER USER but it will not catch a user change his password where we must audit the ACTION/STATEMENT USER instead and this catches a user changing their own password with ALTER USER but it doesn't audit ALTER USER when its changing someone else's password with ALTER USER. This is the difference between a privilege and a statement. i.e. we can issue CREATE OR REPLACE PROCEDURE... when we have CREATE ANY PROCEDURE (a privilege) and create a PL/SQL procedure in another schema then we use a privilege BUT if we have a PL/SQL procedure as the owner we can still do CREATE OF REPLACE PROCEDURE... and in this case its a statement.
Confusing!
One final example; if we revoke CREATE PROCEDURE from the user PLSQL can we still ALTER or DROP it?
SQL> connect sys/oracle@//192.168.56.18:1521/freepdb1 as sysdba
Connected.
SQL> revoke create procedure from plsql;
Revoke succeeded.
SQL>
SQL> connect plsql/plsql@//192.168.56.18:1521/freepdb1
Connected.
SQL> alter procedure test compile;
Procedure altered.
SQL> drop procedure test;
Procedure dropped.
SQL>
Interesting; We only need CREATE PROCEDURE to create a procedure and afterwards even if we no longer have CREATE PROCEDURE we can still issue, compile, alter, drop, debug against our own objects.
This is the OBJECT OWNER PRINCIPAL and also the difference between a privilege and a statement.
So after installation of an application we no longer need the CREATE privileges BUT we could not stop an attacker from dropping, altering,. re-creating, compiling and debugging the schema objects as the schema. We could use Database Vault or simple DDL triggers to prevent this
#oracleace #sym_42 #oracle #database #security #plsql #permissions #grants
