Results 1 to 5 of 5

Thread: Users,Roles and privileges in Oracle

  1. #1
    Join Date
    Jan 2010
    Posts
    53

    Users,Roles and privileges in Oracle

    Hello ,I have created a database and after that I have created two more users Named 'Olvin' and 'John' with some password. I granted the create tablespace and create session privileges with admin option to "Olvin" only.After one day,The "Olvin" has passed same privilege to "John" and I revoked the granted privilege from "Olvin" but "john" is still running with that privilege.Why ? please tell me the reason of it.
    Last edited by Garlands; 29-01-2010 at 01:55 PM.

  2. #2
    Join Date
    Jan 2008
    Posts
    1,521

    Users,Roles and privileges in Oracle

    Privileges

    The privileges are right to execute the SQL statement or to access the objects of other users in the database.A privilege can be assign to a role or a user of database.The privileges are already defined in oracle database,you can't create your own privilege.

    The Oracle follows two types of privileges:

    1. System privileges
    2. Object privileges

  3. #3
    Join Date
    Apr 2008
    Posts
    1,948

    Users,Roles and privileges in Oracle

    SYSTEM PRIVILEGE :

    The oracle database is rich set of privileges.The 9i and 10g has near about 157 and 173 privileges. But mostly only few of them is being used for simple operations which is described below :

    * Create session (a user can't login without this privilege)
    * Create table
    * Create view
    * Create procedure
    * Sysdba
    * Sysoper

    To know about the privileges that you have right now,query on this view All_tab_privs_made .
    Last edited by Praetor; 29-01-2010 at 02:45 PM.

  4. #4
    Join Date
    May 2008
    Posts
    2,012

    Users,Roles and privileges in Oracle

    OBJECT PRIVILEGES:

    This type of privileges is granted by the owner of the particular object to another user of the database.The privileges are listed here and it is granted according to the object which depends on owner of the object.

    The privileges are as follows:SELECT,INSERT,UPDATE,DELETE,ALTER,DEBUG,FLASHBACK,ON COMMIT REFRESH,QUERY REWRITE,REFERENCES,EXECUTE,ALL

    I have a table named "my_table" and I wanted to grant it to user "blend".He requires to perform select,insert and delete operation on "my_table" object.I will grant him like this :

    Code:
    SQL> GRANT SELECT,INSERT,DELETE ON MY_TABLE TO BLEND;
    Last edited by Katty; 29-01-2010 at 02:40 PM.

  5. #5
    Join Date
    Apr 2008
    Posts
    2,005

    Issues with revoking privileges

    You are using a database with two user and you have given the system privilege to one of the user named "Olvin" and he passed the same privilege to another user named "john".
    You revoked the system privilege from olvin but john is still using this privilege, because the system privileges doesn't follow the same rule as object privileges.

    The system privilege is granted using the "ADMIN OPTION".The only grantee can revoke that privileges .

    You need to assign the same privilege again to olvin and then olvin will revoke that privilege from john.

Similar Threads

  1. SBS 2008 Cannot see roles and features
    By Zylawys in forum Networking & Security
    Replies: 1
    Last Post: 11-05-2012, 11:21 AM
  2. Local privileges of domain users
    By Spencer in forum Active Directory
    Replies: 3
    Last Post: 29-04-2010, 12:04 AM
  3. Roles creation issue in oracle database
    By Ainsley in forum Software Development
    Replies: 4
    Last Post: 01-02-2010, 06:00 PM
  4. FSMO roles
    By bethamprashanth in forum Networking & Security
    Replies: 3
    Last Post: 26-05-2009, 12:03 PM
  5. How to Assign the Users and Roles in VB and VB.NET
    By Pratyush in forum Software Development
    Replies: 3
    Last Post: 30-03-2009, 11:59 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Page generated in 1,711,711,786.28154 seconds with 17 queries