Home > General, Oracle > Database Users and Roles Stats

Database Users and Roles Stats

Users:

1. List of Users

set pages 999 lines 100
col username	format a20
col status	format a8
col tablespace	format a20
col temp_ts	format a20
select	username
,	account_status status
,	created
,	default_tablespace tablespace
,	temporary_tablespace temp_ts
from	dba_users
order	by username
/

Roles:

1. Find a role

select	*
from 	dba_roles
where	role like '&role'
/

2. List roles granted to a user

select	grantee
,	granted_role
,	admin_option
from	dba_role_privs
where	grantee like upper('&username')
/

3.  List system privileges granted to a role

select	privilege
,	admin_option
from	role_sys_privs
where	role like '&role'
/

4. List table privileges granted to a role

select	owner || '.' || table_name "TABLE"
,	column_name
,	privilege
,	grantable
from	role_tab_privs
where	role like '&role'
/

Links:

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: