Granting SELECT Access to Your Tables

If you have created a table in your own personal schema in an Oracle database, others do not have permissions to access that object. You may at some point wish to allow SELECT access to another user. To do this in SAS, you can use PROC DBLOAD as follows:

proc dbload dbms=odbc;
user=’yourusername‘;
password=’password‘;
dsn=database; /* this is the name of your ODBC Connection to the database. */
sql grant select on yourschema.yourtablename to anotherusername;
run;

Or you can use explicit pass-through syntax to grant access:

proc sql;
connect to oracle (user=yourusername password=password);
execute (grant select on yourusername.yourtablename
to anotherusername) by oracle;
disconnect from oracle;
quit;

Finally, you can do this directly in the Oracle database itself just by executing the following statement which is passed through in the syntax above:

grant select on yourusername.yourtablename to anotherusername;