DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: datafiles, users

  1. #1
    Join Date
    Mar 2001
    Posts
    3

    Talking

    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

  2. #2
    Join Date
    Feb 2001
    Location
    Scotland
    Posts
    200
    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

  3. #3
    Join Date
    Feb 2001
    Location
    Scotland
    Posts
    200

    Thumbs up

    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

  4. #4
    Join Date
    Mar 2001
    Posts
    3
    Hi Alison!

    Thanks. That was just what I was looking for.

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