Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Am I assigning roles and granting permissions correctly here?

Posted on 2007-11-23
14
Medium Priority
?
1,603 Views
Last Modified: 2013-12-18
I am attempting to create users, create/assign roles, and grant permissions.  Unfortunately, I do not have permission to create a user.  Can someone take a look at this and let me know if it is correct?  Thank you!
create user S_Miller identified externally;
create user F_Hoyle identified by password;
 
create role ADMISSIONS_CLERK;
create role BILLING_CLERK;
 
grant create session to ADMISSIONS_CLERK, BILLING_CLERK;
 
grant all 
on PATIENT_DEMOGRAPHIC, PATIENT_ADMISSION, INSURANCE_COMPANY, PATIENT_INSURANCE, WARD_INFORMATION, PATIENT_LOCATION 
to ADMISSIONS_CLERK;
 
grant select (Charge_Code)
on PATIENT_CHARGES 
to ADMISSIONS_CLERK;
 
 
grant all (Charge_Code)
on PATIENT_CHARGES
to BILLING_CLERK;
 
grant select 
on PATIENT_DEMOGRAPHIC, PATIENT_ADMISSION, INSURANCE_COMPANY, PATIENT_INSURANCE, WARD_INFORMATION, PATIENT_LOCATION 
to BILLING_CLERK;
 
 
alter user S_Miller
default role BILLING_CLERK;
 
alter user F_Hoyle
default role ADMISSIONS_CLERK;
 
set role BILLING_CLERK;
set role ADMISSIONS_CLERK;

Open in new window

0
Comment
Question by:CharleneS77
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
  • +2
14 Comments
 

Author Comment

by:CharleneS77
ID: 20341496
Correction to:

grant select (Charge_Code)
on PATIENT_CHARGES
to ADMISSIONS_CLERK;
 
grant all (Charge_Code)
on PATIENT_CHARGES
to BILLING_CLERK;

grant select
on CHARGE_CODE, PATIENT_CHARGES 
to ADMISSIONS_CLERK;
 
grant all
on CHARGE_CODE, PATIENT_CHARGES
to BILLING_CLERK;

Open in new window

0
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 20341793
Seems ok to me. Did you try it? Did it work?
0
 
LVL 27

Accepted Solution

by:
sujith80 earned 400 total points
ID: 20341797
What is the error?

As which user you are performing these actions?

You should first connect to a user who has DBA role/ Who has sufficient privileges to run the above script.

For example you may connect as user SYSTEM and run the script , but you should qualify the object names with the owner names in the scirpt. I.e. make changes like.

grant all
on <owner>.CHARGE_CODE, <owner>.PATIENT_CHARGES
to BILLING_CLERK;
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 18

Assisted Solution

by:Jinesh Kamdar
Jinesh Kamdar earned 800 total points
ID: 20341800
NO, wait!

>> grant select on PATIENT_DEMOGRAPHIC, PATIENT_ADMISSION, INSURANCE_COMPANY, PATIENT_INSURANCE, WARD_INFORMATION, PATIENT_LOCATION to BILLING_CLERK;

The above statement is invalid.

You can include just a single object name following the TO in any GRANT statement. Split them up into individual GRANTs and then you shoulld be fine.
0
 

Author Comment

by:CharleneS77
ID: 20343908

>>>>What is the error?

create user S_Miller identified externally
*

ERROR at line 1:
ORA-01031: insufficient privileges

>>>>For example you may connect as user SYSTEM and run the script , but you should qualify the object names with the owner names in the scirpt.

I don't think I have the privilege to login as SYSTEM or SYS.  Is there any other way I can test my SQL statements?  Any online or downloadable tools or utilities?

>>>>You can include just a single object name following the TO in any GRANT statement. Split them up into individual GRANTs and then you shoulld be fine.

I only have BILLING_CLERK "following" the TO in the GRANT statement.  Do you mean "prior to" the TO in the GRANT statement?
0
 

Author Comment

by:CharleneS77
ID: 20343976
Here is what I have changed it to:

create user S_Miller identified externally;
create user F_Hoyle identified by password;

create role ADMISSIONS_CLERK;
create role BILLING_CLERK;

grant create session to ADMISSIONS_CLERK;
grant create session to BILLING_CLERK;

grant all
on PATIENT_DEMOGRAPHIC
to F_Hoyle.ADMISSIONS_CLERK;

grant all
on PATIENT_ADMISSION
to F_Hoyle.ADMISSIONS_CLERK;

grant all
on INSURANCE_COMPANY
to F_Hoyle.ADMISSIONS_CLERK;

grant all
on PATIENT_INSURANCE
to F_Hoyle.ADMISSIONS_CLERK;

grant all
on WARD_INFORMATION
to F_Hoyle.ADMISSIONS_CLERK;

grant all
on PATIENT_LOCATION
to F_Hoyle.ADMISSIONS_CLERK;

grant select
on CHARGE_CODE
to F_Hoyle.ADMISSIONS_CLERK;

grant select
on PATIENT_CHARGES
to F_Hoyle.ADMISSIONS_CLERK;

grant all
on CHARGE_CODE
to S_Miller.BILLING_CLERK;

grant all
on PATIENT_CHARGES
to S_Miller.BILLING_CLERK;

grant select
on PATIENT_DEMOGRAPHIC
to S_Miller.BILLING_CLERK;

grant select
on PATIENT_ADMISSION
to S_Miller.BILLING_CLERK;

grant select
on INSURANCE_COMPANY
to S_Miller.BILLING_CLERK;

grant select
on PATIENT_INSURANCE
to S_Miller.BILLING_CLERK;

grant select
on WARD_INFORMATION
to S_Miller.BILLING_CLERK;

grant select
on PATIENT_LOCATION
to S_Miller.BILLING_CLERK;

alter user S_Miller
default role BILLING_CLERK;

alter user F_Hoyle
default role ADMISSIONS_CLERK;

set role BILLING_CLERK;
set role ADMISSIONS_CLERK;


My errors are below:
create user S_Miller identified externally
*
ERROR at line 1: 
ORA-01031: insufficient privileges 
 
 
create user F_Hoyle identified by password
                                  *
ERROR at line 1: 
ORA-01031: insufficient privileges 
 
 
create role ADMISSIONS_CLERK
*
ERROR at line 1: 
ORA-01031: insufficient privileges 
 
 
create role BILLING_CLERK
*
ERROR at line 1: 
ORA-01031: insufficient privileges 
 
 
grant create session to ADMISSIONS_CLERK
*
ERROR at line 1: 
ORA-01031: insufficient privileges 
 
 
grant create session to BILLING_CLERK
*
ERROR at line 1: 
ORA-01031: insufficient privileges 
 
 
to F_Hoyle.ADMISSIONS_CLERK
          *
ERROR at line 3: 
ORA-00933: SQL command not properly ended 
 
 
to F_Hoyle.ADMISSIONS_CLERK
          *
ERROR at line 3: 
ORA-00933: SQL command not properly ended 
 
 
to F_Hoyle.ADMISSIONS_CLERK
          *
ERROR at line 3: 
ORA-00933: SQL command not properly ended 
 
 
to F_Hoyle.ADMISSIONS_CLERK
          *
ERROR at line 3: 
ORA-00933: SQL command not properly ended 
 
 
to F_Hoyle.ADMISSIONS_CLERK
          *
ERROR at line 3: 
ORA-00933: SQL command not properly ended 
 
 
to F_Hoyle.ADMISSIONS_CLERK
          *
ERROR at line 3: 
ORA-00933: SQL command not properly ended 
 
 
to F_Hoyle.ADMISSIONS_CLERK
          *
ERROR at line 3: 
ORA-00933: SQL command not properly ended 
 
 
to F_Hoyle.ADMISSIONS_CLERK
          *
ERROR at line 3: 
ORA-00933: SQL command not properly ended 
 
 
to S_Miller.BILLING_CLERK
           *
ERROR at line 3: 
ORA-00933: SQL command not properly ended 
 
 
to S_Miller.BILLING_CLERK
           *
ERROR at line 3: 
ORA-00933: SQL command not properly ended 
 
 
to S_Miller.BILLING_CLERK
           *
ERROR at line 3: 
ORA-00933: SQL command not properly ended 
 
 
to S_Miller.BILLING_CLERK
           *
ERROR at line 3: 
ORA-00933: SQL command not properly ended 
 
 
to S_Miller.BILLING_CLERK
           *
ERROR at line 3: 
ORA-00933: SQL command not properly ended 
 
 
to S_Miller.BILLING_CLERK
           *
ERROR at line 3: 
ORA-00933: SQL command not properly ended 
 
 
to S_Miller.BILLING_CLERK
           *
ERROR at line 3: 
ORA-00933: SQL command not properly ended 
 
 
to S_Miller.BILLING_CLERK
           *
ERROR at line 3: 
ORA-00933: SQL command not properly ended 
 
 
alter user S_Miller
*
ERROR at line 1: 
ORA-01031: insufficient privileges 
 
 
alter user F_Hoyle
*
ERROR at line 1: 
ORA-01031: insufficient privileges 
 
 
set role BILLING_CLERK
         *
ERROR at line 1: 
ORA-01919: role 'BILLING_CLERK' does not exist 
 
 
set role ADMISSIONS_CLERK
         *
ERROR at line 1: 
ORA-01919: role 'ADMISSIONS_CLERK' does not exist

Open in new window

0
 
LVL 28

Assisted Solution

by:Naveen Kumar
Naveen Kumar earned 800 total points
ID: 20344961
1) you have to run those statements from an oracle user who has DBA privileges ( only users with dba privileges or users who have that privilege can create users )

2)
grant select
on WARD_INFORMATION
to S_Miller.BILLING_CLERK;   -> s_miller.billing_clerk can be changed as billing_clerk

so modified one should be as shown below :

grant select
on scott.WARD_INFORMATION --> instead of scott put the owner of the table ward_information
to BILLING_CLERK;

Thanks
0
 

Author Comment

by:CharleneS77
ID: 20347668
I understand I am not able to create users without DBA privileges.  So I will ignore the "insufficient privileges" errors and the invalid or missing privilege errors


The other part was my mistake.  I changed it to:


grant select
on F_Hoyle.CHARGE_CODE, F_Hoyle.PATIENT_CHARGES
to ADMISSIONS_CLERK;

grant all
on F_Hoyle.PATIENT_DEMOGRAPHIC, F_Hoyle.PATIENT_ADMISSION, F_Hoyle.INSURANCE_COMPANY, F_Hoyle.PATIENT_INSURANCE, F_Hoyle.WARD_INFORMATION, F_Hoyle.PATIENT_LOCATION
to ADMISSIONS_CLERK;

grant select
on S_Miller.PATIENT_DEMOGRAPHIC, S_Miller.PATIENT_ADMISSION, S_Miller.INSURANCE_COMPANY, S_Miller.PATIENT_INSURANCE, S_Miller.WARD_INFORMATION, S_Miller.PATIENT_LOCATION
to BILLING_CLERK;

grant all
on S_Miller.CHARGE_CODE, S_Miller.PATIENT_CHARGES
to BILLING_CLERK;


The last question I have about this is:  Why do I get different errors for the first and the last statement?   I do not understand what is causing the "missing keyword" errors. See below.



on F_Hoyle.CHARGE_CODE, F_Hoyle.PATIENT_CHARGES
                      *
 
ERROR at line 2: 
ORA-00905: missing keyword 
 
 
on F_Hoyle.PATIENT_DEMOGRAPHIC, F_Hoyle.PATIENT_ADMISSION, F_Hoyle.INSURANCE_COMPANY, F_Hoyle.PATIENT_INSURANCE, F_Hoyle.WARD_INFORMATION, F_Hoyle.PATIENT_LOCATION
                              *
 
ERROR at line 2: 
ORA-00990: missing or invalid privilege 
 
 
on S_Miller.PATIENT_DEMOGRAPHIC, S_Miller.PATIENT_ADMISSION, S_Miller.INSURANCE_COMPANY, S_Miller.PATIENT_INSURANCE, S_Miller.WARD_INFORMATION, S_Miller.PATIENT_LOCATION
                               *
 
ERROR at line 2: 
ORA-00990: missing or invalid privilege 
 
 
on S_Miller.CHARGE_CODE, S_Miller.PATIENT_CHARGES
                       *
 
ERROR at line 2: 
ORA-00905: missing keyword 

Open in new window

0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 20348108
This is a bit weird but i do get the same behaviour. May be this a bug which
is existing and nobody cares to fix this in the oracle database because
it is not a real bug at all ( just gives incorrect error messages depending
on the number of objects after the ON keyword in the grant command ).

Actaully speaking, there are even a lot of places where we can get to
see these type of incorrect error messages.

1) GRANT SELECT
ON F_Hoyle.CHARGE_CODE, F_Hoyle.PATIENT_CHARGES
TO ADMISSIONS_CLERK;

This is giving "ORA-00905: missing keyword"

2)
GRANT SELECT
ON F_Hoyle.CHARGE_CODE, F_Hoyle.PATIENT_CHARGES, f_hoyle.abc
TO ADMISSIONS_CLERK;

the below one gives "ORA-00990: missing or invalid privilege".

Thanks
0
 
LVL 18

Assisted Solution

by:Jinesh Kamdar
Jinesh Kamdar earned 800 total points
ID: 20348898
I do agree with nav about the errors being a bit wierd (check out the code snippet).

However, the right syntax for a GRANT is to include only a single object-name following the "ON" keyword.

Granting Object Privileges

GRANT { object_privilege [(column_list)] [, object_privilege [(column_list)] ]...
|ALL [PRIVILEGES]}
ON [schema.]object
TO {user|role|PUBLIC}[, {user|role|PUBLIC} ]...[WITH GRANT OPTION]
SQL> GRANT SELECT ON dual TO jkamdar;
 
Grant succeeded.
 
SQL> GRANT SELECT ON dual, dual TO jkamdar;
GRANT SELECT ON dual, dual TO jkamdar
                    *
ERROR at line 1:
ORA-00905: missing keyword
 
SQL> GRANT SELECT ON dual, dual, dual TO jkamdar;
GRANT SELECT ON dual, dual, dual TO jkamdar
                    *
ERROR at line 1:
ORA-00990: missing or invalid privilege
 
SQL> GRANT SELECT ON dual, dual, dual, dual TO jkamdar;
GRANT SELECT ON dual, dual, dual, dual TO jkamdar
                    *
ERROR at line 1:
ORA-00990: missing or invalid privilege
 
SQL> 

Open in new window

0
 
LVL 1

Expert Comment

by:sthompsonaap
ID: 20358010
It looks as for 1, you are not prefixing the tables with the correct schemas (unless f_hoyle and s_miller both own tables named patient_demographic).  You should prefix them with the correct user which can be identified by doing
select owner, table_name from dba_tables where table_name in ('PATIENT_DEMOGRAPHICS', 'PATIENT_ADMISSION', 'INSURANCE_COMPANY', 'PATIENT_INSURANCE', 'WARD_INFORMATION', 'PATIENT_LOCATION');
If you do not have access to dba_tables but you can try all_tables (granted you have permssions to the table itself).
You want to prefix it with the actual table owner.
grant select on owner.table_name to ADMISSIONS_CLERK, BILLING_CLERK; should work fine.
As far as I am aware, you cannot do multiple object level permissions like that, but you can grant them to multiple users.
So why are you getting missing keyword?  It's because it's not expecting a comma there, it's look for "TO".
0
 

Author Comment

by:CharleneS77
ID: 20378758
>>>>However, the right syntax for a GRANT is to include only a single object-name following the "ON" keyword.

That is correct.  I am no longer getting the missing keyword error.  Thank you!


>>>>GRANT SELECT ON dual TO jkamdar;

So does this mean I do not need to prefix each table with the user?

grant select on F_Hoyle.CHARGE_CODE to ADMISSIONS_CLERK;
grant select on CHARGE_CODE to ADMISSIONS_CLERK;
grant select on CHARGE_CODE to F_Hoyle;


>>>>You should prefix them with the correct user which can be identified by doing
select owner, table_name from dba_tables where table_name in ('PATIENT_DEMOGRAPHICS', 'PATIENT_ADMISSION', 'INSURANCE_COMPANY', 'PATIENT_INSURANCE', 'WARD_INFORMATION', 'PATIENT_LOCATION');

Are you saying to do this...

grant select
on select owner, table_name from dba_tables
where table_name
in ('PATIENT_DEMOGRAPHICS', 'PATIENT_ADMISSION', 'INSURANCE_COMPANY', 'PATIENT_INSURANCE', 'WARD_INFORMATION', 'PATIENT_LOCATION')
.INSURANCE_COMPANY
to BILLING_CLERK;

...instead of this?

grant select
on S_Miller.INSURANCE_COMPANY
to BILLING_CLERK;




0
 
LVL 28

Assisted Solution

by:Naveen Kumar
Naveen Kumar earned 800 total points
ID: 20379185
No....

it should be in the form :

grant select
on S_Miller.INSURANCE_COMPANY
to BILLING_CLERK;

You cannot put sql select statements in the grant command.

Thanks
0
 
LVL 1

Expert Comment

by:sthompsonaap
ID: 20381613
The select "select owner, table_name from dba_tables where table_name .." was to determine that actual owner of the table.
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question