roles (quota/select privs)


I have a requirement to allow a user(s) to create tables/indexes/...
but only in a specific tablespace.

I know this works:

alter user xxx quota 0 on users;
....
....
....
for each tablespace.


Instead of altering several users is it possible to set a quota of 0 for each
tablespace in a role and than assign the role to a user

Secondly, I have a script that generates DML, which will grant "select"
on every table to a role:



SELECT 'GRANT SELECT ON ' || u.owner || '.' || u.object_name ||' TO test_role;'
FROM dba_objects u
WHERE u.object_type IN ('TABLE','VIEW','SEQUENCE')
and owner = 'XXX';

-- Sample output ---
GRANT SELECT ON XXX.ACCOMPLICE_SERIAL TO test_role;
GRANT SELECT ON XXX.ACCOUNT_SERIAL TO test_role;
GRANT SELECT ON XXX.ADDRESS_SERIAL TO test_role;

Though this will work if a new table is added to the schema I will have
a permission problem unless I re-run the script.

Is there a way to do something like this:

grant select any table from yyy to test_role;

I am looking to make this a dynamic method where I don't have to worry
about new tables being added.

Posted On: Thursday 1st of November 2012 01:18:38 AM Total Views:  111
View Complete with Replies

RELATED TOPICS OF Oracle PROGRAMMING LANGUAGE




Privileges on Oracle roles - Limit?

I am working on a system that will provide some basic roles to the user. As the data in the application grows, the number of (object) privileges for each role in expected to grow to about 2000 or more. Does oracle have a limitation on the number of privileges that can be granted to a role Could there be a hit on the performance if each role has these high number of privileges granted to it.
VIEWS ON THIS POST

155

Posted on:

Thursday 1st November 2012
View Replies!

privileges do not work through roles

Hi I have created role role_A and granted select any table system privileges to role_A. I have also created user A. I grant the role_A to user A. However, when I login to user A I could not select any tables. What's wrong here Is there a scrip I need to run to enable this feature. It seems like all of the privileges I grant through a role do not work. I have to specifically grant privileges to user. Please help!
VIEWS ON THIS POST

126

Posted on:

Thursday 1st November 2012
View Replies!

security/roles

Hi all, I have a question, I don't get it. Any way, below is the what I have: 1. I have create an external user named SCOTT with appdba role grant to this user 2. appdba role had "alter any index "privilege" and to other privilege 3. in SCOTT schema, I create a procedure, basically, it just go outthere and rebuild the index for other schema named DON 4. the procedure is compiled on SCOTT 5. when I run the procedure, I got the error saying "the index in DON schema does not exist" what am I missing here
VIEWS ON THIS POST

139

Posted on:

Thursday 1st November 2012
View Replies!

roles to users

We are using Oracle 8.1.6 and are interested in knowing all the roles a particular user id has been grated. What is the SQL we can use to get it.
VIEWS ON THIS POST

170

Posted on:

Friday 2nd November 2012
View Replies!

Which table list user roles?

I need to determing which users are to be killed from the database by determining which users use a certain role. I don't know which table to check to determine which users have this role granted. Anybody know what table that is. Iam connected as sys so if that will help, now u know.
VIEWS ON THIS POST

253

Posted on:

Friday 2nd November 2012
View Replies!

User creation and roles

.. I have to create close to 25 users on 3 instances and I have a list of user names. I am trying to run the below queries hoping to enter the username only once when i run the script, but this does not work. How do I modify the sql to do that (Interactive on the prompt).. create user &1 identified by &1 default tablespace aapdata temporary tablespace aapdata quota unlimited on aapdata; alter user &1 quota 0 on system; grant connect to &1; grant cgaap_user to &1; Secondly, I can know the available roles from dba_roles, and I know the users and their roles through user_role_privs.granted_role, but how do i see the privileges for the same (which view).. I can see the same in TOAD, but not SQL Plus..
VIEWS ON THIS POST

99

Posted on:

Friday 2nd November 2012
View Replies!

Session_roles

ORACLE 8.1.7 ON LIUNX I want to give the above roles to the system/manager user. SELECT_CATALOG_ROLE HS_ADMIN_ROLE EXECUTE_CATALOG_ROLE DELETE_CATALOG_ROLE JAVA_ADMIN JAVA_DEPLOY AQ_ADMINISTRATOR_ROLE how to give set up these.
VIEWS ON THIS POST

131

Posted on:

Sunday 4th November 2012
View Replies!

Query to display roles

Dear all, I need to display all the database roles(i am really interested in user defined roles) with corresponding privileges for that roles.. I need help here.
VIEWS ON THIS POST

149

Posted on:

Sunday 4th November 2012
View Replies!

user roles and privileges

Hi DBAs, Does anyone have a scipt to find out the various oracle roles and privileges for users in the database
VIEWS ON THIS POST

245

Posted on:

Wednesday 7th November 2012
View Replies!

roles

How can I find out what privileges are granted to a given role
VIEWS ON THIS POST

154

Posted on:

Wednesday 7th November 2012
View Replies!

what are the roles & resonsibilities of dba in dataware housing

what database activities normal DBA should monitor and control on dataware housing is it(dataware housing) needs any extra database knowledge
VIEWS ON THIS POST

146

Posted on:

Wednesday 7th November 2012
View Replies!

user roles question

I have 3 tables in Oracle 8i. Table A contains basic product information, Table B contains advanced product information and Table C contains product financial information. There are three types of users who will access the tables. User X can query and view all fields in all tables, user Y can query and view only table B fields whereas user Y can view only a few fields in table C. How can i create these users and grant them SELECT privileges accordingly Do I need to work with the PRODUCT _USER_PROFILE table provided by Oracle or can I make my own user role tables and if how Also, this solution has to be implemented on a web interface using Java/ JSP, how can I use Oracle's Single Sign on product
VIEWS ON THIS POST

141

Posted on:

Wednesday 7th November 2012
View Replies!

Scripts for multiple selections,roles,modules in a company!

Hi all! I want a script for the following: Modules:9(Nine).Companies:8(Eight). Oracle v8i.OS:NT. Guidelines: 1.Any user who logs on from the front end screen should know in which module he is working with what roles assigned and in which company.The frontend screen for user creation must accept username,pwd,company code. 2.A user may select modules,company name with company code,Role names to work on. 3.A user may have access to either one or multiple modules in a company he selects to work upon. 4.A user may have either one or multiple roles in one or multiple modules in a company. 5.Each module consists of 3(three) users i.e., STAFF,OFFICER,MANAGER. 6.Staff would have only:select,Insert privileges. 7.Officer would have only:select,insert,update privileges. 8.Manager would have only:select,insert,update,delete privileges. Please treat this as imp. Take care. Amitst.
VIEWS ON THIS POST

275

Posted on:

Wednesday 7th November 2012
View Replies!

How to implement membership and roles in asp.net with oracle 10g database?Any help please!!

, I am building a website using asp.net with an oracle 10g database,but I don't know how to implement membership and roles with this database.Is there anyway to use the asp.net membership and roles providers which are already built-in with oracle 10g database Please,any help would be appreciated. Thank you. , mamun22s ,
VIEWS ON THIS POST

238

Posted on:

Wednesday 7th November 2012
View Replies!

asp.net mysql odbc and membership roles

all, I am building this asp.net website with mysql and login controls. I used mysql connector/net and found that the hosting company doesn't support it and they recommended me to use odbc. Can someone point me to a good article or explain me here how to setup the login controls,membership, user roles with odbc connection Appreciate any help..
VIEWS ON THIS POST

392

Posted on:

Wednesday 7th November 2012
View Replies!

Is there a OracleRoleprovider to set user access level according to the roles defined to the user

iwant to develop a menu wherein the user logged can only see few menus if his role access is defined as user and if his role access is defiend as admin he wud see the whole menu. i want to achieve this through the functionality which is provided by microsoft called as asp.net configuration wherein we can set the roles . i have done this in SQl but not able to do it using oracle Do u hva e any idea about this.
VIEWS ON THIS POST

221

Posted on:

Wednesday 7th November 2012
View Replies!

MySql membership and roles

I thought I'd give MySql a go, but I'm having no luck at all getting membership and roles working. I'm using MySql 5.1 and Connector/.Net 5.1.3 and Visual Studio 2005. I've created a database using MySql Admin and added the following to web.config: I've also added the two .dlls (MySql.Data.dll and MySql.Web.dll) into the site's bin folder per someone's suggestion on another forum. When trying to test the connection using the asp.net web site administration tool (after selecting MySqlMembershipProvider and MySqlRoleProvider) I get an error message stating that it couldn't establish a connection to the database.Using Visual Studio 2005 server explorer, however, I am able to connect to the database using the connection info listed above and interact with it...creating tables, adding data, etc.; When I check the event viewer, I see the following: An exception occurred communicating with the data source. Action: GetUsers Exception: MySql.Data.MySqlClient.MySqlException: Access denied for user ''@'localhost' (using password: NO) at MySql.Data.MySqlClient.MySqlStream.OpenPacket() at MySql.Data.MySqlClient.NativeDriver.Authenticate411() at MySql.Data.MySqlClient.NativeDriver.Authenticate() at MySql.Data.MySqlClient.NativeDriver.Open() at MySql.Data.MySqlClient.MySqlPool.GetPooledConnection() at MySql.Data.MySqlClient.MySqlPool.GetConnection() at MySql.Data.MySqlClient.MySqlConnection.Open() at MySql.Web.Security.MySQLMembershipProvider.GetUsers(Int32 pageIndex, Int32 pageSize, Int32& totalRecords) Can anyone point me in the right direction
VIEWS ON THIS POST

177

Posted on:

Wednesday 7th November 2012
View Replies!

MySql with my_aspnet_roles

Hi Guys, I'm using MySql and have it hooked up to an asp.net site using the .Net Connector. I was wondering if I could use the auto- generated roles and membership database provider (with tables such as my_aspnet_roles and my_aspnet_membership) or would I have to create roles and membership tables of my own (like the one given here) Many
VIEWS ON THIS POST

120

Posted on:

Wednesday 7th November 2012
View Replies!

MySQL default DB 'provider' for ASP.NET 2.0 roles, profiles and membership

My Hoster doesn't have MS SQL but does have MySQL. I would like to use the new ASP.NET 2.0 functionality of roles, profiles, membership which only function provided the proper DB is setup beforehand. Has anyone setup an empty MySQL database that I could copy or load into MySQL on my Hoster's server
VIEWS ON THIS POST

219

Posted on:

Wednesday 7th November 2012
View Replies!

roles and privs

, What view do I query to in order to know what roles and privileges have been granted to users; I know the view v$pw_passwordfile only shows users who have been granted the sysdba and sysoper role, but what about the others
VIEWS ON THIS POST

186

Posted on:

Thursday 15th November 2012
View Replies!