1

Topic: Help with safety how correctly to adjust?

There is a table t1:
Columns And | | a C
There is simple Function Func1 (@Param1 int) obtaining the data from this table in it one simple request of a type:
SELECT A, FROM t1 WHERE A C = Param1
How correctly to adjust the rights that the user could not read the table directly but only by means of the given function
Also there is a similar procedure
I.e. it is necessary to make to adjust the right that the user could change the data in the table only by means of procedure

2

Re: Help with safety how correctly to adjust?

Easier not to give any rights, except as on function and procedure

3

Re: Help with safety how correctly to adjust?

o-o;
And made. Took away all rights from the table used in function. Also received the following:
The SELECT permission was denied on the object ' GetOrgFromBase ', database ' Posrednik ', schema ' dbo '.

4

Re: Help with safety how correctly to adjust?

Lamazoid wrote:

o-o;
And made. Took away all rights from the table used in function. Also received the following:
The SELECT permission was denied on the object ' GetOrgFromBase ', database ' Posrednik ', schema ' dbo '.

It would be desirable, that SELECt t1.A from Tbl1 it was accessible;
And GetOrgFromBase which as a matter of fact also is SELECt t1.A from Tbl1 (simply with some checks and restrictions) worked

5

Re: Help with safety how correctly to adjust?

At function the circuit too dbo?
And in any other basis does not climb?

6

Re: Help with safety how correctly to adjust?

Was mistaken in before the message certainly SELEC t1.a FROM TBL1 t1 - It should not be accessible

7

Re: Help with safety how correctly to adjust?

o-o wrote:

at function the circuit too dbo?
And in any other basis does not climb?

Yes. Basis unpretentious. 2 tables 2 of function and 2 procedures. Any indirect bases, circuits ..

8

Re: Help with safety how correctly to adjust?

Once again, all objects used in function;
In the same circuit are, as function?
Whether there are objects of other basis?

9

Re: Help with safety how correctly to adjust?

Then  someone changed pens.
We search that write about  functions and about  it here GetOrgFromBase.
I.e. show result of request for function and for GetOrgFromBase

select name, principal_id, schema_id
from sys.objects
where name = ' GetOrgFromBase'

10

Re: Help with safety how correctly to adjust?

o-o wrote:

then  someone changed pens.
We search that write about  functions and about  it here GetOrgFromBase.
I.e. show result of request for function and for GetOrgFromBase

select name, principal_id, schema_id
from sys.objects
where name = ' GetOrgFromBase'

Returns 0 records

11

Re: Help with safety how correctly to adjust?

Well means nevertheless not in that basis look (it is necessary in basis Posrednik).
The same your name from your error:

wrote:

The SELECT permission was denied on the object ' GetOrgFromBase ', database ' Posrednik ', schema ' dbo '.

12

Re: Help with safety how correctly to adjust?

o-o wrote:

well means nevertheless not in that basis look (it is necessary in basis Posrednik).
The same your name from your error:
it is passed...

I look in that basis.

13

Re: Help with safety how correctly to adjust?

Why not to name at all all functions and tables their own names?
Itself and associates tangle.
"At me 1, and an error I receive about GetOrgFromBase".
Also what I should think, what 1 and is GetOrgFromBase?
Or what 1 it I twist or function, and there inside still any GetOrgFromBase?

14

Re: Help with safety how correctly to adjust?

Lamazoid wrote:

it is passed...
I look in that basis.

Forgive. Looked from under the restricted user.
From under the administrator: here
GetOrgFromBase NULL 1

15

Re: Help with safety how correctly to adjust?

Lamazoid wrote:

it is passed...
I look in that basis.

I.e. the object such is not present, but the error about it is?
And you generally who, ob_owner in e or the user deprived of civil rights?
And that to see normal that, on what at you the rights are not present

16

Re: Help with safety how correctly to adjust?

o-o wrote:

it is passed...
I.e. the object such is not present, but the error about it is?
And you generally who, ob_owner in e or the user deprived of civil rights?
And that to see normal that, on what at you the rights are not present

In last the message unsubscribed. Under the user deprived of civil rights -
Just it is visible nothing
And under the administrator
GetOrgFromBase NULL 1

17

Re: Help with safety how correctly to adjust?

Lamazoid wrote:

From under the administrator: here
GetOrgFromBase NULL 1

Well and where for function?
You did not write function name, substitute already him in the code;
I have no concept, as function is called

18

Re: Help with safety how correctly to adjust?

Lamazoid wrote:

In last the message unsubscribed. Under the user deprived of civil rights -

I of type on operation.
I read this topic, I type the answer, me pull;
I am returned, I add the answer, I send the message.
Well and so it turns out that you already answered, and I still do not know about it

19

Re: Help with safety how correctly to adjust?

o-o wrote:

it is passed...
Well and where for function?
You did not write function name, substitute already him in the code;
I have no concept, as function is called

Here the code launched from - under the user:
set @UID = ' c5490a38-5f25-409a-8ad2-3175c63eef76'
select t1.* from dbo. GetOrgFromBase (@UID, ' 852456 ', 3) t1
Here Function:
ALTER FUNCTION [dbo]. [GetOrgFromBase]
(
@UserId [uniqueidentifier], @UserPassword varchar (10), @OrgCode int =0
)
RETURNS TABLE
AS
RETURN
(
select OrgCode, OrgName, LSMask FROM dbo. Organisations t1
Where t1.OrgCode=Case when @OrgCode <> 0 then @OrgCode Else t1.OrgCode End And (dbo. CheckUserAccess (@UserId, @UserPassword) =1)
)
Function used in previous :
ALTER FUNCTION [dbo]. [CheckUserAccess]
(
@UserID [uniqueidentifier], @UserPassword varchar (10)
)
RETURNS Bit
AS
BEGIN
declare @Pwd as varbinary (128)
select @Pwd=t1.UserPassword FROm dbo. ProgrammUsers t1 WHERE UserID = UserID
RETURN pwdcompare (@UserPassword ,@Pwd)
END
Any function does not fulfill.

20

Re: Help with safety how correctly to adjust?

o-o wrote:

it is passed...
Well and where for function?
You did not write function name, substitute already him in the code;
I have no concept, as function is called

it will be clarified, as for function the same.
And then I again will ask:
What objects at you in function?
1  also is GetOrgFromBase;
Or not?
Because time a possession chain is broken, it is necessary to search, where.
And if it GetOrgFromBase in any I twist;
That and for I twist it is necessary to check up the circuit and .
More shortly, all objects participating in your function are necessary to me;
With all intermediate I twist/functions;
With the  and circuits.
I.e. , in one place collect all and write in a type
name, principal_id, schema_id.
We search, at whom not dbo

21

Re: Help with safety how correctly to adjust?

Here for these here all also it is necessary to me :
[dbo]. [GetOrgFromBase]
[dbo]. [CheckUserAccess]
dbo. Organisations
dbo. ProgrammUsers

select name, principal_id, schema_id
from sys.objects
where object_id in (select object_id (' dbo. GetOrgFromBase ')
union all select object_id (' [dbo]. [CheckUserAccess] ')
union all select object_id (' dbo. Organisations')
union all select object_id (' dbo. ProgrammUsers')
)

22

Re: Help with safety how correctly to adjust?

o-o wrote:

it is passed...
it will be clarified, as for function the same.
And then I again will ask:
What objects at you in function?
1  also is GetOrgFromBase;
Or not?
Because time a possession chain is broken, it is necessary to search, where.
And if it GetOrgFromBase in any I twist;
That and for I twist it is necessary to check up the circuit and .
More shortly, all objects participating in your function are necessary to me;
With all intermediate I twist/functions;
With the  and circuits.
I.e. , in one place collect all and write in a type
name, principal_id, schema_id.
We search, at whom not dbo

Result of performance of request:
select name, principal_id, schema_id
from sys.objects
where name In (' Organisations','ProgrammUsers','GetOrgFromBase ', ' CheckUserAccess')
name principal_id schema_id
Organisations NULL 1
GetOrgFromBase NULL 1
CheckUserAccess NULL 1
ProgrammUsers NULL 1
Where ProgrammUsers:

USE [Posrednik]
GO
/ ****** Object: Table [dbo]. [ProgrammUsers] Script Date: 10/05/2017 2:31:28 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo]. [ProgrammUsers] (
[UserID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_ProgramUsers_UserID] DEFAULT (newid ());
[UserPhone] [varchar] (12) NOT NULL;
[UserPassword] [varbinary] (128) NOT NULL;
[UserMessage] [varchar] (300) NULL;
[IsActiv] [bit] NOT NULL CONSTRAINT [DF_ProgramUsers_IsActiv] DEFAULT ((0));
CONSTRAINT [PK_ProgramUsers] PRIMARY KEY CLUSTERED
(
[UserID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF

Organisation:

USE [Posrednik]
GO
/ ****** Object: Table [dbo]. [Organisations] Script Date: 10/05/2017 2:32:40 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo]. [Organisations] (
[OrgCode] [int] IDENTITY (1,1) NOT NULL;
[OrgName] [varchar] (250) NOT NULL;
[LSMask] [varchar] (20) NULL;
CONSTRAINT [PK_Organisations] PRIMARY KEY CLUSTERED
(
[OrgCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF

And I uncovered functions by a post above

23

Re: Help with safety how correctly to adjust?

Wait;
So at you the error that is not present  on function , instead of that inside!!!

wrote:

The SELECT permission was denied on the object ' GetOrgFromBase '

select t1.* from dbo. GetOrgFromBase (@UID, ' 852456 ', 3) t1
ALTER FUNCTION [dbo]. [GetOrgFromBase]

So it is necessary to give on function , it on internal tables is not necessary.
At you an error about function, instead of about its internal objects

24

Re: Help with safety how correctly to adjust?

Lamazoid wrote:

And made. took away all rights from the table used in function . Also received the following:
The SELECT permission was denied on the object ' GetOrgFromBase ', database ' Posrednik ', schema ' dbo '.

No!
Visors of the right from the function

25

Re: Help with safety how correctly to adjust?

o-o wrote:

it is passed...
Is not present !
Visors of the right from the function

Broke off a high:-D