CODEWITHSUNDEEP
sasdb2sql-grantproc-sql
krushna ratnaparkhi
krushna ratnaparkhi

Reputation: 37

How to grant privileges on DB2 table create by data step in SAS?

I have connected to the DB2 database using libname statement and created a table using DATA Step as follows:

 libname db2lib   db2   user=user pw=pwd database=dbtest schema=schema1;


 data db2lib.test_table;
   var1 = "1234";
 run;

Table got created successfully but nobody else able to run the SELECT query on it.

I know how to grant permissions to the table using PROC SQL execute but is there any way to GRANT privileges as I am connecting to DB2 using LIBNAME.

Upvotes: 0

Views: 941

Answers (1)

Richard
Richard

Reputation: 27498

I am unaware of any DB2 CREATE TABLE options that grant permissions.

However, if there are any, the data set option DBCREATE_TABLE_OPTS= could be specified in your SAS code:

DBCREATE_TABLE_OPTS= Data Set Option
Specifies DBMS-specific syntax to add to the end of the CREATE TABLE statement.

Details
You can use this option to add DBMS-specific clauses at the end of the SQL CREATE TABLE statement. The SAS/ACCESS engine passes the SQL CREATE TABLE statement and its clauses to the DBMS. The DBMS then executes the statement and creates the DBMS table. This option applies only when you are creating a DBMS table by specifying a libref associated with DBMS data.

As you know, the GRANT statement can be issued through The SQL Procedure EXECUTE statement.

Did you know CONNECT can use the existing LIBNAME ?

Proc SQL;
  connect using DB2LIB;
  execute (
    GRANT … 
  ) by DB2LIB;

Upvotes: 2

Related Questions