Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1655
  • Last Modified:

Am I assigning roles and granting permissions correctly here?

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
CharleneS77
Asked:
CharleneS77
  • 5
  • 3
  • 3
  • +2
5 Solutions
 
CharleneS77Author Commented:
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
 
Jinesh KamdarCommented:
Seems ok to me. Did you try it? Did it work?
0
 
sujith80Commented:
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
Jinesh KamdarCommented:
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
 
CharleneS77Author Commented:

>>>>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
 
CharleneS77Author Commented:
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
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
 
CharleneS77Author Commented:
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
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
 
Jinesh KamdarCommented:
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
 
sthompsonaapCommented:
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
 
CharleneS77Author Commented:
>>>>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
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
 
sthompsonaapCommented:
The select "select owner, table_name from dba_tables where table_name .." was to determine that actual owner of the table.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 5
  • 3
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now