Grant one or more roles to one or more users using shell script that calls bteq

Database

Grant one or more roles to one or more users using shell script that calls bteq

Hi Folks,

I have the following piece of code for granting 1 or more roles to 1 or more users. The code(function) below works for 1 user but I find it difficult to get it working when specifyin multiple users as each user has to be within double quotes "" and separated by a comma. Any help will be much appreciated.

grantaccess() {
echo "Enter Teradata Rolename(s) to be updated:"
read TDROLE
echo "Enter Teradata Username(s) to be added to Role(s):"
read TDUSER
echo "Issueng Grant statement..."
bteq <
.run file=mylogon;
GRANT ${TDROLE} TO "${TDUSER}";
.IF ERRORCODE <> 0 THEN .QUIT 101 else .QUIT 0
EOF
}

Basically, when I run the script, it prompts me to enter rolename(s) and username(s). And as the GRANT command allows you to specify more than 1 user on the same command (i.e. grant to "user1", "user2", "user3";), how can I change the above function to achieve that? The number of users that can be passed to the script can vary, it cud be 1,3 or 10 users and want the script(function) to be able to handle that.
NB! I shud only b prompted once to enter role(s)/user(s) but the grant statement be able to accept the number of roles/users that I entered at the prompt command.

When I run the script it, it comes with a menu like below:

1. Grant Access
2. Revoke Access
3. Exit

Enter your choice: 1
Enter Teradata Rolename(s) to be updated:
role1
Enter Teradata Username(s) to be added to Role(s):
user1 user2

And the ultimate statement being run via bteq will be...grant role1 to "user1", "user2";
Any form of help will be much appreciated.

Thanks in advance,
Plentyfish
3 REPLIES
N/A

Re: Grant one or more roles to one or more users using shell script that calls bteq

What happens with your function when you just input arguments like your example at the bottom? Does it fail? Does it only grant to the first user?

Re: Grant one or more roles to one or more users using shell script that calls bteq

It interprets it as one input, i.e. "user1 user2". But entering just user1 works.
N/A

Re: Grant one or more roles to one or more users using shell script that calls bteq

OK. Then you'll need to parse the string. I'd look at the sed and/or awk utilities. It should be available on most UNIX servers and I think you can get a port of it for Windows, if that's your environment. It should allow you to easily split the input into multiple tokens based on whitespace. Then, you'll need to add the commas between them. Google has plenty of good examples of sed and awk jobs.