Home » SQL & PL/SQL » SQL & PL/SQL » Create User (merged 4)
Create User (merged 4) [message #377135] Sat, 20 December 2008 11:29 Go to next message
mkhalil
Messages: 108
Registered: July 2006
Location: NWFP Peshawar Pakistan
Senior Member
I have created a user by using following commond
connect system/manager
grant connect,resource to hms identified by micro
grant dba to hms


Then create another user say a by using the following
create user a identified by a
grant connect,resource to a


But by connecting a/a i am unable to access the tables already created in the dba i.e. hms.

Could anybody help me to guide in my above problem. I am new to Oracle. I have developed an application but i am unable deploy the same on client/server environment.

Please help me. Thanks in advance.


[MC: Duplicates deleted]

[Updated on: Sat, 20 December 2008 11:43] by Moderator

Report message to a moderator

Re: Create User (merged 3) [message #377139 is a reply to message #377135] Sat, 20 December 2008 11:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to grant privilege on object of hms to a.

If you don't know Oracle you shoud first read:
Database Concepts
SQL Reference

And don't use predefined roles (connect, resource).

And don't multipost your question.

Regards
Michel

[Updated on: Sat, 20 December 2008 11:44]

Report message to a moderator

Re: Create User [message #377162 is a reply to message #377135] Sun, 21 December 2008 00:09 Go to previous messageGo to next message
mkhalil
Messages: 108
Registered: July 2006
Location: NWFP Peshawar Pakistan
Senior Member
Thank you Michel for your so quick response and guidence. But I have very limited time to deply the application. Please guide me what i have to do. After creating a user how can i grant privileges to that user so that he may connect with his own user id and password but can access all tables of dba through different forms.
Re: Create User [message #377163 is a reply to message #377162] Sun, 21 December 2008 00:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
After creating a user how can i grant privileges to that user so that he may connect with his own user id and password but can access all tables of dba through different forms.

Just click on the first link I gave.

Regards
Michel
Re: Create User (merged 4) [message #377183 is a reply to message #377135] Sun, 21 December 2008 07:25 Go to previous messageGo to next message
mkhalil
Messages: 108
Registered: July 2006
Location: NWFP Peshawar Pakistan
Senior Member
I have almost solved my problem. By seach this forum I found a post by barbara Boehmer. Now I have granted the following privs.
create user a identified by a DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp PROFILE DEFAULT ACCOUNT UNLOCK;
GRANT CREATE SESSION TO a;
GRANT CREATE TABLE TO a;
GRANT CREATE VIEW TO a;
GRANT CREATE synonym TO a;
ALTER USER a QUOTA UNLIMITED ON users;
GRANT DELETE, INSERT, SELECT, UPDATE ON AMENITY TO a WITH GRANT OPTION;

Now the user a can access to the tables of dba i.e. hms but query in a tabular form cannot execute and show the message "ORACLE errer:unable to perform query'.
Would you please anybody help me in this regard.
Re: Create User (merged 4) [message #377192 is a reply to message #377183] Sun, 21 December 2008 09:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is NOT an Oracle message.
Try to get the Oracle message (if any).
Also try to read what we point you. I think clicking on GRANT in my first post will give you the answer faster that picking statement you don't know their usage till one fulfils your need.

Regards
Michel
Re: Create User (merged 4) [message #377195 is a reply to message #377192] Sun, 21 December 2008 10:23 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Actually, I'd say that this is an Oracle error message - it just comes from Forms.

In order to find out error root, go to Forms runtime window's Help menu and click onto the "Display Error"; it will show you (and us) ORA-XXXXX error code and - possibly - statement which had failed.
Re: Create User (merged 4) [message #377359 is a reply to message #377195] Mon, 22 December 2008 08:53 Go to previous messageGo to next message
mkhalil
Messages: 108
Registered: July 2006
Location: NWFP Peshawar Pakistan
Senior Member
absolutely it is an oracle error. Because the message is "Table or View does not exist".
Now i have created a user from dba say user a by applying the following commands.
grant connect,resource to a identified by a

grant select any table,insert any table,update any table,
delete any table to a


Now the problem is that if i add the user id of dba before the name of table i.e. hms.amenity then its ok otherwise the form does run and pointer go to the error where a table name has been used in the select commond. Whereas, i do not want write the user name of dba before the name of each table name where these tables has been used in the select commond.

Is there any way to grant something to user a that when we run the form from user a it does not require the user name of dba before the name of table.

Help and guidance of any would be highly appreciated.
Re: Create User (merged 4) [message #377371 is a reply to message #377359] Mon, 22 December 2008 10:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
absolutely it is an oracle error. Because the message is "Table or View does not exist".

So why did you say it?

Once again you MUST read the concepts and sql manuals BEFORE trying to do anything otherwise you will be there with the same (kind of) error in months.

Regards
Michel
Re: Create User (merged 4) [message #377407 is a reply to message #377359] Mon, 22 December 2008 16:51 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
When you try to select from a table, by default it looks for the table in the current schema (user). That is why, if the table is in another schema (belongs to another user), then you have to preface it with that schema (user) name. One way around this is to create a synonym, but why? What is wrong with putting the schema (user) name in front of the table name?
Re: Create User (merged 4) [message #377413 is a reply to message #377407] Mon, 22 December 2008 22:28 Go to previous messageGo to next message
mkhalil
Messages: 108
Registered: July 2006
Location: NWFP Peshawar Pakistan
Senior Member
Thanks Barbara for so kind help. You are right but i think it would be some hot coding because when next time a dba create his userid with a different userid from that which already prefix with each table name then how he can change each form which will be provided to the client in fmx format. If Synonm is the solution. Would you please guide me in detail that how and where i should create synonm and how can i use it.

regards

M.Khalil
Re: Create User (merged 4) [message #377421 is a reply to message #377413] Mon, 22 December 2008 23:42 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
Ideally there should be a schema for the application and that should not change. The DBA login should be separate. The syntax for synonyms is available in the online documentation, but it will not solve your problem, as you will then have to change the schema names in the synonym definitions.

Re: Create User (merged 4) [message #377427 is a reply to message #377421] Tue, 23 December 2008 00:17 Go to previous messageGo to next message
mkhalil
Messages: 108
Registered: July 2006
Location: NWFP Peshawar Pakistan
Senior Member
So what would be the solution of my problem. Would you please help and guide me step by step. I shall appreciate you cooperation in this regard.
Re: Create User (merged 4) [message #377444 is a reply to message #377427] Tue, 23 December 2008 01:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just a remark: Your topic started 3 days ago, you had time to read the books I mentioned, if you did you'd know how to do it by yourself, now you are still complaining for detailed steps, and you will be in several months if you don't change your mind.

Regards
Michel
Re: Create User (merged 4) [message #377466 is a reply to message #377444] Tue, 23 December 2008 03:17 Go to previous messageGo to next message
mkhalil
Messages: 108
Registered: July 2006
Location: NWFP Peshawar Pakistan
Senior Member
Thanks Michel Cadot. I agree with your view point. I also know that this is the best and forever solution. I am also studying the links from the date provided by you. I appreciate your effort done for me. But many topic are still unclear to me. I want to clarify them. Would you not like to help others.
Re: Create User (merged 4) [message #377595 is a reply to message #377466] Tue, 23 December 2008 13:33 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
Looking at this realistically and seriously and considering that your profile says:

Occupation: Service Man
Biography: I am working as Deputy Manager in State Life Insurance Corporation of Pakistan. My interest is only to develop databases using Oracle.

It is very dangerous for you to be trying to set up an application for such a business without having proper training and experience so that you know what you are doing. If you do not know how to set up proper security, you could be putting your company and your customers at risk. Most people who would be setting up such a database would have taken Oracle classes and other computer classes and done a lot of studying and practicing. A company like that should be able to afford to hire a DBA, developer, consultant, or whatever they need or send existing employees to Oracle classes. This site is not intended to do your work for you. It is intended for providing help when you are stuck on a particular item.

You need to read about roles and privileges. Your misuse of terminology indicates you have no understanding of these things. In your database, you can create various schemas/users, such as hms and a. There are also some that must already exist in your database, such as system. There are various privileges that can be granted to such users, such as select, insert, update, and delete, create table, create procedure, and so on. You can also create roles and grant privileges to those roles, so that when you want to grant a group of privileges to a user, all you have to do is grant the role that has all of the necessary privileges to the user. Some of these roles already exist, such as dba, which is very powerful and should not be granted to just anybody, and resource.

When you want to create an application that many users can access, then typically you create one schema/user such as hms that contains all of the tables, procedures, forms, and whatever that application needs. You create a role, such as hms_users for common user access to the application in the hms schema, figure out what privileges most users will need to run your application, and grant those privileges to that role. Then when you create a new user, such as a, who needs access to run the application, then you just grant the hms_users role to a. This is a very simplified explanation and there are other factors. Typically you want to restrict users to the application only and not direct access to the underlying pieces. So, while hms might have access to create tables and procedures and such and could select from its own tables, the hms_users role would only have privileges to run certain procedures. The procedures would run with the privileges of hms, so they could access the hms tables and such, but users who only have the hms_users privilege could not connect through SQL*Plus or some such thing and make modifications. Users would only be able to access the application.

So, in summation, step by step:

1. Create your hms schema/user.
2. Grant the minimum privileges individually to hms that will be needed to create the application's tables, procedures, forms, or whatever. In general, you want to avoid granting powerful roles such as dba and resource and only grant what is needed.
3. Create the hms_users role.
4. Create your users, such as a.
5. Grant the hms_users role to user a and any other users who will need to run the application.
6. Create everything that your application needs in your hms schema, all of your tables, procedures, forms, or whatever.
7. Grant whatever privileges are needed to run the application to the hms_users role.

Here is a very simple example:

SCOTT@orcl_11g> -- 1.  create schema/user hms:
SCOTT@orcl_11g> CREATE USER hms IDENTIFIED BY hms
  2  /

User created.

SCOTT@orcl_11g> -- 2.  grant minimal required privileges individually to hms:
SCOTT@orcl_11g> GRANT CREATE SESSION TO hms
  2  /

Grant succeeded.

SCOTT@orcl_11g> GRANT CREATE TABLE TO hms
  2  /

Grant succeeded.

SCOTT@orcl_11g> GRANT CREATE PROCEDURE TO hms
  2  /

Grant succeeded.

SCOTT@orcl_11g> ALTER USER hms QUOTA 100m ON users
  2  /

User altered.

SCOTT@orcl_11g> -- 3.  create hms_users role with some privileges:
SCOTT@orcl_11g> CREATE ROLE hms_users
  2  /

Role created.

SCOTT@orcl_11g> GRANT CREATE SESSION TO hms_users
  2  /

Grant succeeded.

SCOTT@orcl_11g> -- 4.  create users:
SCOTT@orcl_11g> CREATE USER a IDENTIFIED BY a
  2  /

User created.

SCOTT@orcl_11g> -- 5.  grant hms_users role to users:
SCOTT@orcl_11g> GRANT hms_users TO a
  2  /

Grant succeeded.

SCOTT@orcl_11g> -- 6.  create application in hms schema:
SCOTT@orcl_11g> CONNECT hms/hms
Connected.
HMS@orcl_11g> 
HMS@orcl_11g> CREATE TABLE hms_tab AS SELECT * FROM DUAL
  2  /

Table created.

HMS@orcl_11g> CREATE OR REPLACE PROCEDURE hms_proc
  2  AS
  3  BEGIN
  4    FOR r IN (SELECT * FROM hms_tab) LOOP
  5  	 DBMS_OUTPUT.PUT_LINE (r.dummy);
  6    END LOOP;
  7  END hms_proc;
  8  /

Procedure created.

HMS@orcl_11g> SHOW ERRORS
No errors.
HMS@orcl_11g> -- 7.  grant privileges to hms_users role:
HMS@orcl_11g> GRANT EXECUTE ON hms_proc TO hms_users
  2  /

Grant succeeded.

HMS@orcl_11g> -- test that user a can execute the hms_proc:
HMS@orcl_11g> CONNECT a/a
Connected.
A@orcl_11g> 
A@orcl_11g> EXECUTE hms.hms_proc
X

PL/SQL procedure successfully completed.

A@orcl_11g>







Re: Create User (merged 4) [message #377704 is a reply to message #377595] Wed, 24 December 2008 07:23 Go to previous messageGo to next message
mkhalil
Messages: 108
Registered: July 2006
Location: NWFP Peshawar Pakistan
Senior Member
Thank you Barbara Boehmer very much and i heartily appreciate your amylases, so kind of efforts for me and advise. You are totally right that i am a service man and working in State Life Insurance as Deputy Manager not as dba. I am an BMA with specialization in Marketing. I have been appointed as Deputy Manager by the State Life Insurance Corporation of Pakistan on the basis of my MBA Degree. At the beginning of my service I have worked in Marketing Department. Now I am working in Policy Holder Service Department but side by side I am also looking after the IT Section of my company because I have done one year Diploma in computer programming and some certificate level courses of Oracle Development. So far, I have developed almost 25 applications for single user of different nature. Therefore, I have no experience of client – server environment.

It is also a fact that in a poor country, other than a corporate sector businessmen can not afford a separate professional DBA for its application rather their policy is take various nature of jobs from one employee and in this way such employee learn from other so kind professionals like you. I am also one of them.

Now I will strictly follow your instructions/guidance and very thankful to you that your step by step guidance has cleared so many concepts of creating a new user considering how much restrictions to avoid the loss of data of an organization which is more important than application.

I am also very thankful to Mr. Michel Cadot who have provided me some written material which I am studying and no doubt will help me too much.

But my last question is still stands on its place that I does not want to place the name of user which owned all tables say hms before the name of any table from which the other user, say a, retrieve the data. In this way I have alter the coding in each form where I have used the select statement i.e. I do not want like EXECUTE hms.hms_proc but like EXECUTE hms_proc from user a.
Re: Create User (merged 4) [message #377708 is a reply to message #377704] Wed, 24 December 2008 07:47 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
mkhalil wrote on Wed, 24 December 2008 08:23

But my last question is still stands on its place that I does not want to place the name of user which owned all tables say hms before the name of any table from which the other user, say a, retrieve the data. In this way I have alter the coding in each form where I have used the select statement i.e. I do not want like EXECUTE hms.hms_proc but like EXECUTE hms_proc from user a.



Well, if you want to continue using Oracle then you are going to have to use it the way was meant to be used.
And what do you think would happen when multiple schemas have the same object?
Re: Create User (merged 4) [message #377737 is a reply to message #377704] Wed, 24 December 2008 13:16 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
As I mentioned in a previous post, you can use a synonym, as in bold in the revised demo below. But this is a questionable practice. If you decide to move the application from hms to a different schema, you would then have to drop the synonym and recreate it using the new schema name, instead of changing the hms in front of hms.hms_proc. I think it is easier to change schema names than drop and recreate synonyms.


SCOTT@orcl_11g> -- 1. create schema/user hms:
SCOTT@orcl_11g> CREATE USER hms IDENTIFIED BY hms
2 /

User created.

SCOTT@orcl_11g> -- 2. grant minimal required privileges individually to hms:
SCOTT@orcl_11g> GRANT CREATE SESSION TO hms
2 /

Grant succeeded.

SCOTT@orcl_11g> GRANT CREATE TABLE TO hms
2 /

Grant succeeded.

SCOTT@orcl_11g> GRANT CREATE PROCEDURE TO hms
2 /

Grant succeeded.

SCOTT@orcl_11g> GRANT CREATE PUBLIC SYNONYM TO hms
2 /

Grant succeeded.

SCOTT@orcl_11g> ALTER USER hms QUOTA 100m ON users
2 /

User altered.

SCOTT@orcl_11g> -- 3. create hms_users role with some privileges:
SCOTT@orcl_11g> CREATE ROLE hms_users
2 /

Role created.

SCOTT@orcl_11g> GRANT CREATE SESSION TO hms_users
2 /

Grant succeeded.

SCOTT@orcl_11g> -- 4. create users:
SCOTT@orcl_11g> CREATE USER a IDENTIFIED BY a
2 /

User created.

SCOTT@orcl_11g> -- 5. grant hms_users role to users:
SCOTT@orcl_11g> GRANT hms_users TO a
2 /

Grant succeeded.

SCOTT@orcl_11g> -- 6. create application in hms schema:
SCOTT@orcl_11g> CONNECT hms/hms
Connected.
HMS@orcl_11g>
HMS@orcl_11g> CREATE TABLE hms_tab AS SELECT * FROM DUAL
2 /

Table created.

HMS@orcl_11g> CREATE OR REPLACE PROCEDURE hms_proc
2 AS
3 BEGIN
4 FOR r IN (SELECT * FROM hms_tab) LOOP
5 DBMS_OUTPUT.PUT_LINE (r.dummy);
6 END LOOP;
7 END hms_proc;
8 /

Procedure created.

HMS@orcl_11g> SHOW ERRORS
No errors.
HMS@orcl_11g> CREATE PUBLIC SYNONYM hms_proc FOR hms.hms_proc
2 /

Synonym created.

HMS@orcl_11g> -- 7. grant privileges to hms_users role:
HMS@orcl_11g> GRANT EXECUTE ON hms_proc TO hms_users
2 /

Grant succeeded.

HMS@orcl_11g> -- test that user a can execute the hms_proc:
HMS@orcl_11g> CONNECT a/a
Connected.
A@orcl_11g>
A@orcl_11g> EXECUTE hms_proc
X

PL/SQL procedure successfully completed.

A@orcl_11g> spool off
Re: Create User (merged 4) [message #377837 is a reply to message #377737] Thu, 25 December 2008 20:55 Go to previous messageGo to next message
mkhalil
Messages: 108
Registered: July 2006
Location: NWFP Peshawar Pakistan
Senior Member
Would you please like a little bit further to define the statement specially QUOTA 100m ON users
ALTER USER hms QUOTA 100m ON users
Re: Create User (merged 4) [message #377840 is a reply to message #377837] Thu, 25 December 2008 21:13 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
You need to start learning to search the online documentation for these things yourself. You can find answers to such simple questions faster than posting a question and waiting for a reply. The following link has a menu that you can use for searching:

http://www.oracle.com/pls/db111/homepage

so you can find examples with accompanying explanation like this:

http://download.oracle.com/docs/cd/B28359_01/network.111/b28531/users.htm#DBSEG10220

I used alter user to assign the quota on the tablespace, but it could have been done when the user was created. The 100m was an arbitrary amount, just for demonstration. The point is to avoid granting an unlimited quota or a role like resource which includes an unlimited quota and other privileges. Only grant what is needed.

Re: Create User (merged 4) [message #377915 is a reply to message #377840] Fri, 26 December 2008 04:31 Go to previous messageGo to next message
mkhalil
Messages: 108
Registered: July 2006
Location: NWFP Peshawar Pakistan
Senior Member
Thank you Barbara. After reading out and going through the guidance and links provided by you and Mr. Michel Cadot, I have decided to create a user and grant him the system and object privileges which he need not depending upon connect,resouce, dba etc.

I have written a script for this purpose. But when i execute/run that script by connecting sys or system user it gives me the following errors.
SQL> connect sys/oadmin
Connected.
SQL> CREATE USER hms IDENTIFIED BY hms DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp PROFILE DEFAULT ACCOUNT UNLOCK;

User created.

SQL> GRANT CREATE SESSION TO hms;

Grant succeeded.

SQL> GRANT SELECT ANY DICTIONARY TO hms;
GRANT SELECT ANY DICTIONARY TO hms
      *
ERROR at line 1:
ORA-00990: missing or invalid privilege


SQL> GRANT ANY PRIVILEGE TO hms;
GRANT ANY PRIVILEGE TO hms
      *
ERROR at line 1:
ORA-00990: missing or invalid privilege


SQL> GRANT ANY OBJECT PRIVILEGE TO hms;
GRANT ANY OBJECT PRIVILEGE TO hms
      *
ERROR at line 1:
ORA-00990: missing or invalid privilege


SQL> GRANT CREATE TABLE TO hms;

Grant succeeded.

SQL> GRANT CREATE SEQUENCE TO hms;

Grant succeeded.

SQL> GRANT CREATE VIEW TO hms;

Grant succeeded.

SQL> GRANT CREATE PROCEDURE TO hms;

Grant succeeded.

SQL> GRANT CREATE TRIGGER TO hms;

Grant succeeded.

SQL> GRANT CREATE USER TO hms;

Grant succeeded.

SQL> GRANT ALTER USER TO hms;

Grant succeeded.

SQL> GRANT CREATE synonym TO hms;

Grant succeeded.

SQL> GRANT CREATE ROLE TO hms;

Grant succeeded.

SQL> GRANT ANY ROLE TO hms;
GRANT ANY ROLE TO hms
      *
ERROR at line 1:
ORA-00990: missing or invalid privilege


SQL> ALTER USER hms QUOTA UNLIMITED ON users;

User altered.

1. Would you please guide me to correct these errors. Why we could not grant these privileges to a user whereas i think the sys user has the privilege to grant these privilege to any user. I think there is no any syntax error in the statements or may be anything else.
2. When we successfully grant the above privilege to hms user then weather hms user will capable to grant SELECT ANY TABLE,SELECT ANY SEQUENCE etc to another user which we create from hms user?
Re: Create User (merged 4) [message #377919 is a reply to message #377915] Fri, 26 December 2008 05:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your errors comes from:

1/ You have a 8i or below version, you should try to work with at least 10.2 version

2/ Some of the privileges you mentioned are erroneous:
GRANT ANY PRIVILEGE TO hms;
GRANT ANY OBJECT PRIVILEGE TO hms;
should be
GRANT GRANT ANY PRIVILEGE TO hms;
GRANT GRANT ANY OBJECT PRIVILEGE TO hms;
Re: Create User (merged 4) [message #377954 is a reply to message #377919] Fri, 26 December 2008 21:03 Go to previous messageGo to next message
mkhalil
Messages: 108
Registered: July 2006
Location: NWFP Peshawar Pakistan
Senior Member
Now my problem have been solved. I am very much thankfull to Mr.Michel Cadot,Mr.Barbara Boehmer,Mr.joy_division,Mr.Littlefoot,Orafaq Website and appologize from all members who mind my mistakes.

I am posting my complete script for those like me can easily solve their problems. I am using Oracle 8i and Developer 6.
--Schema/User
--hms Schema
--connect system/manager;

CREATE USER hms IDENTIFIED BY hms DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp PROFILE DEFAULT ACCOUNT UNLOCK;
GRANT CREATE SESSION TO hms;
GRANT GRANT ANY PRIVILEGE TO hms;
GRANT CREATE TABLE TO hms;
GRANT CREATE SEQUENCE TO hms;
GRANT CREATE VIEW TO hms;
GRANT CREATE PROCEDURE TO hms;
GRANT CREATE TRIGGER TO hms;
GRANT CREATE USER TO hms;
GRANT ALTER USER TO hms;
GRANT CREATE synonym TO hms;
GRANT CREATE ROLE TO hms;
GRANT GRANT ANY ROLE TO hms;
ALTER USER hms QUOTA UNLIMITED ON users;

--hms Role
CREATE ROLE hms_users;
GRANT CREATE SESSION TO hms_users;
GRANT SELECT ANY TABLE,SELECT ANY SEQUENCE,INSERT ANY TABLE,UPDATE ANY TABLE TO hms_users;
GRANT DELETE ON guest to hms_users;
GRANT DELETE ON guestothchg to hms_users;
GRANT DELETE ON receipt to hms_users;

--Receptionist Schema
CREATE USER rcpnst IDENTIFIED BY rcpnst DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp PROFILE DEFAULT ACCOUNT UNLOCK;
GRANT hms_users TO rcpnst;
ALTER USER hms QUOTA 500K ON users;
Re: Create User (merged 4) [message #377956 is a reply to message #377954] Fri, 26 December 2008 21:27 Go to previous message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
Although you may have gotten your code to run without errors, it is not a good example to follow. Since you have granted insert any table and update any table to the hms_users role and granted the hms_users role to rcpnst, then rcpsnt can insert or update any table in any schema, including adding rows to your system tables, like dual, and crashing your whole system. You have still granted way too many privileges. Just grant the minimum needed to hms, then only grant execution on the hms procedures to hms_users. Hms_users doesn't need privileges to insert and update. When users with the hms_users role execute the procedure, the procedure will execute with the privileges of hms. So, it is only hms that needs the privileges. Hms can access the tables in its own schema without special privileges. If you are not using procedures and need the rcpsnt to be able to insert and update directly, then grant insert and update on the specific tables in the hms schema only. When you use "any" it means in any schema.


Previous Topic: Cursors
Next Topic: Better than GOTO
Goto Forum:
  


Current Time: Wed Dec 07 02:40:57 CST 2016

Total time taken to generate the page: 0.06845 seconds