Solved

Am I assigning roles and granting permissions correctly here?

Posted on 2007-11-23
14
1,419 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
  • 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 100 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
 
LVL 18

Assisted Solution

by:Jinesh Kamdar
Jinesh Kamdar earned 200 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 200 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 200 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 200 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

706 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now