giving admin option with update instead of grant
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: giving admin option with update instead of grant

Hybrid View

  1. #1
    Join Date
    Oct 2002
    Posts
    391

    giving admin option with update instead of grant

    i did a "grant connect, resource to user" if i want to grant them the admin option, i can do it with a "grant connect, resource to user with admin option ". my question, is can i do it with an update statement instead? thanks

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Let me ignore your question, if favour of advising you not to grant these roles to users.

    Create your own roles that define exactly what system privileges the users are to have instead. It is extremely unlikely that your requirements match up with CONNECT and RESOURCE.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758

    Re: giving admin option with update instead of grant

    Originally posted by yls177
    my question, is can i do it with an update statement instead? thanks
    Code:
    grant update on table_name to username with grant option;
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

  4. #4
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Sanjay :

    I think, he wants to update SYS owned base objects with the roles/prvilages he wants to grant users rather than Oracle spcified commands. I am just guessing from the way he has post the message

    can i do it with an update statement instead?
    yls177:

    In that case, try I bet it will be sucidal
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  5. #5
    Join Date
    Oct 2002
    Posts
    391
    Originally posted by abhaysk
    Sanjay :

    I think, he wants to update SYS owned base objects with the roles/prvilages he wants to grant users rather than Oracle spcified commands. I am just guessing from the way he has post the message



    right, i am asking is it possible to update SYS owned base objects witht he roles/privilegs instead of the oracle specified commands

    thanks

  6. #6
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Originally posted by yls177
    right, i am asking is it possible to update SYS owned base objects witht he roles/privilegs instead of the oracle specified commands

    thanks
    Well you can create your own roles instead of using predefined roles.
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

  7. #7
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422
    Originally posted by yls177
    right, i am asking is it possible to update SYS owned base objects witht he roles/privilegs instead of the oracle specified commands
    Yes, it's possible to update SYS data dictionary tables, but you don't want to do this in production.
    It's not supported and the outcome can be fatal.
    You can try it on some test database you can afford losing, and tell us the results...
    Tomaž
    "A common mistake that people make when trying to design something completely
    foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams

  8. #8
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by TomazZ
    You can try it on some test database you can afford losing, and tell us the results...
    Results are working good, but didnt expect this coz once when I tried to change owner..got the server ending up with Ora-600.

    But any ways if PRO box, then NO NO.

    Code:
    TEST:ABHAY> create user Test_Privs_By_updt_Sys_obj identified by test;
    
    User created.
    
    TEST:ABHAY> select * from user$ where name=upper('Test_Privs_By_updt_Sys_obj');
    
                   USER# NAME                                          TYPE# PASSWORD                                    DATATS#            
    -------------------- ------------------------------ -------------------- ------------------------------ -------------------- ----------
                      28 TEST_PRIVS_BY_UPDT_SYS_OBJ                        1 220F30565A887930                                  0            
    
    TEST:ABHAY> select * from user$ where name='CONNECT';
    
                   USER# NAME                                          TYPE# PASSWORD                                    DATATS#            
    -------------------- ------------------------------ -------------------- ------------------------------ -------------------- ----------
                       2 CONNECT                                           0                                                   0            
    
    TEST:ABHAY> select max(SEQUENCE#) from sysauth$;
    
          MAX(SEQUENCE#)
    --------------------
                     345
    
    TEST:ABHAY> insert into sysauth$ values(28,2,346,1);
    
    1 row created.
    
    TEST:ABHAY> commit;
    
    Commit complete.
    
    TEST:ABHAY> select * from dba_role_privs where grantee='TEST_PRIVS_BY_UPDT_SYS_OBJ';
    
    GRANTEE                        GRANTED_ROLE                   ADM DEF
    ------------------------------ ------------------------------ --- ---
    TEST_PRIVS_BY_UPDT_SYS_OBJ     CONNECT                        YES YES
    
    TEST:ABHAY> select distinct u.* from sysauth$ au, user$ u where u.USER#=au.GRANTEE# and u.name='DBA';
    
                   USER# NAME                                          TYPE# PASSWORD                                    DATATS#            
    -------------------- ------------------------------ -------------------- ------------------------------ -------------------- ----------
                       4 DBA                                               0                                                   0            
    
    TEST:ABHAY> insert into sysauth$ values(28,4,347,0);
    
    1 row created.
    
    TEST:ABHAY> commit;
    
    Commit complete.
    
    TEST:ABHAY> select * from dba_role_privs where grantee='TEST_PRIVS_BY_UPDT_SYS_OBJ';
    
    GRANTEE                        GRANTED_ROLE                   ADM DEF
    ------------------------------ ------------------------------ --- ---
    TEST_PRIVS_BY_UPDT_SYS_OBJ     DBA                            NO  YES
    TEST_PRIVS_BY_UPDT_SYS_OBJ     CONNECT                        YES YES
    
    TEST:ABHAY> conn TEST_PRIVS_BY_UPDT_SYS_OBJ/TEST
    Connected.
    TEST:ABHAY> create table abhay.emp (EMP_ID Number, EMP_NAME Varchar2(20));
    
    Table created.
    
    TEST:ABHAY> select * from abhay.emp;
    
    no rows selected
    
    TEST:ABHAY> insert into abhay.emp values(1,'Abhay');
    
    1 row created.
    
    TEST:ABHAY> commit;
    
    Commit complete.
    
    TEST:ABHAY> select * from abhay.emp ;
    
                  EMP_ID EMP_NAME
    -------------------- --------------------
                       1 Abhay
    
    
    TEST:ABHAY> conn / as sysdba
    Connected.
    TEST:ABHAY> delete from sysauth$ where grantee#=28 and PRIVILEGE#=4;
    
    1 row deleted.
    
    TEST:ABHAY> select * from dba_role_privs where grantee='TEST_PRIVS_BY_UPDT_SYS_OBJ';
    
    GRANTEE                        GRANTED_ROLE                   ADM DEF
    ------------------------------ ------------------------------ --- ---
    TEST_PRIVS_BY_UPDT_SYS_OBJ     CONNECT                        YES YES
    
    TEST:ABHAY> commit;
    
    Commit complete.
    
    TEST:ABHAY> conn TEST_PRIVS_BY_UPDT_SYS_OBJ/TEST
    Connected.
    TEST:ABHAY> select * from abhay.EMP;
    select * from abhay.EMP
                        *
    ERROR at line 1:
    ORA-00942: table or view does not exist
    
    
    TEST:ABHAY> create table abhay.emp1(ID number);
    create table abhay.emp1(ID number)
    *
    ERROR at line 1:
    ORA-01031: insufficient privileges
    
    
    TEST:ABHAY>

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  9. #9
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422
    Originally posted by abhaysk
    Results are working good, but didnt expect this coz once when I tried to change owner..got the server ending up with Ora-600.
    Interesting, looks better than changing owner...
    Still, this is to simple test to rely on.
    So far we can only say: it still has a potential to work, but it needs a lot of testing.
    Hacking data dictionary really amuses you, doesn't it
    Tomaž
    "A common mistake that people make when trying to design something completely
    foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams

  10. #10
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by TomazZ
    Interesting, looks better than changing owner...
    I guess its impossible - mean chaging owner by updating sys objects.

    Originally posted by TomazZ
    Still, this is to simple test to rely on.
    So far we can only say: it still has a potential to work, but it needs a lot of testing.
    Adding a ROW or 2 in SYSAUTH$, I think will not lead to serious issues, but yes update & delete are dangerous.
    Well if on PRO nothing silly is advisible even after black box testing or gorilla testing

    Originally posted by TomazZ
    Hacking data dictionary really amuses you, doesn't it
    pretty much
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

Posting Permissions

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


Click Here to Expand Forum to Full Width