datafiles, users

Hi all!

Couple of questions for you:

Is it better to have bigger datafiles than more and smaller?

Do anybody have a script that lists all users in database with their roles and privs?

In 817 on NT is it nessessary to restart the listener after you have restarted the instance service?

/Martin

Hi there Martin,

I'm not sure about the other question, but I use the following script to see what roles each user has.

Rem --- Script to analyse what privilages a user has

col username for a10
col default_tablespace for a15
col temporary_tablespace for a15
col profile for a15
col granted_role for a15
break on username

select
u.username,
u.default_tablespace,
u.temporary_tablespace,
u.created,
u.profile,
r.granted_role
from sys.dba_users u, dba_role_privs r
where u.username like upper('&1')
and (upper('&&2') in ('*','%')
and u.username=r.grantee
or exists(
/*
select *
from sys.dba_role_privs
where grantee = u.username
and granted_role like upper('&2')
*/
select 0
from sys.sysauth$ sa, sys.user$ r
where sa.grantee#=u.user_id
and sa.privilege#=r.user#
and r.name like upper('&&2'))
)
order by u.username
/

This script allows you to select which user / role your interested in
1 is the username
2 is the role
If you want to show all users with all roles then just put a percent (%) in when prompted.

Hope this helps

Alison

Hi Martin, me again

I new that I had another script which might be of use to you, This one not only shows the user and role it also gives you all the privileges, I think this might be more what you were looking for,

I have noticed that it can be a bit system intensive if you have many many users (because of the unions) however It's still one of my favorites.

Hope it helps

col username for a10
col owner for a10
col table_name for a10
col "Granted Role" for a15
col privilege for a25
col "DEFAULT" for a15
COL "TEMPORARY" FOR A15
col "dummy" noprint

select u.username,
u.Default_tablespace "DEFAULT",
u.temporary_tablespace "TEMPORARY",
owner,
privilege,
' ' "Granted Role",
1 "dummy"
from dba_users u, dba_tab_privs t
where u.username=t.grantee
union
select username,
' ',
' ',
' ',
privilege,
' ',
2 "dummy"
from dba_users u, dba_sys_privs s
where u.username=s.grantee
union
select username,
' ',
' ',
' ',
' ',
granted_role,
3 "dummy"
from dba_users u, dba_role_privs r
where u.username = r.grantee
union
select username,
default_tablespace,
temporary_tablespace,
' ',
' ',
' ',
4 "dummy"
from dba_users u, dba_role_privs r
where u.username = r.grantee
ORDER BY 1

Hi Alison!

Thanks. That was just what I was looking for.