Follow us: Connect on YouTube Connect on YouTube Connect on YouTube

Pages

Tuesday, 11 April 2017

SQL Grant and Revoke



SQL Grant

This is used to grant the privileges to other users
Syntax:
     Grant <privileges> on <object_name> to <user_name> [with grant option];
Ex:
     SQL> grant select on student to sudha;         -- you can give individual privilege

      SQL> grant select, insert on student to sudha;       -- you can give set of privileges

     SQL> grant all on student to sudha;                -- you can give all privileges
   
     The sudha user has to use dot method to access the object.

     SQL> select * from saketh.student;

     The sudha user can not grant permission on student table to other users. To get this type of option use the following.

     SQL> grant all on student to sudha with grant option;  
          
     Now sudha user also grant permissions on student table.


SQL Revoke

This is used to revoke the privileges from the users to which you granted the privileges.
Syntax:
     Revoke <privileges> on <object_name> from <user_name>;
Ex:
     SQL> revoke select on student form sudha; -- you can revoke individual privilege

     SQL> revoke select, insert on student from sudha;            -- you can revoke set of privileges

      SQL> revoke all on student from sudha;       -- you can revoke all privileges
Share this article :

4 comments:

  1. hi Dayakar,

    Amaze! I have been looking bing for hours because of this and i also in the end think it is in this article! Maybe I recommend you something helps me all the time?

    i am trying to create a database to calculate employees’ net montly payments for lunch. But i have a problem and cannot find a way to solve it. I can describe my problem shortly as follows:
    I have three table. In the first one, i keep the employees’ personal details like id, name, dob etc. In the second one i keep the net workday number for each month along 10 years. In the third one, i want to keep all employees’ ids, net workday numbers at the second table as a default value and the payments he/she have to do.
    At the first sight, i think that i can do it like the second table for ten years. But, due to the fact that there can be a rotation among the employees, this table will not work as i hope.
    I think that it can be a solution saving the third table at the beginning of the next month, but how can i do? Or, what will be your solution? (Please share your sample codes)


    Thank you very much and will look for more postings from you.


    Obrigado,
    Irene Hynes

    ReplyDelete
  2. Ohayo,


    Best thing I have read in a while on this SQL Grant and Revoke . There should be a standing ovation button. This is a great piece.

    1) I am new to Oracle, Im not a DBA and I ve tring to figure out this error in a log:

    ERROR in QUETOL.tol_lc_movimientos_pg.movimientos SQLCODE[=ORA-20109:
    ORA-20109: ORA-01422: exact fetch returns more than reques]


    The problem arises I dont even know exactly this error which object references to: ie package, procedure,table,etc

    QUETOL.tol_lc_movimientos_pg.movimientos

    I tried to find this object as a Package but i could not find it?

    SQL> SELECT object_name,OBJECT_TYPE,CREATED,STATUS,GENERATED FROM dba_objects
    2 WHERE object_name = 'QUETOL.tol_lc_movimientos_pg.movimientos'
    3 AND object_type = 'PACKAGE';

    no rows selected

    I tried to find as any other object but i didn’t get anything.
    SQL> SELECT object_name,OBJECT_TYPE,CREATED,STATUS,GENERATED FROM dba_objects
    2 WHERE object_name LIKE '%tol_lc_movimientos_pg.movimi%'
    3 ;

    no rows selected

    I thought it was a Package so I could see its DML code.

    I appreciate your help in advanced to identify which Object this log error point to point.


    Anyways great write up, your efforts are much appreciated.


    Obrigado,

    ReplyDelete
  3. Hi Dayakar,

    Fully agree on SQL Grant and Revoke . We’re seeing a lot of projects tackle big complex problems but few seem to have taken into consideration and in particular reasons to adopt.

    My table
    column type is clob. Field value contain double quotes

    id,text_clob,date

    1,"\"data1\",0,1,1,1,\"data2\"","2018-03-03 09:08:45"

    can you kindly help loader query. i cant use replace in clob field.






    It was cool to see your article pop up in my google search for the process yesterday. Great Guide.
    Keep up the good work!



    Thank you,
    Ajeeth

    ReplyDelete
  4. Hey,


    I learnt so much in such little time about #topic. Even a toddler could become smart reading of your amazing articles.
    I have requirement like

    select COL1,Col2 from A
    minus
    Select COL1,COL2 from B;

    both tables have 50+ millions record so I want to automate a job which will execute this query for 1millions records for one time and then again 1 millions for second time...like loop and store output in record type.



    But nice Article Mate! Great Information! Keep up the good work!


    Kind Regards

    ReplyDelete