join query [message #626406] |
Mon, 27 October 2014 12:28 |
evoradba
Messages: 144 Registered: April 2005 Location: Canada
|
Senior Member |
|
|
Hello
how can I join this sql query to spool only 1 file out. basically I need a sql to see all users, permissions, roles and last time they logged into the database
SELECT DISTINCT d.username "Username", d.account_status "Account Status", d.profile "Profile" FROM dba_users d, v$pwfile_users p WHERE p.username (+) = d.username ORDER BY initcap(d.username)
/
select grantee "Username", granted_role "Role", admin_option "Admin Option", default_role "Default" from sys.dba_role_privs order by grantee
/
|
|
|
Re: join query [message #626408 is a reply to message #626406] |
Mon, 27 October 2014 12:33 |
|
Littlefoot
Messages: 21808 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
spool your_File.txt
select distinct ...
select grantee ...
spool off;
EDIT: Or, you could even UNION (ALL) those SELECT statements - just make sure that columns you select match by number and data type (which, basically, means that you have to include TO_CHAR(NULL) into the first SELECT).
[Updated on: Mon, 27 October 2014 12:35] Report message to a moderator
|
|
|
Re: join query [message #626409 is a reply to message #626406] |
Mon, 27 October 2014 12:47 |
evoradba
Messages: 144 Registered: April 2005 Location: Canada
|
Senior Member |
|
|
thanks
what I mean is how to join these 2 query's into a single sql statement
SELECT DISTINCT d.username "Username", d.account_status "Account Status", d.profile "Profile" FROM dba_users d, v$pwfile_users p WHERE p.username (+) = d.username ORDER BY initcap(d.username)
/
select grantee "Username", granted_role "Role", admin_option "Admin Option", default_role "Default" from sys.dba_role_privs order by grantee
/
|
|
|
Re: join query [message #626410 is a reply to message #626409] |
Mon, 27 October 2014 12:50 |
John Watson
Messages: 8931 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Quote:what I mean is how to join these 2 query's into a single sql statement
UNION ALL. As said already.
Quote:and last time they logged into the database You'll need to query user$.spare6 for this.
|
|
|
|
Re: join query [message #626412 is a reply to message #626411] |
Mon, 27 October 2014 13:02 |
evoradba
Messages: 144 Registered: April 2005 Location: Canada
|
Senior Member |
|
|
it does not have to be distinct - I just need one query to select username, account_status, profile, v$pwfile_users from dbausers and join with grantee grated_role , admin_option , default_role from sysdbaroles
so basically join the 2 into 1
thank you
|
|
|
|
Re: join query [message #626414 is a reply to message #626413] |
Mon, 27 October 2014 13:22 |
evoradba
Messages: 144 Registered: April 2005 Location: Canada
|
Senior Member |
|
|
sorry
what I need is a query to select all of these into one single statement
SELECT DISTINCT d.username "Username", d.account_status "Account Status", d.profile "Profile" FROM dba_users d, v$pwfile_users p WHERE p.username (+) = d.username ORDER BY initcap(d.username)
/
select grantee "Username", granted_role "Role", admin_option "Admin Option", default_role "Default" from sys.dba_role_privs order by grantee
|
|
|
Re: join query [message #626415 is a reply to message #626414] |
Mon, 27 October 2014 13:23 |
John Watson
Messages: 8931 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
A compound query (as suggested by LF) is a single statement. What's your problem with it?
--update: incidentally, why are you including v$pwfile_users in the query? It adds nothing.
[Updated on: Mon, 27 October 2014 13:25] Report message to a moderator
|
|
|
|
Re: join query [message #626418 is a reply to message #626416] |
Mon, 27 October 2014 13:54 |
evoradba
Messages: 144 Registered: April 2005 Location: Canada
|
Senior Member |
|
|
we need it in a single select statement to
select username, account_status, profile from dba_users
then join with
select grantee, granted_role, admin_option, default_role from sys.dba_role_privs
how do I join them into one?
|
|
|
|
|
Re: join query [message #626421 is a reply to message #626419] |
Mon, 27 October 2014 14:08 |
evoradba
Messages: 144 Registered: April 2005 Location: Canada
|
Senior Member |
|
|
how to join these two selects from dba_users and sys.dba_role_privs
select username, account_status, profile from dba_users
select grantee, granted_role, admin_option, default_role from sys.dba_role_privs
|
|
|
|
Re: join query [message #626424 is a reply to message #626421] |
Mon, 27 October 2014 14:48 |
evoradba
Messages: 144 Registered: April 2005 Location: Canada
|
Senior Member |
|
|
-:) here we go
how to I get a select from dba_users and from sys.dba_roles_privs
to get
username, account_status , profile grantee , granted_role, admin_option ,default_role
thanks
|
|
|
|
Re: join query [message #626426 is a reply to message #626424] |
Mon, 27 October 2014 15:05 |
evoradba
Messages: 144 Registered: April 2005 Location: Canada
|
Senior Member |
|
|
thanks
Im trying this
SQL> select a.username , a.account_status, a.profile , b.grantee, b.granted_role, b.admin_option, b.default_role from a dba_users b sys.dba_role_privs;
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
|
|
|
|
|
|
Re: join query [message #626432 is a reply to message #626431] |
Mon, 27 October 2014 15:43 |
evoradba
Messages: 144 Registered: April 2005 Location: Canada
|
Senior Member |
|
|
here we go
sorry bad day sometimes cant think -;)
--col UserName format a30
--col Server format a15
--col DBInstance format a15
--col DatabaseName format a15
--col SchemaObjects format a30
--col Roles format a20
col LastLogonDate format a15
col ResourceOwner format a15
col EmplID format a10
set colsep ,
set lines 500
set pages 50000
set echo off
set feedback off
--set sqlprompt ''
--set trimspool on
set headsep off
spool MRA5_&DBName._&Date..csv
select a.username, a.account_status, a.profile, a.expiry_date, b.grantee, b.granted_role, b.admin_option, b.default_role from dba_users a, sys.dba_role_privs b
order by a.username
/
spool off
exit
|
|
|
|
Re: join query [message #626435 is a reply to message #626434] |
Mon, 27 October 2014 15:54 |
evoradba
Messages: 144 Registered: April 2005 Location: Canada
|
Senior Member |
|
|
I get what I need
USERNAME ,ACCOUNT_STATUS ,PROFILE ,EXPIRY_DA,GRANTEE ,GRANTED_ROLE ,ADM,DEF
------------------------------,--------------------------------,------------------------------,---------,---------------------------- --,------------------------------,---,---
TEST ,LOCKED ,APPLICATION ,08-APR-14,SYS ,XDB_SET_INVOKER ,YES,YES
|
|
|
|