-
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
-
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.
-
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"
-
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"
-
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
-
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"
-
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
-
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"
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|