Grant Select on a schema 1

[MSIsam]

Is there a way to grant select access to a schema without specifying the tables? I would want to grant access to a role, not user.

I think that other DBMS can do it but was just wondering if oracle can.

Thanks,
Sam (newbie)

 

[Turkbear]

Hi,
Nope..AFAIK,Permissions are on an Object by Object basis..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[SantaMufasa]

To expand on Turkbear's correct assessment, you can write a script that, in turn, writes a script to grant (to a specific ROLE, <role_name>) the accesses you wish to grant on the objects you want accessed. Then, after running the script, you GRANT <role_name> to each user you want to have access.

Does this help a bit, Sam?

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[MSIsam]

thanks SantMufasa,
I think this makes sense. Do you have an example of this? I am a bit unclear on how to go about this.

Thanks!
Sam

 

[SantaMufasa]

Sam,

Section 1 -- Code contents of script that I call "Grants.sql". (You must save this code to a script and run the script since there is an ACCEPT/PROMPT pair that a copy-and-paste-only would disrupt.):

set echo off

rem
rem This script generates a list of tables in one Oracle user that you want 
rem another user have privileges over them.
rem 
rem The output grantprivs.sql goes TO your current working directory, the
rem output will create all grants for cross-user privileges
rem

set linesize 160
set pagesize 0
set echo off
set feedback off
set verify off

accept grantee prompt 'Enter grantee: '
spool grantprivs.sql

select 'spool grantprivs.log' from dual;

rem *******************************
rem *** Grants for current user ***
rem *******************************
select 'GRANT SELECT, INSERT, UPDATE, DELETE ON ' || table_name || ' TO &grantee;'
from user_tables;

select 'spool off' from dual;
spool off

set heading on
set feedback on
set verify on
prompt
prompt When you are ready to actually grant these privileges,
prompt    issue the SQL*Plus command "@grantprivs"
prompt

Section 2 -- Sample invocation and results of "Grants.sql":

SQL> @grants
Enter grantee: yada
spool grantprivs.log
GRANT SELECT, INSERT, UPDATE, DELETE ON A TO yada;
GRANT SELECT, INSERT, UPDATE, DELETE ON ACCOUNT TO yada;
GRANT SELECT, INSERT, UPDATE, DELETE ON ADDRESS TO yada;
...
GRANT SELECT, INSERT, UPDATE, DELETE ON X_TABLE TO yada;
GRANT SELECT, INSERT, UPDATE, DELETE ON YADA2 TO yada;
GRANT SELECT, INSERT, UPDATE, DELETE ON YOURTABLE TO yada;
spool off

When you are ready to actually grant these privileges,
issue the SQL*Plus command "@grantprivs"

SQL>

Section 3 -- Proof of concept running the generated "grantprivs.sql" script for the newly created role, "YADA":

SQL> create role yada;

Role created.

SQL> @grantprivs

Grant succeeded.


Grant succeeded.

GRANT SELECT, INSERT, UPDATE, DELETE ON ADDRESS TO yada
                                        *
ERROR at line 1:
ORA-30657: operation not supported on external organized table

...

Grant succeeded.


Grant succeeded.


Grant succeeded.

SQL> grant yada to dhunt;

Grant succeeded.

Notice in the above results that we cannot grant any privileges besides SELECT on the ADDRESS table...the ADDRESS table is a table of a flat-file. Also notice that following the grants, I was able to grant all the privileges in the "grantprivs.sql" script to Oracle user, DHUNT, by simply saying, "grant yada to dhunt;".

Let us know if you have questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[MSIsam]

thanks SantaMufasa,
this should do the trick!