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
Printable View
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.
Quote:
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 :
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
yls177:Quote:
can i do it with an update statement instead?
In that case, try I bet it will be sucidal
right, i am asking is it possible to update SYS owned base objects witht he roles/privilegs instead of the oracle specified commandsQuote:
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
thanks
Well you can create your own roles instead of using predefined roles.Quote:
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
Yes, it's possible to update SYS data dictionary tables, but you don't want to do this in production.Quote:
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
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...
Results are working good, but didnt expect this coz once when I tried to change owner..got the server ending up with Ora-600.Quote:
Originally posted by TomazZ
You can try it on some test database you can afford losing, and tell us the results...
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.
Interesting, looks better than changing owner... ;)Quote:
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.
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 :D
I guess its impossible - mean chaging owner by updating sys objects.:rolleyes:Quote:
Originally posted by TomazZ
Interesting, looks better than changing owner... ;)
Adding a ROW or 2 in SYSAUTH$, I think will not lead to serious issues, but yes update & delete are dangerous.Quote:
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.
Well if on PRO nothing silly is advisible even after black box testing or gorilla testing;)
pretty much:D :cool:Quote:
Originally posted by TomazZ
Hacking data dictionary really amuses you, doesn't it :D