1 - (me) If these grants to SYS objects are needed to be done once at the start of the deployment of the application then i don't see an issue to simply login as SYSDBA and make the grants; its better than granting these grants to another user
2 - Bryns suggestion is to grant to another user WITH GRANT so it can make the grants to further users. My issue with this is that the grants are made to a user who doesn't need them simply so it can be used to grant to further users. OK, its better than logging in as SYSDBA if these grants need to be done regularly.
3 - (me) I also suggested making a quick API to be used by the DBA so that a non SYS user can make grants without having to have the grants made to it. This would use DBMS_SYS_SQL.PARSE_AS_USER() and run the code then as SYS. Bryn told me off for suggesting this as its unsupported to use this package in customer code - he is right we should not use un-supported methods and code. This package is in fact used in a lot of places such as APEX but Bryn is of course right that if the package were changed or removed because its unsupported then your code would break.
Anyway whilst I am not supposed to do this; sorry Bryn.:-). I wanted to make one point about this package use; so here is a quick demo. First connect as SYS and create a user called HACKER and grant him CREATE SESSION so he can log on and also grant EXECUTE on DBMS_SYS_SQL (This is a 12.2 database):
SQL> connect sys/oracle1@//192.168.56.95:1539/orcl.localdomain as sysdba
Connected.
SQL>
SQL> create user hacker identified by hacker;
User created.
SQL> grant create session to hacker;
Grant succeeded.
SQL> grant execute on dbms_sys_sql to hacker;
Grant succeeded.
SQL>
Now connect as the new hacker user and use the package to run code as SYS and grant DBA to ourselves:
SQL> connect hacker/hacker@//192.168.56.95:1539/orcl.localdomain
Connected.
SQL>
SQL> !vi s.sql
SQL> get s
1 declare
2 sqlt varchar2(32767):='grant dba to hacker identified by hacker';
3 i integer;
4 begin
5 i:=sys.dbms_sys_sql.open_cursor();
6 sys.dbms_sys_sql.parse_as_user(i,sqlt,dbms_sql.native,0);
7 sys.dbms_sys_sql.close_cursor(i);
8* end;
SQL> @s
PL/SQL procedure successfully completed.
SQL> sho user
USER is "HACKER"
SQL> select * from user_role_privs;
USERNAME
--------------------------------------------------------------------------------
GRANTED_ROLE
--------------------------------------------------------------------------------
ADM DEL DEF OS_ COM INH
--- --- --- --- --- ---
HACKER
DBA
NO NO YES NO NO NO
SQL> select * from session_roles;
no rows selected
SQL>
We got the DBA role it's in USER_ROLE_PRIVS but its not enabled. We can enable if we want. But now the point I want to make:
SQL> !vi s.sql
SQL> get s
1 declare
2 sqlt varchar2(32767):='grant sysdba to hacker';
3 i integer;
4 begin
5 i:=sys.dbms_sys_sql.open_cursor();
6 sys.dbms_sys_sql.parse_as_user(i,sqlt,dbms_sql.native,0);
7 sys.dbms_sys_sql.close_cursor(i);
8* end;
SQL> @s
declare
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SYS_SQL", line 1412
ORA-06512: at line 6
SQL>
We ran the same piece of code but this time try and grant SYSDBA not DBA and it does not work; this is because we are running as SYS and not SYSDBA; interesting; The dictionary accessibility is safe so we are protecting %ANY% rights and we cannot connect as SYS without SYSDBA but we cannot grant SYSDBA because we would need to be SYSDBA to do it. So whilst this package can run code as any user; we used an ID :=0 to mean run as SYS it is not SYSDBA.
