Solved

how to grant  user with the DBA role

Posted on 2009-04-13
3
5,857 Views
Last Modified: 2013-12-18
Hi,

How can I check all the existing privileges of a curresnt user/schema. How can I grant the user with DBA role. And also how can I revoke the DBA role to the user in future if I do not want it. Any sample code, links, resourcces highly appreciatged. Thanks in advance.
0
Comment
Question by:gudii9
3 Comments
 
LVL 22

Assisted Solution

by:Ivo Stoykov
Ivo Stoykov earned 150 total points
ID: 24135230
Hi
Oracle privileges could be System privileges and Object privileges. User following dics about privilege information in Oracle :

DBA_SYS_PRIVS : The System Previleges.

DBA_TAB_PRIVS : The Table Privileges.

DBA_ROLE_PRIVS : Roles consist of a set of privileges which can be assigned to users instead of granting privileges one by one.

DBA_COL_PRIVS : The Table Column Privileges.


HTH
Ivo Stoykov
0
 
LVL 28

Accepted Solution

by:
Naveen Kumar earned 350 total points
ID: 24135659
also if you want to grant some role/privilege to some other user, then you can use as shown below :

grant DBA to scott;   -- DBA is the role name here which we are granting to user scott,
                                 -- so you can substitute any role name which exists in the
                                 -- database in the place of DBA in this statement.

grant select any table to user1 ; -- 'select any table' is a privilege which we are granting to
                                                    -- user user1

if you want to revoke some privileges, then the syntax will be as shown below :

revoke DBA from scott;
revoke select any table from user1;

also if you want to enable the user scott to grant the DBA role which he was granted to other users then the statement should be in the below format :

grant DBA to scott with admin option; -- with admin option clause says that scott can grant
                                                            -- the DBA role to other users. This clause can be used along
                                                            -- when granting a system privilege or a role to a users.

if scott user runs the below command ( assuming he has an emp table ) in this schema :
grant delete on emp to user1 with grant option; -- with grant option enables user1 to
                                                                            -- grant delete object privilege on this emp table
                                                                            -- to some other users.

For more and complete information, browse thru :

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9013.htm#BABEFFEE
0
 
LVL 7

Author Closing Comment

by:gudii9
ID: 31569759
Thank you
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Oracle - SQL Query with Function 3 65
dbms_crypto.decrypt   errors out 6 40
having some issue on pl sql procedure 1 29
SQL2016 to ORACLE11G linked-server 6 48
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question