1

The users SYS and SYSTEM should never be used since there's a risk of damaging the SYS schema: "In general, unless the documentation tells you, you will NEVER LOG IN as sys or system, they are our internal data dictionary accounts and not for your use. You will be best served by forgetting they exist." (http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2659418700346202574)

What is the best and the most secure way for granting SYS privileges to a user? For example:

GRANT EXECUTE ON sys.dbms_pipe TO foobar;

Which user should I use instead of SYS or SYSTEM?

Toru
  • 905
  • 1
  • 9
  • 28
  • 2
    There is nothing wrong with granting privileges to execute sys procedures/packages to users, staying with principle of minimal privilege as much as possible. – Kombajn zbożowy Oct 29 '13 at 21:49

3 Answers3

2

This is one of the very rare cases where I disagree with Tom Kyte. In my opinion, it is perfectly sensible to grant privileges on SYS objects as SYS. The same applies to SYSTEM.

Frank Schmitt
  • 30,195
  • 12
  • 73
  • 107
1

You are granting an EXECUTE privilige on a package owned by SYS, you can't specify another schema name there. It has to be either SYS, or you can drop the schema name in this situation and just use GRANT EXECUTE ON dbms_pipe TO user_name;

Przemyslaw Kruglej
  • 8,003
  • 2
  • 26
  • 41
0

I would take all the objects that the privileges need to be granted to and create a special role that would have those privileges. This role would be way easier to manage than multiple grants to multiple users.

I'd also ask a question: why do you need this? Can this be done otherwise? Is it good design?

Alexander Nikolaev
  • 21
  • 4
  • 1
    One has to remember that privileges granted through roles are not recognized in PL/SQL. – Przemyslaw Kruglej Oct 29 '13 at 22:12
  • In my experience, grants on SYS/SYSTEM objects to roles are totally useless, because in most cases you'll want to use them in PL/SQL and therefore need direct grants anyway. But YMMV of course. – Frank Schmitt Oct 29 '13 at 22:39