Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2005
    Posts
    85

    Unanswered: create table permission

    Hi,

    Is there any query available to check the existence of 'CREATE TABLE' permission in a schema
    ?

    Please advice,

    Thanks,
    MiraJ

  2. #2
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    there's USER_TAB_PRIVS maybe you'll find what you need in it

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    CREATE TABLE privilege is contained in DBA role named CONNECT. Therefore, if you have system privileges on a database you use, this query will give you all users that are allowed to create tables:
    Code:
    SELECT grantee
      FROM dba_role_privs
     WHERE granted_role IN (SELECT ROLE
                              FROM role_sys_privs
                             WHERE PRIVILEGE = 'CREATE TABLE')

  4. #4
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    Littlefoot,
    it doesn't work if user is not associated with role. I did run it against my DB, but result was only 4-5 system users, however there are much more users which can create tables.

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Well, as the matter of fact, I run your query on my system and none of the listed privileges was CREATE TABLE There were alter, delete, execute, index, insert, references, select and update, but not CREATE TABLE. Do you really have this privilege listed in 'USER_TAB_PRIVS'?

    I guess it depends on how users are created ... when I create them, I grant CONNECT and RESOURCE as default and that's all most of them ever need. As CREATE TABLE is one privilege of the CONNECT role, I expected (obviously wrong) that all users created in Oracle databases that are granted the CONNECT role also can CREATE TABLEs.

  6. #6
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    My suggestion was wrong. USER_TAB_PRIVS describe something else.

Posting Permissions

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