Re: App schema with SYS privileges?

sang wrote:
> Thanks for your answers! I'll follow it up.
>
> But one question, if I have the privilege to create user, would I be
> able to give that user full system privileges, making him a more
> powerful user than me? Or are privileges 'hereditary'?
>
> regards
> Sang

That depends entirely upon which privileges you can grant, and upon how you grant them. Simply because you have privileges does not mean you can grant them to others. Granting privileges to other users using 'WITH ADMIN OPTION' is the only way you can allow another user to propagate their privileges to others, and, obviously, this is not the most secure of methodologies. As an example:

SQL> create user granttest identified by ******** default tablespace tools temporary tablespace temp quota unlimited on tools;

User created.

SQL> grant create session to granttest;

Grant succeeded.

SQL> grant select any table to granttest with admin option;

Grant succeeded.

SQL> grant create any table to granttest;

Grant succeeded.

SQL> grant create user to granttest;

Grant succeeded.

SQL> connect granttest/********
Connected.
SQL> create user mytest identified by yark default tablespace tools temporary tablespace temp quota unlimited on tools;

User created.

SQL> grant create session to mytest;
grant create session to mytest
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> grant select any table to mytest;

Grant succeeded.

SQL> connect / as sysdba
Connected.
SQL> drop user mytest;

User dropped.

SQL> drop user granttest;

User dropped.

SQL> Note how only the SELECT ANY TABLE privilege can be passed to another user by GRANTTEST; no other privileges GRANTTEST has been given can be granted to any user GRANTTEST creates. Also, GRANTTEST can create users but cannot grant them CREATE SESSION, a necessary privilege, as that was granted to GRANTTEST without using the 'with admin option' clause.

So, some privileges CAN be passed on to others, provided you grant them to a user with that intent. By default this won't be possible. And I would think carefully about granting any privilege using the 'with admin option' clause.

David Fitzjarrell Received on Wed Sep 28 2005 - 21:14:01 CDT