oracle table permissions in a schema in sas smc

[vallsas]

Hi,

I have created a library oracle database schema connecting, and assigned the group which is user credentials stored. 

the requirement is in a library 50 tables are there and would like to give the users to read permissions based on the business requirement need to give permission on specific tables not the entire schema in sas management console.(9.4 m5).

Is there this kind of provision for table which are oracle schema configured as sas lib and granting user on the specific tables instead of whole library sas smc as an admin.

[SASKiwi]

If you register the Oracle tables in SAS metadata, you can choose to store only those you want to allow read permission. Those that aren't registered will not be visible or readable by users. Bear in mind though, that users can assign their own LIBNAME in code and bypass metadata permissions. If you want a more secure solution, then doing that via Oracle database permissions is the way to go.

[vallsas]

thank you.

we need to register all the tables as business user are using the EG so they need to know the what are all the tables are there to explore the data.

because some business users need to access all the tables. some of the are dont require to access all the tables in a library (which is configured in sas smc) .

I want to know how to restrict in sas smc by table wise in sas smc for some users.

as you mentions by Oracle database permissions can you share some more info if not possible in smc.

[LinusH]

In SMC you can set pretty much any permission you want, but as @SASKiwi points out, if users bypass metadata these permissions will be ignored.

This can be prevneted if you use an Oracle account that has a secret password.

But when it comes to security, you should try to protect as close too the source as possible.

So create on or more Oracle users, and set appropriate permissions for them in Oracle.

When you use this account, you will only see relevant tables when registering in SMC.

[SASKiwi]

As @LinusH says this type of requirement is best done using Oracle permissions. Talk to your Oracle DBA to set this up.

[vallsas]

If oracle permissions created(new user) for every set of tables then it will be too many users to be on each schema.(for example - each schema having 500 tables, 20 schemas).

and every time if we need to add or remove the permissions on the one user then need to ask DBA to grant or remove permissions. is it correct?

[LinusH]

You usually mangae this by using groups.

Best case sceanrio you have single-signon throughout your aplpications landscape, inclding SAS and Oracle.

This is rerely the case though.

Another options is batch AD integration.

But generally you end up in ascenario when a "permission group" will be implemented as one Oracle user.

Then in SAS, you tie a metadata group to each Oracle (group) user.

This will keep the changes to a minimum - maintain group memberships in SAS, schema/table permission in Oracle.

[Sajid01]

The exact solution depends on your local situation, but at the high level the practice is to views corresponding each set of requirements in the oracle database and assign permissions in oracle and create necessary libraries in the metadata server.
It is a collective effort of the SAS and Oracle teams.