Re: A Query about GRANT ALL PRIVILEGES in ORACLE

Thanks a lot Pete. Your Response was very informative

Enjoy your Day
Bye

Pete Finnigan <plsql_at_petefinnigan.com> wrote in message news:<BAswIjAJoOIARxyT_at_peterfinnigan.demon.co.uk>...
> Hi,
>
> Try this;
>
> Connected to:
> Personal Oracle9i Release 9.2.0.1.0 - Production
> With the Partitioning, OLAP and Oracle Data Mining options
> JServer Release 9.2.0.1.0 - Production
>
> SQL>
> SQL> sho user
> USER is "SYSTEM"
> SQL> select * from system_privilege_map
> 2 where name like '%PRIV%';
>
> PRIVILEGE NAME PROPERTY
> ---------- ---------------------------------------- ----------
> -167 GRANT ANY PRIVILEGE 0
> -244 GRANT ANY OBJECT PRIVILEGE 0
>
> SQL>
> SQL> -- Create a new user with just create session (to log on) and grant
> SQL> -- any privilege to, well grant all privileges.
> SQL> create user emil identified by emil;
>
> User created.
>
> SQL> grant create session, grant any privilege to emil;
>
> Grant succeeded.
>
> SQL> -- because we want to test this privilege create a second user to
> SQL> -- test it with
> SQL> create user zulia identified by zulia;
>
> User created.
>
> SQL> -- connect as emil and grant all privileges to Zulia
> SQL> connect emil/emil_at_sans
> Connected.
> SQL> grant all privileges to zulia;
>
> Grant succeeded.
>
> SQL> -- connect as system and find out if it worked.
> SQL> connect system/manager_at_sans
> Connected.
>
> SQL> select count(*),grantee
> 2 from dba_sys_privs
> 3 where grantee in ('MDSYS','EMIL','ZULIA')
> 4* group by grantee
> SQL> /
>
> COUNT(*) GRANTEE
> ---------- ------------------------------
> 2 EMIL
> 139 MDSYS
> 139 ZULIA
>
> SQL>
>
> We used MDSYS as a checkpoint as MDSYS has all privileges granted to it
> by default in a default installation of Oracle. The privilege you need
> therefore is GRANT ANY PRIVILEGE.
>
> I should ask WHY?, it is not a good idea to grant all privileges to any
> user in the database. Just grant the privileges that are needed by your
> user. Use the least privilege principle.
>
> hth
>
> kind regards
>
> Pete
Received on Thu Feb 05 2004 - 04:48:08 CET