A Query about GRANT ALL PRIVILEGES in ORACLE

**Pete Finnigan** · Jun 27 '08, 05:55 PM

Re: A Query about GRANT ALL PRIVILEGES in ORACLE

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>
SQLsho user
USER is "SYSTEM"
SQLselect * from system_privileg e_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.
SQLcreate user emil identified by emil;

User created.

SQLgrant 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
SQLcreate user zulia identified by zulia;

User created.

SQL-- connect as emil and grant all privileges to Zulia
SQLconnect emil/emil@sans
Connected.
SQLgrant all privileges to zulia;

Grant succeeded.

SQL-- connect as system and find out if it worked.
SQLconnect system/manager@sans
Connected.

SQLselect count(*),grante e
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
--
Pete Finnigan
email:pete@pete finnigan.com
Web site: http://www.petefinnigan.com - Oracle security audit specialists
Book:Oracle security step-by-step Guide - see http://store.sans.org for details.

**Mark D Powell** · Jun 27 '08, 05:55 PM

Re: A Query about GRANT ALL PRIVILEGES in ORACLE

addverma@netsca pe.net (Amardeep Verma) wrote in message news:<45d3f402. 0402040351.7a9b fb48@posting.go ogle.com>...

Hi,
I have a quick question. Which role/privileges are required before
a user can give the statement "GRANT ALL PRIVILEGES"?
>
Thanking you in Advance
>
Have a nice day

From the 9.2 SQL manual: >>
ALL [PRIVILEGES]
Specify ALL to grant all the privileges for the object that you have
been granted with the GRANT OPTION. The user who owns the schema
containing an object automatically has all privileges on the object
with the GRANT OPTION. (The keyword PRIVILEGES is provided for
semantic clarity and is optional.)
<<

So any object owner can grant all on object to someuser_or_rol e

And it would appear that a DBA can grant all privileges to a user or
role:

I created a user named bob then I issued, "grant all privileges to
bob"
Next I connected as Bob and queries user_sys_privs.
I got 140 rows returned.

When I reconneted to my DBA id I queried dba_sys_privs for grantee =
'DBA' and got 138.

HTH -- Mark D Powell --

**Amardeep Verma** · Jun 27 '08, 05:55 PM

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@petefinn igan.comwrote in message news:<BAswIjAJo OIARxyT@peterfi nnigan.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>
SQLsho user
USER is "SYSTEM"
SQLselect * from system_privileg e_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.
SQLcreate user emil identified by emil;
>
User created.
>
SQLgrant 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
SQLcreate user zulia identified by zulia;
>
User created.
>
SQL-- connect as emil and grant all privileges to Zulia
SQLconnect emil/emil@sans
Connected.
SQLgrant all privileges to zulia;
>
Grant succeeded.
>
SQL-- connect as system and find out if it worked.
SQLconnect system/manager@sans
Connected.
>
SQLselect count(*),grante e
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

**Amardeep Verma** · Jun 27 '08, 05:55 PM

Re: A Query about GRANT ALL PRIVILEGES in ORACLE

Thanks Mark. Your points cleared up many items.

Have a nice day
Bye

Mark.Powell@eds .com (Mark D Powell) wrote in message news:<2687bb95. 0402040631.27e9 09b2@posting.go ogle.com>...

addverma@netsca pe.net (Amardeep Verma) wrote in message news:<45d3f402. 0402040351.7a9b fb48@posting.go ogle.com>...

Hi,
I have a quick question. Which role/privileges are required before
a user can give the statement "GRANT ALL PRIVILEGES"?

Thanking you in Advance

Have a nice day

>
From the 9.2 SQL manual: >>
ALL [PRIVILEGES]
Specify ALL to grant all the privileges for the object that you have
been granted with the GRANT OPTION. The user who owns the schema
containing an object automatically has all privileges on the object
with the GRANT OPTION. (The keyword PRIVILEGES is provided for
semantic clarity and is optional.)

<<
>
So any object owner can grant all on object to someuser_or_rol e
>
And it would appear that a DBA can grant all privileges to a user or
role:
>
I created a user named bob then I issued, "grant all privileges to
bob"
Next I connected as Bob and queries user_sys_privs.
I got 140 rows returned.
>
When I reconneted to my DBA id I queried dba_sys_privs for grantee =
'DBA' and got 138.
>
HTH -- Mark D Powell --

A Query about GRANT ALL PRIVILEGES in ORACLE

A Query about GRANT ALL PRIVILEGES in ORACLE

Comment

Comment

Comment

Comment