Results 1 to 10 of 10

Thread: Drop User

  1. #1
    Join Date
    Jul 2005
    Posts
    36

    Unanswered: Drop User

    Hello!
    I have a Database in wich I create users for it and then these users creates tables in this Database. If I drop a table I want that automatically the user who created it desapear too. I have read something about it and I Know I have to do it with TRIGGERS but I don´t Know how! Can You help me?

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    What if the user created TWO tables and only drops ONE?
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Jul 2005
    Posts
    36
    It doesn´t matter I have to drop the user anyway. Please help me

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by carcaharas
    It doesn´t matter I have to drop the user anyway. Please help me
    Surely you don't mean that? If the user has two tables, and drops one (i.e. still has one left) you wouldn't want to drop the user?

    There may be a way to do this using system event triggers, but I don't know for sure. It is a very odd thing to want to do!

  5. #5
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    I can be done with a Database (DDL) trigger.
    This requirement is too odd to be true, but maybe this post is just a HOMEWORK assignment exercise? .
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  6. #6
    Join Date
    Jul 2005
    Posts
    36

    Red face

    Ok, I'm going to try to explain the problem better. First of all, I'm Spanish and I have problems with my english so it is very difficult for me to make you understand what I exactly want, i try anyway:

    I am making an application whose name is (more or less) "AUTOCORRECTION SERVER OF SQL EXERCISES" In my application there are two types of users:
    first the ALUMN who only makes exercises, in the other hand the ADMIN who add new exercises and new databases to my application. Ok,the aplication have a principal database(obviously). When the ADMIN want to add a new database(for the alumns to make exercises with it) what he really makes is add the tables to de principal database using a new user, I mean the process is:
    THE ADMIN WANT TO ADD A NEW DATABASE
    1. Create a new user.
    2. Connect to the database with this new user
    3. Add all the tables to conform the new database

    THE ALUMN WANT TO WORK WITH THE NEW DATABASE
    1.Connect to the principal database with the user that is related to it(this way the alumn only have acces to the tables that correspond with the database)


    Ok, the admin can add databases but also can delete them, and here is my problem I of course know how to drop the tables, but I want that when the tables disapear the user too!

    DO YOU UNDERSTAND ME NOW? I DO MY BEST TRYING TO EXPLAIN ME BUT IT'S REALLY DIFFICULT.
    I HOPE YOU CAN HELP ME NOW IT'S IMPORTANT.
    THANKS.I LOVE THIS FORO

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    DROP USER {username} CASCADE;
    will drop the user and all objects owned by that user
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Jul 2005
    Posts
    36
    ok!thanks! so if I drop first the user the tables creates for this user will disapear too?
    thanks!!!!!!!!!!!!!!!!!!!!!!!

  9. #9
    Join Date
    Jan 2003
    Location
    China
    Posts
    38
    Can a user drop itself? If the user wanna drop its own tables? Does that work?
    Fan Yi

  10. #10
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    An "ordinary" user (the one without system privileges) is unable to drop any user as it will run into "ORA-01031: insufficient privileges" error. If you grant him a "drop user" privilege, this user is still unable to drop itself as it will run into "ORA-01940: cannot drop a user that is currently connected". Therefore, suicide is impossible in Oracle world.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •