Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2013
    Posts
    3

    Unanswered: DB2 create User + DB and drop User + DB :S

    Hi guys,

    i'm from germany, sorry for my bad language

    at First my system infos..

    --> db2level
    DB21085I Diese Instanz oder Installation (Instanzname, sofern zutr. "DB2") verwendet "64" Bit und DB2-Codefreigabe "SQL10050" mit Aktua"0601010E".
    Informationstokens: "DB2 v10.5.0.420", "s130528", "NTX64105" und Fi
    Produkt ist in "C:\PROGRA~1\IBM\SQLLIB" mit DB2-Kopienamen "DB2COPY installiert.

    --> db2licm -l
    Produktname: "DB2 Express-C"
    Lizenztyp: "Ohne Gewährlei
    Ablaufdatum: "Permanent"
    Produkt-ID: "db2expc"
    Versionsnummer: "10.5"
    Maximale Anzahl CPUs: "2"
    Maximale Speicherkapazität (GB): "16"
    Durchsetzungsrichtlinie: "Normaler Stopp"

    and no my Problem.
    --> I need the right sql commands for create user + user_db and drop user + user_db !

    Yes, I know that you can not create User in DB2, because DB2 use the user from the OS.

    So i have search for the right commands for my problem, and here my results:
    my Example use the user ABC and the Schema ABC

    -- CREATE: --
    -- 1. grant connect
    GRANT CONNECT ON DATABASE TO USER ABC;
    -- 2. create schema
    CREATE SCHEMA ABC AUTHORIZATION ABC;
    -- 3. grant (all rights) on the schema to the user (so the user is like a dbowner)
    GRANT ALTERIN, CREATEIN, DROPIN ON SCHEMA ABC TO ABC;
    ---------------------------------------------------------------------

    -- DELETE: --
    -- 1.revoke the right
    Revoke ALTERIN, CREATEIN, DROPIN ON SCHEMA ABC FROM ABC;
    -- 2.revoke connect
    Revoke connect ON database FROM USER ABC;
    -- 3.find the tables that the user has created
    select 'drop table '||rtrim(tabschema)||'.'||rtrim(tabname) from syscat.tables where tabschema = 'ABC';
    -- 3.1 run the results form 3.
    drop table ABC.TEST;
    -- 4.drop schema
    DROP SCHEMA ABC RESTRICT;

    Can you please tell me if my procedure is correct?
    or is there another possibility to create user their own databases or similar ?

    regards from Germany

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by zar_sas View Post
    i'm from germany, sorry for my bad language
    You don't have to use bad language even if you are from Germany.

    Quote Originally Posted by zar_sas View Post

    Can you please tell me if my procedure is correct?
    or is there another possibility to create user their own databases or similar ?
    If you tried the procedure and it did what you expected, then it's correct. If it didn't do what you expected, tell us what you expected and what actually happened.

    For one, revoking the CONNECT privilege from a user will not necessarily prevent that user from connecting to the database: each user is a member of PUBLIC, which by default has the CONNECT privilege and some others as well.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jul 2013
    Posts
    3
    Ok, my problem is by step 3.1 -> the delete Statements (1.post)

    I want to drop the table TEST from the schema test123
    --> drop table test123.TEST

    and then i get a failure Message:

    "TEST123.TEST" ist ein nicht definierter Name.. SQLCODE=-204, QLSTATE=42704, DRIVER=3.63.108

    Why i cant't drop tables from another schema/user?

  4. #4
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    I am pretty sure that SQLSTATE 42704 means that the table does not exist.
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  5. #5
    Join Date
    Jul 2013
    Posts
    3
    But the table exsit in schema test123 -.-

    The error was the second step by the --create-- statements
    CREATE SCHEMA ABC AUTHORIZATION ABC;

    only the user ABC can drop the table TEST, but i want that the user XXX can drop the table TEST from the schema ABC

    so i must create the schema ABC AUTHORIZATION XXX;

    and now i can drop tables as USER XXX from the schma ABC

  6. #6
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

Tags for this Thread

Posting Permissions

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