Compiled Messages: ------------------------------------------------------------ Message no. 7239 Posted by Phanindra Maddhi (pmaddhi) on Wednesday, April 5, 2006 9:54pm Subject: ROLES How many roles can be assign to a user? ------------------------------------------------------------ Message no. 7240[Branch from no. 7239] Posted by Krishnamurth Ashwini (kashwini) on Wednesday, April 5, 2006 10:32pm Subject: Re: ROLES Phanindra, I think a user can be assigned any number of roles and there is no restriction to the number a roles a user can have. Ash ------------------------------------------------------------ Message no. 7243[Branch from no. 7239] Posted by Harold Harris (hjharris2) on Thursday, April 6, 2006 12:11am Subject: Re: ROLES System privileges There are quite a few system priviliges: in Oracle 9.2, there are 157 of them, and 10g has 173. Object privileges can be assigned to the following types of objects: Tables select, insert, update, delete, alter, debug, flashback, on commit refresh, query rewrite, references, all Views select, insert, update, delete, under, references, flashback, debug Sequence alter, select Packeges, Procedures, Functions (Java classes, sources...) execute, debug Materialized Views delete, flashback, insert, select, update Directories read, write Libraries execute User defined types execute, debug, under Operators execute Indextypes execute I believe that these different privileges can be bundled into roles which can be assigned to the user. HH ------------------------------------------------------------ Message no. 7248[Branch from no. 7243] Posted by Kelli Lemon (kmlemon) on Thursday, April 6, 2006 12:53am Subject: Re: ROLES Harold, Where did you find this list of privileges? Kelli ------------------------------------------------------------ Message no. 7254[Branch from no. 7239] Posted by Alex Auffenorde (aaauffenorde) on Thursday, April 6, 2006 1:25am Subject: Re: ROLES I would agree, there is no limit to the number of roles a user may be assigned to. -Alex ------------------------------------------------------------ Message no. 7256[Branch from no. 7239] Posted by Imran Pathan (ipathan) on Thursday, April 6, 2006 8:44am Subject: Re: ROLES Yes i agree with all the above that there is no limit to the number of roles a user can be given. And Harold how did you manage to get such a big list!!!! Imran ------------------------------------------------------------ Message no. 7257[Branch from no. 7248] Posted by Harold Harris (hjharris2) on Thursday, April 6, 2006 11:51am Subject: Re: ROLES Here is the website I found the information on. http://www.adp-gmbh.ch/ora/misc/users_roles_privs.html HH ------------------------------------------------------------ Message no. 7258[Branch from no. 7239] Posted by Venkat Munagala (vrmunagala) on Thursday, April 6, 2006 12:53pm Subject: Re: ROLES Hi phanindra, I too feel that there is no restriction to number of roles that can be assigned to a user. ------------------------------------------------------------ Message no. 7259[Branch from no. 7258] Posted by Rohini Shrestha (rshrestha) on Thursday, April 6, 2006 3:01pm Subject: Re: ROLES I agree with you guys. There is no restrictions Rohini ------------------------------------------------------------ Message no. 7260 Posted by Rhonda Nichols (renichols2) on Thursday, April 6, 2006 3:14pm Subject: Restrictions I found this and thought it might be useful..... Restrictions on Granting System Privileges and Roles A privilege or role cannot appear more than once in the list of privileges and roles to be granted. You cannot grant a role to itself. You cannot grant a role IDENTIFIED GLOBALLY to anything. You cannot grant a role IDENTIFIED EXTERNALLY to a global user or global role. You cannot grant roles circularly. For example, if you grant the role banker to the role teller, then you cannot subsequently grant teller to banker. http://download- west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_912a.htm#2077596 ------------------------------------------------------------ Message no. 7262[Branch from no. 7260] Posted by Phanindra Maddhi (pmaddhi) on Thursday, April 6, 2006 3:26pm Subject: Re: Restrictions yaa it is useful information about roles Thank u ------------------------------------------------------------ Message no. 7263[Branch from no. 7260] Posted by Waseem Manzoor (wmanzoor) on Thursday, April 6, 2006 3:57pm Subject: Re: Restrictions Gr8 information Rhonda Thanks Waseem Manzoor ------------------------------------------------------------ Message no. 7264[Branch from no. 7259] Posted by Waseem Manzoor (wmanzoor) on Thursday, April 6, 2006 3:59pm Subject: Re: ROLES I personaly think you can assign as many roles as you want. Depends on the version which you are using and how many roles available in that version. Waseem manzoor ------------------------------------------------------------ Message no. 7276[Branch from no. 7239] Posted by Sagun Piya (srpiya2) on Thursday, April 6, 2006 11:45pm Subject: Re: ROLES As far as I know, we can assign as many roles as we can to a user ------------------------------------------------------------ Message no. 7277[Branch from no. 7260] Posted by Sagun Piya (srpiya2) on Thursday, April 6, 2006 11:45pm Subject: Re: Restrictions Useful information. Thankyou Rhonda Sagun ------------------------------------------------------------ Message no. 7279[Branch from no. 7276] Posted by Abhash Bhandary (abhandary) on Friday, April 7, 2006 9:33am Subject: Re: ROLES I think everyone of you are correct on there being no limit on the number of roles Abhash ------------------------------------------------------------ Message no. 7280[Branch from no. 7277] Posted by Abhash Bhandary (abhandary) on Friday, April 7, 2006 9:34am Subject: Re: Restrictions Thankyou for sharing Rhonda Abhash ------------------------------------------------------------ Message no. 7285[Branch from no. 7260] Posted by Harold Harris (hjharris2) on Friday, April 7, 2006 10:32am Subject: Re: Restrictions Good information Rhonda. HH ------------------------------------------------------------ Message no. 7286[Branch from no. 7285] Posted by Rohini Shrestha (rshrestha) on Friday, April 7, 2006 1:06pm Subject: Re: Restrictions Thankx for information Rohini ------------------------------------------------------------ Message no. 7289[Branch from no. 7260] Posted by Venkat Munagala (vrmunagala) on Friday, April 7, 2006 2:12pm Subject: Re: Restrictions Thank you for the information Rhonda ------------------------------------------------------------ Message no. 7290[Branch from no. 7260] Posted by Antoinette Lockett (arlockett) on Friday, April 7, 2006 2:25pm Subject: Re: Restrictions Rhonda thank you very much for the information. ------------------------------------------------------------ Message no. 7294[Branch from no. 7239] Posted by Gnaneshwar Bukka (gbukka) on Friday, April 7, 2006 2:36pm Subject: Re: ROLES Too late..but....I agree with each and every one of you.... Gnaneshwar Bukka. ------------------------------------------------------------ Message no. 7300[Branch from no. 7243] Posted by Bikash Adhikari (badhikari) on Friday, April 7, 2006 5:21pm Subject: Re: ROLES A very informative answer Harold. And i do agree with others that there can be many roles. BIkash ------------------------------------------------------------ Message no. 7301 Posted by Bikash Adhikari (badhikari) on Friday, April 7, 2006 6:14pm Subject: Query avaliability

Is there any specific query that can provide the information as to which user have the rights, or privileges, to access a given object ?

------------------------------------------------------------ Message no. 7302[Branch from no. 7301] Posted by Rohini Shrestha (rshrestha) on Friday, April 7, 2006 6:24pm Subject: Re: Query avaliability Bikash, Information on user object and system access privileges is contained in the rdbms data dictionary tables. To find the right or priviledge of a given object, we can use DBA_TAB_PRIVS which is one of the dictionary table. Since the DBA_TAB_PRIVS dictionary table (view) contains all grants on all objects in the database, this table is suitable for being queried for any Oracle object like tables, views, stored code, etc. Hope it helps Rohini ------------------------------------------------------------ Message no. 7303[Branch from no. 7301] Posted by Krishnamurth Ashwini (kashwini) on Friday, April 7, 2006 6:35pm Subject: Re: Query avaliability Hi Bikash, More answer for your "exact question", which has the answer very similar to the one written by Rohini can be found in the following website... http://www.jlcomp.demon.co.uk/faq/privileges.html Ash ------------------------------------------------------------ Message no. 7304[Branch from no. 7303] Posted by Rohini Shrestha (rshrestha) on Friday, April 7, 2006 7:33pm Subject: Re: Query avaliability Further, below are some codes that will help you to identify the privileges of user as well as roles. * to find out what privileges you have granted to what users select grantee, table_name, privilege from dba_tab_privs where grantor like 'YOURUSERNAME%'; * to find what system privileges have been granted to roles Select * from role_sys_privs where role like ‘ROLENAME%’; * to find what table privileges have been granted to roles Select * from role_tab_privs where role like ‘ROLENAME%’; * to find what system privileges have been granted to users and roles Select * from dba_sys_privs where grantee like ‘USERORROLENAME; For further information, visit http://72.14.203.104/search?q=cache:_BHW1YalfaUJ:business.baylor.edu/gina_green/teaching/oracle/instructions/querying_dd_tbls.doc+oracle:+data+dictionary+DBA_TAB_PRIVS+&hl=en&gl=us&ct=clnk&cd=6 Thankx Ash, for providing link for previous post. Rohini ------------------------------------------------------------ Message no. 7306[Branch from no. 7239] Posted by Swarna Bangaru (sbangaru) on Friday, April 7, 2006 8:53pm Subject: Re: ROLES There is no limit in assigning a number of roles to the user. ------------------------------------------------------------ Message no. 7307[Branch from no. 7257] Posted by Krupa Kandavalli (kkandavalli) on Friday, April 7, 2006 8:54pm Subject: Re: ROLES Good info, thankyou ------------------------------------------------------------ Message no. 7308[Branch from no. 7260] Posted by Krupa Kandavalli (kkandavalli) on Friday, April 7, 2006 8:55pm Subject: Re: Restrictions Thank you for the info. ------------------------------------------------------------ Message no. 7318[Branch from no. 7307] Posted by Gwendolyn Mobley (gdmobley) on Saturday, April 8, 2006 2:10am Subject: Re: ROLES great info thanks ------------------------------------------------------------ Message no. 7319[Branch from no. 7260] Posted by Gwendolyn Mobley (gdmobley) on Saturday, April 8, 2006 2:12am Subject: Re: Restrictions informative link, thanks for the info Gwen ------------------------------------------------------------ Message no. 7320[Branch from no. 7301] Posted by Waseem Manzoor (wmanzoor) on Saturday, April 8, 2006 4:39am Subject: Re: Query avaliability thnx Rohini and Ash, it helped a lot ------------------------------------------------------------ Message no. 7328[Branch from no. 7240] Posted by Muhammad Latif (mlatif) on Saturday, April 8, 2006 2:09pm Subject: Re: ROLES Hey Phanindra: There is no restriction on the number of roles that can be assigned to a user. I agree with HH. Very useful information indeed. Thanks. Arsal ------------------------------------------------------------ Message no. 7329[Branch from no. 7260] Posted by Muhammad Latif (mlatif) on Saturday, April 8, 2006 2:13pm Subject: Re: Restrictions Hey Rhonda: Nice to see you are doing extra reading! Just kidding. Thanks for sharing. Arsal ------------------------------------------------------------ Message no. 7330[Branch from no. 7301] Posted by Kelsey Pooley (kjpooley) on Saturday, April 8, 2006 2:15pm Subject: Re: Query avaliability It seems like it would be difficult to keep track of the privileges each user has when using sql plus. In the book (starting on page 890) the examples are from the Enterprise Manager Console, which is a graphical user interface, and is point and click. Surely large companies with many users would utilize this tool rather than type commands at the sql> prompt. I feel like I am looking for a needle in a haystack sometimes when using sql! Kelsey ------------------------------------------------------------ Message no. 7331[Branch from no. 7302] Posted by Muhammad Latif (mlatif) on Saturday, April 8, 2006 2:20pm Subject: Re: Query avaliability Hey Bikash: I agree with Rohini. You can query the "dba_tab_privs" table to get your answer. Thanks. Arsal ------------------------------------------------------------ Message no. 7333[Branch from no. 7300] Posted by Harold Harris (hjharris2) on Saturday, April 8, 2006 2:28pm Subject: Re: ROLES Thank you BIkash. HH ------------------------------------------------------------ Message no. 7340[Branch from no. 7257] Posted by Gwendolyn Mobley (gdmobley) on Saturday, April 8, 2006 8:31pm Subject: Re: ROLES HH, this info is much better than Oracle site. thanks for the info. gwen ------------------------------------------------------------ Message no. 7341[Branch from no. 7302] Posted by Gwendolyn Mobley (gdmobley) on Saturday, April 8, 2006 8:34pm Subject: Re: Query avaliability Rohini, good info thanks Gwen ------------------------------------------------------------