473,320 Members | 2,162 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

A Query about GRANT ALL PRIVILEGES in ORACLE

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
Jul 19 '05 #1
4 98919
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@sans
Connected.
SQL> grant all privileges to zulia;

Grant succeeded.

SQL> -- connect as system and find out if it worked.
SQL> connect system/manager@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
--
Pete Finnigan
email:pe**@petefinnigan.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.
Jul 19 '05 #2
ad******@netscape.net (Amardeep Verma) wrote in message news:<45**************************@posting.google. 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_role

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 --
Jul 19 '05 #3
Thanks a lot Pete. Your Response was very informative

Enjoy your Day
Bye

Pete Finnigan <pl***@petefinnigan.com> wrote in message news:<BA**************@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@sans
Connected.
SQL> grant all privileges to zulia;

Grant succeeded.

SQL> -- connect as system and find out if it worked.
SQL> connect system/manager@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

Jul 19 '05 #4
Thanks Mark. Your points cleared up many items.

Have a nice day
Bye

Ma*********@eds.com (Mark D Powell) wrote in message news:<26**************************@posting.google. com>...
ad******@netscape.net (Amardeep Verma) wrote in message news:<45**************************@posting.google. 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_role

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 --

Jul 19 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

12
by: | last post by:
I issued the mysql command Grant all on *.* to myid@localhost identified as "mypw"; and recieved the error message ERROR 1045: Access denied for user" '@localhost' (Using password: NO) I...
3
by: Marc | last post by:
Hello, I have 3 users in my MySQL server. One of them has Grant access (actually: all privileges). I cannot login as this user using phpMyAdmin - my password is not accepted (though I' m sure it...
0
by: Charles Cantrell | last post by:
I have recently set up mySQL on a Mandrake release of Linux (Version 7 of Mandrake, I believe), using the binary 4.0.13 standard release. The set up and start up all were normal, as far as I...
0
by: Marc | last post by:
Hello, I have 3 users in my MySQL server. One of them has Grant access (actually: all privileges). I cannot login as this user using phpMyAdmin - my password is not accepted (though I' m sure it...
3
by: mel_palmeruk | last post by:
Hi I'm new to MySQL and am wanting to be connected to the MySQL server and query across diffrent db's. To my understanding a different db in MySQL is the same as a schema in Oracle. Am I correct...
6
by: john_woo | last post by:
Hi, db2 GRANT all on a_table_name to user me is for a table, I'm wondering possible to grant all on all_tables to user me, without listing all tables' names? -- Thanks John
2
by: jmarr02s | last post by:
I am creating a Pass Through Query. Here is my code: SELECT MDSDBA_CINTAKE.RECVDATE, MDSDBA_CINTAKE.CMPSRC, Count(MDSDBA_CINTAKE.CMPSRC) AS CountOfCMPSRC FROM MDSDBA_CINTAKE WHERE...
4
by: Amardeep Verma | last post by:
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
2
nomad
by: nomad | last post by:
Hello Everyone. I need some help in create Privileges for a new db. I'm using a host call addr.com and I hate them for there cpanel sucks. any I hav to write a MySQL to Grant All Privileges for a...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.