Not good for N-Tier Architecture
Gowrishankar Holalkere, October 18, 2002 - 10:26 pm UTC
Tom
I agree with your suggestion if
1)You have handfull of users to manage or
2)You really dont care about the number of concurent connections and resource reusability and connection effieciency.
If you have a 3/N-tier architecture, using single oracle account and managing access/roles in application layer is more effeicient and scalable. Also most of the comercial class applications use LDAP kind of service for autentication and user account management.
So the decision would be based on the requirement. Long term, Short Term, Scalable or Quick and Dirty and ...
Here are my questions
1)Please do let me know if you you how your 1 account per user would work in a N tier env, other then making a dedicated connection for each user.
2)Is there a way to make the db connection independent of the user autentication. Something where the application layer can attach/pass the user credentials with every call it makes to the database thr a common connection?? Am I making any sense??
Thanks
October 19, 2002 - 10:35 am UTC
Well, I run a system with 40k users.
It is n-tier.
I *do care* somewhat about those things (trust me, i do).
I argue it is NEITHER more efficient NOR more scalable.
I argue that what it does it lock the data into your application, limiting its reused. N-tier is just "todays" architecture. Just like host based was last centuries and client server was after that. There will be some other architecture.
By doing these crucial things in the APPLICATION ITSELF, you are locking yourself into an architecture for ever (many of the client server and host based systems are out there not cause people want them -- but because they cannot move OFF of them -- the logic is all wrapped around itself in the application -- you cannot access the data safely/securely WITHOUT the app).
See
</code>
http://otn.oracle.com/docs/products/ias/doc_library/90200doc_otn/core.902/a90146/fundamen.htm#1006392 <code>
for a description of proxy authentication.
It lets you setup a PHYSICAL connection to the database and then "set user" within that connection. The physical connection -- slow. The "set user" is just setting up a session state (something YOU MUST do anyway with a connection pool -- you cannot let bits of a previous state hang out across connections)
#2 not only makes sense -- we do it.
Additional Questions based on your Answer
Meyer Tollen, October 19, 2002 - 11:21 am UTC
Tom you mentioned
"1) I create real, honest, auditable -- grantable -- users in the database."
Assuming a mod/PLSQL application... say an online service application where you want as many members of the "public" to join/pay as posible...would you have code the creates the Oracle user, that gets executed after they fill out the sign up form, maybe via a package?
When users logon to your service (web site) and you authenticate their password and your app writes a "logged on" cookie (since I am thinking you would have them connect via the DAD)...how do you validate that they entered a correct ORACLE username/password combination....w/out a connection attempt from their account (if you are doing this)?
I realize this is making assumtions based on your answer...let me know if this type of application is not what you had in mind....Regardless, I am still curious about the specific way to do oracle validation via the DAD.
Thanks,
Meyer
October 19, 2002 - 11:49 am UTC
I would have a public dad which everyone can use and when you login you get switched to a dad where the username password is not stored with the dad so you are asked for your username/password and mod_plsql uses that to authenticate you.
Your followup.....?'s
Meyer, October 28, 2002 - 9:45 am UTC
Tom you said this at the bottom:
<QUOTE>
I would have a public dad which everyone can use and when you login you get
switched to a dad where the username password is not stored with the dad so you
are asked for your username/password and mod_plsql uses that to authenticate
you.
</QUOTE>
I tried it and it works well. I have some followup questions since this is WAY different that I have done in the past. But am going to implement this way. 1 and 2 are most important. 3-4 are because I am curious if you can explain.
1)When an employee leaves, but you still need to access their related data (and be able to reference who they were). Do you drop the user?? Change their password and keep old emps as users...or what.
2)If I want to reference the current user inside my code written in plsq packages should I use this w/each call:
sys_context( 'userenv', 'current_user' );
3)Is it possible to create a custom HTML logon page (other then the IE-does-it-w-a-POPUP logon screen) with this no-password DAD model? If yes, How would your custom logon form authenticate/logon the blank DAD username/pword (psydo-sql if posible)?
4)When the DAD is logged on, I do not see a cookie recorded in my browser, but it behaves that way? How does the DAD/browser know whos is logged on? I have managed this in the past w/my own "you-are-logged-on-cookies".
Thanks,
Meyer
October 28, 2002 - 12:47 pm UTC
1) drop the user -- your "data" won't go away. the records you have about them are safely tucked in your table. There is no correlation between their database account and "their records"
2) or just
x := USER;
3) nope. basic authentication is handled by the browser for us. it is totally browser driven
4) the browser does the work -- we need no cookies, it just happens. See
</code>
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:822026909613 <code>
for how this protocol works under the covers.
Great, just clarify one point
Meyer, October 28, 2002 - 1:55 pm UTC
<quote>
1) drop the user -- your "data" won't go away. the records you have about them
are safely tucked in your table. There is no correlation between their database
account and "their records"
</quote>
I understand that...but not sure how you are doing this:
Say you create an application that only has 1 table of transactions for users (say hours they worked). And you were going to create reports that show transactions by user.
1)Based on your logic, would your applications transaction table have a column similar to "username" or "user_id" ?(or both), where does this column get its value and where does it join to? (maybe USERNAME or USER_ID from DBA_USERS?)
I am trying to figure out how you handle droping a user who quits and not creating another w/the same account later or making it so it does not matter
(Jen Smith, John Smith)= jsmith.
Or
if you use USER_ID (from DBA_USERS)...and if so do you keep their "name" w/the data OR in another table your application manages outside of DBA_USERS?
As always, THANKS!
Meyer
October 28, 2002 - 7:23 pm UTC
I learned the hard way many years ago -- DO NOT USE THE "USERID" as a key to anything. Why?
o people get married (go figure), they change their names and get quite upset when their user name is their maiden name
o people get divorced (see #1 above)
o people from foreign countries "americanize" their names sometimes (happens more often then I can count now)
usernames are not imutable. You can record their username but you need a "real key" for them -- employee id, system generated number, something.
When Oracle flattened their domain (eg: my email WAS asktom_us@oracle.com, now it is just tkyte@oracle.com), we had this issue in spades. do not use a username as a key -- it is functionally dependent data and nothing more or less.
Found the way (Expert One on One)
Meyer, November 18, 2002 - 5:46 pm UTC
Tom,
I found an implementation to bridge the oracle-DB accounts with the my-own-app-user-info-table, and keep them in sync programmatically.
Example in your book
chapter 15: Autonomous Transactions
Page: 666, Performing DDL in Trigger
I have learned so much from your book, THANKS.
Meyer
what for all these users?
reader, March 13, 2003 - 11:53 pm UTC
Tom,
after the installation of my database using Database configuration assistant, i logged in to see many users...version 9.2.0.1.0. as follows...
ge$kumar@devadw.world>select username from all_users ;
USERNAME
==============================
SYS
SYSTEM
DBSNMP
OUTLN
WMSYS
WKSYS
ORDSYS
ORDPLUGINS
MDSYS
CTXSYS
XDB
ANONYMOUS
ODM
ODM_MTR
WKPROXY
QS_ADM
HR
OE
PM
SH
RMAN
QS
QS_WS
QS_ES
QS_OS
QS_CBADM
QS_CB
QS_CS
SCOTT
MATRIX
WASCNFG
EJSADMIN
1)Could you please tell me what for all these users and when these users are used?. matrix,wascnfg,ejsadmin were created by me. i do know few users from this but majority of these users are new to me.
2)if i dont want any of these users, how to avoid creating that? say i doesnt want rman to be created, how to stop that at the time of creation.
3)I see tablespaces like XDB, ODM, DRSYS what are these used for and again if i dont want to create those tablespaces, how should i tell database configuration assistant to, NOT to create those tablespace. is it possible?
ge$kumar@devadw.world>select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
==============================
SYSTEM
UNDOTBS1
TEMP
DRSYS
EXAMPLE
INDX
ODM
TOOLS
USERS
XDB
Thanks ...
March 14, 2003 - 5:55 pm UTC
USERNAME Mystery
Aamir Abbas, June 05, 2003 - 7:12 am UTC
Dear Tom,
Please solve this mystery....
SQL> select * From all_users;
USERNAME USER_ID CREATED
------------------------------ --------- ---------
SYS 0 04-SEP-03
SYSTEM 5 04-SEP-03
...
s_aman 96 04-JUN-03
AAMIR_ABBAS 97 05-JUN-03
56 rows selected.
A user named "s_aman" appears in lower-case...
All application users were created using 9i Enterprise Console.
we cannot login using this user.
SQL> connect aamir_abbas/aamir@psl
Connected.
SQL> connect s_aman/s_aman@psl
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> alter user s_aman identified by s_aman;
alter user s_aman identified by s_aman
*
ERROR at line 1:
ORA-01918: user 'S_AMAN' does not exist
SQL> drop user s_aman;
drop user s_aman
*
ERROR at line 1:
ORA-01918: user 'S_AMAN' does not exist
Now, I created the same user using SQL*Plus...
and it says...
SQL> select * From all_users;
USERNAME USER_ID CREATED
------------------------------ --------- ---------
SYS 0 04-SEP-03
SYSTEM 5 04-SEP-03
...
s_aman 96 04-JUN-03
AAMIR_ABBAS 97 05-JUN-03
S_AMAN 98 05-JUN-03
57 rows selected.
What does it mean?
What's wrong in my db????
June 05, 2003 - 8:38 am UTC
someone has:
create user "s_aman" ......
using quoted identifiers.
alter user "s_aman" identified by foo;
connect using Forms..
Aamir Abbas, June 06, 2003 - 11:32 am UTC
Dear Tom,
Thanks. It helped...
But how will Oracle Forms will distinguish both of these?
How will user connect to "s_aman" using Forms.? because if the user will type his id on login screen, it will always connect to S_AMAN....
Thanks again for your kind help.
June 06, 2003 - 11:37 am UTC
don't use quoted identifiers, you are only asking for trouble if you do. tools are not "expecting it" and may or may not support it.
roles identified externally
Geo, June 06, 2003 - 4:26 pm UTC
What does it mean by
create role role_name
identified externally;
and what is its usage in real world?
I understand creating a user identified externally in relation to ops$ accounts in unix. I have never used role in that respect. if you could clarify this with a demo if possible, it would be helpful. Thanks in advance.
June 06, 2003 - 5:07 pm UTC
it means you would have the role if you belonged to the unix group the role was named after.
[tkyte@tkyte-pc-isdn tkyte]$ groups
tkyte <b>apache</b> ora920 dba
[tkyte@tkyte-pc-isdn tkyte]$ plus
SQL*Plus: Release 9.2.0.3.0 - Production on Fri Jun 6 17:06:08 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
ops$tkyte@ORA920> select * from session_roles;
ROLE
------------------------------
CONNECT
DBA
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
EXECUTE_CATALOG_ROLE
DELETE_CATALOG_ROLE
EXP_FULL_DATABASE
IMP_FULL_DATABASE
GATHER_SYSTEM_STATISTICS
WM_ADMIN_ROLE
JAVA_ADMIN
JAVA_DEPLOY
XDBADMIN
OLAP_DBA
14 rows selected.
<b>no apache, but</b>
ops$tkyte@ORA920> create role apache identified externally;
Role created.
ops$tkyte@ORA920> Disconnected from Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
[tkyte@tkyte-pc-isdn tkyte]$ plus
SQL*Plus: Release 9.2.0.3.0 - Production on Fri Jun 6 17:06:23 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
ops$tkyte@ORA920> select * from session_roles;
ROLE
------------------------------
CONNECT
DBA
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
EXECUTE_CATALOG_ROLE
DELETE_CATALOG_ROLE
EXP_FULL_DATABASE
IMP_FULL_DATABASE
GATHER_SYSTEM_STATISTICS
WM_ADMIN_ROLE
JAVA_ADMIN
JAVA_DEPLOY
XDBADMIN
OLAP_DBA
<b>APACHE</b>
15 rows selected.
<b>
now there is.</b>
and other users are not in it, unless they are in the unix group:
[ora920@tkyte-pc-isdn ora920]$ id
uid=501(ora920) gid=501(ora920) groups=501(ora920),502(dba)
[ora920@tkyte-pc-isdn ora920]$ plus
SQL*Plus: Release 9.2.0.3.0 - Production on Fri Jun 6 17:07:56 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
ops$ora920@ORA920> select * from session_roles;
ROLE
------------------------------
DBA
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
EXECUTE_CATALOG_ROLE
DELETE_CATALOG_ROLE
EXP_FULL_DATABASE
IMP_FULL_DATABASE
GATHER_SYSTEM_STATISTICS
WM_ADMIN_ROLE
JAVA_ADMIN
JAVA_DEPLOY
XDBADMIN
OLAP_DBA
13 rows selected.
is it still a group?
Reader, June 06, 2003 - 7:11 pm UTC
In your example, is 'apache' a group created at the os level just like a 'dba' group? As a os user if I am included in the group 'apache', I can have it represented in the database as a role? What privs the 'apache' role/group(?) contain in your example?
June 06, 2003 - 8:19 pm UTC
yes (that was the point of "groups" to show that apache was a unix group.
in my example, apache the group/role contained no privs -- only because I didn't grant anything to apache. grant something to it and I would have had that priv.
Good
R.Chacravarthi, October 01, 2003 - 11:46 am UTC
Dear sir,
well and wish the same from you.
When we have a role, is it possible to find out what are the
privileges that make up the role?
Thanks in advance.
October 01, 2003 - 1:52 pm UTC
dba_tab_privs
dba_sys_privs
dba_role_privs
where grantee = 'role name'
Help to identify the roles
A reader, May 26, 2004 - 3:19 pm UTC
Hi, Tom,
We try to develop a form system to dynamically grant various roles to different users in order to execute some of the process by the screen of the forms pulling from application menu.
Would you give me a query for all of the roles that have been granted to all of the database users both
directly(D) and indirect(I) please?
Does the following will do?
SELECT USERNAME, GRANTED_ROLE, 'D' GTYPE
FROM DBA_ROLE_PRIVS INNER JOIN ALL_USERS ON ( GRANTEE=USERNAME )
UNION
SELECT DISTINCT USERNAME, ROLE_ROLE_PRIVS.GRANTED_ROLE, 'I' as GRANT_TYPE
FROM DBA_ROLE_PRIVS INNER JOIN ALL_USERS on ( GRANTEE=USERNAME )
INNER JOIN ROLE_ROLE_PRIVS on (DBA_ROLE_PRIVS.GRANTED_ROLE = ROLE )
WHERE NOT EXISTS (SELECT 1
FROM DBA_ROLE_PRIVS drp
WHERE DRP.GRANTED_ROLE = ROLE_ROLE_PRIVS.GRANTED_ROLE and DRP.GRANTEE = USERNAME
)
Is it a practical query(feasible in real application world) Or the query missing something?
Thanks
May 26, 2004 - 4:39 pm UTC
Not current user
A reader, May 26, 2004 - 5:58 pm UTC
Hi, Tom,
Regarding my above (1 level above) question, you gave
me a link. It is only for current user, not for all
users. What I want to know is for all of the DB users,
give me their roles of both direct and indirect.
Thanks
May 27, 2004 - 8:26 am UTC
That would be a hugely large result set on anything other than a toy database.
Would not the logic be to pick a user - and then display the roles for that user. Anything else would be unwieldy to say the least.
If so, you should be able to take the refereneced query and make it work for you (change uid to that users user_id, change user to their name and wah-lah)
If not, you are heading down the wrong path for an interactive application. the result set would be enormous.
But, here you go, first query does a hierarchy of sorts with repeats. second one is flattened -- just the username -> role.
select decode(level,1,grantee,rpad('*',2*level,'*')||grantee) name ,
granted_role
from dba_role_privs
start with grantee in ( select username from dba_users )
connect by prior granted_role = grantee
/
select distinct
substr( max(name) over (order by r),11) name,
granted_role
from (
select rownum r,
decode(level, 1, to_char(rownum,'fm0000000000') || grantee ) name ,
granted_role
from dba_role_privs
start with grantee in ( select username from dba_users )
connect by prior granted_role = grantee
)
/
Displaying all roles by user . . .
Tak Tang, May 27, 2004 - 8:29 am UTC
I asked this same question when I first became a DBA.
What roles do I have?
TANGT@DEVC> select grantee, granted_role
2 from dba_role_privs
3 where grantee = user
4 /
GRANTEE GRANTED_ROLE
------------------------------ ------------------------------
OPS$HOME\TANGT DBA
1 row selected.
What roles are granted to the DBA role?
TANGT@DEVC> select grantee, granted_role
2 from dba_role_privs
3 where grantee = 'DBA'
4 /
GRANTEE GRANTED_ROLE
------------------------------ ------------------------------
DBA OLAP_DBA
DBA XDBADMIN
DBA PLUSTRACE
DBA JAVA_ADMIN
DBA JAVA_DEPLOY
DBA WM_ADMIN_ROLE
DBA EXP_FULL_DATABASE
DBA IMP_FULL_DATABASE
DBA DELETE_CATALOG_ROLE
DBA SELECT_CATALOG_ROLE
DBA EXECUTE_CATALOG_ROLE
DBA GATHER_SYSTEM_STATISTICS
12 rows selected.
Show me all roles granted to the DBA role, whether directly, or indirectly. This type of query is called 'hierarchical'.
TANGT@DEVC> select grantee, granted_role
2 from dba_role_privs
3 start with grantee = 'DBA' or grantee = 'PUBLIC'
4 connect by prior granted_role = grantee
5
TANGT@DEVC> /
GRANTEE GRANTED_ROLE
------------------------------ ------------------------------
DBA OLAP_DBA
OLAP_DBA SELECT_CATALOG_ROLE
SELECT_CATALOG_ROLE HS_ADMIN_ROLE
DBA XDBADMIN
DBA PLUSTRACE
DBA JAVA_ADMIN
DBA JAVA_DEPLOY
DBA WM_ADMIN_ROLE
DBA EXP_FULL_DATABASE
EXP_FULL_DATABASE SELECT_CATALOG_ROLE
SELECT_CATALOG_ROLE HS_ADMIN_ROLE
EXP_FULL_DATABASE EXECUTE_CATALOG_ROLE
EXECUTE_CATALOG_ROLE HS_ADMIN_ROLE
DBA IMP_FULL_DATABASE
IMP_FULL_DATABASE SELECT_CATALOG_ROLE
SELECT_CATALOG_ROLE HS_ADMIN_ROLE
IMP_FULL_DATABASE EXECUTE_CATALOG_ROLE
EXECUTE_CATALOG_ROLE HS_ADMIN_ROLE
DBA DELETE_CATALOG_ROLE
DBA SELECT_CATALOG_ROLE
SELECT_CATALOG_ROLE HS_ADMIN_ROLE
DBA EXECUTE_CATALOG_ROLE
EXECUTE_CATALOG_ROLE HS_ADMIN_ROLE
DBA GATHER_SYSTEM_STATISTICS
PUBLIC PLUSTRACE
25 rows selected.
So show me all roles granted to myself whether directly or indirectly, and do some tidying of the format . . . This is actually quite a leap, for which I will apologise in advance . . .
TANGT@DEVC> select u.username, r.role
2 from dba_users u, dba_roles r
3 where u.username = user
4 and r.role in
5 (
6 select granted_role
7 from dba_role_privs v1
8 where v1.grantee = u.username
9 union all
10 select v2.granted_role
11 from dba_role_privs v2
12 start with v2.grantee =
13 ( select granted_role
14 from dba_role_privs v3
15 where v3.grantee = u.username
16 )
17 or v2.grantee = 'PUBLIC'
18 connect by prior v2.granted_role = v2.grantee
19 )
20 /
USERNAME ROLE
------------------------------ ------------------------------
OPS$HOME\TANGT DBA
OPS$HOME\TANGT SELECT_CATALOG_ROLE
OPS$HOME\TANGT EXECUTE_CATALOG_ROLE
OPS$HOME\TANGT DELETE_CATALOG_ROLE
OPS$HOME\TANGT EXP_FULL_DATABASE
OPS$HOME\TANGT IMP_FULL_DATABASE
OPS$HOME\TANGT GATHER_SYSTEM_STATISTICS
OPS$HOME\TANGT HS_ADMIN_ROLE
OPS$HOME\TANGT WM_ADMIN_ROLE
OPS$HOME\TANGT JAVA_ADMIN
OPS$HOME\TANGT JAVA_DEPLOY
OPS$HOME\TANGT XDBADMIN
OPS$HOME\TANGT OLAP_DBA
OPS$HOME\TANGT PLUSTRACE
14 rows selected.
Now modify line 3 to select the users you do or do not want.
I sugguest excluding SYS and SYSTEM for starters.
Takmeister
Reader
A reader, January 25, 2005 - 1:46 pm UTC
What are the circumstances in which ROLES get invalidated
for an user who had been granted that role
January 25, 2005 - 3:16 pm UTC
roles don't get "invalidated"
user name approach
Jianhui, February 17, 2005 - 3:59 pm UTC
Tom,
I have seen some shops having different usernames for database application schema owners.
For instance, in development database, they have schema owner userid like app_dev, in test, the counterpart is app_test, in production it will be app_prod.
The pros of this approach is it's easier to use LDAP for centralized userid management, obviously the cons are that it causes migration problems when refreshing from one database to another.
But if all development, test, production databses' user IDs have same name, i.e. app, then how can we take advantage of LDAP or other centralized user ID management? Because in this case we 'll have to let user ID app have access to all databases, while using different ID, we can control whether app_dev should be in development DB only, and so on. I thinks same question applies to UNIX ID management in large organations, who have lots of machines, databases, apps., etc.
Please comment.
Best Regards
February 17, 2005 - 7:07 pm UTC
well, if you have a "global user" you have a global user.
The way the html/db guys (and many others) have gotten around this is all of their scripts reference ^OWNER (they use ^ instead of & as the substitution command). Use of a schema name in code to be run in the database is forbidden -- you reference ^OWNER. When you install, they ask "what name would you like" and it substitutes it in.
In client code, you can simply "alter session set current_schema=FOO" after connect in order to pick up the right schema (and don't hardcode the names)
Roles and Users
Mark, May 04, 2005 - 11:43 am UTC
Hi Tom,
If you could elaborate on this I would appreciate it. Again I am constantly reading about this subject and hope to find an answer but...
I have a Schema called HEALT4. this is the USER we log into within our Web App. All objects accessed are owned by this USER, HEALT4.
I created a second user RPT_USER.
I created a ROLE called RPT_ROLE.
I want USER RPT_USER to see and access certain objects that are within the HEALT4 schema. Namely, a subset of tables, functions, views, and procedures.
Is this possible without using Synonyms?
I know i can create public synonyms for HT4.OBJECT_NAME ad nauseum. I sthat the only way to go or is there an automagical way to do it via a role privilege?
Thanks!
May 04, 2005 - 1:42 pm UTC
rpt_user can issue
alter session set current_schema=healt4;
and then
select * from t
will as if they executed
select * from healt4.t
the setting of the schema changes how names are resolved, it doesn't change their access privileges or anything like that.
Revelation...
Mark, May 05, 2005 - 9:04 am UTC
Thanks Tom!
I had a small revelation in that I can use the proc_role code in a LOGON script to detect if it is a Report User logging in. If so, I can set the session environment (CURSOR_SHARING = FORCE and CURRENT_SCHEMA). This solves both my problems for today.
Great answers as always.
Create role identified externally
Nitin, June 02, 2005 - 1:38 pm UTC
1. Does the role get assigned only for OS Authenticated Roles (i.e. users created with identified externally)?
2. Or will it get assigned to DB users authenticated by DB?
My test shows that it does not get assigned for DB users.
Example:
Connected to:
Oracle8i Release 8.1.7.4.0 - Production
JServer Release 8.1.7.4.0 - Production
SQL> !id
uid=17004(testdmo) gid=40000(testdbsoft)
SQL> create role testdbsoft identified externally;
Role created.
SQL> create user test identified by test;
User created.
SQL> grant resource to testdbsoft;
Grant succeeded.
SQL> grant create session to test;
Grant succeeded.
SQL> conn test/test@testdbdemo
Connected.
SQL> select * from session_roles;
no rows selected
June 02, 2005 - 5:16 pm UTC
you are going over a network connection -- OS *anything* shouldn't be enabled or working.
Users and Roles
A reader, July 02, 2005 - 3:13 am UTC
Tom how could I know all the users created in the database along with the roles assigned to each of them?
Some thing like this
Users Roles
----- -----
U1 R1
...
Rn
... ...
Un R1
...
Rn
Thanks
July 02, 2005 - 9:26 am UTC
ops$tkyte@ORA9IR2> l
1 select username, granted_role
2 from dba_users, dba_role_privs
3* where username = grantee(+) order by 1
ops$tkyte@ORA9IR2> /
USERNAME GRANTED_ROLE
------------------------------ ------------------------------
A
ANONYMOUS
B
BIG_TABLE DBA
C
CTXSYS RESOURCE
CTXSYS CONNECT
CTXSYS DBA
......
That's great...
A reader, July 04, 2005 - 3:12 am UTC
Tom,
Thank you very much for the nice solution. It might be simple for others but great for me :)
Thanks again.
Is it possible to get such details when connected by any user without 'dba' role?
July 04, 2005 - 10:36 am UTC
you just need select on those two views so yes, you can get such details if
a) you have select on those views
b) someone creates that as a view and grants select on this new view to you
c) someone with the ability to run that query creates a procedure that shows this information and grants you the ability to run the procedure.
Simply The Great...
A reader, July 05, 2005 - 3:07 am UTC
Tom I love the way you respond.
Thanks again.
OPS$ account
reader, August 18, 2005 - 11:01 am UTC
Is creating OPS$ account specific for UNIX env? Can it be done on windows? If so how? Thanks.
August 18, 2005 - 4:30 pm UTC
works on windows too.
you set the os_authent_prefix if you want. and then:
grant dba to "OPS$XP10GR1\TKYTE" identified by foobar;
^^^ os authent prefix
^^^^^^^ windows domain name you are on
^ slash
^^^^^ os user name.
and have the authentication services in your init.ora permit "NTS" authentication
what is windows domain name? How to create one? Thanks.
reader, August 18, 2005 - 10:18 pm UTC
August 18, 2005 - 11:26 pm UTC
C:\Documents and Settings\tkyte>echo %USERDOMAIN%
TKYTE-HP
C:\Documents and Settings\tkyte>
(or ask your admin)
ANONYMOUS
A reader, February 22, 2006 - 8:30 am UTC
just for the sake of interest:
what is User ANONYMOUS for ?
(found him in DBA_USERS)
and why his password is anonymous ?
February 22, 2006 - 9:08 am UTC
used by Oracle Application Express (htmldb).
Users Info
Dawar, March 16, 2006 - 10:29 am UTC
Tom,
I am using Oracle (R) Enterprise Manager Console Version 9.2.0.1.0.
I wanted to know thw base view or table name of USERS folder as below:
Clicks on plus sign of Database tree.
select your database.
Open your database subfolder by clicking on plus sign of your Database.
You see six sub folders.
Instance, Schema, Security, Storage, Distributed & Warehouse
Select "Security" by open it subfolder by clicking on plus sign.
Pl. select users.
You will see the users info on EM.
Do you know which view or table this information base?
You can see information of users in following fields.
Username , Account Status,Expire Date, Default Tablespace,Temporary,the,Profile
I wanted to know base view or table name.
I check dba_users & all_users but thier description is different than above.
Dawar
March 16, 2006 - 2:35 pm UTC
Look again.
ops$tkyte@ORA9IR2> desc dba_users
Name Null? Type
---------------------------------------- -------- ----------------------------
<b> USERNAME NOT NULL VARCHAR2(30)</b>
USER_ID NOT NULL NUMBER
PASSWORD VARCHAR2(30)
<b> ACCOUNT_STATUS NOT NULL VARCHAR2(32)</b>
LOCK_DATE DATE
<b> EXPIRY_DATE DATE</b>
<b> DEFAULT_TABLESPACE NOT NULL VARCHAR2(30)</b>
<b> TEMPORARY_TABLESPACE NOT NULL VARCHAR2(30)</b>
CREATED NOT NULL DATE
<b> PROFILE NOT NULL VARCHAR2(30)</b>
INITIAL_RSRC_CONSUMER_GROUP VARCHAR2(30)
EXTERNAL_NAME VARCHAR2(4000)
Check For Users and Roles that Have Privileges With “ANY” Included
Dawar Naqvi, March 21, 2006 - 1:42 pm UTC
Tom,
I wanted to see any user or a role has privileges that include the word “ANY” in them,
this can be useful to an attacker.
A good example would be the privilege "SELECT ANY TABLE".
Is there any script to bring
all user or a role have privileges that include the word "ANY" in them?
cheers,
Dawar
March 22, 2006 - 2:42 pm UTC
select * from dba_sys_privs where privilege like '% ANY %';
SET ROLE ...
A reader, July 18, 2006 - 8:24 am UTC
Hi Tom,
Consider the following scenario.
SQL> connect system/password
Connected.
SQL> create user some_user identified by some_user;
User created.
SQL> grant connect to some_user;
Grant succeeded.
SQL> alter user some_user default role connect;
User altered.
SQL> grant some_role to some_user;
Grant succeeded.
SQL> connect some_user/some_user
Connected.
SQL> select * from session_roles;
ROLE
------------------------------
CONNECT
SQL> set role some_role;
Role set.
SQL> select * from session_roles;
ROLE
------------------------------
R1_00
User (some_user) has granted two roles.
1. connect --> default
2. some_role --> non-default
When user connected to database, the session has enabled only the default role CONNECT implicitly.
When SET ROLE ... statement is issued to enable a non-default role SOME_ROLE explicitly then there is only one role enabled in the session.
Where goes the default role CONNECT?
There is a need to grnat roles to user, some as default and others non-default. Default roles should be enabled implicitly upon connect and non-default roles to be enabled explicitly as required. Non-default roles should append the default ones, not replace them.
whats the way-out?
July 18, 2006 - 8:50 am UTC
you turned off connect, you set only some_role on.
set role some_role, connect;
would have set both on.
way out - query session_roles to see what is one, build SQL statement to set role to what is currently ON as well as the new one you want.
SET ROLE ...
A reader, July 19, 2006 - 2:53 am UTC
Tom,
The way out you stated looks somewhat verbose.
I think the guy who wrote this SET ROLE ... thing was in a bit hurry. He forgot a keyword so that the roles be appended rather than replaced. Some thing like this.
SET ROLE <roles> [ADDITIVE]
I am sure the development team at Oracle is working on the next version. How worthy it would be if this little thing be included!
For now would you like to show the PL/SQL example for the said purpose. (the verbose one ;)
July 19, 2006 - 9:07 am UTC
you can write the plsql - it is rather straight forward.
and if you care to share your efforts, that would be great.
SET ROLE ...
A reader, July 20, 2006 - 4:09 am UTC
Oh yes sure, I can share.
Yes I can do PL/SQL but at very novice level. Bear it!
declare
cursor c1 is
select role from session_roles;
-- the non-default role(s)
v_roles varchar2(100) := 'some_role';
v_sql varchar2(100) := 'set role ';
begin
for r1 in c1 loop
v_roles := v_roles || ', ' || r1.role;
end loop;
v_sql := v_sql || v_roles;
execute immediate v_sql;
end;
/
I know this anonymous block is the simplest one for you and of course the verbose one for me. ;)
Tom, you didn't share your thoughts over the SET ROLE ... statements' required syntax. I'm quite inquisitive!
July 22, 2006 - 4:38 pm UTC
The only time - the ONLY times - I've used set role:
a) to turn all of them off - for testing. or to turn them all on - for testing. set role none; set role all; worked great for that.
b) in an application that needs a specific set of roles. It would set those roles. Any other roles? That application didn't care, that application needed roles "a, b, c" - it would SET a, b, c
So, no, from my personal perspective - I have not seen the need to be additive.
Database User vs Generic username for Web Applications
Alex, July 24, 2006 - 2:32 pm UTC
Tom
When you said "one database user account per real world user", does this apply to those Web applications that have millions of real world users too ?
Compared to having generic Oracle logon ids (users are not likely to know what that is) that does the actual login to the database via the web app, How secure is giving users direct access to the database, and what if they find out the Port number, Host name and able to get into the network they would have direct access to the database using a tool like SQL Plus wouldn't they ?
I'm very curious about this approach, because I have never met an architect that design authentication this way.
July 24, 2006 - 3:00 pm UTC
everything is relative.
common sense rules.
use common sense... use what makes sense...
would I do millions of users "myself" - nope, we would be getting into the realm of "get me a directory service right now please".
Alexander, February 07, 2011 - 11:56 am UTC
Hi Tom,
Whenever I try and create a materialized view for a developer using our dba account with the dba role, I get a permission problem. The reason ended up being I did not have select privileges granted directly to me, and the object owner not having create table permissions.
So my question is, to get around this in the past, I just log in as sys + sysdba and I don't have to grant myself any extra. Why? Thanks.
February 09, 2011 - 7:12 am UTC
DO NOT EVER DO THAT. That is just about the worst practice I know of - it is up there in the top 10 "do not do things"
sysdba is magic
sysdba is special
sysdba should not be used most of the times it is used (you don't need it to start a database or shutdown - most DBA's shouldn't have it)
You know what the problem is - the account creating the materialized view didn't have the necessary privileges. You should
a) get them
b) document them
c) then create your materialized view
and don't do something like this in your DBA account - that is way overprivileged for this. The owner of the materialized view should be a development account with the LEAST set of privileges possible - and those privileges would be documented so everyone knows why they exist (will help with you audits greatly)
Alexander, February 09, 2011 - 10:12 am UTC
We promote developer's changes from a dev database to test/production using our dba account with the dba role. But at times like this, even having dba doesn't get the job done. I'm just wondering what SYS has that the DBA role doesn't. I wouldn't do this if I didn't have to. We support hundreds of applications, frankly no one has the time to spend on figuring out all the different permissions to the hundreds of schemas across 50 databases and grant each individually so that now our dba accounts are unique to each database.
February 10, 2011 - 4:54 pm UTC
... We promote developer's changes from a dev database to test/production using our
dba account with the dba role. ..
that shouldn't be done. You should promote these changes in the application schema itself, using the application schema's. That way you KNOW exactly what privileges you need and why you need them - they are documented and understood.
Please do not use sys, it is special, it is magic, stop doing that - you'll end up with a mess.
We support hundreds of applications,
frankly no one has the time to spend on figuring out all the different
permissions to the hundreds of schemas across 50 databases and grant each
individually so that now our dba accounts are unique to each database.
that is a scary thing to say. The developers CERTAINLY do have the time - it is part of their job.
Sorry, I totally disagree with you here - and I hope you have no data of any importance in your databases - because your security controls are really not even there.
Alexander, February 09, 2011 - 10:22 am UTC
Also, we don't really use the schema accounts because like I said there are hundreds of them and we can't be managing the passwords for all of them. Typically we lock them, unless it's an application that requires it to connect using that account.
February 10, 2011 - 4:55 pm UTC
dba can unlock it, then use it, then lock it again. That is what you should be doing.
Alexander, February 11, 2011 - 10:44 am UTC
How do you propose managing the passwords for that many schemas doing things that way?
February 14, 2011 - 7:23 am UTC
you are the dba, why do you care about the passwords.
unlock the account and password it any way you want (if you can unlock, you can set the password). Use it, lock it, go away from it.
Alexander, February 14, 2011 - 9:30 am UTC
Auditors want to know how the passwords for every kind of account is being managed. If it isn't being managed, you'll likely end up with an "A" finding.
But let's say that's not an issue, how is what you're saying any more secure?
You said, "you're a dba, why do you care about passwords" So by that same token, I could say, "why do I care about using schemas".
They way we are currently doing it allows us to strip the schemas of any privileges, even connect, and lock them. Only a select few of dbas are allowed access to the dba accounts.
So tell me, why is our data not secure this way?
February 14, 2011 - 9:49 am UTC
... "why do I care about using schemas". ...
because now you actually KNOW what privileges the schema needs for their stuff, what privileges the schemas have - instead of using sysdba to create things for them because you DON"T KNOW what privileges they may or may not need. That is why. That is why your AUDITORS would care as well.
These schemas already exist, I'm not sure what password management you are concerned about?
Also, you could always have these schemas grant you "connect through" and you could log into them using YOUR password.
Your DBA's can already do what they do - that wouldn't change.
However you would now for the first time have actual documentation of what privileges your application schema's can and cannot do - that is truly "good" and more secure. Now the auditors can ask (should ask) why does A have select any table - isn't that a bit over the top?
YOU WANT your schemas to have the least set of privileges they need to do their job. As you have it now, you don't really understand or KNOW what privileges you've indirectly granted them - do you.
predefined users and roles
A reader, February 06, 2013 - 11:32 am UTC
Hi Tom,
Do Oracle provide any view or table to identify the list of predefined users and roles that are created initially when the database is created?
Or can we use any query to find those predefined users and roles?
Thanks!
February 06, 2013 - 2:27 pm UTC
predefined roles and users
A reader, February 06, 2013 - 4:28 pm UTC
Thanks Tom.
I also looked at those links before I posted the question above.
So there are no views provided by Oracle to identify the predefined users or roles?
Thanks!
February 07, 2013 - 6:21 am UTC
no, just documentation
Oracle eBusiness Suite
Emad, May 06, 2014 - 1:37 pm UTC
Hello Tom,
We are currently using Oracle users as an application users.
However, recently, during an Oracle Database Security course, the trainer said that Oracle uses Application users (One Big user) in eBusiness Suite.
I have talked about if this is true, then how Oracle will manage the security, auditing... etc??
He said it is all managed from the application (eBusiness Suite).
Is that true?? and if so, how come Oracle uses application users while in all of the documentations it is always better to go for Oracle users
Thanks
Roles with Default Status in dba_role_privs
Mukund Nampally, January 02, 2015 - 11:55 am UTC
Hi Tom,
What does the default column in dba_role_privs convey?
There is no clear information available in Oracle Documentation.
eg - connecting as dba and running the below query
select * from dba_role_privs where grantee = 'ZZ_TEST_PRD'
grantee granted_role admin_option default
--------- ------------- ----------- -------
ZZ_TEST_PRD CENTNOTF_USER NO YES
ZZ_TEST_PRD DM_SELO_ROLE NO NO
only one of the role is set as default
and then I use -
connecting as ZZ_TEST_PRD
1. set role all
2. select * from session_roles
ROLE
-----
DM_SELO_ROLE
CENTNOTF_USER
If I use - set role all; the user is able to get all the roles even though that role is not a default role?
what does "set role all" signify?
what does "default_role" in "dba_role_privs" signify?
Thanks
query a Table
Rajeshwaran Jeyabal, October 27, 2016 - 2:13 pm UTC
Team - I am on 12c(12.1.0.2), why do i am able to describe a table, but not able to query that?
demo@ORA12C> set linesize 71
demo@ORA12C> set role none;
Role set.
demo@ORA12C> select * from session_roles;
no rows selected
demo@ORA12C> select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SQL TRANSLATION PROFILE
DEBUG ANY PROCEDURE
DEBUG CONNECT SESSION
CREATE VIEW
ALTER SESSION
5 rows selected.
demo@ORA12C> desc rajesh.big_table
Name Null? Type
----------------------------------- -------- -------------------------
OWNER NOT NULL VARCHAR2(128)
OBJECT_NAME NOT NULL VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(128)
OBJECT_ID NOT NULL NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(23)
CREATED NOT NULL DATE
LAST_DDL_TIME NOT NULL DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
NAMESPACE NOT NULL NUMBER
EDITION_NAME VARCHAR2(128)
SHARING VARCHAR2(13)
EDITIONABLE VARCHAR2(1)
ORACLE_MAINTAINED VARCHAR2(1)
ID NUMBER
demo@ORA12C> select count(*) from rajesh.big_table;
select count(*) from rajesh.big_table
*
ERROR at line 1:
ORA-01031: insufficient privileges
demo@ORA12C>
October 28, 2016 - 2:10 am UTC
DEBUG ANY PROCEDURE gives you that
SQL> create user demo identified by demo;
User created.
SQL> grant connect to demo;
Grant succeeded.
SQL> connect demo/demo
Connected.
SQL> desc scott.emp
ERROR:
ORA-04043: object scott.emp does not exist
SQL> select * from scott.emp;
select * from scott.emp
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> conn system/system
Connected.
SQL> grant DEBUG ANY PROCEDURE to demo;
Grant succeeded.
SQL> connect demo/demo
Connected.
SQL> desc scott.emp
Name Null? Type
----------------------------------------------------------------------- -------- ------------------------------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SQL> select * from scott.emp;
select * from scott.emp
*
ERROR at line 1:
ORA-01031: insufficient privileges
DBMS_PRIVILEGE_CAPTURE and DESCRIBE
Mikhail Velikikh, October 28, 2016 - 7:50 am UTC
Hello Team,
Could you please explain why we cannot capture privileges used to execute a DESCRIBE scott.emp command in Rajesh's example?
SQL> grant connect to tc identified by tc;
Grant succeeded.
SQL>
SQL> grant CREATE SQL TRANSLATION PROFILE to tc;
Grant succeeded.
SQL> grant DEBUG ANY PROCEDURE to tc;
Grant succeeded.
SQL> grant DEBUG CONNECT SESSION to tc;
Grant succeeded.
SQL> grant CREATE VIEW to tc;
Grant succeeded.
SQL> grant ALTER SESSION to tc;
Grant succeeded.
SQL>
SQL>
SQL> exec dbms_privilege_capture.create_capture ( -
> name => 'TC_CAPTURE', -
> type => dbms_privilege_capture.g_context, -
> description => 'Try to figure out what privileges are needed for the describe command', -
> condition => q'#sys_context('userenv', 'session_user')='TC'#')
PL/SQL procedure successfully completed.
SQL>
SQL> exec dbms_privilege_capture.enable_capture( 'TC_CAPTURE')
PL/SQL procedure successfully completed.
SQL>
SQL> conn tc/tc
Connected.
SQL>
SQL> set serverout off
SQL>
SQL> describe scott.emp
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SQL>
SQL> conn tc_dba/tc_dba
Connected.
SQL>
SQL> exec dbms_privilege_capture.disable_capture( 'TC_CAPTURE')
PL/SQL procedure successfully completed.
SQL> exec dbms_privilege_capture.generate_result( 'TC_CAPTURE')
PL/SQL procedure successfully completed.
SQL>
SQL> col used_role for a10
SQL> col sys_priv for a20
SQL> col obj_priv for a10
SQL> col user_priv for a10
SQL> col object_owner for a20
SQL> col object_name for a30
SQL> col object_type for a12
SQL>
SQL> select used_role,
2 sys_priv,
3 obj_priv,
4 user_priv,
5 object_owner,
6 object_name,
7 object_type
8 from dba_used_privs;
USED_ROLE SYS_PRIV OBJ_PRIV USER_PRIV OBJECT_OWNER OBJECT_NAME OBJECT_TYPE
---------- -------------------- ---------- ---------- -------------------- ------------------------------ ------------
PUBLIC EXECUTE SYS DBMS_APPLICATION_INFO PACKAGE
PUBLIC SELECT SYSTEM PRODUCT_PRIVS VIEW
PUBLIC EXECUTE SYS DBMS_OUTPUT PACKAGE
PUBLIC SELECT SYS DUAL TABLE
CONNECT CREATE SESSION
PUBLIC SELECT SYS DUAL TABLE
6 rows selected.
December 20, 2016 - 12:00 am UTC
I spoke to the product manager for DataVault - the view is that this *should* be captured, and a bug has been logged.