OraFAQ Forum: Security » Question about granting tables

Home » RDBMS Server » Security » Question about granting tables

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Question about granting tables [message #120519]

Fri, 20 May 2005 05:44

Uwe
Messages: 260
Registered: February 2003
Location: Zürich, Switzerland

Senior Member

Hi all,

I know that this is basic, but I do not know if there is a quicker possibility.

I have 2 Users, User 2 is created with "like User 1".
But I need to grant select on all tables, views and synonyms, which owned by user 1, to user 2.

kind regards
Uwe

Report message to a moderator

Re: Question about granting tables [message #120560 is a reply to message #120519]

Fri, 20 May 2005 08:08

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

how did you create this another user?
a simple script like this should help.
spool the output.
and run the script.
You may need to add specific entries for other missing objects.

scott@9i > @cr_user_like
Enter user to model new user to: SCOTT
Enter new user name: ANOTHERSCOTT
Enter new user's password: ANOTHERTIGER
create user ANOTHERSCOTT identified by ANOTHERTIGER default tablespace USERS temporary tablespace TEMP profile DEFAULT;
grant DBA to ANOTHERSCOTT;
grant CONNECT to ANOTHERSCOTT;
grant RESOURCE to ANOTHERSCOTT;
grant UNLIMITED TABLESPACE to ANOTHERSCOTT;
grant SELECT ANY DICTIONARY to ANOTHERSCOTT;
alter user ANOTHERSCOTT default role DBA;
alter user ANOTHERSCOTT default role CONNECT;
alter user ANOTHERSCOTT default role RESOURCE;
scott@9i > get cr_user_like
  1  set pages 0 feed off veri off lines 500
  2  accept oldname prompt "Enter user to model new user to: "
  3  accept newname prompt "Enter new user name: "
  4  accept psw     prompt "Enter new user's password: "
  5  -- Create user...
  6  select 'create user &&newname identified by &&psw'||
  7         ' default tablespace '||default_tablespace||
  8         ' temporary tablespace '||temporary_tablespace||' profile '||
  9         profile||';'
 10  from   sys.dba_users
 11  where  username = upper('&&oldname');
 12  -- Grant Roles...
 13  select 'grant '||granted_role||' to &&newname'||
 14         decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION')||';'
 15  from   sys.dba_role_privs
 16  where  grantee = upper('&&oldname');
 17  -- Grant System Privs...
 18  select 'grant '||privilege||' to &&newname'||
 19         decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION')||';'
 20  from   sys.dba_sys_privs
 21  where  grantee = upper('&&oldname');
 22  -- Grant Table Privs...
 23  select 'grant '||privilege||' on '||owner||'.'||table_name||' to &&newname;'
 24  from   sys.dba_tab_privs
 25  where  grantee = upper('&&oldname');
 26  -- Grant Column Privs...
 27  select 'grant '||privilege||' on '||owner||'.'||table_name||
 28         '('||column_name||') to &&newname;'
 29  from   sys.dba_col_privs
 30  where  grantee = upper('&&oldname');
 31  -- Set Default Role...
 32  select 'alter user &&newname default role '|| granted_role ||';'
 33    from sys.dba_role_privs
 34   where grantee = upper('&&oldname')
 35*    and default_role = 'YES';

I didnt write the above script.
It fairly met my requirements, so i used it looooong back.
The credit should goto some one else.

Report message to a moderator

Re: Question about granting tables [message #120896 is a reply to message #120560]

Tue, 24 May 2005 09:42

Uwe
Messages: 260
Registered: February 2003
Location: Zürich, Switzerland

Senior Member

Thanks Mahesh,

it works with some small modifications.
One thing what was missing were the tables which owned by the original user. The script only looks for granted things.
And the other was a connection problem. This Program has silly restictions. I couldn't grant on tables or views which are owned by the user even if I tried as sys. I have to implement a connect in this script, which makes it not portable.
But it works

Here's my script:

set pages 0 feed off veri off lines 500
accept oldname prompt "Enter user to model new user "
accept newname prompt "Enter new user Name "
accept pw prompt "Enter Users Password "
spool users.sql
select 'create user &&newname identified by &&pw'||
' default tablespace '||default_tablespace||
' temporary tablespace '||temporary_tablespace||' profile ' ||
profile||';'
from sys.dba_users
where username = upper('&&oldname');
select 'grant '||granted_role|| ' to &&newname'||
decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION')||';'
from sys.dba_role_privs
where grantee = upper('&&oldname');
select 'alter user &&newname default role '|| granted_role ||';'
from sys.dba_role_privs
where grantee = upper('&&oldname')
and default_role = 'YES';
prompt connect deskrepo/deskrepo
select 'grant '||privilege||' to &&newname'||
decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION')||';'
from sys.dba_sys_privs
where grantee = upper('&&oldname');
select 'grant '||privilege|| ' on '||owner||'.'||table_name||' to &&newname;'
from sys.dba_tab_privs
where grantee = upper('&&oldname')
and privilege in ('SELECT', 'REFERENCES');
select 'grant '||privilege||'on '||owner||'.'||table_name||
'('||column_name||') to &&newname;'
from sys.dba_col_privs
where grantee = upper('&&oldname');
prompt connect desk/desk
select 'grant select on '||owner||'.'||table_name||' to &&newname;'
from sys.dba_tables
where owner = upper('&&oldname');
spool off

ciao
Uwe

Report message to a moderator

Previous Topic:	Security
Next Topic:	Preventing end-users from connecting using 3rd party softwares

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jan 08 03:34:26 CST 2025